Not getting normal DDL command while fetching DDL of table

Question

Hi I was fetching DDL of a Table using dbms_metadata.get_ddl('TABLE', 'A_fetch') . While fetching DDL i noticed that there are additional technical term(metdata) coming from DDL.

CREATE TABLE "A_test" 
(   "ID" NUMBER, 
"NAME" VARCHAR2(90 BYTE) COLLATE "USING_NLS_COMP", 
"ADDRESS" VARCHAR2(90 BYTE) COLLATE "USING_NLS_COMP", 
"GENDER" CHAR(1 BYTE) COLLATE "USING_NLS_COMP"
)  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "QNBDEV" ; 

Expected :I just want normal DDl like create table A_test(id number,name varchar2(90),address varchar2(90),gender char(1);

Answer 1

Set the format you want the DBMS_METADATA package to output using:

BEGIN
  DBMS_METADATA.set_transform_param(
    DBMS_METADATA.session_transform,
    'SQLTERMINATOR',
    TRUE
  );
  DBMS_METADATA.set_transform_param(
    DBMS_METADATA.session_transform,
    'PRETTY',
    FALSE
  );
  DBMS_METADATA.set_transform_param(
    DBMS_METADATA.session_transform,
    'SEGMENT_ATTRIBUTES',
    FALSE
  );
  DBMS_METADATA.set_transform_param(
    DBMS_METADATA.session_transform,
    'STORAGE',
    FALSE
  );
END;
/

Then you can use:

SELECT DBMS_METADATA.GET_DDL( 'TABLE', 'A_test', 'SCHEMA_NAME' ) AS ddl
FROM   DUAL;

Which outputs:

DDL
CREATE TABLE "SCHEMA_NAME"."A_test" ("ID" NUMBER, "NAME" VARCHAR2(90), "ADDRESS" VARCHAR2(90), "GENDER" CHAR(1));

You can also run it all as a single PL/SQL block:

DECLARE
  v_sql CLOB;
BEGIN
  DBMS_METADATA.set_transform_param(
    DBMS_METADATA.session_transform,
    'SQLTERMINATOR',
    TRUE
  );
  DBMS_METADATA.set_transform_param(
    DBMS_METADATA.session_transform,
    'PRETTY',
    FALSE
  );
  DBMS_METADATA.set_transform_param(
    DBMS_METADATA.session_transform,
    'SEGMENT_ATTRIBUTES',
    FALSE
  );
  DBMS_METADATA.set_transform_param(
    DBMS_METADATA.session_transform,
    'STORAGE',
    FALSE
  );
  SELECT DBMS_METADATA.GET_DDL( 'TABLE', 'A_test', USER )
  INTO   v_sql
  FROM   DUAL;

  DBMS_OUTPUT.PUT_LINE(v_sql);
END;
/

fiddle