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)

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]

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