Home » SQL & PL/SQL » SQL & PL/SQL » IN/OUT/INOUT PL-SQL arguments from Perl
IN/OUT/INOUT PL-SQL arguments from Perl [message #37100] Thu, 17 January 2002 11:35 Go to next message
Kyle
Messages: 4
Registered: December 2000
Junior Member
Anyone

Does anyone know the correct syntax for calling and passing IN/OUT/INOUT parameters to a PL/SQL Procedure from Perl. I can get it to work if the Procedure does not take any parameters. However, when I try to set up the script to pass one IN argument and one OUT argument, I am getting errors.

The Perl statement call would be similar to the following after the database connection has been made via UNIX:
Any help anyone can provide would be greatly appreciated.

$stmt = "BEGIN test( '${now}', $ab_success ); END;";

$egm_sth = $dbh->prepare ($stmt )|| die "nPrepare error: $DBI::err .... $DBI::errstrn";

$egm_sth ->execute() || die "nExecute error: $DBI::err .... $DBI::errstrn";

Thank you,
Kyle
Re: IN/OUT/INOUT PL-SQL arguments from Perl [message #37102 is a reply to message #37100] Thu, 17 January 2002 12:09 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Does this help??
from:
http://osi.oracle.com/~tkyte/ResultSets/index.html

$ cat demo.pl
#!/usr/local/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect('dbi:Oracle:','scott','tiger') or die $DBI::errstr;
my $sth1 = $dbh->prepare(q{create or replace package types as
type cursorType is ref cursor;
end;});
$sth1->execute;
> >
$sth1 = $dbh->prepare(q{
create or replace function sp_ListEmp return types.cursorType
as l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;});
$sth1->execute;
$sth1 = $dbh->prepare(q{
BEGIN
:cursor := sp_ListEmp;
END;
});
my $sth2;
$sth1->bind_param_inout(":cursor", $sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute();
while ( my @row = $sth2->fetchrow_array ) { print join("&#124",@row),"n"; }
=================================
Also see dbi.symbolstone.org
Previous Topic: need to create a "read only" user
Next Topic: Triggers
Goto Forum:
  


Current Time: Sun Jul 12 15:38:53 CDT 2020