Home » RDBMS Server » Server Administration » Text from a ddl (Oracle 8.1.7.4 Aix 4.3.3)
Text from a ddl [message #340883] Thu, 14 August 2008 15:16 Go to next message
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
Re: Text from a ddl [message #340884 is a reply to message #340883] Thu, 14 August 2008 15:20 Go to previous messageGo to next message
BlackSwan
Messages: 25037
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!
Re: Text from a ddl [message #340885 is a reply to message #340884] Thu, 14 August 2008 15:30 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

anacedent wrote on Thu, 14 August 2008 15:20
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!


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
Re: Text from a ddl [message #340887 is a reply to message #340883] Thu, 14 August 2008 15:38 Go to previous messageGo to next message
BlackSwan
Messages: 25037
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;
Re: Text from a ddl [message #340895 is a reply to message #340887] Thu, 14 August 2008 15:55 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

thank you very much!!!!!!
this work for me (it may be cause is oracle 8?)
Re: Text from a ddl [message #340922 is a reply to message #340883] Thu, 14 August 2008 23:55 Go to previous message
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
Previous Topic: Problem creating database
Next Topic: ERROR ORA-01114
Goto Forum:
  


Current Time: Mon Dec 05 08:57:36 CST 2016

Total time taken to generate the page: 0.21097 seconds