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

I've written up a sample of how to do
on my web-site. It's a bit unwieldy,
but it works.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Klim Samgin wrote in message <823gce$ghm$1_at_nnrp1.deja.com>...
>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...
Received on Wed Dec 01 1999 - 10:05:25 CST