How can I...
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
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
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.