Re: HELP PLEASE! Problems using returning a cursor to DBI & DBD::Oracle using Stored Procedures

[Michael A Chase]

On 02/04/2004 12:55 PM, Michael Cesar wrote:

Heres the entire script.

Michael Cesar

#!/usr/local/bin/perl -w

use strict;
use DBI;
use Data::Dumper;
use DBD::Oracle qw(:ora_types);
my (@result_arref,@row);

my $dbh = DBI->connect(
            'dbi:Oracle:XXX',
            'abc',
            '******',
) || die $DBI::errstr;
eval {
    my ($SSP,$rc);
This prepare() could be do() if you really intend to create the package
every time the script runs.
BTW, if you are really putting the SELECT text in the script,
prepare()ing a SELECT by itself and executing that would be more
efficient and avoids having a cursor returned from a package.
    my $sth1 = $dbh->prepare(
        q{
        CREATE OR REPLACE PROCEDURE sp_getCaseFile2(
           tid      IN    NUMBER,
           sid          OUT   NOCOPY NUMBER,
           sname        OUT       NOCOPY VARCHAR2,
           cnum         OUT       NOCOPY NUMBER,
           cdate        OUT       NOCOPY DATE,
           ctext        OUT       NOCOPY VARCHAR2,
           aname        OUT       NOCOPY VARCHAR2
        )
        IS
           v_tid        NUMBER  := tid;
           v_sid        NUMBER;
           v_sname      VARCHAR2(16);
           v_cnum   NUMBER;
           v_cdate  VARCHAR2(8);
           v_ctext  VARCHAR2(128);
           v_aname  VARCHAR2(32);
        CURSOR csr_caseFile(v_tid NUMBER) is
        SELECT
            aa.agent_name, ajs.suspect_name, ajs.suspect_id, ajtc.CLUE_NUMBER,
            to_char(ajtc.CREATE_DATE, 'MON-DD') dt, ajtc.team_id, ajc.clue_desc
        FROM
            ajis_agent aa, ajis_suspect ajs, ajis_team_clue ajtc, ajis_clue ajc
        WHERE
            aa.team_id = v_tid AND
            aa.team_id = ajtc.team_id AND
            ajtc.clue_number = ajc.clue_number AND
            aa.agent_id = ajtc.agent_id AND
            ajc.suspect_id = ajs.suspect_id AND
            ajs.active = 'Y'
        order by ajs.suspect_id;
        v_cfile csr_caseFile%ROWTYPE;

        /* Check the cursor is open, else open it.Fetch data into cursor record.
        */
        BEGIN
        IF (NOT csr_caseFile%ISOPEN) THEN
        OPEN csr_caseFile(v_tid);
        END IF;
        LOOP
            FETCH csr_caseFile INTO v_cfile;
            EXIT WHEN csr_caseFile%NOTFOUND;
    
            /* Assign cursor result set to output variables, one record at a time.
            */

            v_sid   := v_cfile.suspect_id;
            v_sname := v_cfile.suspect_name;
            v_cnum  := v_cfile.clue_number;
            v_cdate := v_cfile.dt;
            v_ctext := v_cfile.clue_desc;
            v_aname := v_cfile.agent_name;
        END LOOP;
        IF (csr_caseFile%ISOPEN) THEN
            CLOSE csr_caseFile;
        END IF;
        EXCEPTION
           WHEN NO_DATA_FOUND
           THEN
              NULL;
           WHEN OTHERS
           THEN
              -- Consider logging the error and then re-raise
              RAISE;
        END sp_getCaseFile2;
        }
    );
    $rc = $sth1->execute;
    
    $SSP = $dbh->prepare("BEGIN sp_getCaseFile2(:tid, :sid, :sname, :cnum, :cdate, :ctext, :aname); END;");
    
    my $tid = 3;
    my $sid;
    my $sname;
    my $cnum;
    my $cdate;
    my $ctext;
    my $aname;
    
    $SSP->bind_param(":tid", $tid );
    $SSP->bind_param_inout(":sid", \$sid, 10);
    $SSP->bind_param_inout(":sname", \$sname, 16);
    $SSP->bind_param_inout(":cnum", \$cnum, 10);
    $SSP->bind_param_inout(":cdate", \$cdate, 8);
    $SSP->bind_param_inout(":ctext", \$ctext, 128);
    $SSP->bind_param_inout(":aname", \$aname, 32);
    
    warn "Executing for the first time...";
    my $cnt = 0;
    $SSP->execute();
    $SSP->fetch();
    while ( $sid ) {
        print "$sid, $ctext\n";
        $SSP->execute();
        last if ($cnt > 10);
        $cnt++;
    }
};
if ($@) {
    my $err = "DB-ERR: $@ [$DBI::errstr]";
    die $err;
}

print Dumper([EMAIL PROTECTED]);
If you can't run the SELECT directly and your friendly local DBA won't 
let you return a cursor from the procedure, you've got about as good as
it gets.  Maybe you can show him
http://search.cpan.org/src/TIMB/DBD-Oracle-1.15/Oracle.ex/curref.pl to
ease his concerns about returning a cursor.  Note PROCEDURE
ref_cursor_close in PACKAGE curref_test.

Look at
http://search.cpan.org/src/TIMB/DBD-Oracle-1.15/Oracle.ex/proc.pl, only
$SSP->execute() is needed, not $SSP->fetch().  $SSP->fetch() would apply
if you were fetch()ing from a SELECT.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.