comments below.
> -----Original Message-----
> From: Hugh J. Hitchcock [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, July 15, 2001 4: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)
Depends on your def of difficult. If you can throw a little money at it
then EasySoft has ODBC products for UNIX.
PS. Didn't read all of this report... But, what I did read seems like a
user question which would be well met on the user list. This is a forum for
discussion about DBI internals by the DBD authors and such.
> 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
>
>
>
>
