Re: Passing Arrays from Perl to Stored Procedures

First tell us what database library do you use. In the Perl standard DBI calling stored procedures is not defined:

Calling stored procedures is currently not defined by the DBI.
Some drivers, such as DBD::Oracle, support it in non-portable
ways.
See driver documentation for more details.)

Sometimes you can call your procedure as a function in a select statement: select StoredFunc(?) from dual. And bind the parameter as usual.

I don't know all the libraries but I believe in most there is no method to pass an generic array as a parameter. Of course - if you are using PostreSQL stored procedures in perl than you can pass parameters as usuall.

Comment on Re: Passing Arrays from Perl to Stored Procedures Download Code

Replies are listed 'Best First'.
Re: Re: Passing Arrays from Perl to Stored Procedures by TheYoungMonk (Sexton) on Mar 07, 2003 at 09:46 UTC
I am also not very familiar with all libraries, but i guess calling stored procedures is supported by using DBI and DBD::Oracle. But if at all there is no method to pass arrays to a SP, is there no other alternative way which can solve the problem mentioned in an optimized manner ? how about any obfuscations which can aim at this...curious.. Expecting some kind of enlightenment from the monk community to resolve this issue...	[reply]
Re: Re: Re: Passing Arrays from Perl to Stored Procedures by iguanodon (Priest) on Mar 07, 2003 at 10:58 UTC
I'm still not really sure what you're trying to do, but maybe bind_param_array is what you're looking for. Note this statement from the docs, though: Note that the default implementation currently only supports non-data returning statements. If you want to get data back from Oracle stored procedures, you need to call bind_param_inout but this method doesn't take an array as an argument. If the values you want to pass are in an array, just pass the array elements.	[reply]
Re: Re: Re: Passing Arrays from Perl to Stored Procedures by zby (Vicar) on Mar 07, 2003 at 10:12 UTC
If you are using Oracle then the most effective way would be to use the Oracle data importing tool to do the job. I can't racall the exact name of it - but I remember it can import data in a few formats (and CSV is one of them) and do the most needed conversions. Update: I've overlooked that you need to call the procedure to do the additionall computation.	[reply]