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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.