RE: URGENT!!--How to call a PL/SQL function which returns a ref c ursor

[Dominic Pain]

I don't know what your SQL code does, frankly, as I'm a MySQL bod and we
don't tend to deal with magic like that :-)

But I think that you're doing $csr->finish too early.  Don't you want to
retrieve some information from the cursor before ditching it?

Or else, if it's as I guess and you're just setting a parameter in the
database, why not save yourself some typing and just say:

$dbh->do(qq{--sql stuff });

Cheers
Dominic Pain


-----Original Message-----
From: Abhilash Shah (abshah) [mailto:[EMAIL PROTECTED]
Sent: 28 August 2003 06:33
To: [EMAIL PROTECTED]
Subject: URGENT!!--How to call a PL/SQL function which returns a ref
cursor
Importance: High



Hi,
Does anybody know how I can call a oracle PL/SQL function in PERL? Below
is my perl code :
use DBI; 

  my($db, $csr, $ret_val); 
# Connect to an Oracle database. 

# db userid and password 
  $db = DBI->connect('dbi:Oracle:ctsdv2','apps','s0mm3r') 
        or die "Unable to connect: $DBI::errstr"; 
# Set Raise Error so you do not have to check every DBI call. 
# See the DBI (perldoc DBI) documentss now if you are not familiar with


# RaiseError. 
print "connected \n";
   $db->{RaiseError} = 1; 

$csr = $db->prepare("
       declare
        type abc refcursor;
       BEGIN 
       abc:= XXCTS_ILS_AM.get_opera_activity_codes;
       END;"); 
        $csr->execute; 

   print "called successfully\n\n"; 
# Close the cursor. 
    $csr->finish; 
# Disconnect from the database. 
    $db->disconnect; 

And below is the error I get when I try to run this code :
DBD::Oracle::db prepare failed: ORA-06550: line 3, column 18:
PLS-00103: Encountered the symbol "REFCURSOR" when expecting one of the
followin
g:

   ; is authid as under
ORA-06550: line 8, column 11:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of
the follow
ing:

   end not pragma final instantiable order overriding static
   member constructor map (DBD: error possibly near <*> indicator at
char 34 in
'
       declare
        type abc <*>refcursor;
       BEGIN
       abc:= XXCTS_ILS_AM.get_opera_activity_codes;


       END;') at testam.pl line 27.
DBD::Oracle::db prepare failed: ORA-06550: line 3, column 18:
PLS-00103: Encountered the symbol "REFCURSOR" when expecting one of the
followin
g:

   ; is authid as under
ORA-06550: line 8, column 11:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of
the follow
ing:

   end not pragma final instantiable order overriding static
   member constructor map (DBD: error possibly near <*> indicator at
char 34 in
'
       declare
        type abc <*>refcursor;
       BEGIN
       abc:= XXCTS_ILS_AM.get_opera_activity_codes;


       END;') at testam.pl line 27.

Any help in this regards is appreciated.

Regards,
Abhilash Shah
[EMAIL PROTECTED]