Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, paul.

Asked: October 08, 2006 - 10:24 am UTC

Last updated: December 23, 2015 - 12:29 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom - you must have writen hundreds of thousands of lines of SQL. Are there any 'clever' techniques you use that fellow professionals respond to with 'wow, I didn't realise that you could do that in SQL so easily'?

Thanks

and Connor said...

Every time I use analytics!

</code> http://asktom.oracle.com/pls/ask/search?p_string=%22rock+and+roll%22

Every time....

but this one wows me:

ops$tkyte%ORA9IR2> variable txt varchar2(25)
ops$tkyte%ORA9IR2> exec :txt := 'a,bb,ccc,d,e,f';

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> pause

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> with data
  2  as
  3  (
  4  select substr (txt,
  5                 instr (txt, ',', 1, level  ) + 1,
  6                 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
  7           as token
  8    from (select ','||:txt||',' txt from dual)
  9  connect by level <= length(:txt)-length(replace(:txt,',',''))+1
 10  )
 11  select * from data;

TOKEN
----------------------------------
a
bb
ccc
d
e
f

6 rows selected.


It won't work until you have 9ir2 and above, but pretty cool to "bind an inlist".

http://asktom.oracle.com/Misc/varying-in-lists.html <code>



Rating

  (47 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Icy

Paul, October 08, 2006 - 3:40 pm UTC

Tom - that really is so cool ...

Paul

Clever SQL

PV Lakshminarasimhan, October 09, 2006 - 1:55 am UTC

It's really great. More than that, Oracle has such GR8 people like Mr.Tom who can explore the various means and ways by using analytics, to have optimum utilization of Oracle, especially to help the community using the same.

We are lucky to have such a site (including the rss feeds).

thanks once again.

problem running this query

A reader, October 09, 2006 - 2:45 am UTC

Tom when i execute the query i get the following error

SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed

please tell me how to proceed

Tom Kyte
October 09, 2006 - 8:28 am UTC

get to 9ir2?

problem running this query

A reader, October 09, 2006 - 2:46 am UTC

Tom when i execute the query i get the following error

SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed

please tell me how to proceed

For "A reader"

Muhammad Riaz Shahid, October 09, 2006 - 3:19 am UTC

You are using old version of sql*plus, use the newer version:

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 9 11:10:15 2006

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter password: *****

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

scott@DB1> ed
Wrote file afiedt.buf

1 with aa as (select * from emp)
2* select * from aa
scott@DB1> /
SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed



SQL*Plus: Release 9.2.0.1.0 - Production on Mon Oct 9 11:12:19 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password: *****

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production


PL/SQL procedure successfully completed.

scott@CMAD> with aa as (select * from emp)
2 select * from aa;

<Result of above query here>

Tom Kyte
October 09, 2006 - 8:28 am UTC

thanks, sqlplus "pre-processes" queries - it peeks at the words you type before submitting to the server.

Here it could not figure out what with meant.

nordine, October 09, 2006 - 10:29 am UTC

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> select level 1 from dual connect by level <= 5;

     LEVEL
----------
         1
On your BLOG the same order produce this output:
SQL> select level l
2    from dual
3  connect by level <= 5;

    L
----------
    1
    2
    3
    4
    5
Is there any explanation to that?
Thanks a lot 

Tom Kyte
October 09, 2006 - 10:31 am UTC

the magic of dual and such.

Use it the way I've demonstrated in 9i (in a with or inline view) else too many things "dual returns 1 record, we can stop fetching from it already"

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1562813956388 <code>

Why "with data"?

A reader, October 09, 2006 - 12:55 pm UTC

I'm not sure I quite understand the use of "with data". The following seems to produce the same result set whether "with data" is used or not. Can you please explain the reason for using "with data"?

variable txt varchar2(25)
exec :txt := 'a,bb,ccc,d,e,f';

SELECT SUBSTR(txt,
INSTR(txt, ',', 1, level) + 1,
INSTR(txt, ',', 1, level + 1) - INSTR(txt, ',', 1, level) - 1) as token
FROM (SELECT ',' || :txt || ',' txt FROM dual)
CONNECT BY level <= LENGTH(:txt) - LENGTH(REPLACE(:txt, ',', '')) + 1;

TOKEN
----------------------------------
a
bb
ccc
d
e
f



Tom Kyte
October 09, 2006 - 1:47 pm UTC

because I am presuming you will be:


select *
from some_table
where some_column in (select * from data)


and select * from data is much easier on the eyes and brain to understand than having the actual query in there.

So, for the same reason you write small subroutines and not 5,000 line subroutines.

You solved a problem that has been bothering me for years

Vidya, October 30, 2006 - 7:49 pm UTC

Thank you so very much.

Now, I can use this to receive a comma separated list (of, say Customer IDs) from the FRONT-END application and return (the Customer details) in a CURSOR much more easily. Of course, I have to look out for performance issues, if any.

Till now, I was populating a VARRAY (requiring the declaration of two DB objects and one PLSQL variable) from the comma separated list (requiring about 30 lines of PLSQL code) and using it (by CASTing it) in the WHERE IN part of the SELECT statement.


but does this happen?

Scott Mattes, March 15, 2007 - 3:23 pm UTC

Tom,
Way WOW, but why does the output from your very first example at the top give

6 rows selected
txt
---------
a,bb,ccc,d,e,f

I ran this on 9.2.0.7.0

Thank you.
Tom Kyte
March 16, 2007 - 2:56 pm UTC

eh? cut and paste the entire example please, mine was run in 9ir2 as well. not sure what you mean.


ugh, should have previewed first

Scott Mattes, March 15, 2007 - 3:28 pm UTC

Tom,
The title of the previous post should have been "but why does this happen" and the output should have also showed the expected results

TOKEN
----------------------------------
a
bb
ccc
d
e
f

6 rows selected
txt
-------
a,bb,ccc,d,e,f

and I am asking why do the last two lines display?

Thank you
Tom Kyte
March 16, 2007 - 3:00 pm UTC

do you have autoprint on?

autoprint will print out the :bind_variables when they are referenced in SQL.


bascially you are printing the bind variable out

Wow

abdul wahab, March 17, 2007 - 7:21 am UTC

Hi Tom,
I also read this WOW query in Oracle Magazine march/april 2007 in your column. I enjoyed stidying it's logic.
But going back to the initial question of this thread.. Can you explain us how you formulated this query..i mean how do you create the logic/magic with variables. all that subtracting and dividing stuff.
Tom Kyte
March 17, 2007 - 5:20 pm UTC

just "logic", the trick was getting N rows numbered 1..N

Once you have that, just a bunch of substrings...

Dominic Brooks, March 18, 2007 - 4:01 am UTC

Great stuff as usual.
I have recently had cause to recommend a similar solution at a client with exactly the problems you would expect from n SQL statements with 1 ... m length IN lists.

It's a shame that OCI has a 4000 limit on VARCHAR2 bound binds as this limits such an approach (although it may be that subsequent attempts to use further workarounds USING LONG/CLOB have been erroneously implemented).

As ever, I think this may often be a workaround for application developers / database designers not using appropriate application->database interfaces (stored procs, O-R views, Oracle collections).

This raises a current frustration, for me, which is that ODP does not support Oracle collections to the same extent as JDBC (which is probably why the application initially resorted to using varying IN lists).

Good One..

Kiran Kumar, March 19, 2007 - 4:21 am UTC

I really appriciate Tom, you are doing such a great job by answering various questions and doubts. this site is really useful. I am a regular reader of this site and enjoying a lot. Keep continue..

Clever Ruby

Casimir Saternos, March 19, 2007 - 12:26 pm UTC

Definitely an interesting solution - and demonstrates the flexibility of Oracle SQL. However, I think that the complexity of the solution suggests that is a problem better solved in another language - for instance, in Ruby:

irb(main):004:0> 'a,bb,ccc,d,e,f'.split(',').each{|t|puts t}
a
bb
ccc
d
e
f
=> ["a", "bb", "ccc", "d", "e", "f"]

Thank you for the creative solution and your tireless efforts - I have benefited greatly from your books and online resources.

WOW

A reader, April 10, 2007 - 4:27 am UTC

Hi Tom,
I tried the wow methoed for lob parsing .
meaning , I have a lob with lines seperated by semicolons.
however, I get an error

Error at line 5
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB


with data
as
(
select substr (txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
as token
-- from (select ','||:txt||',' txt from dual)
from (select blob_content txt
from wwv_flow_file_objects$
where filename='test.sql')
connect by level <= length(:txt)-length(replace(:txt,',',''))+1
)
select * from data;

is there a way to do that ?

10x,

sagsag
Tom Kyte
April 10, 2007 - 10:44 am UTC

you'll want to write a pipelined function to process that.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425#28960621306776

will get you started, you'll need to use utl_raw.cast_to_raw and cast_to_varchar2 of course...

my mistake

A reader, April 10, 2007 - 4:52 am UTC

working on BLOB is not a good idea
I added the utl_raw.cast_to_varchar2 and it works fine.
still have some problems .to workout.

regards,

sagsag

With a longer delimiter

dave, July 11, 2007 - 3:21 pm UTC

Hi Tom,

Thank you very much for the site. I have found it, and your book very useful indeed.

I had an opportunity to use the query at the top of this thread today, but with a delimiter of more than one character. I thought I would share the modified version. Not rocket science, but might save someone some time.

SQL> variable txt varchar2(25)
SQL> variable dl  varchar2(3)
SQL>
SQL> exec :txt := 'a!@!bb!@!ccc!@!d!@!e!@!f'; :dl := '!@!';

PL/SQL procedure successfully completed.

SQL>
SQL> with data
  2  as
  3  (
  4  select substr (txt,
  5                 instr (txt, :dl, 1, level  ) + dlen,
  6                 instr (txt, :dl, 1, level+1) - instr (txt, :dl, 1, level) - dlen )
  7           as token
  8    from (select :dl||:txt||:dl txt, length(:dl) dlen from dual)
  9  connect by level <= (length(:txt)-length(replace(:txt,:dl,'')))/dlen+1
 10  )
 11  select * from data;

TOKEN
--------
a
bb
ccc
d
e
f

with data as select

James, November 19, 2007 - 11:00 am UTC

Only one problem...

I have no idea what "with data as select" actually does.

I've searched on google for a while and not found any explanation, syntax or discussion. Just a few 'tricks.'

Is "with data" as old as the hills? Is it like trying to find out what 'select' does? I only ask as I've never coded a "with data" query and would like to read about it: when to use it, how it performs, syntax, capabilities.

I feel so inadequate.

Tom Kyte
November 21, 2007 - 12:55 pm UTC

it is a new 9i feature and is part of ANSI SQL and is called "subquery factoring"


http://www.google.com/search?q=%22subquery+factoring%22

CMG, November 21, 2007 - 9:01 am UTC

the word "data" here is just the alias assigned to the query that's defined by the "select" statement. You could just as easily use "Tom" instead of "data".

WITH tom AS SELECT 'x' FROM DUAL ...
just means I'm going to define a query (SELECT 'x' FROM DUAL) which I'll refer to from this point onwards as if it were a table called "tom". Then all references to "tom" actually refer to the results of that query.
If you look at the explain plan for queries using WITH clauses, you can see that the query is executed early on and stored as a virtual table which is then used later on.


subquery factoring - reuse of results

Richard Smith, November 21, 2007 - 11:21 am UTC

CMG: Actually, it depends on your usage of the results from the subquery factoring. If you use it to move some inline view out of the main query (for readability) and that data is referenced only once, the optimizer will probably treat it as if it were still an inline view, using the usual complex view merging techniques.

If, however, your factored subquery is used multiple times in the main query, it very likely will be stored as a virtual table and the results reused by the main query, which should result in faster response overall. It all depends on the cost of pre-computing the subquery and joining it to the main query results.

Not binding but a row from a different table

rc, November 26, 2007 - 3:39 pm UTC

(oracle  11.1.0.6.0)

You don't always want to bind an inlist, sometimes you want to select the inlist from a table. I came with this solution: 

SQL> create table testclob (id number(10) , list clob) ;

Tabel is aangemaakt.

SQL> insert into testclob values (1,'a1,a2,a3');

1 rij is aangemaakt.

SQL> select substr (txt,
                 instr (txt, ',', 1, level  ) + 1,
                 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) token
 from
  (
  select ','||list||',' txt , list from testclob
  ) t
 connect by level <= length(t.list)-length(replace(t.list,',',''))+1
;

TOKEN
-------
a1
a2
a3

/* for geeks only: 

What however if you insert another row into testclob?

insert into testclob values (2,'b1,b2,b3');

SQL> select substr (txt,
                 instr (txt, ',', 1, level  ) + 1,
                 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) token
 from
  (
  select ','||list||',' txt , list from testclob
  ) t
 connect by level <= length(t.list)-length(replace(t.list,',',''))+1
;

TOKEN
-------------------------------
a1
a2
a3
b3
b2
a3
b3
b1
a2
a3
b3
b2
a3
b3

14 rijen zijn geselecteerd.

14 = 4 * 2 + 2 * 2 + 1 * 2

delete testclob;
insert into testclob values  (1,'a1,a2,a3,a4');
insert into testclob values  (2,'b1,b2,b3,b4');

will give 30 rows, 30 = 8 * 2 + 4 * 2 + 2 * 2 + 1 * 2 

*/ 


Tom Kyte
November 27, 2007 - 3:03 pm UTC

well, if you have a table, one would question "why do not you just STORE ROWS"

I would reject this code in real life, there is absolutely no logical reason to store:
insert into testclob values  (1,'a1,a2,a3,a4');
insert into testclob values  (2,'b1,b2,b3,b4');



you should have stored:

(1,'a1')
(1,'a2')
(1,'a3')
....


and just use a subquery, no tricks.

settings and trees

rc, November 28, 2007 - 4:19 pm UTC

Sometimes you have settings table where customers can do all kind of different settings:

create table settings (name varchar2(25), setting varchar2(200) );

Customers can use this to configure our applications.

We sometimes have settings like:

name             setting
---------------  ----------
LAW_OWNER_CODES  BEL,JUI,FAL 

We want to built queries like:

select * from real_estate 
where lawcode in ('BEL','JUI','FAL');

But I understand that you want me to create an extra table "settings_children" with a foreign key relation to "settings". 

The geeks only part is more about trees than the "binding in list":

I know understand that you can you use 'connect by level < ' to generate binary trees (and all the paths in a binary tree) if you have a table with two rows instead of one: 

SQL> create table roots ( root number(1) );

Tabel is aangemaakt.

SQL> insert into roots values (1);

1 rij is aangemaakt.

SQL> insert into roots values (2) ;

1 rij is aangemaakt.

select path from
(
select root, substr(sys_connect_by_path(root,'/'),1,30) path
from
(select root from roots)
connect by level < 4
)
where path like '/1%'; 

gives:

PATH
------------------------------
/1
/1/1
/1/1/1
/1/1/2
/1/2
/1/2/1
/1/2/2

7 rijen zijn geselecteerd.

Tree:

          1
       /     \
     1         2 
   /   \      / \ 
  1     2   1    2

If you 

insert into roots values (3);

you can generate a trinary tree. 


PATH
------------------------------
/1
/1/1
/1/1/1
/1/1/2
/1/1/3
/1/2
/1/2/1
/1/2/2
/1/2/3
/1/3
/1/3/1
/1/3/2
/1/3/3

13 rijen zijn geselecteerd.

I don't know if generating binary or trinary trees is useful but I believe it is fun! 


generic tree view generator

rc, November 28, 2007 - 4:46 pm UTC

variable l_level number
variable l_branches number

exec :l_level := 4;
exec :l_branches := 3;

select path from
(
select num, substr(sys_connect_by_path(num,'/'),1,30) path
from
( select level num from dual connect by level < :l_branches+1)
connect by level < :l_level+1
)
where path like '/1%';


SQL Query Problem

A Reader, December 07, 2007 - 11:43 am UTC

I have a table RELS like this :

RELS
===========================================
E1 E2 Relation
===========================================
Dharm Sunny Deol Father
Sunny Deol Bobby Deol Brother
Hema Dharm Wife
Esha Hema Daughter
===========================================

Please help me to find the name of people who are orphans.

The answer should be :

Dharm
Hema

Thanks

Is this doable in SQL

J B, December 21, 2007 - 2:44 am UTC

Hi tom,

I tried to write SQL for this model, but couldn't achieve the satisfactory SQL so far.

Just want to ask if this is doable?

Thanks
Tom Kyte
December 21, 2007 - 2:53 pm UTC

I have no idea what you mean.

Writing SQL

Jb, December 21, 2007 - 11:51 pm UTC

Hi Tom,

The above mentioned question, where the reader is asking to find the name of persons who are "orphans", is that doable in SQL.

I am not sure if that can be done. Just wanted to have your comments as you are the best in the SQL writing business.

I hope it clarifies.
Tom Kyte
December 22, 2007 - 11:50 am UTC

hah, that orphan example was so ill specified - I won't even read it.

RELS
===========================================
E1 E2 Relation
===========================================
Dharm Sunny Deol Father

so, is Dharm the father of Sunny Deol or vice verse.

no create table
no inserts
NO LOOK either.

The Orphans

rc, December 22, 2007 - 12:40 pm UTC

I hope this is the solution for the orphan question. It is a complicated problem (imho) so this solution needs a lot of testing!!

I believe Dharm is the father of Sunny Deol.

drop table rels;

create table rels (e1 varchar2(20) , e2 varchar2(20) , relation varchar2(20) );


insert into rels values ('Dharm','Sunny','Father');
insert into rels values ('Sunny','Bobby','Brother');
insert into rels values ('Hema','Dharm','Wife');
insert into rels values ('Esha','Hema','Daughter');
insert into rels values ('Sunny','John','Brother');
insert into rels values ('Dharm','Iris','Brother');
insert into rels values ('Marc','Iris','Brother');
insert into rels values ('Bo','Dharm','Mother');
insert into rels values ('Ha','Ra', 'Broher');
commit;

create or replace view not_orphans
as
select distinct e1,e2,relation
from
(
with parents as
(
select distinct e1 ep
from relsv
where relation = 'Parent'
)
select level l,e1,e2,relation
from (
select e1,e2,'Parent' relation
from rels
where relation in ('Father','Mother')
union
select e2,e1,'Parent'
from rels
where relation in ('Son','Daughter')
union
select e1,e2,'Sibling'
from rels
where relation in ('Sister','Brother')
union
select e2,e1,'Sibling'
from rels
where relation in ('Sister','Brother')
)
, parents
connect by nocycle e1 = prior e2
start with e1=ep
)
where l > 1
/



select e1
from rels
where e1 not in
(
select e1 from not_orphans
union
select e2 from not_orphans
)
and relation not in ('Daughter','Son')
union
select e2
from rels
where e2 not in
(
select e1 from not_orphans
union
select e2 from not_orphans
)
/



orphan question, oops mistake

rc, December 22, 2007 - 12:55 pm UTC

drop table rels;

create table rels (e1 varchar2(20) , e2 varchar2(20) , relation varchar2(20) );

insert into rels values ('Dharm','Sunny','Father');
insert into rels values ('Sunny','Bobby','Brother');
insert into rels values ('Hema','Dharm','Wife');
insert into rels values ('Esha','Hema','Daughter');
insert into rels values ('Sunny','John','Brother');
insert into rels values ('Dharm','Iris','Brother');
insert into rels values ('Marc','Iris','Brother');
insert into rels values ('Bo','Dharm','Mother');
insert into rels values ('Ha','Ra', 'Broher');
commit;

First simplify the relations and double the sibling relations:

create or replace view relsv
as
select e1,e2,'Parent' relation
from rels
where relation in ('Father','Mother')
union
select e2,e1,'Parent'
from rels
where relation in ('Son','Daughter')
union
select e1,e2,'Sibling'
from rels
where relation in ('Sister','Brother')
union
select e2,e1,'Sibling'
from rels
where relation in ('Sister','Brother')
/

create or replace view not_orphans
as
select distinct e1,e2,relation
from
(
with parents as
(
select distinct e1 ep
from relsv
where relation = 'Parent'
)
select level l,e1,e2,relation
from relsv
, parents
connect by nocycle e1 = prior e2
start with e1=ep
)
where l > 1
/

I use nocycle because we have doubled the sibling relations.

select e1 orphan
from rels
where e1 not in
(
select e1 from not_orphans
union
select e2 from not_orphans
)
and relation not in ('Daughter','Son')
union
select e2
from rels
where e2 not in
(
select e1 from not_orphans
union
select e2 from not_orphans
)
/

ORPHAN
--------------------
Bo
Ha
Hema
Ra

I hope this works!!

orphan question

rc, December 23, 2007 - 4:24 am UTC

It doesn't work, I give up.

{One of the mistakes is that I didn't create a check constraint on the relation column}.

Some Help

JB, December 26, 2007 - 6:47 am UTC

Hi Tom,

Very complex problem.

Dharm being the father of Sunny, how can we derive the SQL to find the orphans.

I am putting the CREATE table and Inserts for you.

CREATE TABLE RELS
( E1 VARCHAR2(20),
E2 VARCHAR2(20),
REL VARCHAR2(20)
)
/
Insert into Rels Values ('Esha','Hema','Daughter');
Insert into Rels Values ('Dharm','Sunny','Father');
Insert into Rels Values ('Bobby','Sunny','Brother');
Insert into Rels Values ('Hema','Dharm','Wife');

Your help is appreciated.

Many thanks in advance.


Tom Kyte
December 26, 2007 - 9:26 am UTC

now, define orphan.


This way of storing relations is sort of, well, not sort - entirely wrong.

You have "X" is father of "Y".

And "A" is daughter of "B" which implies that "B" is either the father or mother of "A", but you don't state that.

No, I'm not going to work on this - too many loose ends here, from an ill defined problem to data that just isn't stored in a logical fashion.


Esha is not an orphan (Hema is mother or Father, I guess since someone named Hema is the wife of Dharm - Hema is the mother)

Sunny is not an orphan, since Dharm is his father

And by extension, assuming Bobby is the full brother of Sunny - Dharm is also his father. So Bobby is not.

But given the information here, I'd have to say that Hema and Dharm may or may not be orphans themselves.


No, this isn't a database problem... The database problem would be how to store this data in the first place - and we are not there yet.

failed to find any documentation

A Reader, December 26, 2007 - 10:25 am UTC

Hi Tom

in wow query

with data
2 as
3 (
4 select substr (txt,
5 instr (txt, ',', 1, level ) + 1,
6 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
7 as token
8 from (select ','||:txt||',' txt from dual)
9 connect by level <= length(:txt)-length(replace(:txt,',',''))+1
10 )

i have failed to find any documentation for the connect by level clause. can to point us to the documentation regarding connect by level clause.
Tom Kyte
December 26, 2007 - 10:48 pm UTC

connect by is documented...

it is just an imaginative use of the clause.

almost nothing is documented short of "fact". The stuff that takes fact and turns it into something useful is what we all get paid for :)

A guy named Mikito
http://asktom.oracle.com/pls/asktom/f?p=100:11:1082395870839153::::P11_QUESTION_ID:34808326503816#34823249753962
http://www.oreillynet.com/databases/blog/2005/03/hotsos_symposium_day_1.html

came up with that gem

Orhan question

RC, December 27, 2007 - 6:27 am UTC

Dear JB, why don't you want to learn from my mistakes?

Please create a check constraint on the relation column!

What kind of relations are we talking about? (Father, Mother, Husband, Wife, Son, Daughter, Brother, Sister?) A check constraint will help and protect you from making errors.

Fist we have to define the question properly.


To: rc from The Netherlands, posted on: November 26, 2007 - 3pm US/Eastern.

Kashif, March 14, 2008 - 11:08 am UTC

RC,

To handle multiple rows that contain delimited data (e.g. as in a table) instead of a single row that is bound in, you have to treat the substr/instr (Tom's) query as a collection subquery, and then unnest the collection using a table expression (TABLE()). I simply added a VARCHAR2 table SQL type to your original requirements:

<code>
$ @a
Connected.
a@CASST
$ create table testclob (id number(10) , list clob) ;

Table created.

a@CASST
$ insert into testclob values (1,'a1,a2,a3');

1 row created.

a@CASST
$ select substr (txt,
  2                   instr (txt, ',', 1, level  ) + 1,
  3                   instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) token
  4   from
  5    (
  6    select ','||list||',' txt , list from testclob
  7    ) t
  8   connect by level <= length(t.list)-length(replace(t.list,',',''))+1
  9  ;

TOKEN
--------------------------------------------------------------------------------
a1
a2
a3

3 rows selected.

a@CASST
$ insert into testclob values (2,'b1,b2,b3');

1 row created.

a@CASST
$ CREATE OR REPLACE
  2  TYPE     t_vc2_50 as table of varchar2(50)
  3  /

Type created.

a@CASST
$ select b.column_value token
  2  from (select id, list, ','||list||',' txt from testclob ) t,
  3  table ( cast ( multiset (
  4  select substr (txt,
  5               instr (txt, ',', 1, level  ) + 1,
  6               instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) token
  7  from dual
  8  connect by level <= length(t.list)-length(replace(t.list,',',''))+1
  9  ) as t_vc2_50 ) ) b
 10  /

TOKEN
--------------------------------------------------
a1
a2
a3
b1
b2
b3

6 rows selected.



Kashif
</code>

reverse the operation

grimms, April 08, 2009 - 7:48 am UTC

Given the table:

TOKEN
----------------------------------
a
bb
ccc
d
e
f

6 rows selected.

Is there also a SQL statement solution that takes it back to comma sep form ?

i.e. output from SQL would be
'a,bb,ccc,d,e,f'


Tom Kyte
April 13, 2009 - 1:27 pm UTC

ops$tkyte%ORA10GR2> create table t as select * from all_users where rownum <= 5;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select substr(max(sys_connect_by_path(username,',')),2) data
  2    from (select username, rownum rn from t)
  3  start with rn = 1
  4  connect by prior rn+1 = rn
  5  /

DATA
--------------------------------------------------
BIG_TABLE,DIP,TSMSYS,LOTTOUSER,MDDATA




use row_number() over (order by whatever) rn

instead of rownum if you want the string in any sort of predicable 'order'

A reader, January 28, 2010 - 9:55 am UTC

Is there anyway I can use a similar technique that you showed in the beginning of this thread (turning a comma-separated string into a table) to do the following?

Table t stores a date range. In this example the range is 2010-01-23 ~ 2010-01-28. There can only be one record in this table.

Each record in table x has a date range that is within the date range stored in table t. I'd like to do an equivalent of MINUS set operation

<Date range specified in table t>
MINUS
<Date range in individual records in table x>;

and displays the differences in rows.

CREATE TABLE t (
  StartDate   DATE,
  EndDate     DATE,
);

INSERT INTO t VALUES (TO_DATE('2010-01-23', 'yyyy-mm-dd'), TO_DATE('2010-01-28', 'yyyy-mm-dd'));


CREATE TABLE x (
  id        INTEGER,
  StartDate DATE,
  EndDate   DATE
);

INSERT INTO x VALUES (1, TO_DATE('2010-01-24', 'yyyy-mm-dd'), TO_DATE('2010-01-27', 'yyyy-mm-dd'));
INSERT INTO x VALUES (2, TO_DATE('2010-01-25', 'yyyy-mm-dd'), TO_DATE('2010-01-26', 'yyyy-mm-dd'));

-- ---------------------------------------------------------------------------
-- Desired output
-- ---------------------------------------------------------------------------
1, 2010-01-23
1, 2010-01-28
2, 2010-01-23
2, 2010-01-24
2, 2010-01-27
2, 2010-01-28


Tom Kyte
January 29, 2010 - 3:52 pm UTC

ops$tkyte%ORA11GR2> with data1
  2  as
  3  (select startdate+level-1 dt
  4     from t
  5   connect by level <= enddate-startdate+1
  6  ),
  7  data2
  8  as
  9  (select x.id, x.startdate, x.enddate, y.column_value dt
 10     from x, table( cast( multiset( select startdate+level-1
 11                                      from dual
 12                                    connect by level <= x.enddate-x.startdate+1) as sys.odcidatelist) ) y
 13  )
 14  select id, data1.dt
 15    from data2 partition by (id) right outer join data1 on (data1.dt = data2.dt)
 16   where data2.dt is null
 17   order by id, data1.dt
 18  /

        ID DT
---------- ---------
         1 23-JAN-10
         1 28-JAN-10
         2 23-JAN-10
         2 24-JAN-10
         2 27-JAN-10
         2 28-JAN-10

6 rows selected.

link no found

A reader, January 30, 2010 - 6:32 pm UTC

In the beginning of this thread,
You said:

Every time I use analytics!


http://asktom.oracle.com/pls/ask/search?p_string=%22rock+and+roll%22

But the link is no found!
Tom Kyte
February 01, 2010 - 10:17 am UTC

try the search screen on the home page

but the url changed a bit over the many years:

http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22rock+and+roll%22

To a reader

MH, February 01, 2010 - 9:08 am UTC

Sql Problem ; Joke

A reader, February 01, 2010 - 12:18 pm UTC

The answer should be :

Dharm
Hema

What about Basanti and Veeru and Gabbar Singh ?

Clever SQL - 10g

Rajeshwaran, Jeyabal, February 03, 2010 - 10:52 pm UTC

Tom,

Here is the " Clever SQL " Version in 10g.

scott@10G> variable txt varchar2(25);
scott@10G> exec :txt := 'a,bb,ccc,d,e,f';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

scott@10G> select trim(regexp_substr(upper(:txt),'[^,]+',1,level)) as Tokens
  2  from dual
  3  connect by level <= length(regexp_replace(:txt,'[^,]'))+1;

TOKENS
--------------------------------
A
BB
CCC
D
E
F

6 rows selected.

Elapsed: 00:00:00.00


Tom Kyte
February 04, 2010 - 12:22 pm UTC

I'll stick with length and substr - regexp, neat but a HUGE cpu cost.

Clever SQL - 10g

Rajeshwaran, Jeyabal, February 09, 2010 - 2:41 am UTC

Tom,

You are right..REGEX are HUGE cpu cost.

scott@10G> create table T
  2  (
  3       x number,
  4       y varchar2(4000)
  5  );

Table created.

Elapsed: 00:00:00.31

LOAD DATA
INFILE *
TRUNCATE 
INTO TABLE T
(
 x RECNUM,
 y POSITION(1:4000)
)

BEGINDATA
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|
.........
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|


scott@10G> DECLARE
  2    l_tokens dbms_sql.varchar2a;
  3  begin
  4    for r in (select y from t)
  5    loop
  6        select substr (txt,
  7                     instr (txt, '|', 1, level  ) + 1,
  8                     instr (txt, '|', 1, level+1) - instr (txt, '|', 1, level) -1 )
  9               as token
 10         bulk collect into l_tokens
 11        from (select r.y as txt FROM dual)
 12        connect by level <= length(txt) - length(replace(txt,'|'))-1;
 13        l_tokens.DELETE;
 14    end loop;
 15
 16    for r in (select y from t)
 17    loop
 18        select trim(regexp_substr((txt),'[^|]+',1,level)) as Tokens
 19        bulk collect into l_tokens
 20        from (select r.y as txt FROM dual)
 21        connect by level <= length(regexp_replace(txt,'[^|]'))-1;
 22         l_tokens.DELETE;
 23    end loop;
 24  end;
 25  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.23

********************************************************************************
DECLARE
  l_tokens dbms_sql.varchar2a;
begin
  for r in (select y from t)
  loop
      select substr (txt,
                   instr (txt, '|', 1, level  ) + 1,
                   instr (txt, '|', 1, level+1) - instr (txt, '|', 1, level) -1 )
             as token
       bulk collect into l_tokens
      from (select r.y as txt FROM dual)
      connect by level <= length(txt) - length(replace(txt,'|'))-1;
      l_tokens.DELETE;
  end loop;
  for r in (select y from t)
  loop
      select trim(regexp_substr((txt),'[^|]+',1,level)) as Tokens
      bulk collect into l_tokens
      from (select r.y as txt FROM dual)
      connect by level <= length(regexp_replace(txt,'[^|]'))-1;
       l_tokens.DELETE;
  end loop;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      2.00       2.38          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.01       2.39          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

SELECT SUBSTR (TXT, INSTR (TXT, '|', 1, LEVEL ) + 1, INSTR (TXT, '|', 1, 
  LEVEL+1) - INSTR (TXT, '|', 1, LEVEL) -1 ) AS TOKEN 
FROM
 (SELECT :B1 AS TXT FROM DUAL) CONNECT BY LEVEL <= LENGTH(TXT) - 
  LENGTH(REPLACE(TXT,'|'))-1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute  15400      1.43       1.44          0          0          0           0
Fetch    15400      2.75       2.72          0          0          0      400400
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    30801      4.20       4.17          0          0          0      400400

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
 400400  CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=2696889 us)
  15400   FAST DUAL  (cr=0 pr=0 pw=0 time=46042 us)

********************************************************************************

SELECT TRIM(REGEXP_SUBSTR((TXT),'[^|]+',1,LEVEL)) AS TOKENS 
FROM
 (SELECT :B1 AS TXT FROM DUAL) CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(TXT,
  '[^|]'))-1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  15400      1.54       1.48          0          0          0           0
Fetch    15400     21.96      21.98          0          0          0      400400
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    30801     23.51      23.46          0          0          0      400400

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
 400400  CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=14184509 us)
  15400   FAST DUAL  (cr=0 pr=0 pw=0 time=46371 us)
********************************************************************************

query detail needed

friend, August 26, 2011 - 3:34 am UTC

hi ,

i have a table having three columns name ,phone_no,offic_no

and having data like ..

name phone_no office_no
abc 1234,4568,1485 1236/5896/5214
bcv 1475/5289/4156 1458/6985,4568


and i need a query which should display result like this

name phone_no office_no
abc 1234
abc 4568
abc 1485
abc 1236
abc 5896
abc 5214

Tom Kyte
August 30, 2011 - 4:34 pm UTC

no create
no inserts
no look


I hate you data model by the way, if I can call it that. What motivates people to store data like that???? I'll never understand.

query detail needed

Rajeshwaran, Jeyabal, August 30, 2011 - 7:31 pm UTC

rajesh@ORA10GR2> create or replace type num_type is table of number;
  2  /

Type created.

Elapsed: 00:00:00.09
rajesh@ORA10GR2>
rajesh@ORA10GR2> create or replace function f(x in varchar2)
  2  return num_type
  3  pipelined is
  4     l_data varchar2(30) := replace(x,',','/');
  5  begin
  6
  7     for r in (select substr( txt,
  8     instr(txt,'/',1,level) + 1,
  9     instr(txt,'/',1,level+1) - instr(txt,'/',1,level) - 1) as y
 10     from ( select '/'||l_data||'/' as txt from dual)
 11     connect by level <= length(l_data) - length(replace(l_data,'/')) + 1 )
 12     loop
 13             pipe row ( to_number(r.y) );
 14             dbms_output.put_line (r.y);
 15     end loop;
 16  end;
 17  /

Function created.

Elapsed: 00:00:00.10
rajesh@ORA10GR2> create table t(x varchar2(30),y varchar2(30),z varchar2(30));

Table created.

Elapsed: 00:00:00.20
rajesh@ORA10GR2> insert into t values ('abc','1234,4568,1485','1236/5896/5214');

1 row created.

Elapsed: 00:00:00.17
rajesh@ORA10GR2> insert into t values ('bcv','1475/5289/4156','1458/6985,4568');

1 row created.

Elapsed: 00:00:00.09
rajesh@ORA10GR2> commit;

Commit complete.

Elapsed: 00:00:00.09
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> select t1.x,t2.column_value
  2  from (
  3  select x,decode(l,1,y,2,z) as val
  4  from t,(select level l from dual connect by level < = 2)
  5  )t1, table(f(t1.val)) t2
  6  /

X          COLUMN_VALUE
---------- ------------
abc                1234
abc                4568
abc                1485
bcv                1475
bcv                5289
bcv                4156
abc                1236
abc                5896
abc                5214
bcv                1458
bcv                6985
bcv                4568

12 rows selected.
rajesh@ORA10GR2>
rajesh@ORA10GR2>

query detail needed

Rajeshwaran, Jeyabal, August 30, 2011 - 8:57 pm UTC

As per your question, here is the query to display the results in two different columns, phone_no# & office_no#

(That I missed out in first reply)

rajesh@ORA10GR2> select t1.x,
  2      max(case when l = 1 then t2.column_value end) as phone_no,
  3      max(case when l = 2 then t2.column_value end) as office_no
  4  from (
  5  select x,decode(l,1,y,2,z) as val,l
  6  from t,(select level l from dual connect by level < = 2)
  7  )t1, table(f(t1.val)) t2
  8  group by t1.x,
  9  case when l = 1 then t2.column_value end,
 10  case when l = 2 then t2.column_value end
 11  order by 1;

X      PHONE_NO OFFICE_NO
----- --------- ---------
abc        1234
abc        1485
abc        4568
abc                  1236
abc                  5214
abc                  5896
bcv        1475
bcv        4156
bcv        5289
bcv                  1458
bcv                  4568
bcv                  6985

12 rows selected.

Elapsed: 00:00:00.06
rajesh@ORA10GR2>

The date range solution

Jichao Li, August 31, 2011 - 1:44 am UTC

Tom,

The magical CONNECT BY query is really a WOW! Regarding your answer to the question (bookmark here http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:73830657104020#2304359700346123406 ), here is an alternative which may be a bit more easily understandable.

SELECT x.id,
       t2.the_date
  FROM x,
       (SELECT (startdate + LEVEL - 1) AS the_date
          FROM t
        CONNECT BY (startdate + LEVEL - 1) <= enddate) t2
 WHERE t2.the_date NOT BETWEEN x.startdate AND x.enddate
 ORDER BY x.id,
          t2.the_date;

Regexp learning

Nagaraju, July 10, 2013 - 11:20 am UTC

Hi Tom,

I didn't know that, Regexp are cpu intensive. thanks for this. I find it little difficult to learn as well. However I find this odd behavior with regexp, because I understand [0-9] and [[:digit:]] are same. But in the below case, [0-9] works, [[:digit:]] don't.

SQL>  select regexp_substr('(906) 553-8725','\([[:digit]]{3}\)') phone_number from dual;

P
-


SQL> select regexp_substr('(906) 553-8725','\([0-9]{3}\)') phone_number from dual;

PHONE
-----
(906)

not sure, why?

Tom Kyte
July 16, 2013 - 3:34 pm UTC

ops$tkyte%ORA11GR2> select regexp_substr('(906) 553-8725','\([[:digit:]]{3}\)') phone_number
  2   from dual;

PHONE
-----
(906)

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select regexp_substr('(906) 553-8725','\([0-9]{3}\)') phone_number from
  2     dual;

PHONE
-----
(906)


missing a colon

Awesome... so how exactly does it work?

Miri Cc, December 22, 2015 - 4:21 pm UTC

I have used the level/ connect by level idea that I copied from another co-worker, but I really want to understand how it works. Can you explain in detail? I'm a novice.
Chris Saxon
December 23, 2015 - 12:29 am UTC

Sorry, are you talking level/connect by in general, or this specific string parsing example ?

SQL - Wishing happy holidays and merry christmas

Rajeshwaran, Jeyabal, December 25, 2015 - 9:34 am UTC

rajesh@ORA11G> select decode
  2          (sign (floor (maxwidth/2) - rownum)
  3          ,1, lpad (' ', floor (maxwidth/2) - (rownum -1))
  4             ||rpad ('*', 2 * (rownum -1), ' *')
  5          ,lpad ('* * *', floor (maxwidth/2) + 3)) "Merry Christmas"
  6    from dual
  7    ,    (select 40 as maxwidth from dual)
  8    connect by level < floor (maxwidth/2) + 5
  9  /

Merry Christmas
---------------------------------------------------------------------------

                   *
                  * *
                 * * *
                * * * *
               * * * * *
              * * * * * *
             * * * * * * *
            * * * * * * * *
           * * * * * * * * *
          * * * * * * * * * *
         * * * * * * * * * * *
        * * * * * * * * * * * *
       * * * * * * * * * * * * *
      * * * * * * * * * * * * * *
     * * * * * * * * * * * * * * *
    * * * * * * * * * * * * * * * *
   * * * * * * * * * * * * * * * * *
  * * * * * * * * * * * * * * * * * *
                  * * *
                  * * *
                  * * *
                  * * *
                  * * *

24 rows selected.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library