X-DDL command

[ Home / SQLBatch Home / Documentation / Commands ]


SYNTAX

X-DDL object_type object_name ;

DESCRIPTION

X-DDL command prints the DDL command for creating the object specified.

PARAMETERS

object_type one of the following:
  • TABLE
  • VIEW
  • SYNOMYM
  • CONSTRAINT
  • INDEX
  • SEQUENCE
  • TRIGGER
  • FUNCTION
  • PROCEDURE
  • PACKAGE
  • PACKAGE BODY
  • TYPE
  • TYPE BODY
  • object_name [owner.]name

    If the owner is omitted the user name specified in the CONNECT command will be used unless you have executed X-SET SCHEMA command before the X-DDL command.

    DDL, an SB special variable controls the output of the X-DDL command.

    EXAMPLE 1

    SQL>x-ddl table sys.obj$;
    CREATE TABLE sys.obj$
    (
            obj#    NUMBER NOT NULL,
            dataobj#        NUMBER,
            owner#  NUMBER NOT NULL,
            name    VARCHAR2(30) NOT NULL,
            namespace       NUMBER NOT NULL,
            subname VARCHAR2(30),
            type#   NUMBER NOT NULL,
            ctime   DATE NOT NULL,
            mtime   DATE NOT NULL,
            stime   DATE NOT NULL,
            status  NUMBER NOT NULL,
            remoteowner     VARCHAR2(30),
            linkname        VARCHAR2(128),
            flags   NUMBER,
            oid$    RAW,
            spare1  NUMBER,
            spare2  NUMBER,
            spare3  NUMBER,
            spare4  VARCHAR2(1000),
            spare5  VARCHAR2(1000),
            spare6  DATE
    )
    TABLESPACE SYSTEM
    PCTFREE 10 PCTUSED 40
    STORAGE(INITIAL 12K NEXT 100K PCTINCREASE 0 )
    

    EXAMPLE 2

    
    SQL>{ddl->name_case='U';}
    SQL>x-ddl table sys.obj$;
    
    CREATE TABLE SYS.OBJ$
    (
            OBJ#    NUMBER NOT NULL,
            DATAOBJ#        NUMBER,
            OWNER#  NUMBER NOT NULL,
            NAME    VARCHAR2(30) NOT NULL,
            NAMESPACE       NUMBER NOT NULL,
            SUBNAME VARCHAR2(30),
            TYPE#   NUMBER NOT NULL,
            CTIME   DATE NOT NULL,
            MTIME   DATE NOT NULL,
            STIME   DATE NOT NULL,
            STATUS  NUMBER NOT NULL,
            REMOTEOWNER     VARCHAR2(30),
            LINKNAME        VARCHAR2(128),
            FLAGS   NUMBER,
            OID$    RAW,
            SPARE1  NUMBER,
            SPARE2  NUMBER,
            SPARE3  NUMBER,
            SPARE4  VARCHAR2(1000),
            SPARE5  VARCHAR2(1000),
            SPARE6  DATE
    )
    TABLESPACE SYSTEM
    PCTFREE 10 PCTUSED 40
    STORAGE(INITIAL 12K NEXT 100K PCTINCREASE 0 )
    
    

    EXAMPLE 3

    
    SQL>{ddl->name_case='L';}
    SQL>{ddl->table->indexes=1;}
    
    CREATE TABLE sys.obj$
    (
            obj#    NUMBER NOT NULL,
            dataobj#        NUMBER,
            owner#  NUMBER NOT NULL,
            name    VARCHAR2(30) NOT NULL,
            namespace       NUMBER NOT NULL,
            subname VARCHAR2(30),
            type#   NUMBER NOT NULL,
            ctime   DATE NOT NULL,
            mtime   DATE NOT NULL,
            stime   DATE NOT NULL,
            status  NUMBER NOT NULL,
            remoteowner     VARCHAR2(30),
            linkname        VARCHAR2(128),
            flags   NUMBER,
            oid$    RAW,
            spare1  NUMBER,
            spare2  NUMBER,
            spare3  NUMBER,
            spare4  VARCHAR2(1000),
            spare5  VARCHAR2(1000),
            spare6  DATE
    )
    TABLESPACE SYSTEM
    PCTFREE 10 PCTUSED 40
    STORAGE(INITIAL 12K NEXT 100K PCTINCREASE 0 )
    
    /
    CREATE UNIQUE INDEX sys.i_obj1 ON sys.obj$ (obj#)
    TABLESPACE SYSTEM
    PCTFREE 10
    STORAGE(INITIAL 12K NEXT 1260K PCTINCREASE 50 )
    
    /
    CREATE UNIQUE INDEX sys.i_obj2 ON sys.obj$ (owner#, name, namespace, remoteowner
    , linkname, subname)
    TABLESPACE SYSTEM
    PCTFREE 10
    STORAGE(INITIAL 12K NEXT 100K PCTINCREASE 0 )
    
    /
    CREATE INDEX sys.i_obj3 ON sys.obj$ (oid$)
    TABLESPACE SYSTEM
    PCTFREE 10
    STORAGE(INITIAL 12K NEXT 12K PCTINCREASE 50 )
    
    

    EXAMPLE 4

    SQL>x-ddl view sys."ALL_SYNONYMS";
                        
    CREATE OR REPLACE VIEW sys.all_synonyms
    (
            OWNER,
            SYNONYM_NAME,
            TABLE_OWNER,
            TABLE_NAME,
            DB_LINK
    )
    AS
    select u.name, o.name, s.owner, s.name, s.node
    from sys.user$ u, sys.syn$ s, sys.obj$ o
    where o.obj# = s.obj#
      and o.type# = 5
      and o.owner# = u.user#
      and (
           o.owner# in (USERENV('SCHEMAID'), 1 /* PUBLIC */)  /* user's private, any
     public */
           or /* user has any privs on base object */
            exists
            (select null from sys.objauth$ ba, sys.obj$ bo, sys.user$ bu
             where bu.name = s.owner
               and bo.name = s.name
               and bu.user# = bo.owner#
               and ba.obj# = bo.obj#
               and ba.grantee# in (select kzsrorol from x$kzsro))
            or /* user has system privileges */
             exists (select null from v$enabledprivs
                     where priv_number in (-45 /* LOCK ANY TABLE */,
                                           -47 /* SELECT ANY TABLE */,
                                           -48 /* INSERT ANY TABLE */,
                                           -49 /* UPDATE ANY TABLE */,
                                           -50 /* DELETE ANY TABLE */)
                     )
           )
    

    Copyright (C) 1999 - 2014 Anatoly Moskovsky Report a site problem