Q
Problem solve Get help with specific problems with your technologies, process and projects.

Executing a procedure from a perl script

How can I execute a procedure from a perl script? I have a procedure where there are CLOB types between the input and output parameters. I need to read this parameter to extract information. How can I do that?

The only perl example I have shows how to return a resultset from a procedure... maybe it'll help you derive the answer you need. Also, you might trying looking at the following link which has source for all the PERL Database interface modules in circulation: http://www.perl.com/CPAN-local/modules/by-category/07_Database_Interfaces/Oraperl/


$ 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("|",@row),"\n"; }

For More Information


This was last published in November 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close