RE: stored precedures with array as parameter

[Steve Baldwin]

PL/SQL Tables (arrays) are not supported by DBD::Oracle.  In this particular
situation, one solution may be to do something like this :

CREATE OR REPLACE PACKAGE mystorage  IS
  TYPE my_tagnames IS TABLE OF varchar2(200) INDEX BY BINARY_INTEGER;

prmTagNames      my_tagnames ;
prmTagValues     my_tagnames ;

FUNCTION  insert_sm_dbi (msgid          IN varchar2,
                        sf_info         OUT VARCHAR2
                       ) RETURN NUMBER;

FUNCTION  insert_sm  (msgid             IN varchar2,
                        tagnames        IN my_tagnames,
                        tagvalues       IN my_tagnames,
                        sf_info         OUT VARCHAR2
                       ) RETURN NUMBER;

 
END mystorage;
/

The package body for insert_sm_dbi would look something like this :

FUNCTION  insert_sm_dbi (msgid          IN varchar2,
                        sf_info         OUT VARCHAR2
                       ) RETURN NUMBER AS
BEGIN
  RETURN insert_sm (msgid, prmTagNames, prmTagValues, sf_info) ;
END ;

In your DBI code, you would do something like this :

#
# Initialise the global arrays
#
$dbh->do(q(
  BEGIN
    mystorage.prmTagNames.DELETE ;
    mystorage.prmTagValues.DELETE ;
  END ;)) ;
:
#
# Populate the global arrays
#
my $sth = $dbh->prepare(q(
  BEGIN
    mystorage.prmTagNames(:idx) := :nam ;
    mystorage.prmTagValues(:idx) := :val ;
  END ;
)) ;
for (my $i = 0 ; $i < scalar @name_arr ; $i++) {
  $sth->bind_param(':idx', $i) ;
  $sth->bind_param(':nam', $name_arr[$i]) ;
  $sth->bind_param(':val', $value_arr[$i]) ;
  $sth->execute ;
}
#
# Call the wrapper function
#
$sth = $dbh->prepare(q(
  BEGIN
    :ret := mystorage.insert_sm_dbi (:msgid, :sf_info) ;
  END ;
)) ;
$sth->bind_param(':msgid', $msgid) ;
$sth->bind_param_inout(':sf_info', \$sf_info, $max_size_of_sf_info) ;
$sth->bind_param_inout(':ret', \$ret, 40) ;
$sth->execute ;

This is all of the top of my head, so there are bound to be some typos, but
hopefully you get the general idea.

Steve

-----Original Message-----
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 June 2004 12:29 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: stored precedures with array as parameter

Have you read the DBD::Oracle perldocs?  I haven't done that myself, but
I'm almost certain the DBD::Oracle perldocs describe how to invoke a
stored procedure.

>>> <[EMAIL PROTECTED]> 06/21/04 11:11AM >>>
Hi all,

I'm looking for a way to call stored precedures directly with one or
more 
arrays as input parameter via DBD:Oracle interface. 
Does anybody has a hint for me how I can do this ?

Let me say that the function that I would like to call looks similar to

the one below:

CREATE OR REPLACE PACKAGE mystorage  IS
  TYPE my_tagnames IS TABLE OF varchar2(200) INDEX BY BINARY_INTEGER;

FUNCTION  insert_sm  (msgid             IN varchar2,
                        tagnames        IN my_tagnames,
                        tagvalues       IN my_tagnames,
                        sf_info         OUT VARCHAR2
                       ) RETURN NUMBER;

 
END mystorage;
/

How should the prepare statement look like and how can I forward the 
array's to the function ?

Thank for any help in advance !

kind regards,

frank