Fetch data from Oracle SP Out Param SYS_REFCURSOR

Question

I have a stored procedure in oracle with out parameter as sys ref_cursor. I need to execute the sp from korn shell . read the output and use it as a email body. But i could not find any example code to read sys ref cursor in unix. below is my code. Also how can i read cur ? print cur doesnt seem to print any output.

#!/usr/bin/ksh

function runproc
{
    #read ref cursor from proc
    cur=`sqlplus -s $connectiondetails <<EOF
        SET PAGESIZE 0 FEEDBACK ON VERIFY OFF HEADING OFF ECHO OFF
        var return_val ;
        exec myproc_retcur(WEEKNUM ,return_val);    
        EXIT;
        EOF`
        print return_val
    return cur
}

#---Start-----
runproc cur
sendmail "Weekly load test results", cur

Answer 1

You have your print return_val in the wrong place; it shoudl be inside the SQL*PLUS command, before the exit, to print out the ref cursor variable.

You also need to prefix return_val with a colon in your procedure call, to indicate it's using the bind variable you just declared - though you omitted the variable type from its declaration as well. This seems to do what you want:

function runproc
{
    #read ref cursor from proc
    cur=`sqlplus -s $connectiondetails <<EOF
        SET PAGESIZE 0 FEEDBACK ON VERIFY OFF HEADING OFF ECHO OFF
        var return_val refcursor
        exec myproc_retcur(14, :return_val);
        print return_val
        EXIT
        EOF`
    return cur
}

You haven't shown where WEEKNUM is coming from so I've hard-coded that to a number for now.

I think you probably want to set feedback off, not on, incidentally.