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

Home -> Community -> Usenet -> c.d.o.server -> Re: how to list all table info use dbms_metadata.get_ddl????

Re: how to list all table info use dbms_metadata.get_ddl????

From: HansF <News.Hans_at_telus.net>
Date: Fri, 01 Apr 2005 13:13:04 GMT
Message-Id: <pan.2005.04.01.14.14.47.554906@telus.net>


On Fri, 01 Apr 2005 04:44:53 -0800, aaa interested us by writing:

> I tried to list all tables under user's schema use
> "dbms_metadata.get_ddl" but fail. Does anyone know
> how to do it?
>
> SQL> select dbms_metadata.get_ddl( 'TABLE',,'USER1')
> from dual;
> select dbms_metadata.get_ddl( 'TABLE',,'USER1') from
> dual
> *
> ERROR at line 1:
> ORA-00936: missing expression
>
>
> Thanks.

From the 9iR2 "Oracle9i Supplied PL/SQL Packages and Types Reference" manual ... "These functions provide a simple way to return the metadata for a single object". Note the 'single object' bit.

Method 1:

Create a loop on USER_TABLES, run the select specifying the table name as parm 2.

Method 2:

Use the sample code provided in the manual

<heavy sigh>

-- 
Hans Forbrich
Oracle training and consulting in Canada
mailto: Fuzzy.GreyBeard _at_ gmail.com   
    or: echo "News.Hans_at_Telus_NOSPAM.net" | sed s/_NOSPAM//g
Received on Fri Apr 01 2005 - 07:13:04 CST

Original text of this message

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