-
many rows procedure into perl
Hi
How do I fetch the values from oracle procedure returning many rows into a perl script? I have no problem with one row... Please advice, thanks!
-
-
Hi,
Ref cursors in perl will code all sql in the script, I do not want that. Basically, I have written a procedure which returns many values. How do i execute this procedure in a perl script?
-
For example, the following codes only return one row from the procedure. How do i return many rows from the procedure to the perl code?
my $sth=$dbh->prepare("begin testproc(:in1,:in2,:in3,: out1,: out2,: out3); end;");
$sth->bind_param("in1",1);
$sth->bind_param("in2",1.1,2,1);
$sth->bind_param("in3","hello");
my $out1;
my $out2;
my $out3;
$sth->bind_input_param("out1",\$out1,20);
$sth->bind_input_param("out2",\$out2,20);
$sth->bind_input_param("out3",\$out3,20);
-
From the code, I don't have the slightest idea of what you are trying to do. What does testproc look like?
Jeff Hunter
-
Hi
I have changed my procedure (testproc) to return the multiple rows of data as a ref cursor type. I can execute it in sqlplus as shown below :
var data ref cursor
begin
testproc(:data)
end;
In this case, how to I execute this procedure from perl?
-
perldoc DBD::Oracle
search for bind_param_inout or ORA_RSET
Jeff Hunter
-
Thats what I have tried using the bind parameters. However, how do i declare the variable as ref cursor? I got an error that wrong type being used when excuting it in perl...
-
-
Thanks, I read that before, I just want to confirm that the following codes means that I have to use my ref cursor procedure (define cursor type and including sql statements) inside the perl script and cannot be called directly from perl script like a normal procedure right?
#!/usr/local/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(ra_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("|",@row),"\n"; }
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|