Re: Select Statement in Stored Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 28 Oct 1998 17:17:57 GMT
Message-ID: <363c4360.10864532@192.86.155.100>

A copy of this was sent to henning_at_rbg.informatik.tu-darmstadt.de (Henning Voss) (if that email address didn't require changing) On Wed, 28 Oct 1998 08:25:35 GMT, you wrote:

>On Mon, 26 Oct 1998 13:24:36 GMT, tkyte_at_us.oracle.com (Thomas Kyte)
>wrote:
>
>--- cut ---
>
>hi,
>
>To make things somewhat more confusing, is it possible (using your
>example) to
>1) call the function without knowing the name of the function to call
>at compile time ?

Yes, but you must use OCI to dynamically prepare this statement. You can mix OCI and pro*c together so you only need enough OCI to accomplish this. It might look like:

void process()
{
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR my_cursor;

    int         empno;
    int         i;
    VARCHAR     ename[40];
EXEC SQL END DECLARE SECTION;
    Cda_Def     refCur;
    Cda_Def     cda;
    Lda_Def     lda;
    int         rc;
    char        sqlstmt[255];

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

EXEC SQL ALLOCATE :my_cursor;

sqllda( &lda );

oopen( &cda, &lda, NULL, -1, -1, NULL, -1 );

sprintf( sqlstmt, "begin :x := sp_listEmp; end;" );

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

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

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

oexec( &cda );

sqlcur( &my_cursor, &refCur, &rc );

for( ;; )
{

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH :my_cursor INTO :ename, empno;

        printf( "'%.*s', %d\n", ename.len, ename.arr, empno );

}

EXEC SQL CLOSE :my_cursor;
}

So, that is using OCI to simply open and execute the procedure that returns a ref cursor and then using pro*c to do the rest...

>2) return multiple tables using out parameters ?

Yes, consider:

SQL> create or replace package types
  2 as
  3 type refCur is ref cursor;   4 end;
  5 /

Package created.

SQL> 
SQL> variable x refcursor;
SQL> variable y refcursor;
SQL> variable z refcursor;
SQL> 
SQL> create or replace procedure many_cursors( x in out types.refCur,
  2                                            y in out types.refCur,
  3                                            z in out types.refCur )

4 is
5 begin

  6      open x for select * from all_users where rownum < 5;
  7      open y for select sysdate from dual;
  8      open z for select object_name, object_type from all_objects
  9      where rownum < 10;

10 end;
11 /

Procedure created.

SQL>
SQL> exec many_cursors( :x, :y, :z );

PL/SQL procedure successfully completed.

SQL>
SQL> print x

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 01-SEP-97
SYSTEM                                  5 01-SEP-97
DBSNMP                                 17 01-SEP-97
TRACESVR                               19 01-SEP-97

SQL> print y

SYSDATE

28-OCT-98 SQL> print z

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ---------------
ACCESS$                        TABLE
ALL_ALL_TABLES                 VIEW
ALL_ARGUMENTS                  VIEW
ALL_CATALOG                    VIEW
ALL_CLUSTERS                   VIEW
ALL_CLUSTER_HASH_EXPRESSIONS   VIEW
ALL_COLL_TYPES                 VIEW
ALL_COL_COMMENTS               VIEW
ALL_COL_PRIVS                  VIEW

9 rows selected.

>3) process the tables (as i do not know the name of the
>function/procedure i also do not know the exact format of the
>resulting tables) ?
>

I'd use OCI for this then as well. Given that you have a REF cursor and want to get the data from it, a simply OCI routine such as:

void print_refcursor( char * refcur_name ) {
Cda_Def * c1 = (Cda_Def *) get_value_of(refcur_name);

int     i;
sb4     dbsize;
sb2     dbtype;
sb4     dsize;
sb2     prec;
sb2     scale;
sb2     nullok;

char * cp[255];
short indicators[255];
char cname[255];

sb4     cname_len;
int     col_cnt = 0;

for( i = 0; i < sizeof(cp)/sizeof(cp[0]); i++ ) {

        cname_len = 255;
        if (odescr(c1, (sword) i+1, &dbsize, &dbtype,
                (sb1 *) cname, &cname_len, &dsize,
                &prec, &scale, &nullok ))
        {
            if (c1->rc == 1007) break; /* NO More Columns... */
            print_error_and_exit(c1);
        }

        printf( "%s%.*s", i?",":"", (int)cname_len, cname );

        col_cnt++;
        cp[i] = (char *) malloc( 255 );

        if (odefin(c1, i+1, (ub1 *) (cp[i]), (sword) 254,
              SQLT_STR, -1, (sb2 *) indicators+i,
              (text *)0, (sword) 0, (sword) 0,
              (ub2 *) 0, (ub2 *) 0)) print_error_and_exit(c1);

}
printf( "\n" );

while (1)
{

        if (ofetch(c1))
        {
            if (c1->rc == 1403) break;
            else               print_error_and_exit( c1 );
        }
        else
        {
            for( i = 0; i < col_cnt; i++ )
                printf( "%s%s", i?",":"", indicators[i]?"(null)":cp[i] );
            printf( "\n" );
        }

}
memset( c1, 0, sizeof(Cda_Def) );
}

for example retrieves and prints out the results of any ref cursor. Since you cannot describe a cursor (only a statement) in pro*c, this will be the way to do it.

Since you can easily mix pro*c and oci, this is pretty straightforward... Bear in mind -- the code I supplied is for EXAMPLE, it contains no error handleing and has some fixed sized arrays and such...

>server: oracle 7.3
>development plattform: Pro*c
>
>every hint is appreciated
>henning
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Oct 28 1998 - 11:17:57 CST