Two Laptop Bag
The moose likes JDBC and Relational Databases and the fly likes Use PL/SQL Loop and variable Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Use PL/SQL Loop and variable" Watch "Use PL/SQL Loop and variable" New topic

Use PL/SQL Loop and variable

Jane Somerfield
Ranch Hand

Joined: Jul 20, 2002
Posts: 193
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

Joined: Dec 20, 2006
Posts: 2565

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

OCUP UML fundamental and ITIL foundation
Jane Somerfield
Ranch Hand

Joined: Jul 20, 2002
Posts: 193
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
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
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 ]
I agree. Here's the link:
subject: Use PL/SQL Loop and variable
jQuery in Action, 3rd edition