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
