Skip to Main Content
Forums

Infrastructure Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle Enterprise Linux 5.0

14728Jun 16 2008 — edited Jun 26 2008
Is this a free distribution, or is support mandatory requirement?

The reason I ask is that I'd like to use it in some development virtual machines, I have installed 11g on OEL5.0 and its installed with no problems.

Is it a requirement that I purchase OEL support?

Comments

436063

Hi,

Hopefully this will solve your problem

vineet@ORCL>desc temp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(30)

vineet@ORCL>select * from temp;

A
------------------------------
5500
554
5540
5539

Elapsed: 00:00:00.04
vineet@ORCL>l
  1  select * from
  2  (
  3  select * from temp
  4  where a in (
  5             select substr(dual_q.input, 1 , in_list.rn )
  6             from (
  7                       select input , length(input) ln
  8                             from (select '&input' input from dual)
  9                  ) dual_q,
 10                     (
 11                             select row_number() over (order by 1) rn from dual
 12                             group by cube(1, 2, 3,4, 5)
 13                     ) in_list
 14             where  dual_q.ln >= in_list.rn
 15     )
 16  order by 1 desc
 17  )
 18* where rownum < 2
vineet@ORCL>/
Enter value for input: 5544
old   8:                                from (select '&input' input from dual)
new   8:                                from (select '5544' input from dual)

A
------------------------------
554

Elapsed: 00:00:00.04
vineet@ORCL>/
Enter value for input: 55397
old   8:                                from (select '&input' input from dual)
new   8:                                from (select '55397' input from dual)

A
------------------------------
5539

Elapsed: 00:00:00.03
vineet@ORCL>/
Enter value for input: 55409
old   8:                                from (select '&input' input from dual)
new   8:                                from (select '55409' input from dual)

A
------------------------------
5540

Elapsed: 00:00:00.03
vineet@ORCL>

Vineet

146850
here is the magic for you.
SELECT MAX (column_a)
	   KEEP (DENSE_RANK FIRST ORDER BY LENGTH(column_a) DESC) magic_4u
  FROM t
 WHERE column_a IN (SELECT     SUBSTR (str, 1, LEVEL)
                          FROM (SELECT :input str
                                  FROM DUAL)
                    CONNECT BY LEVEL <= LENGTH (str))
for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning
Laurent Schneider
keep it simple !
SQL> create table t as select '5500' a from dual
 union select '554' from dual 
 union select '5540' from dual 
 union select '5539' from dual;

Table created.

SQL> select max(a) a from t where '&input' like a||'%';
Enter value for input: 5544
A
----
554
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 24 2008
Added on Jun 16 2008
#generic-linux, #linux
11 comments
1,680 views