Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

[perl] How call function/procedure that returns SYS_REFCURSOR type?

753103Feb 11 2010 — edited Feb 11 2010
I've got some simple procedure which returns record(s):

CREATE OR REPLACE PROCEDURE "GET_SYS_DATE"
(
RESULTSET IN OUT SYS_REFCURSOR
)
IS

BEGIN
OPEN RESULTSET FOR
SELECT SYSDATE FROM DUAL;
END;

In perl i invoke it with somthing like this:

my $ret;

my $s= "BEGIN GET_SYS_DATE(:1); END;";
my $sth = $dbh->prepare($s);
$sth->bind_param_inout(1, \$ret, 0 { TYPE => XXX}); # tried to use many DBD::SQL_* types (SQL_ROW, SQL_REF, etc.)
$sth->execute(); #... but without luck

I always get:
DBD::ODBC::st execute failed: [Oracle][ODBC][Ora]ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_SYS_DATE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Of course if i use other datatype in SP (e.g. VARCHAR2) and bind it as SQL_VARCHAR it works well... Moreover, if i use DBD::Oracle and bind it as ORA_RSET type it also works.

Is it possible that ODBC doesn't know SYS_REFCURSOR type? Then, is there any way to call SP and retrieve cursor from perl without using DBD::Oracle?

Or, if it is possible, then how to retrieve that cursor and data stored within it? Any help?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 11 2010
Added on Feb 11 2010
1 comment
4,506 views