Friday, June 02, 2006

Varying in lists...

Varying in lists. This is such a frequently asked question on asktom that I’ve decided just to write up the various ways you can do this.

First the problem statement: You have a string, it looks like
  • 1, 2, 3, 4
  • ‘A’, ‘B’, ‘C’
Or something similar. You would like to retrieve all rows from some table/query such that some column is in that string. That is, you would like to execute:
  • select * from t where x in (1,2,3,4)
  • select * from t where y in (‘A’,’B’,’C’)
You would like to use bind variables (because you’ve heard through the grapevine that bind variables are “good”). However, when you try:


SQL> variable txt varchar2(25)
SQL> exec :txt := ' ''SYS'', ''SYSTEM'' '
PL/SQL procedure successfully completed.

SQL> print txt

TXT
--------------------------------
'SYS', 'SYSTEM'

SQL> select *
2 from all_users
3 where username in (:txt);
no rows selected

SQL> select *
2 from all_users
3 where username in ('SYS','SYSTEM');

USERNAME USER_ID CREATED
---------- ---------- ---------
SYS 0 30-JUN-05
SYSTEM 5 30-JUN-05


It does not seem to work at all. When you “bind” the inlist – no data, when you hard code it – data is found. The reason – well, that should be clear, the example above that used the bind variable in this case is equivalent to this query with literals:
SQL> select *
2 from all_users
3 where username in ( ' ''SYS'', ''SYSTEM'' ' );
no rows selected

There is a single string, a single value in that in list. What we need to do is turn that into a “set”. Here are some approaches you can take.

If you have a finite number of items in the in-list
By all means just bind the individual elements. That is, in the above example, suppose we let the user pick up to 10 items in a pick list. I would strongly encourage the query you use to be:

Select * from all_users where username in ( :bv1, :bv2, :bv3, … :bv10 );

And you would NOT use a single string, you would bind 10 inputs to this query (binding NULLs for any bind variable they did not set a value for). This works well for small lists – it would be quite tedious obviously for dozens or hundreds of items.

If you are in Oracle 8i
We can use a function that returns a collection. We’ll make it so that we can “query a string” – use the string as if it were a table itself. The concept goes like this. We’ll need a collection type (I’ll just use a table of varchar2(4000)), and a PLSQL function that will parse a delimited string and return it as a collection:

SQL> create or replace type str2tblType as table of varchar2(4000)
2 /
Type created.

SQL> create or replace
2 function str2tbl
3 ( p_str in varchar2,
4 p_delim in varchar2 default ',' )
5 return str2tblType
6 as
7 l_str long default p_str || p_delim;
8 l_n number;
9 l_data str2tblType := str2tblType();
10 begin
11 loop
12 l_n := instr( l_str, p_delim );
13 exit when (nvl(l_n,0) = 0);
14 l_data.extend;
15 l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));
16 l_str := substr( l_str, l_n+1 );
17 end loop;
18 return l_data;
19 end;
20 /
Function created.

SQL> column column_value format a10
SQL> exec :txt := 'SYS, SYSTEM'
PL/SQL procedure successfully completed.

SQL> select *
2 from TABLE( cast( str2tbl(:txt) as str2TblType ) )
3 /

COLUMN_VAL
----------
SYS
SYSTEM

SQL> select *
2 from all_users
3 where username in
4 (select *
5 from TABLE( cast( str2tbl(:txt) as str2TblType ) )
6 )
7 /

USERNAME USER_ID CREATED
---------- ---------- ---------
SYS 0 30-JUN-05
SYSTEM 5 30-JUN-05


If you are in 9iR2 and above
Then we can skip the function all together and just use DUAL to generate rows and parse the string. Consider:
SQL> select level l
2 from dual
3 connect by level <= 5;

L
----------
1
2
3
4
5

So, we can use DUAL to generate rows and then using substr/instr – effectively parse the bind variable and return the i'th element from it. For example:
SQL> exec :txt := 'SYS, SYSTEM'
PL/SQL procedure successfully completed.

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

TOKEN
----------------------------------
SYS
SYSTEM

Once we have that accomplished – the rest is easy:
SQL> with data
2 as
3 (
4 select
5 trim( substr (txt,
6 instr (txt, ',', 1, level ) + 1,
7 instr (txt, ',', 1, level+1)
8 - instr (txt, ',', 1, level) -1 ) )
9 as token
10 from (select ','||:txt||',' txt
11 from dual)
12 connect by level <=
13 length(:txt)-length(replace(:txt,',',''))+1
14 )
15 select *
16 from all_users
17 where username in (select * from data);

USERNAME USER_ID CREATED
---------- ---------- ---------
SYSTEM 5 30-JUN-05
SYS 0 30-JUN-05

Now, some people look at that “with data” bit and say “that is too much, too hard to code that every time”. We can use a VIEW to hide the complexity here – and use a stored procedure as our way to “bind to the view” (this is sort of a parameterized view in effect). It would look like this:
SQL> create or replace context my_ctx using my_ctx_procedure
2 /
Context created.

SQL> create or replace
2 procedure my_ctx_procedure
3 ( p_str in varchar2 )
4 as
5 begin
6 dbms_session.set_context
7 ( 'my_ctx', 'txt', p_str );
8 end;
9 /
Procedure created.

SQL> create or replace view IN_LIST
2 as
3 select
4 trim( substr (txt,
5 instr (txt, ',', 1, level ) + 1,
6 instr (txt, ',', 1, level+1)
7 - instr (txt, ',', 1, level) -1 ) )
8 as token
9 from (select ','||sys_context('my_ctx','txt')||',' txt
10 from dual)
11 connect by level <=
12 length(sys_context('my_ctx','txt'))
13 -length(replace(sys_context('my_ctx','txt'),',',''))+1
14 /
View created.

SQL> exec my_ctx_procedure( :txt )
PL/SQL procedure successfully completed.

SQL> select *
2 from all_users
3 where username in
4 (select * from IN_LIST);

USERNAME USER_ID CREATED

---------- ---------- ---------
SYSTEM 5 30-JUN-05
SYS 0 30-JUN-05

Now, you “bind” your queries in-list by calling MY_CTX_PROCEDURE and the view does the rest.
POST A COMMENT

88 Comments:

Blogger Hae-Kwang said....

Not related to the actual topic of the post but related, I think:
---
Tom Kyte said...
You would like to retrieve all rows from some table/query such that some column is in that string.
===
There's actually something related to this that I've been wondering about on and off lately that the statement quoted above reminded me of. Is there a way to find out what TABLE_NAME AND COLUMN_NAME a particular value is found in if I know the exact value, but have no idea what table or column might contain this information, or if I don't know what they are called? I asked our DBAs and they don't know, and someone that uses SQL Server commented that one could query in the master database.

Fri Jun 02, 10:50:00 AM EDT  

Blogger Thomas Kyte said....

hae-kwang said...

I don't understand what you are asking.

Fri Jun 02, 10:55:00 AM EDT  

Anonymous RobH said....

wow.....

The 9i technique just blew my mind....lemme go pick it up

Fri Jun 02, 11:09:00 AM EDT  

Blogger Hae-Kwang said....

thomas kyte said...
---
Sorry, I should've specified the question rather than simply describing it. Is there a way to use a value of a column to find out what table / column it is found in? This'd come in handy when it comes to tables and columns that exist I don't know about.

Fri Jun 02, 11:50:00 AM EDT  

Blogger Thomas Kyte said....

sorry - still not getting it.

how can you have a "value of a column" and not know the column itself?

Are you asking for a "database wide search - here is some value, find me all of the table.columns that just happen to have this value"?

Fri Jun 02, 11:55:00 AM EDT  

Blogger Hae-Kwang said....

---
thomas kyte said...
Are you asking for a "database wide search - here is some value, find me all of the table.columns that just happen to have this value"?
===
Yes! Sorry about that. I need to work on describing things clearly.

thomas kyte said...
---
how can you have a "value of a column" and not know the column itself?
===
The value is displayed on the front-end's GUI, but at times I find it difficult to interpret the GUI's information and tie it to the *_NAME for the TABLE and COLUMN.

That database-wide search seems like exactly what I've been wanting to find out about for a while. Is that possible?

Fri Jun 02, 12:01:00 PM EDT  

Blogger Tony Ackley said....

Hae-Kwang said...

like this?

select TABLE_NAME, COLUMN_NAME from dba_tab_columns where COLUMN_NAME like '%_NAME%';

Fri Jun 02, 12:19:00 PM EDT  

Anonymous RobH said....

You would need some privs to select from dba_*, why not all_tab_columns?

Fri Jun 02, 12:30:00 PM EDT  

Anonymous Dave said....

I think he has a value and he needs to know which table / column that value belongs in.

There is a procedure search_schema on asktom which can do that

so search for it on askTom and you shall find it

Fri Jun 02, 12:44:00 PM EDT  

Blogger Thomas Kyte said....

Indeed - search_schema or find_string

Fri Jun 02, 01:10:00 PM EDT  

Blogger Hae-Kwang said....

Thanks! The question in the find_string seems to be what I need. Is there an updated link to http://www.oracle.com/oramag/code/tips2001/index.html?120201.html that can be provided (the snippet mentioned in the answer) as it is a broken link?

Fri Jun 02, 03:15:00 PM EDT  

Blogger Thomas Kyte said....

Doesn't matter that link is "broken" (the one on asktom pointing to a 5 year old article)

Reason: I wrote this:

...
You can start with this snippet:

...dead link here...

You'll want to modify it to skip over numeric columns -- lobs/raws and such
(easy to add a where datatype in ( 'VARCHAR2', 'CHAR' ) to the process!

Be aware -- it isn't going to be *fast*. Another enhancement to it would be to
make it search for all columns in a table at the same time (instead of a full
scan per column).

Oh well, might as well just do it then:
.........

And I proceded to write the code. You have no use of that original code which didn't do what you want :)

Fri Jun 02, 03:20:00 PM EDT  

Anonymous dhinds said....

There's a much easier way to accomplish the original trick:

select * from all_users
where ','||:txt||',' like '%,'||username||',%'

where :txt is a comma separated list like 'SYS,SYSTEM'. Use any delimiter you like: chr(9) might be a good choice.

Fri Jun 02, 04:44:00 PM EDT  

Blogger Thomas Kyte said....

dhinds said...

Sure, if you like doing a full scan when an index would likely be preferred...

I explicitly avoided even mentioning that way - way too "slow" in general.

Fri Jun 02, 04:57:00 PM EDT  

Anonymous mikito harakiri said....

The "in" list is just a relation (temporary or otherwise) which is joined with the rest of the query. Treating "in" list as a relation simplifies a lot. How to optimize joins, for example is well known, versus for "in" list predicate you have to consider all sort of transformations like rewriting the predicate

x in (1,2)

into

x = 1 or x = 2

Perhaps it's a good idea to deprecate "in" lists in SQL altogether?

Fri Jun 02, 06:05:00 PM EDT  

Anonymous ST-Lin said....

Hi Tom,
I had try the method and I have two questions about the topic:
1. about Execution Plan.if the list contain 1 item ex: 'SYS' and contain many items ,maybe more than 1000,ex:'SYS,SYSTEM,...etc'. But I got the same execute plan ,then I had not good performance for the query. How to let it know that my condition has how many items and get the suitable plan?
2. When I use dynamic sql statement,
my list length > 4000 bytes , use "USING IN" statement will cause ORA-01461 exception alert.
EX:
declare
l_sql varchar2(10000);
l_list varchar2(10000);
begin
-- length(l_list) > 4000 bytes
l_list := 'a,b,c.....etc';
l_sql := ' select * from employees where department_id in
( select trim(trim ('''''''' from substr (txt, instr (txt, '','', 1, level ) + 1,
instr (txt, '','', 1, level+1) - instr (txt, '','', 1, level) -1 ) )) as token
from (select '',''||:x || '','' txt from dual )
connect by level <= length(:x) -length(replace(:x,'','',''''))+1 )
';
execute immediate l_sql using in l_list,l_list,l_list;

end;
/

This will cause ORA-01461.
Please how to solve this.
Thanks a lot.

Mon Jun 05, 07:15:00 AM EDT  

Blogger Thomas Kyte said....

st-lin said

read this article - the Query Plans with Temporary Tables section and the "Next Steps"

2) yes, there is a 4,000 byte limit on strings. If you have more than that - we need to talk. What are you doing in the client that would cause this to happen? for I cannot imagine a human being picking hundreds or thousands of elements from a list - hence I question your "logic" here.

Mon Jun 05, 07:30:00 AM EDT  

Anonymous Anonymous said....

Hi Tom,

i tried the solution for 9iR2 but it didn't work:

SELECT * FROM in_list;

TOKEN
--------------------------------------------------------------------------------
'SYS'
'SYSTEM'

the view and app context are returning data.

SELECT DISTINCT username FROM all_users WHERE username LIKE 'SYS%';

USERNAME
------------------------------
SYS
SYSTEM

all_users has the data we want...

select *
from all_users
where username in
(select * from IN_LIST);

USERNAME USER_ID CREATED
------------------------------ ---------- -----------

SQL>

no rows????? Guessing the extra apostrophes are to blame. I'm going to try and take them out but I don't really understand the solution!!!

Mon Jun 05, 12:08:00 PM EDT  

Blogger Thomas Kyte said....

re-read the example, you'll see I set the string:

...
using substr/instr – effectively parse the bind variable and return the i'th element from it. For example:
SQL> exec :txt := 'SYS, SYSTEM'
PL/SQL procedure successfully completed.

SQL> with data
.....


and yes, it is the quotes, they obviously do not belong there when doing it as a true "IN (SET)"

Mon Jun 05, 12:12:00 PM EDT  

Anonymous Anonymous said....

so.....how to remove the quotes so it works? I really don't understand how it works - i was hoping to put this into my app and just use it (i do get the app context bit though, just the view is beyond my basic understanding).

Mon Jun 05, 12:18:00 PM EDT  

Anonymous Anonymous said....

ignore my last post, i got it working.

I've said it before but i'll say it again - AskTom has everything but finding it is really hard.

The method for 9iR2 is new to me and I have been diligently keeping track on the questions regarding varying IN lists.

Thanks though!

Mon Jun 05, 12:32:00 PM EDT  

Blogger Thomas Kyte said....

well, I just sort of demonstrated it ??

:bv := 'a,b,c,d,e,f,g'

just pop a delimited list in there??

And please, make sure you understand before you use....

Mon Jun 05, 12:33:00 PM EDT  

Blogger Thomas Kyte said....

it is hard to find "this" on asktom because it isn't really *there*

Else, I would have just pointed to it. I wrote this specifically because it wasn't published so much... and kept coming up as a question

Mon Jun 05, 12:35:00 PM EDT  

Anonymous Leo Mannhart said....

Thomas Kyte said

...for I cannot imagine a human being picking hundreds or thousands of elements from a list - hence I question your "logic" here...


Me I can as I have to ;-)
Here, they even had to overcome the limit of 1000 elements in an in-list. Of course the performance is way too slow, the CPU consumption is high, a lot of "similar" statements in the shared pool ...

We will now go for temporary tables as they still insist, that they need to load all the values when starting up the application (the user might want to scroll down later on). Can I say "Java-developers" here? Yes, this is the people thinking they can do sorting and the like much faster than anybody else ... /rant

Tue Jun 06, 07:08:00 AM EDT  

Blogger Robert said....

Tom, comparing to this route below, which is "better" or your preference ?

create or replace type split_tbl
as table of varchar2(32767);

create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
)
return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));

else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;

SQL> select * from table(split('one,two,three'));

one
two
three

Tue Jun 06, 10:03:00 AM EDT  

Blogger Thomas Kyte said....

robert said

"better" than what? I demonstrated a function in 8i - that if 8i had a pipelined operator would have been pipelined, but in 9i I would prefer to not have to create such a function since I don't need it.

Tue Jun 06, 10:09:00 AM EDT  

Anonymous Rob Paterson said....

I can't seem to get the "select level l from dual connect by level <= 5" approach to work in 9iRel2.

Am I doing something wrong?


1* select level l from dual connect by level <= 5
DEV> /

L
----------
1

DEV> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

DEV>

Tue Jun 06, 10:14:00 PM EDT  

Blogger Thomas Kyte said....

inline view or "with" it like I did.

Tue Jun 06, 10:18:00 PM EDT  

Blogger David Aldridge said....

Tom,

For the finite list method where you bind in null's for the "surplus" variables, it seems that if the users are consistently supplying some varying number of values less than the number that are bound then the optimizer is going to produce incorrect cardinality estimates for them. Potentially it will be over-estimating by a factor of 10, which could be a significant issue if supplying one value generally indicates index-based access and supplying ten values generally indicates a full table scan.

It seems that in such a case it might be worthwhile looking at the sensitivity of the execution plan to this cardinality, and optionally using some if-then-else logic to use different statements for ranges of values where the number significantly impacts the plan.

For example ...

if values_supplied = 1 then
select ... where col1 = :bv1;
elsif values_supplied = 2 then
select ... where col1 in (:bv1,:bv2);
elsif values_supplied between 3 and 10 then
select ... where col1 in (:bv1,:bv2,...,:bv10);
else
use other method;
end if;

So that synthesis of methods extends to choosing between different methods based on the number of items chosen -- a straight equality where only one item is chosen from a list, the DUAL-based parsing method for high numbers of values, etc..

Thu Jun 08, 06:13:00 PM EDT  

Anonymous Anonymous said....

You discussed a method for converting a comma/pipe-separated list into a column of values.
What about the other way around?

Wed Jul 26, 07:46:00 PM EDT  

Anonymous Anonymous said....

SQL> create or replace type str2tblType as table of varchar2(4000)2 /Type created.SQL> create or replace2 function str2tbl3 ( p_str in varchar2,4 p_delim in varchar2 default ',' )5 return str2tblType6 as7 l_str long default p_str || p_delim;8 l_n number;9 l_data str2tblType := str2tblType();10 begin11 loop12 l_n := instr( l_str, p_delim );13 exit when (nvl(l_n,0) = 0);14 l_data.extend;15 l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));16 l_str := substr( l_str, l_n+1 );17 end loop;18 return l_data;19 end;20 /

What is the other way around

Wed Jul 26, 07:48:00 PM EDT  

Blogger Thomas Kyte said....

search for stragg on asktom, it'll do the grouping.

Wed Jul 26, 07:48:00 PM EDT  

Anonymous Anonymous said....

Tried to use your in_list function to insert rows into another schema. Is there any way around the error "remote operations not permitted on object tables or user-defined type columns"?

Mon Sep 11, 11:33:00 AM EDT  

Blogger Thomas Kyte said....

when you said "another schema", did you really mean "another database?"

Mon Sep 11, 11:35:00 AM EDT  

Blogger Paweł Barut said....

Tom!
What you think about my solution using xmlType and xmlSequence?

SQL> var list varchar2(200)
SQL> exec :list := '2,4,6,8,10,34,33';

PL/SQL procedure successfully completed.

SQL> select items.extract('/l/text()').getStringVal() item
2 from table(xmlSequence(
3 extract(XMLType('<all><l>'||
4 replace(:list,',','</l><l>')||'</l></all>')
5 ,'/all/l'))) items;

ITEM
--------------------------------------------------------------------------------
2
4
6
8
10
34
33

7 rows selected.

Simple! Isn’t it?
Paweł

Fri Oct 20, 06:38:00 PM EDT  

Anonymous eric said....

I used the 9i method and it is pretty slick. Thanks for posting it.

I did run into some performance problems because indexes were not being used when I was using in_list in a subquery. I added a cardinality hint in the in_list view to cut the estimated number of rows returned from dual to 10 from 8k. In my case the number of list items is usually small.

This is the same advice that you gave on asktom for str2tbl, but I didn't see the same advice given for the 9i method.

select /*+ cardinality( dual 10 ) */ ','||sys_context('ctx_ddpkyweb','txt')||',' txt
from dual

Thu Nov 02, 05:08:00 PM EST  

Blogger Thomas Kyte said....

but the cardinality from dual is 1, not 8k!

Thu Nov 02, 07:06:00 PM EST  

Anonymous eric said....

Tom,

Here is the explain plan from my query showing the cardinality of dual to be 8168:

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3307 Card=1 Bytes=126)
1 0 NESTED LOOPS (Cost=3307 Card=1 Bytes=126)
2 1 NESTED LOOPS (Cost=3306 Card=1 Bytes=76)
3 2 VIEW (Cost=3302 Card=1 Bytes=30)
4 3 SORT (GROUP BY) (Cost=3302 Card=1 Bytes=156)
5 4 HASH JOIN (Cost=3290 Card=7 Bytes=1092)
6 5 TABLE ACCESS (FULL) OF 'TBL_SUBR_COVERAGE' (Cost=3273 Card=667 Bytes=16675)
7 5 VIEW OF 'IN_LIST' (Cost=16 Card=8168 Bytes=1070008)
8 7 CONNECT BY (WITHOUT FILTERING)
9 8 TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=8168)
10 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_SUBR_COVERAGE' (Cost=4 Card=1 Bytes=46)
11 10 INDEX (RANGE SCAN) OF 'PK_SUBR_COVERAGE' (UNIQUE) (Cost=3 Card=1)
12 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_GSD' (Cost=1 Card=1 Bytes=50)
13 12 INDEX (UNIQUE SCAN) OF 'PK_GSD' (UNIQUE)

Here is the explain plan for the same query with the cardinality hint on dual:

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=83 Card=1 Bytes=126)
1 0 NESTED LOOPS (Cost=83 Card=1 Bytes=126)
2 1 NESTED LOOPS (Cost=82 Card=1 Bytes=76)
3 2 VIEW (Cost=78 Card=1 Bytes=30)
4 3 SORT (GROUP BY) (Cost=78 Card=1 Bytes=156)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_SUBR_COVERAGE' (Cost=5 Card=1 Bytes=25)
6 5 NESTED LOOPS (Cost=66 Card=1 Bytes=156)
7 6 VIEW OF 'IN_LIST' (Cost=16 Card=10 Bytes=1310)
8 7 CONNECT BY (WITHOUT FILTERING)
9 8 TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=10)
10 6 INDEX (RANGE SCAN) OF 'PK_SUBR_COVERAGE' (UNIQUE) (Cost=3 Card=2)
11 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_SUBR_COVERAGE' (Cost=4 Card=1 Bytes=46)
12 11 INDEX (RANGE SCAN) OF 'PK_SUBR_COVERAGE' (UNIQUE) (Cost=3 Card=1)
13 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_GSD' (Cost=1 Card=1 Bytes=50)
14 13 INDEX (UNIQUE SCAN) OF 'PK_GSD' (UNIQUE)

I can't post the query as-is, but I'll try to create a scaled down case that illustrates the behavior. I have created an in_list view and included this view in a subquery. Adding cardinality hints to the subquery had no effect.

Mon Nov 06, 09:04:00 AM EST  

Blogger Thomas Kyte said....

Oh wait, you are using old software - got it, DUAL is not analyzed, that is the "guess" for that un-analyzed table

ops$tkyte%ORA9IR2> set linesize 1000
ops$tkyte%ORA9IR2> select * from dual;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'



ops$tkyte%ORA9IR2> select /*+ first_rows */ * from dual;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 Card=8168 Bytes=16336)
1 0 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)



DYNAMIC_SAMPLING would fix that right up:

ops$tkyte%ORA9IR2> select /*+ first_rows dynamic_sampling(dual 3) */ * from dual;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 Card=1 Bytes=2)
1 0 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=1 Bytes=2)

Mon Nov 06, 09:09:00 AM EST  

Anonymous eric said....

Thanks. The un-analyzed guess is what I was getting at with my initial post.

Dynamic Sampling is a better solution. I guess I should have read your Oracle Magazine article first.

The execution plan using dynamic sampling is as follows:

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=38 Card=1 Bytes=126)
1 0 NESTED LOOPS (Cost=38 Card=1 Bytes=126)
2 1 NESTED LOOPS (Cost=37 Card=1 Bytes=76)
3 2 VIEW (Cost=33 Card=1 Bytes=30)
4 3 SORT (GROUP BY) (Cost=33 Card=1 Bytes=156)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_SUBR_COVERAGE' (Cost=5 Card=1 Bytes=25)
6 5 NESTED LOOPS (Cost=21 Card=1 Bytes=156)
7 6 VIEW OF 'IN_LIST' (Cost=16 Card=1 Bytes=131)
8 7 CONNECT BY (WITHOUT FILTERING)
9 8 TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=1)
10 6 INDEX (RANGE SCAN) OF 'PK_SUBR_COVERAGE' (UNIQUE) (Cost=3 Card=2)
11 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_SUBR_COVERAGE' (Cost=4 Card=1 Bytes=46)
12 11 INDEX (RANGE SCAN) OF 'PK_SUBR_COVERAGE' (UNIQUE) (Cost=3 Card=1)
13 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_GSD' (Cost=1 Card=1 Bytes=50)
14 13 INDEX (UNIQUE SCAN) OF 'PK_GSD' (UNIQUE)

Mon Nov 06, 09:59:00 AM EST  

Anonymous Rasika said....

Hi Tom,

As alwasy you find simple way to do things... But I have following question

It's very clear how you used the context and view to return the list,
but my question is how do we use this as a common view where all other SPs could use

To clarify my question,
In a given SP I'm building a dynamic query
(of course with the help of sys_context to use bind variables)
and based on some conditions i have two EXISTS clauses and each needs to use IN_LIST

Ex1:

Select ...

from...

where
...

AND EXISTS (SELECT 1 FROM t1 where ... t1.c1 IN ('A','B','C'))
AND EXISTS (SELECT 1 FROM t2 where ... t2.c2 IN ('1','2','3'))
...

Ex2 or simply
SELECT ... FROM t1,...tn where t1.c1 IN ('A','B','C') and t2.c2 IN ('1','2','3')


and if we assume EXISTS conditions are built dynamically as explained in your post
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1288401763279

Since the IN_LIST uses single 'txt' property from the sys_context, is there a way to use same
IN_LIST view but with two lists. ('A,B,C' and '1,2,3')

or do we have to go with the 'with' option instead of IN_LIST view method

Thank you

Fri Dec 08, 07:04:00 AM EST  

Blogger Thomas Kyte said....

Rasika said...

What immediately popped into my head was....

create view v1...
create view v2...
create view v_as_many_as_you_want...

and each accesses it's own context.

Fri Dec 08, 07:09:00 AM EST  

Anonymous Rasika said....

Thanks Tom for your time,
(It's really amazing your response time - just 5 mins, anyone in anywhere can talk to you and reply is guaranteed.. how to do you keep track of all the threads and their contents so accurately)

BTW even though I have some more questions related to "context" I'm not going to make your blog another question forum - waiting till the door of asktom opens

Thanks again

Fri Dec 08, 11:11:00 AM EST  

Anonymous Anonymous said....

Tom,

Sorry to pile onto an already long set of comments but I'm wondering how best to perform something similar where both values to be compared are string lists. In some cases I would need to return only rows where list a is a subset of list b and in others where at least one item in list a is in list b. The lists are constrained to contain up to 8 letters ABCDEFGH (eg 'ABDF'). I can see how to do it writing a comparison function but want to find the most efficient method so that I can perform queries where the two lists are part of a join.

Could regexp do this?

Thanks,

Greg

Thu Dec 21, 04:41:00 PM EST  

Anonymous Anonymous said....

Beaware that in Oracle 9, query
select level
from dual
connect by level<20
returns up to 10 records regardess of "level<..." fragment

In Oracle 10 this code works as expected.

Wed Mar 14, 06:56:00 AM EDT  

Anonymous Anonymous said....

Tom, I thought you had asktom site where we can ask all kinds of questions, but after looking at this thread, I felt it moved here in this blob ;-). Just kidding.
I want to know what are your comments on Pawel Barut's Solution using XML Types? It looked so simple and works well. I 've tested with simple lists and works pretty neat. Can you Please post your comments on that solution?

Thu Aug 09, 05:14:00 PM EDT  

Blogger Tommy said....

The DBMS_UTILITY.comma_to_table and DBMS_UTILITY.table_to_comma procedures allow you to split and rejoin the values in a CSV record:

SET SERVEROUTPUT ON
DECLARE
l_list1 VARCHAR2(50) := 'A,B,C,D,E,F,G,H,I,J';
l_list2 VARCHAR2(50);
l_tablen BINARY_INTEGER;
l_tab DBMS_UTILITY.uncl_array;
BEGIN
DBMS_OUTPUT.put_line('l_list1 : ' || l_list1);

DBMS_UTILITY.comma_to_table (
list => l_list1,
tablen => l_tablen,
tab => l_tab);

FOR i IN 1 .. l_tablen LOOP
DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
END LOOP;

DBMS_UTILITY.table_to_comma (
tab => l_tab,
tablen => l_tablen,
list => l_list2);

DBMS_OUTPUT.put_line('l_list2 : ' || l_list2);
END;
/

Fri Feb 01, 07:40:00 AM EST  

Blogger Thomas Kyte said....

@Tommy -

those utilities are useless in the real world.

Here is an excerpt from Expert one on one Oracle regarding them. The are to be used for IDENTIFIERS (30 characters only, subject to many rules - try it with NUMBERS for example :) )




COMMA_TO_TABLE, TABLE_TO_COMMA
These two utilities either take a comma delimited string of IDENTIFIERS and parse them into a PL/SQL table (COMMA_TO_TABLE) or take a PL/SQL table of any type of string and make a comma delimited string of them (TABLE_TO_COMMA). I stress the word IDENTIFIERS above because COMMA_TO_TABLE uses NAME_TOKENIZE to parse the strings – hence as we saw in that section, we need to use valid Oracle identifiers (or quoted identifiers). This still limits us to 30 characters per element in our comma-delimited string however.

This utility is most useful for applications that want to store a list of tablenames in a single string for example and have them easily converted to an array in PL/SQL at runtime. Otherwise, it is of limited use. If you need a general purpose “COMMA_TO_TABLE” routine that works with comma delimited strings of data, see Chapter 20, Using Object Relational Features. In the “SELECT * from PLSQL_FUNCTION” section, I demonstrate how to do that.

Here is an example using this routine and demonstrating how it deals with long identifiers and invalid identifiers:

scott@TKYTE816> declare
2 type vcArray is table of varchar2(4000);
3
4 l_names vcArray := vcArray( 'emp,dept,bonus',
5 'a, b , c',
6 '123, 456, 789',
7 '"123", "456", "789"',
8 '"This is a long string, longer then 32 characters","b",c');
9 l_tablen number;
10 l_tab dbms_utility.uncl_array;
11 begin
12 for i in 1 .. l_names.count
13 loop
14 dbms_output.put_line( chr(10) ||
15 '[' || l_names(i) || ']' );
16 begin
17
18 dbms_utility.comma_to_table( l_names(i),
19 l_tablen, l_tab );
20
21 for j in 1..l_tablen
22 loop
23 dbms_output.put_line( '[' || l_tab(j) || ']' );
24 end loop;
25
26 l_names(i) := null;
27 dbms_utility.table_to_comma( l_tab,
28 l_tablen, l_names(i) );
29 dbms_output.put_line( l_names(i) );
30 exception
31 when others then
32 dbms_output.put_line( sqlerrm );
33 end;
34 end loop;
35 end;
36 /

[emp,dept,bonus]
[emp]
[dept]
[bonus]
emp,dept,bonus

So, that shows that it can take the string emp,dept,bonus and break it into a table and put it back together again.

[a, b, c]
[a]
[ b ]
[ c]
a, b, c

This example shows that if you have whitespace in the list, it will be preserved. You would have to use the trim function to remove leading and trailing white space if you do not want any.

[123, 456, 789]
ORA-00931: missing identifier

This shows that to use this procedure on a comma delimited string of numbers, we must go one step further as demonstrated below:

["123", "456", "789"]
["123"]
[ "456"]
[ "789"]
"123", "456", "789"

Here it is able to extract the numbers from the string. Note however, how it not only retains the leading whitespace but it also retains the quotes. It would be up to you to remove them if you so desire.

["This is a long string, longer then 32 characters","b",c]
ORA-00972: identifier is too long

PL/SQL procedure successfully completed.

And this last example shows that if the identifier is too long (longer then 30 characters) it will raise an error as well – these routines are only useful for strings of 30 characters or less. While it is true that TABLE_TO_COMMA will take larger strings then 30 characters, COMMA_TO_TABLE will not be able to undo that work.

Fri Feb 01, 08:11:00 AM EST  

Anonymous thom said....

So easy even a MS SQL Server guy can figure it out, I've posted my function as an example. Thanks Tom

create or replace function uf_split (divisionid char) return types.ref_cursor
as
split_cursor types.ref_cursor;
begin
open split_cursor for
with division_tbl as
(select
trim( substr (divisionid,
instr (divisionid, ',', 1, level ) + 1,
instr (divisionid, ',', 1, level+1)
- instr (divisionid, ',', 1, level) -1 ) )
as token
from (select ','||divisionid||',' divisionid
from dual)
connect by level <=
length(divisionid)-length(replace(divisionid,',',''))+1
)
select distinct business_unit_id,division_id from sewn.style where division_id in (select * from division_tbl);
return split_cursor;
end uf_split;
/

Wed Apr 23, 01:54:00 PM EDT  

Anonymous Anonymous said....

Thanks, this is quite handy. But I can't figure out how to modify it to use multiple expressions -- for example, the 9iR2 SQL Reference doc has a SQL statement under the Expression Lists page utilizing multiple expressions:


SELECT * FROM employees
WHERE (first_name, last_name, email) IN
(('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA'))


Can this Context/View method be used in 9iR2 for this kind of IN expression?

Mon Sep 15, 12:25:00 PM EDT  

Anonymous Anonymous said....

I'm an Oracle bigot by trade, but ran across something in SQL Server to create a comma separated list pretty easily in T-SQL. It's strange how SQLServer lets you perform multiple assignments to variables in the midst of a SELECT statement that returns more than one row. If we try this in PL/SQL with a SELECT bla INTO, we get query returned more that one result...

Just food for thought - figured I throw it out there...

declare @testcode varchar(100)
begin
select @testcode = isnull(@testcode + ', ', '') + isnull(t.thing,'')
from (select top 5 substring(table_name,1,1) thing
from information_schema.tables
) t
print @testcode
end

Output:
R, R, V, F, T

Thu Nov 20, 04:13:00 PM EST  

Blogger Sachin said....

Tom,

I did some tests based on your suggestions and found the results a bit surprising -- using the suggested method, we spent more CPU cycles... i have mentioned my test case here (http://oracle-online-help.blogspot.com/2009/02/index-range-scan-vs-nested-loop-in-in.html) -- Can you confirm whether i did that the right way or am i missing anything?

Sachin

Mon Feb 09, 01:40:00 AM EST  

Blogger Thomas Kyte said....

@Sachin

you say "more cpu". Ok, do you understand how much cpu you will literally BURN through constantly if you hard parse a statement with a hard coded literal list of values over and over in a multi-user situation?

Tell you what - I always post my examples... I don't see yours... Probably - you have a change in plan between using binds vs not and we can fix that.

So, please, always post something tangible that we can actually look at and comment on..

Mon Feb 09, 08:45:00 AM EST  

Anonymous Anonymous said....

using 11gr2,
I didn't get past step 1:

SELECT LEVEL 1 FROM DUAL CONNECT BY LEVEL <= 5
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

I guess this is the issue:
http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it

Mon Jul 12, 01:25:00 PM EDT  

Blogger Thomas Kyte said....

@anonyomous

that is not the issue at all.

the issue is you wrote

select level 1 - the NUMBER ONE

whereas I wrote:

select level l - the letter L

sorry for the confusion, L is an alias for level in that query, not the number one.

Tue Jul 13, 06:25:00 AM EDT  

Anonymous Anonymous said....

Hi Tom,

a simple question for you with the binding of larger values, and this is more of a question about sys_context than your technique.

Basically, if you bind a "large" value in, sys_context seems to truncate the output, i.e.

create or replace context my_ctx using my_ctx_procedure
/

create or replace
procedure my_ctx_procedure( p_str in varchar2 )
as
begin
dbms_session.set_context( 'my_ctx', 'txt', p_str );
end;
/

exec my_ctx_procedure(LPAD('x', 500, 'x'));

You can see a difference between

select LPAD('x', 500, 'x') from dual;

and

select sys_context('my_ctx','txt')
from dual

what am I doing wrong?

Regards

Mon Jul 19, 04:32:00 AM EDT  

Blogger Thomas Kyte said....

@Anonymous

see http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions182.htm#SQLRF06117 and check out the length parameter.

Mon Jul 19, 04:34:00 AM EDT  

Anonymous Anonymous said....

Thanks Tom! As informative as ever.

Mon Jul 19, 04:39:00 AM EDT  

Blogger SweaterFreak said....

Tom, is there a way to do inside the stored procedure? when I place "with data as...." bit inside the stored proc, it complains that txt shouldn't be varchar....am I missing something? thanks

Wed Dec 15, 06:56:00 PM EST  

Blogger Thomas Kyte said....

@SweaterFreak

what you are missing is an example and version information.


ops$tkyte%ORA11GR2> create or replace procedure p
2 as
3 l_data varchar2(30) := 'a,b,c,d';
4 begin
5
6 for x in (
7 with data
8 as
9 (
10 select
11 trim( substr (txt,
12 instr (txt, ',', 1, level ) + 1,
13 instr (txt, ',', 1, level+1)
14 - instr (txt, ',', 1, level) -1 ) )
15 as token
16 from (select ','||l_data||',' txt
17 from dual)
18 connect by level <=
19 length(l_data)-length(replace(l_data,',',''))+1
20 )
21 select * from data
22 ) loop
23 dbms_output.put_line( x.token );
24 end loop;
25 end;
26 /

Procedure created.

ops$tkyte%ORA11GR2> exec p
a
b
c
d

PL/SQL procedure successfully completed.

Thu Dec 16, 02:45:00 AM EST  

Blogger Sachin said....

Tom,
The solution is always great which you have given but there is one tricky requirement where I'm having comma seperated values which comes as input to stored proc, which solution would you propose that of using temporary tables i.e insert values in temp tables and then process values from that table or is there any quick fix to these problems?

Tue Apr 26, 06:10:00 AM EDT  

Anonymous Anonymous said....

I just found an interesting approach on one of the Oracle forums:

select regexp_substr(x, '[^,]+', 1, level) x
from (select '1,2,3,4,5,6,7' x
from dual)
connect by level <= length(x) - length(replace(x,',')) + 1;

Mon May 23, 04:08:00 AM EDT  

Blogger Thomas Kyte said....

@Anonymous with regexp_substr

tkprof 1,000 iterations of using substr versus regexp.

regular expressions are 'cool'.
But they eat CPU for lunch - and they are really really hungry at lunch.

Mon May 23, 07:25:00 AM EDT  

Blogger siddharth said....

Thanx tom..beutifully explained...
It answered the puzzled behaviour of my query..i appreciate ur help..thanks...:)

Mon Aug 01, 02:46:00 AM EDT  

Blogger siddharth said....

This comment has been removed by the author.

Mon Aug 01, 02:48:00 AM EDT  

Blogger vinit said....

I get an error invalid data type

Mon Oct 10, 12:00:00 PM EDT  

Blogger Thomas Kyte said....

@vinit


and my car won't start.

now we are even.

show your work - be assured that these examples actually *do work* if you use them as is (these are cut and pastes).

So, I don't know what part you skipped, what you typed in wrong - but you don't show your work - so we'll never know.

Mon Oct 10, 12:07:00 PM EDT  

Blogger Mikhail Gavryuchkov said....

Hi Tom,

Thank you for the article - very useful information. I am trying to apply this technique to our database and have problems with it. One of our tables has a column with comma-separated values in it, something like this (I am trying to follow your example):

ID VALUE
----------------
1 SYS,SYSTEM
2 DBA,ADMIN

I will try to emulate the db by a subquery. The following query works as expected:

select
trim(substr(txt,
instr(txt, ',', 1, level) + 1,
instr(txt, ',', 1, level + 1)
- instr(txt, ',', 1, level) -1 ) )
as token
from (
select ','||txt||',' txt, txt as pure_txt from (
select 'SYS,SYSTEM' as txt from dual
)
)
connect by level <= length(pure_txt) - length(replace(pure_txt, ',', '')) + 1;

TOKEN
-----
SYS
SYSTEM

However, when I try to add one more record

select
trim(substr(txt,
instr(txt, ',', 1, level) + 1,
instr(txt, ',', 1, level + 1)
- instr(txt, ',', 1, level) -1 ) )
as token
from (
select ','||txt||',' txt, txt as pure_txt from (
select 'SYS,SYSTEM' as txt from dual
union
select 'DBA,ADMIN' as txt from dual
)
)
connect by level <= length(pure_txt) - length(replace(pure_txt, ',', '')) + 1;

it return more than 4 expected records:

TOKEN
-----
DBA
ADMIN
SYSTEM
SYS
ADMIN
SYSTEM

Can you please try it out and see if it's possible to make it work?

Thanks!
Mikhail

Thu Oct 13, 07:14:00 PM EDT  

Blogger Thomas Kyte said....

@Mikhail


ops$tkyte%ORA11GR2> create table t ( x int, y varchar2(20) );

Table created.

ops$tkyte%ORA11GR2> insert into t values (1 , 'a,b' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values (2, 'c,d' );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select *
2 from (select x, y, ','||y||',' txt from t )t, TABLE( cast( multiset(
3 select
4 trim( substr (txt,
5 instr (txt, ',', 1, level ) + 1,
6 instr (txt, ',', 1, level+1)
7 - instr (txt, ',', 1, level) -1 ) )
8 as token
9 from dual
10 connect by level <=
11 length(y)-length(replace(y,',',''))+1
12 ) as sys.odciVarchar2List ) )
13 /

X Y TXT COLUMN_VAL
---------- -------------------- ---------------------- ----------
1 a,b ,a,b, a
1 a,b ,a,b, b
2 c,d ,c,d, c
2 c,d ,c,d, d

Fri Oct 14, 03:35:00 AM EDT  

Blogger Mikhail Gavryuchkov said....

Hey Tom,

Thanks so much! Amazingly it works! I spent much time trying to break it, I ran it on our production db on a set of about 1000 records and it's blazing fast! It seems a much better approach than you gave in your initial post. Or is there any catch? :) Limitations?

Mikhail

Fri Oct 14, 08:27:00 PM EDT  

Blogger Thomas Kyte said....

@Mikhail

it is exactly the *same* approach I took in the initial writing.

The approach is:

take a string, parse it around commas using dual with a connect by level <= length - turn a single string into a set.

Now, your twist is that you had rows with strings - not just "a string", but a set of strings. You need to turn each of those strings into a set (one at a time) and join that set back to its originating row.

So, I used nested table unnesting to do that (search documentation for table unnesting). It is like a "self join without a where clause".

We are simply taking each individual string and one at a time, parsing it - turning it into a set - and joining back to its originating row.

But fundamentally - it is exactly the same process as used for the "varying in lists", just in a different location in the query.

Fri Oct 14, 10:59:00 PM EDT  

Anonymous Anonymous said....

Hi Tom,

I would like top apply the things discussed here to a project that relies heavily on Hibernate. It is easy to replace the SQL that Hibernate creates for IN lists, e.g. I can tell Hibernate to write

WHERE key IN (select * from TABLE( cast( str2tbl( 'arg1, arg2, arg3' ) as str2TblType ) )

instead of

WHERE key IN ( arg1, arg2, arg3 )

like in your 8i example. This works with Hibernate since I can turn the argument list into a string parameter and pass that parameter to the appropriate function at the same place where the original IN list would have been. However, there seems to be no ( or at least no easy way ) to place something like "WITH data AS" or "exec my_ctx_procedure( 'arg1, arg2, arg3' )" at the beginning of the generated SQL which is necessary for your later examples.

Unfortunately, the 8i solution has one drawback: I cannot use it to do a query like

WHERE ( key1, key2 ) IN ( ( arg1, arg2 ), ( arg3, arg4 ) )

So i wonder if there is a way to get the best of both worlds: To use as many columns in the IN list as I like and pass the paramters as a single string without the need to set context variables or define "WITH data AS" clauses.

Mon Oct 17, 10:49:00 AM EDT  

Blogger Thomas Kyte said....

@Anonymous

first of all - sort of stinks to have to try to trick your TOOL into doing things doesn't it. You are being so productive trying to find ways AROUND your tool. (get my drift?)


anyway, of course you can do

where (x,y) in ..... with 8i


what if your string looked like:

a,b,c,d,e,f,g,h

and you turned str2tbl into something that returns

a,b
c,d
e,f
g,h

I'm pretty sure you could turn the:

select *
from TABLE( cast( str2tbl(:txt) as str2TblType ) )


into something like:

select substr( column_value, 1, instr( column_value, ',' )-1 ) key1, substr( column_value, instr( column_value, ',' ) key2 from ...


couldn't you...

MAKE SURE TO USE BIND VARIABLES AND NOT STRING LITERALS PLEASE - that is most important.

Mon Oct 17, 06:57:00 PM EDT  

Anonymous Anonymous said....

first of all - sort of stinks to have to try to trick your TOOL into doing things doesn't it. You are being so productive trying to find ways AROUND your tool. (get my drift?)
You're absolutely right, the tool I'm trying to trick is Oracle which doesn't support a variable amount of bind variables. Fortunately Hibernate is flexible enough to support the rewriting of its SQL generation parts.

into something like:

select substr( column_value, 1, instr( column_value, ',' )-1 ) key1, substr( column_value, instr( column_value, ',' ) key2 from ...

Am I right in my assumption that one cannot return a table with a variable amount of columns as the result of a function? Which in turn makes it necessary to concatenate the different columns into a single one with CSVs that have to be parsed by the query.
Since the number of columns in my IN statements will most likely not exceed 2 or 3 it may be easier to provide a separate str2tbl function for each case.

MAKE SURE TO USE BIND VARIABLES AND NOT STRING LITERALS PLEASE - that is most important.
But that's exactly what I'm trying to do. Since one cannot use a variable amount of bind variables I am tring to concatenate all data into a single bind variable. Or did I get that wrong?

Tue Oct 18, 03:26:00 AM EDT  

Blogger Thomas Kyte said....

@Anonymous -


ummm, Oracle supports as many bind variables as you want. If you want my opinion - I wrote this article for the "lets create comma separated strings because we like to" crowd.

But we can have as many or as few binds as you want.

The 'tool' you are working around is hibernate - which won't let you exploit many sql constructs - you yourself wrote some of them out above.

Remember - you yourself wrote:

However, there seems to be no ( or at least no easy way ) to place something like "WITH data AS" or "exec my_ctx_procedure( 'arg1, arg2, arg3' )" at the beginning of the generated SQL which is necessary for your later examples.


which seems to me to somewhat be at odds with your strange statement now of:

Fortunately Hibernate is flexible enough to support the rewriting of its SQL generation parts.

If in fact the latter is true and you were misspeaking before - then we would not be having this discussion - would we?


Am I right in my assumption that one cannot return a table with a variable amount of columns as the result of a function?

you would be mistaken. The functions can return a table of an object type - the object type can have as many (well, up to 1000 - but each of the 1000 could be an object itself...) attributes as you like.




ops$tkyte%ORA11GR2> create or replace type myScalarType
2 as object
3 ( x int,
4 y date,
5 z varchar2(30)
6 )
7 /

Type created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace type myArrayType as table of myScalarType
2 /

Type created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace function foo return myArrayType
2 as
3 l_data myArrayType := myArrayType();
4 begin
5 l_data.extend(2);
6 l_data(1) := myScalarType( 1, sysdate, 'hello' );
7 l_data(2) := myScalarType( 2, sysdate+1, 'world' );
8
9 return l_data;
10 end;
11 /

Function created.

ops$tkyte%ORA11GR2> show errors
No errors.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from table( foo );

X Y Z
---------- --------- ------------------------------
1 18-OCT-11 hello
2 19-OCT-11 world

Tue Oct 18, 03:46:00 AM EDT  

Anonymous Anonymous said....

But we can have as many or as few binds as you want.
Yes, of course - if you don't mind that for each number of binds you generate a different statement in the v$sqlarea. Exactly what we want to avoid, right?

The 'tool' you are working around is hibernate
Well, obviously this depends on the point of view one takes. Using a context variable or the "WITH data AS" prefix looks clearly like a hack or a workaround to me. The clear and straightforward solution would be to use a single bind for the IN list and Oracle doesn't seem to support that. Replacing the IN clause exactly at the time it is generated is easily supported by Hibernate and follows the simple engineering principle of temporal and spatial locality. Generating some upfront code to generate a side effect ( e.g. squeeze a variable into some context ) and exploiting the side effect sometime later instead of directly providing a parameter where it is needed doesn't look very clean to me. I do not doubt that it is somehow possible to do that trick with Hibernate, but I want to keep my code as clean as possible.

you would be mistaken. The functions can return a table of an object type - the object type can have as many (well, up to 1000 - but each of the 1000 could be an object itself...) attributes as you like.
Maybe I got it wrong, but I think that's not what I meant. I asked for a structure with a variable amount of columns, i.e. a return type that contains a single column for one call and and two columns for a different call. Our software uses IN statements in probably thousands of places and probably most or all of them do not use more than three columns. So I could just write three different functions with three different return types, but I wonder if there is a more generic approach.

Tue Oct 18, 04:25:00 AM EDT  

Blogger Thomas Kyte said....

... would be to use a single bind for the IN list and Oracle doesn't seem to support that. ...

but it does, that is done via the TABLE clause. If you bound an *object* it would be trivial.


ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 l_data sys.odciNumberList := sys.odciNumberList( 1,2,3,42,55 );
3 begin
4 open :x for select * from table( l_data );
5 end;
6 /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print x

COLUMN_VALUE
------------
1
2
3
42
55


single bind, set of data - no problem. Can hibernate do object types?



the output of a SQL query - the number of columns, their names, their types - is hard bound to the statement when it is compiled.

So, no, once parsed - the number, names and types of columns is fixed.

You'd have to set up a function that could return up to say "10" or whatever columns - and then select the ones you wanted - if you don't want to create more than one object type and table of that type.

Tue Oct 18, 04:39:00 AM EDT  

Anonymous Anonymous said....

Thanks for your help so far! Now I have a lot of options to try, my head is spinning a bit :-)
After spinning a while I came back to one of the most simple solutions, a statement with a fixed number of elements in the IN list, filling them with NULLs if necessary ( and providing a fallback solution for the rare case more elements are needed ).
Is there a performance penalty to pay or are there other pitfalls?

Tue Oct 18, 07:40:00 AM EDT  

Blogger Thomas Kyte said....

@Anonymous

the only pitfall would be/could be bind peeking.

If the first execution had an unusual number of binds (all of them NOT NULL or none of them NOT NULL) the estimated cardinality could be way off - resulting in a bad plan.

If bind peeking isn't a term you are familiar with, see

site:tkyte.blogspot.com bind peeking

on google search. It probably will NOT be a problem unless you have the exceptional case of "lots of not nulls when there are usually few not nulls" or "lots of nulls when there are usually few" on the FIRST hard parse of the query.

Tue Oct 18, 11:02:00 AM EDT  

Anonymous Anonymous said....

Hi Tom,

I would like to know how to generate a dynamic sql proc which has no inputs and only a cursor output ?

Thanks in advance ...!!!

Fri Feb 03, 06:08:00 AM EST  

Blogger Thomas Kyte said....

@anonymous

just do it?

create procedure p( x in out sys_refcursor )
as
begin
open x for 'whatever you would like';
end;

Fri Feb 03, 07:15:00 AM EST  

Blogger Donat Callens said....

another regexp solution:
SELECT regexp_substr('&string_list.', '[[:alnum:]_]+', 1, level) AS list_item
FROM dual
CONNECT BY regexp_instr('&string_list.', '[[:alnum:]_]+', 1, level) > 0 ;
It has the advantage of not hard coding the separation character, so you can use whichever you like.
As per the performance issue, varying IN lists do usually not have a high amount of items.

Fri May 24, 05:45:00 AM EDT  

Blogger Thomas Kyte said....

@Donat

str2tbl takes a deliminter in as a bind value as well, it defaults to ',' but can be anything you want and can be bound in.

where ever you see ',', you can mentally replace that with :x if you want (and in most places, it already is)

Fri May 24, 06:30:00 AM EDT  

Blogger Sean said....

Great post, used it again today. Is there anyway to have a dynamic "using" clause? A sort of related issue. My app allows users to submit an indeterminate number of filters, and thus I have an indeterminate number of bind variables. So far my technique has been to "pad" the query and binds with stuff like this"

...and 1 = :1

and then just having a dummy bind value of 1.

Is there a smarter way to do this?

Wed Jun 05, 10:55:00 AM EDT  

Blogger Thomas Kyte said....

@sean,

not sure what you mean.

do you mean like this:

http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html


it isn't a dynamic using clause, that stays static, but allows for binds to be effectively optimized away if they do not contribute to the predicate.

Wed Jun 05, 11:22:00 AM EDT  

Blogger Sean said....

Thanks for the quick reply Tom.

I somehow mis-understood the Oracle documentation on DBMS_SQL package. I need to fetch the results using bulk collect into a record collection, and thought I would be unable to use the DBMS_SQL.Parse and DBMS_SQL.Bind_Variable. All I needed to do was read a little further down the page and switch to NATIVE dynamic SQL using DBMS_SQL.TO_REFCURSOR.


I read this:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#BHCIBJBG

Specifically this line...
"In these situations, you must use native dynamic SQL instead of the DBMS_SQL package:

The dynamic SQL statement retrieves rows into records."

And took it to literally mean I could not use DBMS_SQL. The answer was literally two paragraphs down. I have read several thousand of Oracle documentation pages...just not all yet!

Thanks again.

Wed Jun 05, 02:46:00 PM EDT  

Anonymous Anonymous said....

is there anyway to use the view approach in 11.2.0.3 (set to 10g compatible mode) without using the separate context procedure?

The problem I have is that I have large group of developers who can use this. most of their code is 'stateless' and/or runs from cognos and/or from hybernate, etc... plus its alot of people to communicate with.

it wont necessarily be possible to have 2 calls and guarantee it comes from the same actual oracle session... can I build the context bind into the view somehow ?

Thu Aug 15, 02:00:00 PM EDT  

Blogger Thomas Kyte said....

@Anonymous

there is certainly a way guarantee it comes from the same oracle session

they

a) grab a connection
b) set the context
c) execute the query
d) retrieve the results
e) do whatever else they need to do
f) release the connection


they are doing this everytime they do a transaction that involves more than one statement already. If they are worried about messing it up put steps b and c into a stored procedure that returns a REF CURSOR (result set)

Thu Aug 15, 02:24:00 PM EDT  

POST A COMMENT

<< Home