"Michael A Chase" <[EMAIL PROTECTED]> wrote in message Mahogany-0.64.2-3260-20020726-082723.00@MCHASE-COMPAQ">news:Mahogany-0.64.2-3260-20020726-082723.00@MCHASE-COMPAQ... > On Fri, 26 Jul 2002 11:16:00 +0530 "Sundarraj, Senthil Kumar (Cognizant)" <[EMAIL PROTECTED]> wrote: > > > I have a stored procedure in oracle accepting array as an input > > parameter and array as an output parameter. how to call this procedure thru > > perl DBI calls.any help at the earliest will be appreciated.
I recently went through as much info sources as possible about this peculiar issue. My problem is that I've a PL/SQL function, taking a string as input parameter, and returning a table of strings. I'd like to map the Oracle "table of varchar2" to a perl array. Interesting things I've found are the following : - comp.databases.oracle.server thread (dated 1997!) where s.o. explains this is possible in Pro*C, but you have to deal yourself with the length of the array. Seems to be possible in any 3gl... what about in perl ? (http://groups.google.fr/groups?q=pl/sql+function+return+type&hl=fr&lr=&ie=U TF-8&oe=UTF-8&selm=345cc77c.13865397%40newshost&rnum=1) - A db-users mailing list thread, written by Tim Bunce (DBD::Oracle main coder, AFAIK), dated 1997, where he says this *ought to be* possible, but it was not implemented at the time... (http://www.geocrawler.com/archives/3/183/1997/7/0/1012264/) - As you noticed, the Todo list inside the DBD-Oracle-1.12 (08/29/2001), says it hasn't been implemented yet. Things I went in to circumvate this issue are : - A pseudo-code example in dbi-users mailing list, dated 1997 as well (Geez ! Was that the only year when people needed to get Oracle array from perl DBI or did I miss stg, and a valuable solution was found since that time ?) : kind of "Very easy to do, but I've never done it..." idea :-/ to make PL/SQL pick the element you need instead of picking up the whole table and then work on it from perl (http://www.bitmechanic.com/mail-archives/dbi-users/Feb1997/0048.html). This doesn't meet my needs since I have to get the whole table in a single call to my stored function. - A hint to use the DBD::Oracle::func() method, which hooks directly onto the database specificities, but sure enough wouldn't be portable... Anyway, although this would help to call my PL/SQL function more easily, I didn't find anything about how to map a varchar2 oracle table to a perl array... or at least a perl array reference. - Use the infamous DBMS_OUTPUT.enable() & DBMS_OUTPUT.put() instead, to print out the info needed and to get strings from Oracle in perl DBI, instead of getting a table as return data type. (http://www.geocrawler.com/archives/3/184/1999/4/0/1547753/) These last two ideas where taken from the post archived at Geocrawler, and were from you, Michael ! > Handling non-scalar arguments is on the TODO list for DBD::Oracle, but it > isn't fully ready yet. You might be able to do it with Oracle::OCI, but I > haven't used it and can't tell you how. > > Another approach would be to use an adapter package that can convert scalar > lists to arrays and back. If the arrays are small enough, an anonymous > block might work. I don't have enouch time to do this, and if I picture it right, this should be the idea in the post archived at bitmechanic.com. But as you (Michael) was the one that posted the DBMS_OUTPUT solution, and as I couldn't get any info about the func() method on perldoc.com, can you tell me wether the func() method would help me in any way to do what I need, and where I could get more documentation about it ? > Give a hobbit a fish and he eats fish for a day. > Give a hobbit a ring and he eats fish for an age. BTW, Michael, your tagline is great for the knowing ! ;-) Sincerly, PHleX
