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
