• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Use PL/SQL Loop and variable

 
Jane Somerfield
Ranch Hand
Posts: 193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to do

SQL> desc <tablename>

for 100 tables in Oracle.

I want to do it in a loop with an array of table names. Can anyone help me?
 
Jan Cumps
Bartender
Posts: 2599
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't have a pl/sql solution,
but try this script in sqlplus.
It will create a new sql script with a desc command for all tables.




When you exit sqlplus, you will find the script describetables.sql.
It contains the command to describe all your tables.

Regards, Jan
 
Jane Somerfield
Ranch Hand
Posts: 193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jan.

I do not want to get all the table's name.

I would like to create an array variable that contains the table names I need
and loop through the elements in the array using pl/sql.
 
Carol Enderlin
drifter
Ranch Hand
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Search on Dynamic PL/SQL.

Basically you can do

EXECUTE IMMEDIATE 'some string' || 'you concatenate' || 'from strings' || ' and variables: ' || v_table;

I don't think I would use that to describe tables (it's getting late, does desc even work from PL/SQL), I'd use the style that Jan suggested where you spool the commands into a text file and then run it. Well, I don't spool much any more since I use toad, but I basically take the output of a select and run it.

select 'desc ' || tablename || ';' from <wherever you are getting the table names>

[Untested code, no oracle available right this minute]
[ February 21, 2007: Message edited by: Carol Enderlin ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic