Jon,
It's great that you asked this, because I've been wondering how to do it
too. It's all in "Programming the Perl DBI" of course: page 126.
Your intuition is correct, the PL/SQL stored procedures should be returning
the value(s), not printing them. The PL/SQL guys at your company know how to
do this, they're probably just messin' with you.
Then you can use a dbi cursor like this:
======================================================================
my $result;
my $returnCode;
my $isbn = '1234123412';
my $csr_hyphenIsbn = $dbh->prepare ("BEGIN
bkpower.hyphenisbn_proc(?,?,?); END;");
$csr_hyphenIsbn->bind_param(2, $isbn);
$csr_hyphenIsbn->bind_param_inout(1, \$result, 13);
$csr_hyphenIsbn->bind_param_inout(3, \$returnCode, 1);
$csr_hyphenIsbn->execute();
=========================================================================
hyphenIsbn_proc is the stored procedure and it takes 3 parameters. The first
and third are output parameters, the 2nd is an input, hence 3 placeholders.
These placeholders are bound with bind_param & bind_param_inout.
The first argument to bind_param & bind_param_inout is the position of the
stored procedure argument. The second argument is the bound variable (note
that sometimes it's a reference). The third argument is the maximum length
(only needed for bind_param_inout).
Not bad!!! You should buy a copy of the book for yourself, so that the guy
who invented this cool stuff gets a few more bucks in royalties.
Regards,
Tim (no relation) Helck
-----Original Message-----
From: Dan Horne [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 05, 2001 4:12 PM
To: 'Jon Williams'; [EMAIL PROTECTED]
Subject: RE: DBI With Oracle & PL/SQL
A simple, but ugly solution
call_function.sql:
set head off
set pages off
set serverout on size 1000000
<exec stored_proccedure>
exit
and then in your perl script
$result= `sqlplus -s $AO \@$SQL/tr_get_batch_no.sql`;
where $AO is your user/password combo. Alternatively, you may be able to
pass back a collection type (varray or nested table) and stick with DBI,
but that would mean your PL/SQL developers would need to change from
dbms_output
Dan
-----Original Message-----
From: Jon Williams [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, September 06, 2001 12:51 AM
To: [EMAIL PROTECTED]
Subject: DBI With Oracle & PL/SQL
Have been using DBI with MySQL & Oracle for some time
now. Recently, the powers that be (i.e my bosses)
decided to use PL/SQL stored procedures for some of
our rather complex queries. The PL/SQL guys are
writing the query results to DBMS_OUTPUT. I cannot
get to the query results via standard DBI syntax (i.e
fetchrow_hashref or fetchrow_arrayref etc...). To get
around this I've been using the 'dbms_output_get'
method to retrieve query results:
my @results = $db_handle -> func ( 'dbms_output_get'
);
Is this the preferred method of retrieving results
from a PL/SQL procedure? Should the PL/SQL guys be
giving me the results in some other fashion instead of
writing to DBMS_OUTPUT? I scanned through a friends
copy of "Programming the Perl DBI" and all I could
find on the subject of DBMS_OUTPUT was a statement
that said DBMS_OUTPUT is usually reserved for
informational or trace messages from PL/SQL
procedures.
Any help you could provide would be great.
Thanks.
Jon.
__________________________________________________
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
