How can I...
How can I bind an in-list. That is, I want the user to pick items on screen from a list box (unknown number) and then bind their values in.
My current quick answer to that:
The only truly accurate answer however? That would be “You cannot”. However, this for me falls into the category of “a trick”. I have other ways to answer this same question – but if someone walked up to me and say “Hey, how do you bind an in-list”, this is what I would draw on the whiteboard (today).
It is a neat “trick” that works in 9iR2 and above. It might need a cardinality hint. It might need some explanation for some people (to help them understand how it does what it does).
There are lots of little “hey, look – isn’t this cool” sort of things in the database. Little teasers. Quick and dirty examples. I like them, they get me thinking. They remind me of something else. They make me see something from a different point of view. They show me a trick that I might be able to apply somewhere else. They are not comprehensive. They are not 100% complete. They might not apply to me in my circumstances.
But that doesn’t make them inherently evil.
I learn something new everyday. Here is my new thing for today… How to create a text index and schedule the synchronization in one step. I did not know we could do that (new 10g feature I missed, read over in the guides). Glad I caught it (it’ll make me more accurate myself in the future). I don’t need it right now, but I’ll file it away and next time it comes up – hopefully I’ll remember it.
If I need to research it, or really dig into it – I’ll do that – but for now, someone just pointing out “hey, look what I just stumbled on” is pretty nice.
What am I trying to say? There are lots of ways to disseminate information. I personally found the idea of little nuggets – tricks and tips – intriguing. What are some of the most attended sessions at conferences? (tips and tricks). What are some of my most popular sessions (tips and tricks).
Time will tell whether a certain resource should stay or go. There is room for ideas of all sizes – small, medium and large. Comprehensive is good – but snippets are not necessarily evil.
My current quick answer to that:
ops$tkyte@ORA10GR2> variable txt varchar2(25)
ops$tkyte@ORA10GR2> exec :txt := '1,2,3,5,42,104'
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> with bound_inlist
2 as
3 (
4 select
5 substr(txt,
6 instr (txt, ',', 1, level ) + 1,
7 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
8 as token
9 from (select ','||:txt||',' txt from dual)
10 connect by level <= length(:txt)-length(replace(:txt,',',''))+1
11 )
12 select *
13 from all_users
14 where user_id in (select * from bound_inlist);
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 30-JUN-05
OPS$TKYTE 104 20-JAN-06
The only truly accurate answer however? That would be “You cannot”. However, this for me falls into the category of “a trick”. I have other ways to answer this same question – but if someone walked up to me and say “Hey, how do you bind an in-list”, this is what I would draw on the whiteboard (today).
It is a neat “trick” that works in 9iR2 and above. It might need a cardinality hint. It might need some explanation for some people (to help them understand how it does what it does).
There are lots of little “hey, look – isn’t this cool” sort of things in the database. Little teasers. Quick and dirty examples. I like them, they get me thinking. They remind me of something else. They make me see something from a different point of view. They show me a trick that I might be able to apply somewhere else. They are not comprehensive. They are not 100% complete. They might not apply to me in my circumstances.
But that doesn’t make them inherently evil.
I learn something new everyday. Here is my new thing for today… How to create a text index and schedule the synchronization in one step. I did not know we could do that (new 10g feature I missed, read over in the guides). Glad I caught it (it’ll make me more accurate myself in the future). I don’t need it right now, but I’ll file it away and next time it comes up – hopefully I’ll remember it.
If I need to research it, or really dig into it – I’ll do that – but for now, someone just pointing out “hey, look what I just stumbled on” is pretty nice.
What am I trying to say? There are lots of ways to disseminate information. I personally found the idea of little nuggets – tricks and tips – intriguing. What are some of the most attended sessions at conferences? (tips and tricks). What are some of my most popular sessions (tips and tricks).
Time will tell whether a certain resource should stay or go. There is room for ideas of all sizes – small, medium and large. Comprehensive is good – but snippets are not necessarily evil.
posted by Thomas Kyte at 11:36 AM
27 Comments:
Excellent tip, as usual. Though you're right, it could prove a little tricky to read.
We battled with a similar problem in our environment, where we always wrap our DB access with functions.
How does one reduce duplication and use a single query for returning the main result set, whilst allowing for different ways of specifying which data to retrieve?
http://robertbaillie.blogspot.com/2005/09/in-thing-simplified.html
Add a function to turn a comma separated list into a table of ids and you've got an alternative approach. Far more code, admitted, but the clarity kind of works for me...
rob -
Yes, that is a lot like my str2table script I used to use/suggest all of the time.
Interesting to re-read that page and watch the comments make the solution "age" over time (as we added pipelined functions and such in 9i..)
we could actually use a view that accesses an application context to do effectively the same thing ( remove the need to have the WITH subquery present). it would require the application to set the context and then run the query.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
A useful variant on Tom's "in-list" query is this "like-in" query:
set autotrace trace;
variable txt varchar2(50);
exec :txt := 'A%,S%,X%';
with bound_inlist 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 )
connect by level <= length(:txt)-length(replace(:txt,',',''))+1
)
select b.*, a.*
from all_users a, bound_inlist b
where a.username like b.token escape '\'
order by a.username;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
806 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
4 rows processed
Oddly enough, I was working on something similar this very morning. However, the variant I had went like this:
set autotrace trace;
variable txt varchar2(50);
exec :txt := 'A%,S%,X%';
with bound_inlist as
( select
substr(txt,
instr(txt, ',', 1, rownum ) + 1,
instr(txt, ',', 1, rownum+1) - instr(txt, ',', 1, rownum) -1)
as token
from
( select ','||:txt||',' txt from dual ),
( select * from dual
connect by level <= length(:txt)-length(replace(:txt,',',''))+1 )
)
select b.*, a.*
from all_users a, bound_inlist b
where a.username like b.token escape '\'
order by a.username;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
37 consistent gets
0 physical reads
0 redo size
806 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
4 rows processed
Tom's version, moving the "connect by" out of the deepest sub-select, results in fewer "consistent gets", so I think that's what I'll be using in the future.
Thank you, Tom, for teaching me how to use "autotrace" over at AskTom.
Bob Shepard
careful wite that LIKE one above.
it can result in duplicates if a string is LIKE two of the entries. In your example with A%, S%, X% - it would not be because a single string cannot start with A and S and X.
But change it to %A%, %S% and you can easily get dups. (just a caution)
Hi,
When i look at your coding tip - i do not understand it how it works - but it gives me some kick to look at the with clause and to see your snippet as answer a collaege asked me some weeks ago!
For me some small interesting pieces makes a lot of appetite to go deeper;
Karl
good example! i tend to use global temporary table before for such stuff, now i think this will be my preferable solution
Haven't tested the 'sync on commit' with 10.2, although one of the biggest problems with ctxsrv in the past, was that high frequency syncs degraded the index quickly and they often ballooned in size.
We did a test back then on ctxsrv versus 5 minutes syncs on an index underpinning a table getting around 1000 dml's per minute. ctxsrv did very very poorly in comparison.
hopefully 10.2 has some more smarts (although there's a metalink note that warns sync-on-commit can lead to fragmentation and index size issues).
bottom line - as with any feature...test, test, test, test
cheers
Connor
Tom,
Totally agree with you about the tips and tricks - in fact the "connect by level <" SQLplus idiom is really recursive magic - I think SQLplus is the one language most capable of magic - especially with analytics.
When I was a predominantly C programmer, I really enjoyed writing recursive code - in fact my favorite program was a 40 line recursive program I wrote for a C programmiong contest. These small ones are what capture "insight" - which is such a wonderful thing.
Moving from C to SQL - the set oriented magic stuff in SQL truly makes life much more interesting - I write a "my favorite SQL code" every alternate week - very rewarding. Thanks to you Tom and asktom for propagating the SQl stuff and all else.
Haven't tested the 'sync on commit'
I would not use that one, my point about this one was I can create and schedule the synchronization on my schedule in one step. Had I been asked that question "how to sync the index", I would have said
o create the index
o create a procedure to sync the index
o use dbms_jobs or the scheduler to have it done
Now it is "create the index and specify the interval of course :)"
It was the pointer to a fact, a tidbit - that I didn't know about.
One of my favorite Hotsos conference presentations was by this goofy person from a country far far far away. It was 9i features you might not know about. It was just bullet after bullet of "stuff". Nothing in detail - just snippets. write them down when they interest you - research them later.
My favorite snippet from that one was "for update wait N" - select for update that waits for some number of seconds and then comes back. I'm writing that down when the presenter (yes, that was Connor) pointed out that he picked up that nugget from me. I had forgotten I had known that :) Little 'tickles' like this sometimes are needed to remind us of something we forgot we knew....
wow that's some awesomely smart SQL there !
I don't recall anyone ever saying that a 'nugget' or a 'teaser' or a snippet is "inherently evil". Publishing a wrong snippet, or a teaser that is technically deficient... that's where we need to be careful. But yours is the first time I've seen the "evil" word used in the present context.
And whilst you, with your vast development experience, are in a position to weigh and measure the worth of 'partial teasers', you might spare a thought for those lacking your extensive experience.
I'm glad that "If [you] need to research it, or really dig into it – [you]’ll do that". Good for you. But most people won't; most people don't. Most people will believe what they read, especially if it has the imprimatur of Tom Kyte on it.
You have a bigger responsibility in this regard than most of us, whether you like that or not.
Howard J. Rogers said....
I don't recall anyone ever saying that a 'nugget' or a 'teaser' or a snippet is "inherently evil".
ummm maybe that's because no one ever said that and Tom wasn't quoting anyone ???
He didn't have to quote someone, Anonymous.
It's a matter of basic English. If Tom writes "But that doesn’t make them inherently evil", then it is a clear implication that someone, somewhere has said otherwise. That is the function of the word "but", after all.
I realise that in a world where capital letters are seemingly dispensible and l33t-sp33k is considered amongst the highest forms of human intellectual achievement this point might pass some by. But it's true nevertheless.
OK then, I'll say it. Some tips are inherently evil.
1. Morally bad or wrong; wicked:
Exposing hashed values of default passwords. Hmmm, not evil if in a patch...
2. Causing ruin, injury, or pain; harmful:
Certain pga_aggregate_target advice
3. Characterized by or indicating future misfortune; ominous:
Setting undocumented parameters.
4. Bad or blameworthy by report; infamous:
See 2 and 3.
5. Characterized by anger or spite; malicious:
Publishing wrong metalink information on usenet.
tfllh
OK then, I'll say it. Some tips are inherently evil.
Absolutely, and when I see them, I call them on it.
Hi Tom,
I love this logic - but to be honest - it is very hard to remember the sequence of INSTR, etc. to parse out the token as you have done it...
To make this much easier - how about encapsulating both the counting of characters and the parsing like so:
SQL> CREATE OR REPLACE FUNCTION charcount (
2 p_string IN VARCHAR2
3 , p_char IN VARCHAR2
4 )
5 RETURN INTEGER DETERMINISTIC AS
6 BEGIN
7 RETURN LENGTH (p_string) - (LENGTH (REPLACE (p_string
8 , p_char
9 , ''
10 )));
11 END charcount;
12 /
Function created.
SQL>
SQL> CREATE OR REPLACE FUNCTION parsestring (
2 p_string IN VARCHAR2
3 , p_delimiter IN VARCHAR2
4 , p_position IN INTEGER
5 )
6 RETURN VARCHAR2 DETERMINISTIC AS
7 v_txt VARCHAR2 (4000);
8 BEGIN
9 v_txt := p_delimiter || p_string || p_delimiter;
10 RETURN SUBSTR (v_txt
11 , INSTR (v_txt
12 , p_delimiter
13 , 1
14 , p_position
15 ) + 1
16 , INSTR (v_txt
17 , p_delimiter
18 , 1
19 , p_position + 1
20 ) - INSTR (v_txt
21 , p_delimiter
22 , 1
23 , p_position
24 ) - 1
25 );
26 END parsestring;
27 /
Function created.
SQL>
SQL> VARIABLE txt VARCHAR2(255);
SQL>
SQL> VARIABLE txt VARCHAR2(255)
SQL> EXEC :txt := 'EMP,DEPT,DUAL';
PL/SQL procedure successfully completed.
SQL>
SQL> WITH bound_inlist AS
2 (SELECT parsestring (:txt
3 , ','
4 , LEVEL
5 ) AS token
6 FROM DUAL
7 CONNECT BY LEVEL <= charcount (:txt, ',') + 1)
8 SELECT owner, table_name, tablespace_name
9 FROM all_tables
10 WHERE table_name IN (SELECT token
11 FROM bound_inlist)
12 /
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS DUAL SYSTEM
SCOTT DEPT SYSTEM
SCOTT EMP SYSTEM
I would not use plsql called from sql here - not necessary, adds overhead, makes the implementation "harder"
I do not find the resulting sql to be so unreadable as to not be readable...
I would not use the plsql here, no. If I were to use plsql - it would be as a pipelined function (search asktom for str2tbl for an example).
I have to disagree with Tom and agree with Anonymous. I can see how the parsestring function can be reused easily over and over again.
Plus the parsestring is much easier to read in a SELECT statement.
I'll say it again - if you are going to implement this in PLSQL, use a pipelined function - don't even use this trick.
If you want performance, don't call plsql from sql if you don't have to (and you don't have to here).
If you want a function, by all means use a PIPELINED function.
ok, ok... point taken.
How about burning parsestring and charcount into the kernel like "SUBSTR" and "INSTR"? That would alleviate your PL/SQL concerns - and provide very useful functions to all Oracle customers...
Just a thought. I'll request an enhancement on metalink :)
Tom,
would you please point me to some documentation that explains the 'with' statement in the beginning of the query?
thanks,
Christoph
The results of searching for subquery factoring on the otn docs...
The "with" clause
Hi
Can we do something like this
CREATE TABLE Dept
2 (
3 deptno NUMBER(3),
4 dept_name VARCHAR2(30),
5 dept_loc VARCHAR2(50)
6 );
SELECT * FROM Dept;
100 SALES EDINBURGH
200 ACCOUNTING LONDON
300 FINANCE GLASGOW
400 EXECUTIVE BRISTOL
CREATE TYPE t_dept_loc IS TABLE OF VARCHAR2(100);
CREATE OR REPLACE PROCEDURE p_print_dept_details (pt_dept_loc IN t_dept_loc
AS
BEGIN
FOR i IN (SELECT deptno
,dept_name
,dept_loc
FROM Dept
WHERE dept_loc IN (SELECT * FROM TABLE(pt_dept_loc)))
LOOP
dbms_output.put_line('****************************');
dbms_output.put_line('Department No : '||i.deptno);
dbms_output.put_line('Department Name : '||i.dept_name);
dbms_output.put_line('Department Location: '||i.dept_loc);
END LOOP;
END;
DECLARE
lt_dept_loc t_dept_loc := t_dept_loc('EDINBURGH','LONDON');
BEGIN
p_print_dept_details(lt_dept_loc);
END;
Please let me know your comments
Thanks
Vonid
yes, you can select from any collection, doesn't matter if the collection is a local varible or a variable returned by a function
Using this trick, I am trying to join such an "in-list" to a table FROM another table.
Giving this test case:
CREATE TABLE Project
(
proj_id NUMBER(5),
proj_name VARCHAR2(50)
);
INSERT INTO Project VALUES (1, 'BIG project');
INSERT INTO Project VALUES (2, 'small project');
CREATE TABLE Emp
(
emp_id NUMBER(5),
emp_name VARCHAR2(50)
);
INSERT INTO Emp VALUES (1, 'John');
INSERT INTO Emp VALUES (2, 'Bill');
INSERT INTO Emp VALUES (3, 'Bob');
INSERT INTO Emp VALUES (4, 'Tom');
CREATE TABLE Team
(
tm_id NUMBER(5),
tm_name VARCHAR2(50),
tm_project NUMBER(5),
tm_members VARCHAR2(200)
);
INSERT INTO Team VALUES (1, 'Team 1', 1, '1,3,4');
INSERT INTO Team VALUES (2, 'Team 2', 1, '');
INSERT INTO Team VALUES (3, 'Team 3', 1, '1,2');
INSERT INTO Team VALUES (4, 'Team 4', 2, '2');
I want to get this result:
TM_NAME PROJ_NAME EMP_NAME
------- ----------- --------
Team 1 BIG project John
Team 1 BIG project Bob
Team 1 BIG project Tom
Team 3 BIG project John
Team 3 BIG project Bill
Team 4 small project Bill
The real situation is much more complexe but this is a good illustration of the relation to establish.
I have to split the "tm_members" column of each Team in order to join each member with the Emp table.
How could I achieve this?
select
t.tm_id,
substr(txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
as token
from
Team t,
(select ','||t.tm_members||',' txt from dual)
connect by level <= length(t.tm_members)-length(replace(t.tm_members,',',''))+1;
Is giving me:
Error: ORA-00904: "T"."TM_MEMBERS": invalid identifier
Why QUICK always go well with DIRTY. Does quick means it is obviously dirty.
Tom,
same question as the above person who asked with some obviously bad syntax in trying to refer to t.tm_members out of scope. If I want to use this trick on a table with multiple rows, can it be done.
For example, my table has:
id path num_segs
-- ------ --------
1 A,B,C 3
2 C,D,E,F 4
I want a result set of:
1 A
1 B
1 C
2 C
2 D
2 E
2 F
To do this I tried:
with s as (select id, ','||path||',' txt, num_segs from mytab where rownum<3)
select id, level seg,
substr(txt,
instr(txt, ',', 1, level) + 1,
instr(txt, ',', 1, level+1) - instr(txt, ',', 1, level) -1) as node
from s
connect by level <= num_segs
order by 1, 2
I limited to the first two rows of my table. Works fine when rownum=1 becuase the set only has 1 row. With 2 rows I get 1 record for the first node, 2 for the second, 4 for the third, and 8 for the 4th. Basically it I get 2 to the power of the level records. If I put three records in the source, then I get 3 to the power of level for each record.
Can I use this trick or will I have to use a function to do this?
POST A COMMENT
<< Home