Home » SQL & PL/SQL » SQL & PL/SQL » dynamic pl/sql method 4
dynamic pl/sql method 4 [message #37411] Wed, 06 February 2002 09:35 Go to next message
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
Re: dynamic pl/sql method 4 [message #37439 is a reply to message #37411] Thu, 07 February 2002 10:57 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:484384
Previous Topic: Query on message at login
Next Topic: moving column from one table to another...
Goto Forum:
  


Current Time: Mon Sep 27 18:00:12 CDT 2021