Thanks to all for help. This one actually solved my problem apologies if I sent to the wrong list. I subscribe to both users and dev H > -----Original Message----- > From: Fleming, Gordon (Bolton) [mailto:[EMAIL PROTECTED]] > Sent: Monday, July 16, 2001 8:29 AM > To: '[EMAIL PROTECTED]' > Subject: RE: Oracle Stored Proc challenge > > > This is not my example but I came across it awhile ago. Maybe this will > help, I haven't tested it: > > CREATE OR REPLACE PACKAGE emp_package AS > TYPE t_emp_cursor IS REF CURSOR; > PROCEDURE employes_by_job( > p_job IN emp.job%TYPE, > employees_cursor IN OUT t_emp_cursor > ); > END emp_package; > > CREATE OR REPLACE PACKAGE BODY emp_package AS > PROCEDURE employees_by_job( > p_job IN emp.job%TYPE, employees_cursor IN OUT t_emp_cursor > ) IS > BEGIN > OPEN employees_cursor FOR SELECT * FROM emp WHERE emp.job = p_job; > END employess_by_job; > END emp_package; > > # > # Example perl code > # > > use strict; > use DBI; > use DBD:Oracle qw(ORA_RSET); > > my $dbh = DBI->connect( "dbi:Oracle", "scott", "tiger", {RaiseError=>1} ); > my $employees_cursor; > my $sql = > "BEGIN " . > "scott.emp_package.employees_by_job(:p_job, :p_employees_cursor); " . > "END"; > my @row; > > my $sth = $dbh->prepare($sql); > > $sth->bind_param( ":p_job", "CLERK" ); > $sth->bind_param_inout( > ":p_employees_cursor", \$employees_cursor, 0, { ora_type => ORA_RSET } > ); > $sth->execute; > $sth->finish; > > while( @row = $employees_cursor->fetchrow_array) { > foreach $_ (@row) { > print $_ if defined $_; > print "\t"; > } > print "\n"; > } > > $employees_cursor->finsih; > $dbh->disconnect; > > -----Original Message----- > From: Hugh J. Hitchcock [mailto:[EMAIL PROTECTED]] > Sent: Sunday, July 15, 2001 7:30 PM > To: [EMAIL PROTECTED] > Subject: Oracle Stored Proc challenge > > > Hi All, > > I was wondering if someone could help me with a bit of a sticky problem. > > I am trying to get a resultset from an Oracle stored procedure using Perl > DBD::Oracle. It is not working... although I definitely can get it to work > as a straight SQL call to ADO in an ASP page I cannot recreate this > functionality in Perl yet. But I'm quite sure it's possible, I just don't > know how... I have been able to execute simple Oracle stored procs that > return a scalar value, or do an update or insert, but this whole > question of > returning an array of a resultset has got me completely stumped in Perl. I > call for assistance.... Here is what is in Oracle: > > CREATE OR REPLACE PACKAGE Country_Pkg > AS > /****************************************************************** > ** In order to use PL/SQL tables we need to declare an individual > ** table data type for each item - these are essentially rrays > *******************************************************************/ > TYPE tblCTRYID IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER; > TYPE tblCTRYCODE IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER; > TYPE tblCTRYNAME IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER; > TYPE tblTZID IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER; > TYPE tblPANREGIONAL IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER; > TYPE tblMASTERPANREG IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER; > TYPE tblCTRYACTIVE IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER; > TYPE tblAUTHORID IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER; > > PROCEDURE GetCountryList( > o_ctryID OUT tblCTRYID , > o_ctryCode OUT tblCTRYCODE , > o_ctryName OUT tblCTRYNAME , > o_ctryTZID OUT tblTZID , > o_ctryPREG OUT tblPANREGIONAL , > o_ctryMPREG OUT tblMASTERPANREG , > o_ctryACTIVE OUT tblCTRYACTIVE , > o_ctryAUTHORID OUT tblAUTHORID); > > > END Country_Pkg; > > CREATE OR REPLACE PACKAGE BODY Country_Pkg > AS > > PROCEDURE GetCountryList( > o_ctryID OUT tblCTRYID , > o_ctryCode OUT tblCTRYCODE , > o_ctryName OUT tblCTRYNAME , > o_ctryTZID OUT tblTZID , > o_ctryPREG OUT tblPANREGIONAL , > o_ctryMPREG OUT tblMASTERPANREG , > o_ctryACTIVE OUT tblCTRYACTIVE , > o_ctryAUTHORID OUT tblAUTHORID) > IS > CURSOR country_cur IS > SELECT COUNTRY_ID , > COUNTRY_CODE , > COUNTRY_NAME , > TIMEZONEID , > PAN_REGIONAL , > MASTER_PAN_REGIONAL, > COUNTRY_ACTIVATION , > AUTHOR_ID > > FROM COUNTRY; > > recCount NUMBER DEFAULT 0; > BEGIN > FOR CountryRec IN country_cur LOOP > > recCount:= recCount + 1; > > o_ctryID(recCount):= CountryRec.COUNTRY_ID; > o_ctryCode(recCount):= CountryRec.COUNTRY_CODE; > o_ctryName(recCount):= CountryRec.COUNTRY_NAME; > o_ctryTZID(recCount):= CountryRec.TIMEZONEID; > o_ctryPREG(recCount):= CountryRec.PAN_REGIONAL; > o_ctryMPREG(recCount):= CountryRec.MASTER_PAN_REGIONAL; > o_ctryACTIVE(recCount):= CountryRec.COUNTRY_ACTIVATION; > o_ctryAUTHORID(recCount):= CountryRec.AUTHOR_ID; > > END LOOP; > > END GetCountryList; > > END Country_Pkg; > / > > I know that this is returning the records, because using ASP with > ODBC I get > all the records back, something like this: > > Const cProcName = "{call Country_Pkg.GetCountryList({resultset 100, > O_CTRYID, O_CTRYCODE, O_CTRYNAME, O_CTRYTZID, O_CTRYPREG, O_CTRYMPREG, > O_CTRYACTIVE, O_CTRYAUTHORID})}" > > Then I just execute sProcName against the ADO connection. > However, in Perl, > I simply cannot get this to work... I've tried using the > procedure as in the > following: > > my $func = $dbh->prepare('BEGIN Country_Pkg.GetCountryList(:O_CTRYID, > :O_CTRYCODE, :O_CTRYNAME, :O_CTRYTZID, :O_CTRYPREG, :O_CTRYMPREG, > :O_CTRYACTIVE, :O_CTRYAUTHORID); END;'); > > $func->bind_param_inout(":O_CTRYID", \$id, 10); > $func->bind_param_inout(":O_CTRYCODE", \$code, 5); > $func->bind_param_inout(":O_CTRYNAME", \$name, 25); > $func->bind_param_inout(":O_CTRYTZID", \$tzid, 3); > $func->bind_param_inout(":O_CTRYPREG", \$preg, 3); > $func->bind_param_inout(":O_CTRYMPREG", \$mpreg, 3); > $func->bind_param_inout(":O_CTRYACTIVE", \$active, 3); > $func->bind_param_inout(":O_CTRYAUTHORID", \$authid, 10); > $func->execute; > > This works just fine with more simple stored procedures that take an > incoming value and return some scalar output values; but of course, this > procedure returns arrays or records in the in-out params. I first tried > using @array vars and sending their \address as in-out params, then tried > using my $array = \@array and sending the $array as the param, then the > \$array as the params... in that case I get the error message that > "bind_param_inout requires a scalar variable". > > So then , seeing as it works just fine as a astring SQL being executed > against a connection in ADO, I decided to try using the same string ( > sProcName above ) just as a straight prepared SQL statement and > executing it > on the DBI connection, then using fetch row array to get the values out. > However, then I get $sth->prepare errors about the curly braces {} being > unexpected symbols. I try removing them and I get other errors. I > tried just > about everything I could think of, but am always returned an error saying > the the statement could not be prepared. > > Can someone out there give me any suggestions on how to get this > to work? I > am about at my wits end. I realise that I could probably use DBI::ODBC, or > just as easily use straight SQL to get the data out of Oracle, but I am > trying to follow certain guidelines: > > 1) We need to use the DBD::Oracle because we need cross-platform use (ODBC > is difficult on UNIX) > 2) We wish to do all our database work, including SELECTS inside of Stored > Procs (compiled code runs more easily, and also adds a layer of > abstraction > to the scripting team) > > My profuse thanks in advance. Sincerely, > > ---------------------------------------- > Hugh J. Hitchcock > Sr. Software Engineer / Project Manager > Tiaxa, Inc. > http://www.tiaxa.com > > >
