Not to sure what your function tims.get_all_curr_descs_items looks like, but
this (simple but possibly dumb) example worked for me:
use strict;
use warnings;
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh;
my $username='xxxx';
my $password='xxxx';
$ENV{ORACLE_SID}='dwhx01';
my $dsn="dbi:Oracle:$ENV{ORACLE_SID}";
$dbh=DBI->connect($dsn, $username, $password, { ora_module_name =>
"testcur.ctest" })
or die "Cannot connect to $dsn: $DBI::errstr";
$dbh->{RaiseError}=1;
$dbh->{AutoCommit}=0;
my $csr = $dbh->prepare(q{
begin
:result := testcur.ctest(:pic);
end;
});
my $temp;
foreach my $pic ('YYYY', 'YYYYMM', 'YYYYMMDDHH24MISS') {
$csr -> bind_param(":pic",$pic);
$csr -> bind_param_inout(":result",\$temp,0,{ ora_type => ORA_RSET });
$csr->execute();
$temp->dump_results;
$csr->finish();
}
$dbh->disconnect();
where the function is defined by:
CREATE OR REPLACE PACKAGE testcur AS
TYPE cursor_ref IS REF CURSOR;
FUNCTION ctest(pic IN VARCHAR2) RETURN cursor_ref;
END testcur;
/
CREATE OR REPLACE PACKAGE BODY testcur AS
FUNCTION ctest(pic IN VARCHAR2)
RETURN cursor_ref IS
mycur cursor_ref;
BEGIN
OPEN mycur FOR
SELECT TO_CHAR(SYSDATE, pic) FROM DUAL;
RETURN mycur;
END ctest;
END testcur;
/
output was:
'2002'
1 rows
'200209'
1 rows
'20020910105647'
1 rows
-----Original Message-----
From: Shao, Chunning [mailto:[EMAIL PROTECTED]]
Sent: Saturday, September 07, 2002 11:08 PM
To: Shao, Chunning; [EMAIL PROTECTED]
Subject: RE: Help: how should we handle it if the pl/sql function
returning is a cursor?
Sorry, guys, I really should not have sent the origianl help request,
actually you guys just discussed the issue a few weeks ago.
But after I read the curref.pl, I still can not make my perl script to work.
Here is my code,
$csr = $dbh->prepare(q{
begin
:result :=
tims.get_all_curr_descs_items(:o_status,:o_error_mesg
,2);
end;
});
my $temp;
$csr -> bind_param_inout(":o_status",\$put_para1,3);
$csr -> bind_param_inout(":o_error_mesg",\$put_para2,1);
$csr -> bind_param_inout(":result",\$temp,5,{ ora_type => ORA_RSET });
$csr->execute();
$temp->dump_results;
$csr->finish();
$dbh->disconnect();
[cshao@beltway ~/test]$ ./timing.pl
0 rows (932: ORA-00932: inconsistent datatypes (DBD ERROR: OCIStmtFetch))
DBD::Oracle::st dump_results failed: ORA-00932: inconsistent datatypes (DBD
ERROR: OCIStmtFetch) at ./timing.pl line 44, <STDIN> line 1.
What I did was wrong?
Thanks for your help.
-----Original Message-----
From: Shao, Chunning
Sent: Fri 9/6/2002 5:25 PM
To: [EMAIL PROTECTED]
Cc:
Subject: Help: how should we handle it if the pl/sql function
returning is a cursor?
Hi, everyone, we have a PL/SQL function which is defined as follows,
FUNCTION get_all_curr_descs_items
(
o_status OUT sp_error_log.error_id%type,
o_error_mesg OUT sp_error_log.error_mesg%type,
i_metro_id IN traffic_item.metro_id%type,
i_minutes_pass_undef IN NUMBER DEFAULT g_undef_rolloff,
i_minutes_pass_def IN NUMBER DEFAULT
g_defined_rolloff
)
RETURN g_generic_cursor_t
When we call it from sqlpus,
SQL> DECLARE
2 TYPE CURSOR_TYPE_0 IS REF CURSOR;
3 "Return Value" CURSOR_TYPE_0;
4 o_status NUMBER(10);
5 o_error_mesg VARCHAR2(2000);
6 BEGIN
7
8 -- Now call the stored program
9 "Return Value" :=
tims.get_all_curr_descs_items(o_status,o_error_mesg,2);
10
11 -- Output the results
12 dbms_output.put_line(SubStr('o_status = '||TO_CHAR(o_status),
1, 255));
13 dbms_output.put_line(SubStr('o_error_mesg =
'||o_error_mesg,1,255));
14
15 COMMIT;
16 EXCEPTION
17 WHEN OTHERS THEN
18 dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||':
'||SQLERRM, 1, 255));
19 RAISE;
20 END;
21 /
PL/SQL procedure successfully completed.
SQL>
but when I call it from perl dbi,
$csr = $dbh->prepare(q{
begin
:result :=
tims.get_all_curr_descs_items(:o_status,:o_error_mesg
,2);
end;
});
$csr -> bind_param_inout(":result",\$temp,5);
$csr -> bind_param_inout(":o_status",\$put_para1,3);
$csr -> bind_param_inout(":o_error_mesg",\$put_para2,250);
$csr->execute();
I got the following error
DBD::Oracle::st execute failed: ORA-06550: line 3, column 14:
PLS-00382: expression is of wrong type
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute) at ./timing.pl
line 41, <STDIN> line 1.
How should I handle if the function return is a cursor instead of a
value?
Thanks for your help
chunning shao
DBA
traffic.com
Australia Post is committed to providing our customers with excellent service. If we
can assist you in any way please either telephone 13 13 18 or visit our website
www.auspost.com.au.
CAUTION
This e-mail and any files transmitted with it are privileged and confidential
information intended for the use of the addressee. The confidentiality and/or
privilege in this e-mail is not waived, lost or destroyed if it has been transmitted
to you in error. If you have received this e-mail in error you must (a) not
disseminate, copy or take any action in reliance on it; (b) please notify Australia
Post immediately by return e-mail to the sender; and (c) please delete the original
e-mail.
