Home » SQL & PL/SQL » SQL & PL/SQL » how can i do this procedure? (oracle 10g xe)
how can i do this procedure? [message #288148] Sat, 15 December 2007 02:21 Go to next message
futi
Messages: 22
Registered: November 2007
Junior Member
Hi...
For example, i have a procedure that print the each column of each row of EMPLOYEES table.

FOR REC IN CURSOR1
LOOP
DBMS_OUTPUT.PUT_LINE(rec.name);
DBMS_OUTPUT.PUT_LINE(rec.surname);
DBMS_OUTPUT.PUT_LINE(rec.salary);
END LOOP;

it is okey.

But i want to create a new procedure that gets the table name as an argument. Then, procedure print each column of each row of that table.
Such as:

CURSOR CURSOR1 IS SELECT * FROM argument1;
...
..
FOR REC IN CURSOR1
LOOP
DBMS_OUTPUT.PUT_LINE(rec.?);
DBMS_OUTPUT.PUT_LINE(rec.?);
DBMS_OUTPUT.PUT_LINE(rec.?);
END LOOP;

i dont know how much columns are in that table? it must be dynamic.
for example, sometimes there are 3 column, sometimes there 5. And i dont know what are the names of columns?(rec.????)

How can i create this dynamic procedure? I am waiting your helps.
thanks
Re: how can i do this procedure? [message #288151 is a reply to message #288148] Sat, 15 December 2007 02:34 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Is there any requirement you use plsql instead of sql.
Re: how can i do this procedure? [message #288160 is a reply to message #288148] Sat, 15 December 2007 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dbms_sql

Regards
Michel
Re: how can i do this procedure? [message #288191 is a reply to message #288160] Sat, 15 December 2007 11:43 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Tom Kyte has a nice print_table procedure that will output the results of any query to the screen in that manner. You can find the code, demonstration, and explanation here:

http://asktom.oracle.com/tkyte/print_table/
Previous Topic: Help needed
Next Topic: SQL questions from psycroptik (merged)
Goto Forum:
  


Current Time: Sun Dec 11 04:15:18 CST 2016

Total time taken to generate the page: 0.38326 seconds