Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL Loop Over Tables for "desc"

Re: PL/SQL Loop Over Tables for "desc"

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Fri, 09 Feb 2007 03:00:40 GMT
Message-ID: <Xns98D1C16727082anacedenthotmailcom@69.28.173.184>


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:

>:= . ( @ % ;

> 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.
>
>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US