OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Text from a ddl (Oracle 8.1.7.4 Aix 4.3.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Text from a ddl [message #340883]

Thu, 14 August 2008 15:16

thorin666
Messages: 144
Registered: March 2007

Senior Member

Hi, i want to know if anyone know where i can get the ddl of any object, table, package, index or view.
I know that toad or sqlnavigator can get the sql from a table or object, but i don't know howto search it using sqlplus. Is there any view to get it?
thank you in advance

Report message to a moderator

Re: Text from a ddl [message #340884 is a reply to message #340883]

Thu, 14 August 2008 15:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

DBMS_METADATA is the current way.
I don't recall if that existed in V8.1 or not.
It can be mined out of the Data Dictionary, but it is messy.
alternatively do
exp full=yes rows=no
imp show=yes

you really, really should be using a supported Oracle DB version at least during the current century!

Report message to a moderator

Re: Text from a ddl [message #340885 is a reply to message #340884]

Thu, 14 August 2008 15:30

thorin666
Messages: 144
Registered: March 2007

Senior Member

anacedent wrote on Thu, 14 August 2008 15:20

anacedent, thank you for your quick response.
i know that you're right(about the version thing), but you must to know that the version of the dbms is not my last word, is a directive indication.

btw, i must to know a ddl of a package body

SELECT dbms_metadata.get_ddl('PACKAGE BODY','NAMEBODY') FROM DUAL;
                     *
ERROR at line 1:
ORA-00904: invalid column name

Report message to a moderator

Re: Text from a ddl [message #340887 is a reply to message #340883]

Thu, 14 August 2008 15:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> desc user_source
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)

SELECT TEXT FROM USER_SOURCE WHERE NAME = 'PACKAGE_NAME' ORDER BY LINE;

Report message to a moderator

Re: Text from a ddl [message #340895 is a reply to message #340887]

Thu, 14 August 2008 15:55

thorin666
Messages: 144
Registered: March 2007

Senior Member

thank you very much!!!!!!
this work for me (it may be cause is oracle 8?)

Report message to a moderator

Re: Text from a ddl [message #340922 is a reply to message #340883]

Thu, 14 August 2008 23:55

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

So you have only export/import option available.

Here it is http://arjudba.blogspot.com/2008/04/extract-ddl-from-dump.html

Use indexfile=y rather than show=y

Report message to a moderator

Previous Topic:	Problem creating database
Next Topic:	ERROR ORA-01114

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 08 07:59:27 CDT 2024