Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, January 27, 2009

DBMS_METADATA

Use DBMS_METADATA package to generate the syntax of any object in database:

For example to get the definition of DEPT table in SCOTT schema you can use this command:

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

Here's an example from Apps:

SQL> select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual;
select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual
*
ERROR at line 1:
ORA-24813: cannot send or receive an unsupported LOB

You'll get the above error if you are trying to use this command when your ORACLE_HOME is the 8.0.6 ORACLE_HOME.

Login through the 10.2.0 ORACLE_HOME

SQL> select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','FND_NODES','APPLSYS')
----------------------------------------------------------------
  CREATE TABLE "APPLSYS"."FND_NODES"
   (    "NODE_NAME" VARCHAR2(30) NOT NULL EN

You need to set long to 2000 or higher to see the full syntax:

SQL> set long2000
SQL> /

DBMS_METADATA.GET_DDL('TABLE','FND_NODES','APPLSYS')
--------------------------------------------------------------------
  CREATE TABLE "APPLSYS"."FND_NODES"
   (    "NODE_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
        "LAST_UPDATED_BY" NUMBER(15,0) NOT NULL ENABLE,
        "CREATION_DATE" DATE NOT NULL ENABLE,
        "CREATED_BY" NUMBER(15,0) NOT NULL ENABLE,
        "LAST_UPDATE_LOGIN" NUMBER(15,0) NOT NULL ENABLE,
        "PLATFORM_CODE" VARCHAR2(30) NOT NULL ENABLE,
        "DESCRIPTION" VARCHAR2(240),
        "BASEPATH" VARCHAR2(20),
        "SUPPORT_CP" VARCHAR2(1),
        "SUPPORT_FORMS" VARCHAR2(1),
        "SUPPORT_WEB" VARCHAR2(1),
        "SUPPORT_ADMIN" VARCHAR2(1),
        "STATUS" VARCHAR2(1),
        "PING_RESPONSE" VARCHAR2(2000),
        "LAST_MONITORED_TIME" DATE,
        "NODE_MODE" VARCHAR2(1),
        "NODE_ID" NUMBER,
        "SERVER_ID" VARCHAR2(64),
        "SERVER_ADDRESS" VARCHAR2(30),
        "HOST" VARCHAR2(256),
        "DOMAIN" VARCHAR2(256),
        "WEBHOST" VARCHAR2(256),
        "VIRTUAL_IP" VARCHAR2(256),
        "SUPPORT_DB" VARCHAR2(1),
        "APPLTOP_ID" RAW(16)
   ) PCTFREE 5 PCTUSED 80 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGI
NG
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_DATA"

1 comment:

Martand Joshi said...

Hi Vikram,

Very useful information and very systematically drafted. Was looking for such package from many days.

Thanks,
Martand