PL/SQL errors

[Unna]

Hi, I am new in writing pl/sql code, I hope you can give me a help!

When I tried to run the code as following,

***************************************************
declare
cursor ctron_cur is
select table_name from dba_tables where owner = 'EMP';
c_ctron_rec ctron_cur%rowtype;
begin
if not ctron_cur%isopen then
open ctron_cur;
end if;
spool c:\ctro_tbls.txt
loop
fetch ctron_cur into c_ctron_rec;
if (ctron_cur%notfound) then
exit;
end if;
describ emp.(c_ctron_rec.table_name);
end loop;
close ctron_cur;
spool off
end;
/

***************************************************

I ran into an error message as,

Input truncated to 1 characters
spool c:\ctro_tbls.txt
*
ERROR at line 9:
ORA-06550: line 9, column 8:
PLS-00103: Encountered the symbol "C" when expecting one of the following:
:= . ( @ % ;


If I removed both lines of spool c:\ctro_tbls.txt and spool off and put them before and after excuting this script, then I got this error message:

Input truncated to 1 characters
describ emp.(c_ctron_rec.table_name);
*
ERROR at line 20:
ORA-06550: line 20, column 11:
PLS-00103: Encountered the symbol "EMP" when expecting one of the following:
:= . ( @ % ;


I don't understand why? Does pl/sql take spool and desc in its block? What should I do to make this program run. Thanks!

[chrisrlong]

Unfortunately, you are mixing languages, as it were. Spool and Desc are SQL*PLUS commands, and not part of SQL or PL/SQL.

What you may want to do is

DBMS_OUTPUT.PUT_LINE ( "describ emp.c_ctron_rec.table_name; "_;

...instead.

Then, when you finish running this anonymous block in SQL*PLUS, you can get the output (however that is done in SQL*PLUS - hate the tool myself :)) and you will have a script that describes all your tables. You can then wrap that in the spool statements and you are good to go. Of course, you could also use one of a hundred utilities and applications out there to browse your tables as well.

Anyway, hope this helps,

- Chris

[Unna]

Hi Chris,

DBMS_OUTPUT.PUT_LINE ( "describ emp.c_ctron_rec.table_name; ");

diplays a line of string with "describ emp.c_ctron_rec.table_name;". I am trying to list a list of tables (over 1000) and their data structures. Is there a way that I can do it in pl/sql? Thanks.

[mber]

Try this:

DBMS_OUTPUT.PUT_LINE ( 'describ emp.'||c_ctron_rec.table_name||';');

[Unna]

Thanks Chris and mber, it works. The first time should work, but I did look carefully. Thanks for your help!

[chrisrlong]

Yeah - what mber said ;).

But again it begs the question: Why? Why even do this to yourself? What exactly are you going to do with a file that contains thousand of lines of describe outputs? Why don't you just selectively query USER_TABLES, USER_TAB_COLUMNS and the other dictionary views for the info you need? Even if you need this huge text file, some queries against the dictionary views would make more sense than 1000 describe outputs. Personally, I'd reverse-engineer the whole thing into ErWin (a 10 minute exercise) and get some pictures of the model that would be a whole lot more useful.

Just my .02

- Chris

[Unna]

I have a big project coming that is going to be written in pl/sql code. Since I am new to pl/sql, I am looking for more chance to warm up, to write pl/sql codes (even though it can be done in a simple sql query) before this project. If you have any PL/SQL codes that you can share with me, would you mind send it to me at: wujee@hotmail.com. That will be really helpful for me to get better with pl/sql. Thanks!