Ron,
thank you very much, that sounds great. Slowly it drove me crazy because I
didn't found any documentation how to do this.
Kind regards,
Frank
"Reidy, Ron" <[EMAIL PROTECTED]>
22.06.2004 15:58
An
<[EMAIL PROTECTED]>, "Hardy Merrill" <[EMAIL PROTECTED]>
Kopie
<[EMAIL PROTECTED]>
Thema
RE: Antwort: Re: stored precedures with array as parameter
You will need to populate the table using code similar to this:
Method 1:
my $sql = qq{
DECLARE
v my_tagnames;
BEGIN
};
my $i = 1;
foreach (@arry)
{
$sql .= " v($i) := '$_';\n";
$i++;
}
$sql .= "END;"
$sth->prepare($sql);
etc.
Method 2:
my $arry_str = join(",",@arry);
$sth->prepare(qq{
DECLARE
v my_tagnames;
BEGIN
dbms_utility.comma_to_table(list => :str, tablen => :len, tab => v);
-- the rest of your code
END;
});
my $len = 0;
$sth->bind_param(':str', $arry_str);
$sth->bind_param_inout(':len', \$len, 5);
$sth->execute;
etc.
In my opinion, Method #2 is the best because it uses bind variables.
--
Ron Reidy
Sr. DBA
Array BioPharma, Inc.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Mon 6/21/2004 11:47 PM
To: Hardy Merrill
Cc: [EMAIL PROTECTED]
Subject: Antwort: Re: stored precedures with array as
parameter
my problem is not the stored procedure call but the handling of the Oracle
type definition in perl.
TYPE my_tagnames IS TABLE OF varchar2(200) INDEX BY BINARY_INTEGER;
It's not clear to me how or even if I can call a stored procedure with
such input values.
Thanks for any help in advance !!!
regards,
frank
"Hardy Merrill" <[EMAIL PROTECTED]>
21.06.2004 19:28
An
<[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
Kopie
Thema
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
This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not
the
intended recipient, please be aware that any disclosure, copying,
distribution
or use of the contents of this information is prohibited. Please notify
the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
