dynamic pl/sql method 4 [message #37411] |
Wed, 06 February 2002 09:35 |
Hao
Messages: 4 Registered: December 2000
|
Junior Member |
|
|
Hi All;
I have a need to build a procedure to retrieve all columns( select *) from different table name from the input parameter(CREATE OR REPLACE PROCEDURE ht_nds_4(
table_in IN VARCHAR2).
Using method 4 I have coded like this:
sql_stmt := 'SELECT * FROM ' || table_in ||
' WHERE ROWNUM < 10';
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, sql_stmt,DBMS_SQL.V7);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
How can I define the DBMS_SQL.DEFINE_COLUMN and DBMS_SQL.COLUMN_VALUE to handle the situation?
Thanks a bunch..
Hao
|
|
|
|