Re: Dynamic Cursors - How?

dov_shnaider_at_my-dejanews.com wrote:

> Hi,
> I need to create a cursor dynamically ( including the Select, The From and The
> Where statements ). I am trying to use the DBMS_SQL package but... I don't see
> how to create a dynamic "dbms_sql.define_column(" and "dbms_sql.column_value"
> statements. So, I can parse a dynamic SELECT statement but I can not
> dynamically retrieve the values. Any ideas ?

Hi,

suppose the query:

    my_query:= 'select firstname,lastname from emp';

then the code is:

....
my_name varchar2(20);
my_lname varchar2(20);
.....

• open cursor:: source_cursor := DBMS_SQL.OPEN_CURSOR;
• parse query: DBMS_SQL.PARSE(source_cursor,my_query,DBMS_SQL.V7);
• define columns: DBMS_SQL.DEFINE_COLUMN(source_cursor,1,my_name,20); DBMS_SQL.DEFINE_COLUMN(source_cursor,2,my_lname,20);
• execute query: ignore := DBMS_SQL.EXECUTE(source_cursor);
• fetch record: LOOP IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN DBMS_SQL.COLUMN_VALUE(source_cursor,1,my_name); DBMS_SQL.COLUMN_VALUE(source_cursor,2,my_lname);

            htp.p(my_name); htp.p(my_lname);

        ELSE
         -- no more row --
             EXIT;
        END IF;

    END LOOP;

• close cursor: DBMS_SQL.CLOSE_CURSOR(source_cursor);

.....

Ok?
Bye............Giuseppe Received on Thu Jun 04 1998 - 10:48:05 CDT