PL/SQL and DESC

[denni...@eaton.com]

I'm at the lowest possible level of the PL/SQL learning curve here...

My goal is to write a script (I believe this is PL/SQL??) that will
describe all tables.

So, I began by creating a cursor, that looks like this:

cursor c is
select table_name
from all_tables
where table_name like '%BANK%';

In the begin/end block, I simply (??) loop through this cursor to get
each table name one at a time. My thought was that I would just do a
"desc" on each of these. It looks like this:

loop
fetch c into field; (field is declared as a char(30)
exit when c%notfound;
describe field
end loop;

When I go to run this, it produces an error saying it wasn't expecting
the word "field" after describe.

What's wrong? What am I missing? Help!!!!

Can anyone suggest a book that will give me a very basic understanding
of SQL, PL/SQL, and SQL*Plus and how this all fits together? I am
really really confused. Thanks in advance...

Dennis Hancy
Eaton Corporation
Cleveland, OH

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[stan_h...@my-deja.com]

In article <7o9f5v$brv$1...@nnrp1.deja.com>,

denni...@eaton.com wrote:
>
>
> I'm at the lowest possible level of the PL/SQL learning curve here...
>
> My goal is to write a script (I believe this is PL/SQL??) that will
> describe all tables.

You can do such a thing w/ PL/SQL, but there is an easier way. Spool
the following SQL output to a file:

select 'DESC '||owner||'.'||table_name

from all_tables
where table_name like '%BANK%';

Then run that file in SQL*Plus....

>
> So, I began by creating a cursor, that looks like this:
>
> cursor c is
> select table_name
> from all_tables
> where table_name like '%BANK%';

For this to work in PL/SQL, you'll need a cursor for the columns also
(ALL_TAB_COLUMNS)....

>
> In the begin/end block, I simply (??) loop through this cursor to get
> each table name one at a time. My thought was that I would just do a
> "desc" on each of these. It looks like this:
>
> loop
> fetch c into field; (field is declared as a char(30)
> exit when c%notfound;
> describe field
> end loop;
>
> When I go to run this, it produces an error saying it wasn't expecting
> the word "field" after describe.

I'm pretty sure it was expecting a rowtype variable for this fetch.
However, I'd expect a different message...

Anyway, there is an easier way to loop through cursors:

for row_var in c
loop
dbms_output.put_line(row_var.table_name) ;
end loop;

This is called an implicit cursor for loop. It dynamicly declares
row_var as a rowtype variable for the records being returned by the
cursor (named c). The implicit for loop also handles the fetch and
exit when %notfound stuff....

Stan
--
Stan Herrman
Database Consultants, www.dci-ltd.com
stan_h...@my-deja.com

[Philippe]

Describe is a Sql/plus command not an oracle command:
try to select in system table ALL_TAB_COLUMNS WHERE TABLE_NAME = YourTable

HTH,
Philippe

denni...@eaton.com wrote in message <7o9f5v$brv$1...@nnrp1.deja.com>...

>
>
>I'm at the lowest possible level of the PL/SQL learning curve here...
>
>My goal is to write a script (I believe this is PL/SQL??) that will
>describe all tables.
>

>So, I began by creating a cursor, that looks like this:
>
>cursor c is
> select table_name
> from all_tables
> where table_name like '%BANK%';
>
>

>In the begin/end block, I simply (??) loop through this cursor to get
>each table name one at a time. My thought was that I would just do a
>"desc" on each of these. It looks like this:
>
>
>loop
> fetch c into field; (field is declared as a char(30)
> exit when c%notfound;
> describe field
>end loop;
>
>
>When I go to run this, it produces an error saying it wasn't expecting
>the word "field" after describe.
>

>What's wrong? What am I missing? Help!!!!
>
>Can anyone suggest a book that will give me a very basic understanding
>of SQL, PL/SQL, and SQL*Plus and how this all fits together? I am
>really really confused. Thanks in advance...
>
>
>Dennis Hancy
>Eaton Corporation
>Cleveland, OH
>
>

[Martin Douglas]

Describe has not been implemented as a function in PL/SQL, if I recall
correctly. You need to use one of the DBMS packages that support such a
call.