is it possible to run dbms_metadata.get_ddl and exclude column default value and virtual columns?

Question

Example:

SELECT dbms_metadata.get_ddl('TABLE', table_name, 'BOB') FROM user_tables WHERE table_name = 'APPOINTMENTS';

With various transforms renders:

create table "bob"."appointments"
       (
        "appointment_id" number(12,0)                   default "bob"."appointment_id"."nextval",
        "appointment_title" varchar2(50 byte),
        "appointment_priority"                          number generated always as <some logic> virtual
       ) ;

Can the default value and virtual column be stripped from the output?

Answer 1

In general, you can use DBMS_METADATA.SET_TRANSFORM_PARAM to change the output for the DBMS_METADATA package.

Given the setup:

CREATE SEQUENCE "appointment_id";

create table "appointments"
       (
        "appointment_id" number(12,0)                   default "appointment_id".nextval,
        "appointment_title" varchar2(50 byte),
        "appointment_priority"                          number generated always as (1) virtual
       ) ;

Then:

SELECT dbms_metadata.get_ddl(
          'TABLE',
          'appointments',
          USER
       )
FROM   DUAL;

Outputs:

DBMS_METADATA.GET_DDL('TABLE','APPOINTMENTS',USER)
CREATE TABLE "FIDDLE_QANZBEWCAVFHDLPFZAAC"."appointments"    ( "appointment_id" NUMBER(12,0) DEFAULT "FIDDLE_QANZBEWCAVFHDLPFZAAC"."appointment_id"."NEXTVAL", "appointment_title" VARCHAR2(50), "appointment_priority" NUMBER GENERATED ALWAYS AS (1) VIRTUAL    ) SEGMENT CREATION DEFERRED   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   TABLESPACE "USERS"

and, if you apply some transform parameters:

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

SELECT dbms_metadata.get_ddl(
          'TABLE',
          'appointments',
          USER
       )
FROM   DUAL;

Then the output changes to:

DBMS_METADATA.GET_DDL('TABLE','APPOINTMENTS',USER)
CREATE TABLE "FIDDLE_HKDFRKLPCSXVVQXILGQE"."appointments"    ( "appointment_id" NUMBER(12,0) DEFAULT "FIDDLE_HKDFRKLPCSXVVQXILGQE"."appointment_id"."NEXTVAL", "appointment_title" VARCHAR2(50), "appointment_priority" NUMBER GENERATED ALWAYS AS (1) VIRTUAL    ) ;

---

A full list of transform parameters is given in the DBMS_METADATA documentation and, to answer your specific question:

Can the default value and virtual column be stripped from the output?

Using only the DBMS_METADATA package, no, there does not appear to be any transform parameter that would disable the output of the DEFAULT or GENERATED clauses for a column.

If you wanted to strip those clauses then you would need to do it after generating the DDL statement by rewriting the query (maybe using a simple regular expression - to look for, in the simplest cases, DEFAULT or GENERATED and remove everything until the next comma or the VIRTUAL keyword - or, in more complicated cases, by parsing the query to an Abstract Syntax Tree and pruning the appropriate branches).

fiddle