Avi,
I had the same question a few months ago, and people in the list were kind
enough to give me a lot of information. Based on what I learned, I wrote the
following short script which illustrates the basic features of using
DBMS_OUTPUT.PUT_LINE. You must use dbh->func() which unfortunately is not
really discussed in "Programming the Perl DBI". I think this script is
self-explanatory but if you have any further questions, feel free to ask.
Tim
============================================================================
========
#!/opt/GNUall/bin/perl
# How to use dbms_output.put_line with Perl DBI
use strict;
use Getopt::Std;
use DBI;
############################### DB Connection
##################################
my $dbh = DBI->connect('dbi:Oracle:Oradev5','bkpower','bkpower'
,{RaiseError=>1 ,AutoCommit=>0}) || die "Cannot Connect: $DBI::errstr";
### DON'T FORGET THIS LINE:
$dbh->func(1000000, 'dbms_output_enable') || die $dbh->errstr;
################## Prepare SQL Statements ################################
###### getDate #####
my $SQL_getDate = q
{
DECLARE
today varchar2(20);
BEGIN
SELECT sysdate into today from dual;
DBMS_OUTPUT.PUT_LINE('Today is ' || today);
END;
};
my $csr_getDate = $dbh->prepare ($SQL_getDate);
#########################################################
###### getCoders #####
my $SQL_getCoders = q
{
DECLARE
user varchar2(20);
CURSOR getUser is
SELECT username
FROM aud.security
WHERE department = 'MIS';
BEGIN
OPEN getUser;
LOOP
FETCH getUser INTO user;
EXIT WHEN getUser%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(user || ' is a programmer.');
END LOOP;
CLOSE getUser;
END;
};
my $csr_getCoders = $dbh->prepare ($SQL_getCoders);
#########################################################
### If you KNOW there's only one statement:
$csr_getDate->execute();
my $text = $dbh->func('dbms_output_get');
print "\n$text\n\n";
### Otherwise:
$csr_getCoders->execute();
my @text = $dbh->func('dbms_output_get');
for my $i (@text) {
print "$i\n";
}
$dbh->disconnect();
exit 0;
============================================================================
==========
-----Original Message-----
From: Avi Vainshtein [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 13, 2001 11:11 AM
To: [EMAIL PROTECTED]
Subject: is it possible to execute a package.procedure from Oraperl.pm
script ?
Hello
I'm trying to execute an PL/SQL procedure from an Oraperl script, but i
get the following error :
for example :
....
&ora_do($unm1_lda,"EXECUTE DBMS_OUTPUT.PUT_LINE('phooo')");
....
ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute)
Regards,
Avi Veinshtein
Oracle DBA
www.telrad.com <http://www.telrad.com>
