Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL
Dynamic SQL [message #301778] Thu, 21 February 2008 12:51 Go to next message
mbalves
Messages: 20
Registered: May 2007
Junior Member
Hi,

This is a academic example to adapt in other situation. I want to show the data content of all tables in screen. As you can see in below example, I create a procedure “printTableContent” which I pass the table name as parameter.
How can I fetch a record in a dynamic SQL, where I don’t know exactly the data structure? (I tried to declare a variable like r cv%rowtype but return an error);
How can go through fields in a record that I don’t know exactly the data structure?

create or replace procedure printTableContent(pTableName varchar) is
TYPE cv_type IS REF CURSOR;
cv cv_type;
begin
open cv for 'Select * from ' || pTableName;
/* code to go through cursor and for each record, go through fields */
close cv;
end;
/

create or replace procedure printTablesContent is
begin
for i in (select table_name from user_tables)
loop
printTableContent(i.table_name);
end loop;
end;
/

As example, If i’m in Visual Basic I do something like:

Set rs = db.OpenRecordset("Select * from " & pTableName)
While Not rs.EOF
For ci = 0 To rs.Fields.Count
' code
Next ci
rs.MoveNext
Wend

Best, Miguel
Re: Dynamic SQL [message #301784 is a reply to message #301778] Thu, 21 February 2008 13:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use dbms_sql. It provides means to get the structure of the fetched rows.
Re: Dynamic SQL [message #301785 is a reply to message #301778] Thu, 21 February 2008 13:24 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at Tom Kyte's print_table procedure.

Regards
Michel
Previous Topic: getting error code ORA-24550
Next Topic: Formatting a SQL Query
Goto Forum:
  


Current Time: Sun Dec 11 00:28:09 CST 2016

Total time taken to generate the page: 0.05649 seconds