Re: DBMS_SQL: How to fetch from dynamically built SELECT?

Hi Klim,

suggestion: you create a view that only returns everything converted to varchar2.

Martin

Klim Samgin wrote:
>
> 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 Fri Dec 10 1999 - 11:52:25 CST