Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL Loop Over Tables for "desc"
aaron_at_mcs-partners.com wrote:
> Yep, I'm a noob. I did rtfm, and I searched Google. Found a couple
> of things that were close, but no cookie. I have hopes someone more
> experienced could shed some light on this.
>
> Using: (Not my choice and no immediate option to upgrade.)
>
> Oracle7 Server Release 7.3.3.6.0 - Production Release
> PL/SQL Release 2.3.3.6.0 - Production
>
>
> I want to loop over the non-system tables and get a "desc" of those
> tables. I could probably do it another way, but I want to know why
> the following doesn't work:
>
> -- set serveroutput on
> declare
> CURSOR table_cur IS SELECT table_name from dba_tables where owner
> not in ('SYSTEM','SYS') order by table_name;
> vtable dba_tables%rowtype;
> begin
> open table_cur;
> FOR vtable IN table_cur
> LOOP
> dbms_output.put_line(vtable);
> describe vtable;
> dbms_output.put_line('');
> END LOOP;
> close table_cur;
> end;
> /
>
>
> I get this output:
>
> describe vtable;
> *
> ERROR at line 9:
> ORA-06550: line 9, column 14:
> PLS-00103: Encountered the symbol "VTABLE" when expecting one of the
> following:
> := . ( @ % ;
> The symbol ":=" was substituted for "VTABLE" to continue.
>
>
> Bonus question: when I enable the "set serveroutput on" line, I get an
> extra error complaining about the following "declare" line. Any ideas
> why?
>
> declare
> *
> ERROR at line 1:
>
> Thanks for any help.
>
I assume that what you want is a "programatic" way to let your code see
the table name, column names, types and lengths.
Try the view named "col". E.g Select * from COL;
The columns are tname (the table name), cname (The column name, and so on.
You can put it in a loop
For X in (Select tname,cname, ...
From COL Where ... )
--Each row (tname, cname etc ) can be processed here End loop; Received on Fri Feb 09 2007 - 21:11:03 CST