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: Lee <Lee_at_JamToday.com>
Date: Fri, 09 Feb 2007 22:11:03 -0500
Message-ID: <eqjd7u$qmm$1@reader2.panix.com>


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

Loop

    --Each row (tname, cname etc ) can be processed here End loop; Received on Fri Feb 09 2007 - 21:11:03 CST

Original text of this message

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