Re: Why can't my Stored Procedure return multiple rows ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 19 Apr 1999 13:07:11 GMT
Message-ID: <371e299c.3407639@192.86.155.100>

A copy of this was sent to yewpc_at_my-dejanews.com (if that email address didn't require changing) On Mon, 19 Apr 1999 06:51:27 GMT, you wrote:

>I have created a stored procedure that will retrieve multiple rows.
>I use an OCI program to call it. The result is it will return me an infinite
>loop with the first row data.
>If I change my stored procedure to a package, then It work fine.
>Does anyone can tell me why ? will stored procedure support multiple rows
>retrieval ?
>
>Thank you
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

they can already and have since cursor variables were introduced in release 7.2. Here is an example that works:

plsql code:

create or replace package types
as

    type cursorType is ref cursor;
end;
/

create or replace procedure getEmps( p_refCur in out types.cursorType ) as
begin

    open p_refCur for select ename, empno from emp order by ename; end;
/

OCI Code (cda and lda assumed to be global variables already allocated and setup for brevity of code, print_error_and_exit does what it says -- prints an error, calls exit(1) ):

void run_it( void )
{
Cda_Def refCur;
char * sqlstmt = "BEGIN getEmps( :my_cursor ); END;"; char ename[50];
short ename_i;
char empno[50];
short empno_i;

    memset( &refCur, 0, sizeof(refCur) );

    if (oparse(&cda, sqlstmt, (sb4)-1, 0, (ub4)VERSION_7))

        print_error_and_exit(&cda);

    if ( obndra(&cda, (text *) ":my_cursor", -1, (ub1 *) &refCur, -1,

                        SQLT_CUR, -1, (sb2 *) 0, (ub2 *) 0, (ub2 *) 0,
                        (ub4) 0, (ub4 *) 0, (text *) 0, 0, 0) )
        print_error_and_exit(&cda);

    if (oexec(&cda))
        print_error_and_exit(&cda);

    if (odefin(&refCur, 1, (ub1 *) ename, sizeof(ename),
               SQLT_STR, -1, (sb2 *) &ename_i,
               (text *)0, (sword) 0, (sword) 0,
               (ub2 *) 0, (ub2 *) 0)) print_error_and_exit(&refCur);

    if (odefin(&refCur, 2, (ub1 *) empno, sizeof(empno),
               SQLT_STR, -1, (sb2 *) &empno_i,
               (text *)0, (sword) 0, (sword) 0,
               (ub2 *) 0, (ub2 *) 0)) print_error_and_exit(&refCur);

    while (1)
    {

        if (ofetch(&refCur))
        {
            if (refCur.rc == 1403) break;
            else                   print_error_and_exit( &refCur );
        }
        else printf( "%s,%s\n",
                      ename_i?"(null)":ename, empno_i?"(null)":empno);

    }
}  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Apr 19 1999 - 08:07:11 CDT