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 in news:1170983748.842791.265820
@j27g2000cwj.googlegroups.com:
> 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:
>:= . ( @ % ;
DESCRIBE is a SQL*Plus command & not valid within PL/SQL.
SELECT 'DESC " || table_name from dba_tables where owner not in ('SYSTEM','SYS') order by table_name;
Spool the results to a file & then invoke this spool file Received on Thu Feb 08 2007 - 21:00:40 CST