RE: Collections in PL/SQL

From: Susan E Teague <Susan_at_fileFRENZY.com>
Date: Fri, 19 Jan 2001 13:45:27 -0500
Message-Id: <10746.127177@fatcity.com>

Thanks so much,
I thought about using dynamic sql last night. I am using ref cursors, which state that you cannot use dynamic sql with them...Can you return a cursor in a function that's not a ref cursor? Make sense? Thanks!
Susan

-----Original Message-----
From: Jamadagni, Rajendra [mailto:rajendra.jamadagni_at_espn.com] Sent: Friday, January 19, 2001 1:30 PM
To: 'ORACLE-L_at_fatcity.com'; 'susan_at_filefrenzy.com' Subject: RE: Collections in PL/SQL
Importance: High

Well I do this kind of thing all the time .... only that I use dynamic SQL so that I can plug in the param list for the in clause. I usually pass parameters in the format 'a,b,c,d,e,f,g' and then run a simple routine to translate them in to "('a','b','c','d')" minus the double quotes of course etc.

This can then be easily made part of a dynamic SQL that I construct. It is working flawlessly for more than 2 years for me. To 'quote' the params I use something like ...

vsz_ParamString := '(' || '''' || replace(pi_instr, ',', ''''||','||'''') || '''' || ')';

This replaces every comma with ',' in the input string. and encloses them in () szSqlStmt := szSqlStmt || ' col_name in ' || vsz_ParamString ;

If you want to use dynamic SQL, this is the easiest way.

HTH
Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
QOTD: Any clod can have facts, but having an opinion is an art ! Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you.

Received on Fri Jan 19 2001 - 12:45:27 CST