Re: recursion in Pro*C

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 27 Oct 1998 18:24:18 GMT
Message-ID: <363a028a.12977390@192.86.155.100>

A copy of this was sent to "Alan D. Mills" <alanmNOSPAM_at_uk.europe.mcd.mot.com> (if that email address didn't require changing) On Tue, 27 Oct 1998 16:03:20 -0000, you wrote:

>You've backed up nicely what I thought. I haven;t used Pro*C since V1.6 and
>wondered if anything had changed regarding scope of cursors.
>
>In reply to your suggestions.
>
>1 - CONNECT BY. The data structures I have are not recursive. It's the
>process that need to be. I don't think this fits my problem. However, nice
>idea. I hadn't considered it.
>
>2- I don't know OCI and suspect that for this task the learning curve would
>be too great. I happy to be correct on this though.
>

oci isn't that hard if you know C.

>3 - The dbms_sql package. Now there's an idea. I haven't done a lot with
>this before. Certainly not for querying data. My select list is dynamic
>also (Pro*C dynamic SQL method 4) so will this idea still work? Also, can I
>use DBMS_SQL to perform a single array fetch. I don't have to worry about
>fetching in a loop as I have a theoretical maximum record size. Is there
>any change in syntax or procedure for this or do I just fire off my
>
>SELECT col, col, col INTO :HostArrayvar1, :hostarrayvar2 etc
>

Well, couple of issues here. you won't be getting a cursor back into the c program -- just data in this case. probably the most efficient way to get the data back is via a PL/SQL table into a C array. Since we don't know the number of columns or rows we'll be getting until runtime, we could just use 1 array with all of the data in it. If we had 5 columns and got 10 rows back, then elements 0, 1, 2, 3, 4 in the C array would be columns 1-5 of row 1, elements 5, 6, 7, 8, 9 would be columns 1-5 of row 2 and so on.

Just to get you going -- here is a sample package that has an 'array' interface for dynamic sql via dbms_sql to be called from pro*c. It has 2 entry points:

create or replace package dynQuery
as

type myArray is table of varchar2(2000) index by binary_integer;

Open and fetch the first rows from a query. You must set the
variables:
p_query -- to the query to be executed
p_rows -- to the SIZE of your C array (the height of the array) --
we will set:
p_cols -- to the number of columns in the select statement
p_rows -- to the number of rows fetched (hence p_cols * p_rows
will be the number of elements in the C array that
you may index
p_data -- the array of data, indexed from 1 on up in pl/sql,
0 on up in C
p_more -- a 'boolean' flag that says whether there is more
data to fetch. 1 indicates YES, 0 indicates NO

    procedure query_to_array( p_query in     varchar2,
                              p_rows  in out number,
                              p_data  out    myArray,
                              p_cols  out    number,
                              p_more  out    number );

    -- Get_More, just like query_to_array above however it uses the query

already opened above to get more data. inputs and outputs are
the same as the above.

    procedure get_more( p_rows  in out number,
                        p_data  out    myArray,
                        p_cols  out    number,
                        p_more  out    number );

end;
/

The package body might look like:

create or replace package body dynQuery as

g_cursor        integer;
g_cols          integer;


procedure query_to_array( p_query in     varchar2,
                          p_rows  in out number,
                          p_data  out    myArray,
                          p_cols  out    number,
                          p_more  out    number )

l_columnValue varchar2(2000);
l_status integer;
begin

g_cursor := dbms_sql.open_cursor;
g_cols := 0;

dbms_sql.parse( g_cursor, p_query, dbms_sql.native );

for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( g_cursor, i, l_columnValue, 2000 );
            g_cols := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;

end loop;

l_status := dbms_sql.execute(g_cursor);

get_more( p_rows, p_data, p_cols, p_more );

end query_to_array;

procedure get_more( p_rows  in out number,
                    p_data  out    myArray,
                    p_cols  out    number,
                    p_more  out    number )

    l_columnValue   varchar2(2000);
    l_idx           integer default 0;
    l_rowcnt        integer default 0;

begin

    p_cols := g_cols;
    p_more := 1;
    p_rows := trunc( p_rows / g_cols );

    loop
        exit when ( l_rowcnt >= p_rows );
        if ( dbms_sql.fetch_rows(g_cursor) <= 0 )
        then
            dbms_sql.close_cursor(g_cursor);
            p_more := 0;
            exit;
        end if;

        l_rowcnt := l_rowcnt + 1;
        for i in 1 .. g_cols loop
            l_idx := l_idx+1;
            dbms_sql.column_value( g_cursor, i, l_columnValue );
            p_data( l_idx ) := l_columnValue;
        end loop;

end loop;

p_rows := l_rowcnt;
end get_more;

end dynQuery;
/

And then the pro*c to interface with this could be:

static void process( char * theQuery )
{
EXEC SQL BEGIN DECLARE SECTION;

    VARCHAR     data[50][255];
    short       data_i[50];
    VARCHAR     query[255];
    int         rows;
    int         cols;
    int         more;
EXEC SQL END DECLARE SECTION;
    int         i;
    int         j;
    int         idx;
    int         totrows = 0;

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

strcpy( query.arr, theQuery );
query.len = strlen( query.arr );

rows = 50;

    EXEC SQL EXECUTE
    BEGIN
        dynQuery.query_to_array( :query, :rows, :data:data_i, :cols, :more );     END;
    END-EXEC;     for( ;; )
    {

printf( "----> rows = %d, cols = %d, more = %d\n", rows, cols, more );

        for( i = 0, idx = 0; i < rows; i++ )
        {
            totrows++;
            for( j = 0; j < cols; j++, idx++ )
            {
                printf( "%s'%.*s'", j?",":"",
                         data[idx].len, data_i[idx]?"":(char *)data[idx].arr );
            }
            printf( "\n" );
        }
        if ( !more ) break;

        rows = 50;

        EXEC SQL EXECUTE
        BEGIN
            dynQuery.get_more( :rows, :data:data_i, :cols, :more );
        END;
        END-EXEC;

}
printf( "-----> %d rows fetched...\n\n", totrows ); }

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50] = { strlen("tkyte/tkyte"), "tkyte/tkyte" }; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR DO sqlerror_hard(); EXEC SQL CONNECT :oracleid;

printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);

process( "select * from emp" );
process( "select * from dept" );

/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE; exit(0);
}

So, in this code, we could fetch upto 50 rows at a time (if you have 1 column) or 1 row at a time (if you had 50 columns). The loop in the middle (over i and j) shows how to index into the array to get the any column of any fetched row. Note that you will want to send an indicator array down with the data array to see if any of the table elements are in fact NULL.

Hope this helps. Its alot easier then method 4 dynamic sql... In version 8 (the above will work in 7 and 8) you can extend this example to get the column names and all with new dbms_sql functions as well...

>and be done with it? I'm simply not familiar with host arrays and embedded
>PL/SQL (as opposed to simple embedded SQL). I guess I need to dig out the
>manuals on that one.
>
>Thanks for the help though. Definitely food for thouight. Ta.

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 Tue Oct 27 1998 - 12:24:18 CST