| Dynamic SQL [message #301778] |
Thu, 21 February 2008 12:51  |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
