DBMS_SQL: How to fetch from dynamically built SELECT?

Hi!
I've got table, for example,

SQL> desc mytab;

 Name                            Null?    Type
 ------------------------------- -------- ----
 F1                                       NUMBER(10)
 F2                                       CHAR(1)
 F3                                       VARCHAR2(15)
 ..................................................
 ..................................................
 Fxx                                      DATE

With DBMS_SQL, my PL/SQL programm dynamically built query on some columns, for example,

....
stmnt varchar2(1500);
....
stmnt = 'select f1, f4, f15, f26 from mytab'

I don't know which columns are in the query, all of them or only F3, or F6 and F11. But I know how many columns are in the query, 10 or 1, or 2.

If I know, which columns are in the query, for example, f1, f2, f3:

stmnt = 'select f1, f2, f3 from mytab',

Oracle Documentation recommends the following way:

..............................................

cursor_name     pls_integer;
ignore          pls_integer;

stmnt           varchar(4000);

rv1             number(20);
rv2             char(100);
rv3             varchar2(1000);

..............................................

• stmnt = 'select f1, f2, f3 from mytab'

    cursor_name := dbms_sql.open_cursor;

    dbms_sql.parse(cursor_name, stmnt, dbms_sql.native);

• Because I know that f1 is number(10),
• f2 is char(1),
• f3 is varchar2(15),
• I type:

    dbms_sql.define_column(cursor_name, 1, rv1);
    dbms_sql.define_column(cursor_name, 2, rv2);
    dbms_sql.define_column(cursor_name, 3, rv3);

    ignore := dbms_sql.execute(cursor_name);

    while dbms_sql.fetch_rows(cursor_name) > 0 loop

        dbms_sql.column_value(cursor_name, 1, rv1);
        dbms_sql.column_value(cursor_name, 2, rv2);
        dbms_sql.column_value(cursor_name, 3, rv3);

        dbms_output.put_line('rv1 = ' || rv1);
        dbms_output.put_line('rv2 = ' || rv2);
        dbms_output.put_line('rv3 = ' || rv3);

    end loop;

    dbms_sql.close_cursor(cursor_name);

..............................................

But my programm doesn't know, which column is on position 1 in the query (date or number or char),
which - on position 2, and et cetera.

So, I can't type

    dbms_sql.define_column(cursor_name, 1, rv1); and

    dbms_sql.column_value(cursor_name, 1, rv1);

because rv1 is number(20) and is not date,

        rv2 is char(100) and is not number, etc. And so on.
And I can't fetch rows from the query...

Can anyone help me?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 01 1999 - 09:55:12 CST