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

Reply via email to