in reply to How I can get the returned cursor from database in PERL program?

Well, since you asked for the answer to be kindly, i won't flame you for not giving anything to go by except:
  1. you have a Perl script
  2. you are accessing Oracle stored procedures
  3. you read the docs for DBD::Oracle
  4. you have some errors
....

Going by this info, i would say what you need to do is ask the question again ... and this time, please give us something more than just "i turned the key and the car didn't start" - just like you would take/tow your car into the mechanic, please feel free to wheel your code on into the Monastery. Just be sure and enclose posted code with <code></code> tags (see the Site How To for more).

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
  • Comment on (jeffa) Re: How I can get the returned cursor from database in PERL program?

Replies are listed 'Best First'.
Re: Re: How I can get the returned cursor from database in PERL program?
by Anonymous Monk on Mar 11, 2002 at 07:30 UTC
    Hello, Sorry for not posting the codes earlier. Herewith i've posted my Oracle stored procedure, Perl code and the error i get. Any help after going thru' these would be very greatful. Thanks in advance. 
    #       ORACLE STORED PROCEDURE         #

CREATE OR REPLACE PACKAGE selectall AS
        TYPE my_cursor IS REF CURSOR;
        PROCEDURE CATEGORYLIST1(CAT_CURSOR OUT my_cursor);
END selectall;
/

CREATE OR REPLACE PACKAGE BODY selectall AS
  PROCEDURE CATEGORYLIST1(CAT_CURSOR OUT my_cursor) IS
     BEGIN
        OPEN CAT_CURSOR FOR 
      SELECT KP_CH_CATEGORY_NAME FROM KP_CH_CATEGORY
         ORDER BY KP_CH_CATEGORY_NAME;
          CLOSE CAT_CURSOR;
        END CATEGORYLIST1;
END selectall;
/


#       Perl code       #

#!/usr/bin/perl -w

BEGIN {
    $|=1;
    open(STDERR,">&STDOUT");
    print("content-type:text/html\n\n");
}

use CGI qw/:all/;
use DBI;
use DBD::Oracle qw(:ora_types);
$ENV{ORACLE_HOME}="/u01/app/oracle/product/8.1.7";
my $dbh = DBI -> connect('dbi:Oracle:host="192.168.7.80";sid="ktpn";po
+rt=1521"', 'cwl', 'cwl', 
                         { RaiseError => 1, AutoCommit => 1 }
            ) || die "Error while connecting to database : $! \n";

my $sth = $dbh->prepare(q{BEGIN:selectall.CATEGORYLIST1(:p_my_cursor);
+ END;});
my $my_cursor;
print "Executed upto this point"."\n";
$sth->bind_param_inout(":p_my_cursor", \$my_cursor, 0, { ora_type => O
+RA_RSET });
$sth->execute;
$sth->finish;

my @row;
while( @row = $my_cursor->fetchrow_array) {
        foreach $_ (@row) {
                print $_;
                print "\n";
        }
}
$my_cursor->finish;
$dbh->disconnect;


#       ERROR   #

Executed upto this point
DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DB
+D ERROR: OCIStmtExecute) at /var/www/html/narayan/cgi-bin/Try.cgi lin
+e 19.
DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DB
+D ERROR: OCIStmtExecute) at /var/www/html/narayan/cgi-bin/Try.cgi lin
+e 19.

    [download]
[reply]
[d/l]
      In the PL/SQL you issue a CLOSE CURSOR command. Oops. If you pass back a closed cursor you're not going to get any data - you can't FETCH from a closed cursor...it's like trying to read from a closed file.

      rdfield

[reply]
[d/l]
[select]
        I've tried returning without closing the cursor in the PL/SQL block also, but still i get the same old error. Can i get some other suggestions to resolve this problem??
[reply]
        I've also tried returning without closing the cursor in PL/SQL block, but still i get the same old error. Any other suggestions to resolve this problem will be of great help. Thanks a lot.
[reply]

In Section Seekers of Perl Wisdom