We’ve all had that common frustration when we’re in the middle of building a query, or a database API, or a PL/SQL module and we need to know the structure of a table. We fire up either a call to DBMS_METADATA or we click on the DDL button in our favourite IDE and the next thing we do is… wait.
SQL> set timing onSQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')------------------------------------------------------------------------ CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME ...Elapsed: 00:00:02.12 <<<=== ugh!
That first call to DBMS_METADATA, whether it’s done directly by you or by the IDE, always takes a little bit of time to spin up, in order for it to marshal all the Java and XML components that it uses. Once that first execution is done, everything is fine, but typically it’s the first execution that only matters.
One of the nice things that came in Oracle Database 26 AI was a new package called DBMS_DEVELOPER, which also got back ported to the later release updates of 19c. Calling DBMS_DEVELOPER to get the dictionary definition of a table is blazingly fast.
SQL> set timing onSQL> select dbms_developer.get_metadata('EMP','SCOTT');DBMS_DEVELOPER.GET_METADATA('EMP','SCOTT')-----------------------------------------------------------------------{"objectType":"TABLE","objectInfo":{"schema":"SCOTT","columns":[{"name":"EMPNO",Elapsed: 00:00:00.04
The output is structured as JSON, which means you can see it’s probably intended as being a subordinate to IDEs or agents or any other component that needs to get the structure of a table. But that got me thinking – maybe I could build a simple layer on top of DBMS_DEVELOPER to get the DDL definition for a table and therefore never have to wait for DBMS_METADATA again. So I created a simple table, made a call to DBMS_DEVELOPER, and then started tinkering around with JSON table expressions to convert the JSON into the DDL for a table.
SQL> WITH input_json AS ( 2 select dbms_developer.get_metadata('EMP','SCOTT','TABLE','ALL') AS doc 3 FROM dual 4 ), 5 parsed AS ( 6 SELECT 7 rownum r, 8 jt.* 9 FROM input_json d 10 CROSS APPLY JSON_TABLE(d.doc, '$' 11 COLUMNS ( 12 table_name VARCHAR2(128) PATH '$.objectInfo.name', 13 schema_name VARCHAR2(128) PATH '$.objectInfo.schema', 14 NESTED PATH '$.objectInfo.columns[*]' 15 COLUMNS ( 16 col_name VARCHAR2(128) PATH '$.name', 17 not_null_flag VARCHAR2(5) PATH '$.notNull', 18 data_type VARCHAR2(60) PATH '$.dataType.type', 19 precision NUMBER PATH '$.dataType.precision', 20 scale NUMBER PATH '$.dataType.scale', 21 length NUMBER PATH '$.dataType.length', 22 size_units VARCHAR2(10) PATH '$.dataType.sizeUnits', 23 ts_precision NUMBER PATH '$.dataType.fractionalSecondsPrecision', 24 dy_precision NUMBER PATH '$.dataType.dayPrecision', 25 yr_precision NUMBER PATH '$.dataType.yearPrecision' 26 ) 27 ) 28 ) jt 29 ) 30 select stmt 31 from ( 32 select 0 x , 'create table ' || schema_name || '.' || table_name || ' (' stmt 33 from parsed 34 where r = 1 35 union all 36 select r, 37 case when r = 1 then ' ' else ',' end || 38 rpad(col_name,30) || ' ' || 39 case 40 when data_type in ('NUMBER','FLOAT','BINARY_DOUBLE','BINARY_FLOAT') then 41 case 42 when scale is not null then 43 data_type||'(' || precision || ',' || scale || ')' 44 when precision is not null then 45 data_type||'(' || precision || ')' 46 else 47 data_type 48 end 49 when data_type in ('VARCHAR2','CHAR','NVARCHAR2','RAW') then 50 data_type ||'('|| length || 51 case when size_units is not null then ' ' || size_units else '' end || ')' 52 when data_type like 'TIMESTAMP%' then 53 replace(case when ts_precision is not null then replace(data_type,'TIMESTAMP','TIMESTAMP('||ts_precision||')') else data_type end,'TIMEZONE','TIME ZONE') 54 when data_type like 'INTERVAL YEAR%' then 55 case when yr_precision is not null then replace(data_type,'YEAR','YEAR('||yr_precision||')') else data_type end 56 when data_type like 'INTERVAL DAY%' then 57 replace(case when dy_precision is not null then replace(data_type,'DAY','DAY('||dy_precision||')') else data_type end,'SECONDS','SECOND') 58 else data_type 59 end || 60 case when not_null_flag = 'true' then ' not null' end as stmt 61 from parsed 62 union all 63 select 99999, ');' 64 ) 65 order by x;STMT---------------------------------------------------------create table SCOTT.EMP ( EMPNO NUMBER(4) not null,ENAME VARCHAR2(10 BYTE),JOB VARCHAR2(9 BYTE),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2));
That all looks very promising, but of course the real test is can it deal with the myriad of permutations that we can stick into a table’s definition. So I then wanted to extend it as much as I could.
SQL> create or replace type address_typ as object ( 2 street varchar2(50), 3 city varchar2(30) 4 ); 5 /Type created.SQL>SQL> create table address_t of address_typ;Table created.SQL> create table t2 ( 2 c1 number(38) default 12 not null 3 ,c2 number(38) default 13 not null 4 ,c3 number(38) generated always as (c2+1) 5 ,c4 number(12,3) not null 6 ,c5 number reservable not null 7 ,c6 number(38) generated as identity not null primary key 8 ,vc1 char(10) invisible 9 ,vc2 varchar2(10) 10 ,vc3 nchar(4) 11 ,vc4 nvarchar2(10) 12 ,f float 13 ,bf1 binary_float 14 ,bf2 binary_double 15 ,x xmltype 16 ,c clob 17 ,nc nclob 18 ,b bfile 19 ,d date 20 ,ts1 timestamp 21 ,ts2 timestamp(3) 22 ,ts3 timestamp with local time zone 23 ,ts4 timestamp with time zone 24 ,i1 interval year to month 25 ,i2 interval day to second 26 ,obj1 sys.odcivarchar2list 27 ,refcol ref address_typ scope is address_t 28 ,rid rowid 29 ,urid urowid(4000) 30 ,js json 31 ,bool boolean 32 ,v vector(256) 33 ,anyd sys.anydata 34 ) 35 compress basic 36 partition by list (c1) automatic 37 ( partition p1 values (10));Table created.
Rest assured this is not the complete set of permutations you could do for a table but I figured it was enough for DBMS developer to have a decent crack at.
I then extended my query to handle the extra bits and pieces – Let’s see how it goes against this table.
SQL> WITH input_json AS ( 2 SELECT dbms_developer.GET_METADATA('T2',user,'TABLE','all') AS doc 3 FROM dual 4 ), 5 parsed AS ( 6 SELECT 7 rownum r, 8 jt.* 9 FROM input_json d 10 CROSS APPLY JSON_TABLE(d.doc, '$' 11 COLUMNS ( 12 table_name VARCHAR2(128) PATH '$.objectInfo.name', 13 schema_name VARCHAR2(128) PATH '$.objectInfo.schema', 14 NESTED PATH '$.objectInfo.columns[*]' 15 COLUMNS ( 16 col_name VARCHAR2(128) PATH '$.name', 17 not_null_flag VARCHAR2(5) PATH '$.notNull', 18 data_type VARCHAR2(60) PATH '$.dataType.type', 19 precision NUMBER PATH '$.dataType.precision', 20 scale NUMBER PATH '$.dataType.scale', 21 length NUMBER PATH '$.dataType.length', 22 size_units VARCHAR2(10) PATH '$.dataType.sizeUnits', 23 ts_precision NUMBER PATH '$.dataType.fractionalSecondsPrecision', 24 dy_precision NUMBER PATH '$.dataType.dayPrecision', 25 yr_precision NUMBER PATH '$.dataType.yearPrecision', 26 -- 27 hidden VARCHAR2(8) PATH '$.hiddenColumn', 28 ident VARCHAR2(8) PATH '$.identityColumn', 29 def_val VARCHAR2(4000) PATH '$.default', 30 virtualColumn VARCHAR2(8) PATH '$.virtualColumn', 31 reserv VARCHAR2(8) PATH '$.reservableColumn' 32 ) 33 ) 34 ) jt 35 ) 36 select stmt 37 from ( 38 select 0 x , 'create table ' || schema_name || '.' || table_name || ' (' stmt 39 from parsed 40 where r = 1 41 union all 42 select r, 43 case when r = 1 then ' ' else ',' end || 44 rpad(col_name,30) || ' ' || 45 case 46 when data_type in ('NUMBER','FLOAT','BINARY_DOUBLE','BINARY_FLOAT') then 47 case 48 when scale is not null then 49 data_type||'(' || precision || ',' || scale || ')' 50 when precision is not null then 51 data_type||'(' || precision || ')' 52 else 53 data_type 54 end 55 when data_type in ('VARCHAR2','CHAR','NVARCHAR2','RAW') then 56 data_type ||'('|| length || 57 case when size_units is not null then ' ' || size_units else '' end || ')' 58 when data_type like 'TIMESTAMP%' then 59 replace(case when ts_precision is not null then replace(data_type,'TIMESTAMP','TIMESTAMP('||ts_precision||')') else data_type end,'TIMEZONE','TIME ZONE') 60 when data_type like 'INTERVAL YEAR%' then 61 case when yr_precision is not null then replace(data_type,'YEAR','YEAR('||yr_precision||')') else data_type end 62 when data_type like 'INTERVAL DAY%' then 63 replace(case when dy_precision is not null then replace(data_type,'DAY','DAY('||dy_precision||')') else data_type end,'SECONDS','SECOND') 64 else data_type 65 end || 66 case 67 when reserv = 'true' then ' reservable' 68 when ident = 'true' then ' generated as identity' 69 when virtualColumn = 'true' then ' generated always as ('||replace(def_val,'\') ||')' 70 when def_val is not null then ' default '||def_val 71 end|| 72 case when not_null_flag = 'true' then ' not null' end as stmt 73 from parsed 74 where hidden = 'false' 75 union all 76 select 99999, ');' 77 ) 78 order by x;STMT---------------------------------------------------------------------create table SCOTT.T2 ( C1 NUMBER(38) default 12 not null,C2 NUMBER(38) default 13 not null,C3 NUMBER(38) generated always as ("C2"+1),C4 NUMBER(12,3) not null,C5 NUMBER not null,C6 NUMBER(38) generated as identity not null,VC2 VARCHAR2(10 BYTE),VC3 NCHAR,VC4 NVARCHAR2(10 CHAR),F FLOAT(126),BF1 BINARY_FLOAT,BF2 BINARY_DOUBLE,X XMLTYPE generated always as (),C CLOB,NC NCLOB,B BFILE,D DATE,TS1 TIMESTAMP(6),TS2 TIMESTAMP(3),TS3 TIMESTAMP(6) WITH LOCAL TIME ZONE,TS4 TIMESTAMP(6) WITH TIME ZONE,I1 INTERVAL YEAR(2) TO MONTH,I2 INTERVAL DAY(2) TO SECOND,OBJ1 ODCIVARCHAR2LIST,REFCOL ADDRESS_TYP,RID ROWID,URID UROWID,JS JSON,BOOL BOOLEAN,V VECTOR,ANYD ANYDATA);
It seems to be valid DDL, but immediately we can see a few things have gone missing.
- I seem to have lost track of the invisible column.
- The partitioning clause is gone.
- Several of the other table-level attributes
- The owner of the object types is missing
- XMLTYPE has some strange default elements
This could be my inadequate parsing of the JSON structure, so I then dug into the raw JSON data to see what components I might be missing.
SQL> SELECT json_Serialize(dbms_developer.GET_METADATA('T2',user,'TABLE','all') returning clob pretty ) AS doc FROM dual;DOC--------------------------------------------------------------{ "objectType" : "TABLE", "objectInfo" : { "name" : "T2", "schema" : "SCOTT", "columns" : [ { "dataType" : { "type" : "NUMBER", "precision" : 38 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : true, "default" : "12 ", "name" : "C1", "virtualColumn" : false }, { "dataType" : { "type" : "NUMBER", "precision" : 38 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : true, "default" : "13 ", "name" : "C2", "virtualColumn" : false }, { "dataType" : { "type" : "NUMBER", "precision" : 38 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "default" : "\"C2\"+1", "name" : "C3", "virtualColumn" : true }, { "dataType" : { "type" : "NUMBER", "precision" : 12, "scale" : 3 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : true, "name" : "C4", "virtualColumn" : false }, { "dataType" : { "type" : "NUMBER" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : true, "name" : "C5", "virtualColumn" : false }, { "dataType" : { "type" : "NUMBER", "precision" : 38 }, "isPk" : true, "hiddenColumn" : false, "isUk" : true, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : true, "notNull" : true, "default" : "\"ADMIN\".\"ISEQ$$_217863\".nextval", "name" : "C6", "virtualColumn" : false }, { "dataType" : { "type" : "VARCHAR2", "length" : 10, "sizeUnits" : "BYTE" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "VC2", "virtualColumn" : false }, { "dataType" : { "type" : "NCHAR", "length" : 4, "sizeUnits" : "CHAR" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "VC3", "virtualColumn" : false }, { "dataType" : { "type" : "NVARCHAR2", "length" : 10, "sizeUnits" : "CHAR" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "VC4", "virtualColumn" : false }, { "dataType" : { "type" : "FLOAT", "precision" : 126 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "F", "virtualColumn" : false }, { "dataType" : { "type" : "BINARY_FLOAT" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "BF1", "virtualColumn" : false }, { "dataType" : { "type" : "BINARY_DOUBLE" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "BF2", "virtualColumn" : false }, { "dataType" : { "type" : "XMLTYPE", "abstractColumnType" : "OPAQUE" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "X", "virtualColumn" : true }, { "dataType" : { "type" : "CLOB" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "C", "virtualColumn" : false }, { "dataType" : { "type" : "NCLOB" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "NC", "virtualColumn" : false }, { "dataType" : { "type" : "BFILE" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "B", "virtualColumn" : false }, { "dataType" : { "type" : "DATE" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "D", "virtualColumn" : false }, { "dataType" : { "type" : "TIMESTAMP", "fractionalSecondsPrecision" : 6 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "TS1", "virtualColumn" : false }, { "dataType" : { "type" : "TIMESTAMP", "fractionalSecondsPrecision" : 3 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "TS2", "virtualColumn" : false }, { "dataType" : { "type" : "TIMESTAMP WITH LOCAL TIME ZONE", "fractionalSecondsPrecision" : 6 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "TS3", "virtualColumn" : false }, { "dataType" : { "type" : "TIMESTAMP WITH TIME ZONE", "fractionalSecondsPrecision" : 6 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "TS4", "virtualColumn" : false }, { "dataType" : { "type" : "INTERVAL YEAR TO MONTH", "yearPrecision" : 2 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "I1", "virtualColumn" : false }, { "dataType" : { "type" : "INTERVAL DAY TO SECOND", "dayPrecision" : 2, "fractionalSecondsPrecision" : 6 }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "I2", "virtualColumn" : false }, { "dataType" : { "type" : "ODCIVARCHAR2LIST", "abstractColumnType" : "ARRAY" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "OBJ1", "virtualColumn" : false }, { "dataType" : { "type" : "ADDRESS_TYP", "abstractColumnType" : "REF" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "REFCOL", "virtualColumn" : false }, { "dataType" : { "type" : "ROWID" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "RID", "virtualColumn" : false }, { "dataType" : { "type" : "UROWID" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "URID", "virtualColumn" : false }, { "dataType" : { "type" : "JSON" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "JS", "virtualColumn" : false }, { "dataType" : { "type" : "BOOLEAN" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "BOOL", "virtualColumn" : false }, { "dataType" : { "type" : "VECTOR" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "V", "virtualColumn" : false }, { "dataType" : { "type" : "ANYDATA", "abstractColumnType" : "STORED IN LOB" }, "isPk" : false, "hiddenColumn" : false, "isUk" : false, "reservableColumn" : false, "isFk" : false, "encryptedColumn" : false, "identityColumn" : false, "notNull" : false, "name" : "ANYD", "virtualColumn" : false } ], "hasBeenAnalyzed" : false, "external" : "NO", "temporary" : "NO", "indexes" : [ { "indexType" : "LOB", "partitioned" : true, "columns" : [ ], "uniqueness" : "UNIQUE", "hasBeenAnalyzed" : false, "toBeDropped" : false, "segmentCreated" : "N/A", "visibility" : "VISIBLE", "name" : "SYS_IL0000217863C00015$$", "status" : "N/A", "compression" : "DISABLED" }, { "indexType" : "LOB", "partitioned" : true, "columns" : [ ], "uniqueness" : "UNIQUE", "hasBeenAnalyzed" : false, "toBeDropped" : false, "segmentCreated" : "N/A", "visibility" : "VISIBLE", "name" : "SYS_IL0000217863C00016$$", "status" : "N/A", "compression" : "DISABLED" }, { "indexType" : "LOB", "partitioned" : true, "columns" : [ ], "uniqueness" : "UNIQUE", "hasBeenAnalyzed" : false, "toBeDropped" : false, "segmentCreated" : "N/A", "visibility" : "VISIBLE", "name" : "SYS_IL0000217863C00017$$", "status" : "N/A", "compression" : "DISABLED" }, { "indexType" : "LOB", "partitioned" : true, "columns" : [ ], "uniqueness" : "UNIQUE", "hasBeenAnalyzed" : false, "toBeDropped" : false, "segmentCreated" : "N/A", "visibility" : "VISIBLE", "name" : "SYS_IL0000217863C00026$$", "status" : "N/A", "compression" : "DISABLED" }, { "indexType" : "LOB", "partitioned" : true, "columns" : [ ], "uniqueness" : "UNIQUE", "hasBeenAnalyzed" : false, "toBeDropped" : false, "segmentCreated" : "N/A", "visibility" : "VISIBLE", "name" : "SYS_IL0000217863C00030$$", "status" : "N/A", "compression" : "DISABLED" }, { "indexType" : "LOB", "partitioned" : true, "columns" : [ ], "uniqueness" : "UNIQUE", "hasBeenAnalyzed" : false, "toBeDropped" : false, "segmentCreated" : "N/A", "visibility" : "VISIBLE", "name" : "SYS_IL0000217863C00032$$", "status" : "N/A", "compression" : "DISABLED" }, { "indexType" : "LOB", "partitioned" : true, "columns" : [ ], "uniqueness" : "UNIQUE", "hasBeenAnalyzed" : false, "toBeDropped" : false, "segmentCreated" : "N/A", "visibility" : "VISIBLE", "name" : "SYS_IL0000217863C00033$$", "status" : "N/A", "compression" : "DISABLED" }, { "indexType" : "NORMAL", "partitioned" : false, "columns" : [ { "name" : "C6" } ], "uniqueness" : "UNIQUE", "hasBeenAnalyzed" : false, "toBeDropped" : false, "segmentCreated" : "NO", "visibility" : "VISIBLE", "name" : "SYS_C0038432", "status" : "VALID", "compression" : "DISABLED" } ], "constraints" : [ { "name" : "SYS_C0038427", "constraintType" : "CHECK - NOT NULL", "searchCondition" : "\"C1\" IS NOT NULL", "columns" : [ { "name" : "C1" } ], "status" : "ENABLED", "deferrable" : false, "validated" : "VALIDATED", "sysGeneratedName" : true }, { "name" : "SYS_C0038428", "constraintType" : "CHECK - NOT NULL", "searchCondition" : "\"C2\" IS NOT NULL", "columns" : [ { "name" : "C2" } ], "status" : "ENABLED", "deferrable" : false, "validated" : "VALIDATED", "sysGeneratedName" : true }, { "name" : "SYS_C0038429", "constraintType" : "CHECK - NOT NULL", "searchCondition" : "\"C4\" IS NOT NULL", "columns" : [ { "name" : "C4" } ], "status" : "ENABLED", "deferrable" : false, "validated" : "VALIDATED", "sysGeneratedName" : true }, { "name" : "SYS_C0038430", "constraintType" : "CHECK - NOT NULL", "searchCondition" : "\"C5\" IS NOT NULL", "columns" : [ { "name" : "C5" } ], "status" : "ENABLED", "deferrable" : false, "validated" : "VALIDATED", "sysGeneratedName" : true }, { "name" : "SYS_C0038431", "constraintType" : "CHECK - NOT NULL", "searchCondition" : "\"C6\" IS NOT NULL", "columns" : [ { "name" : "C6" } ], "status" : "ENABLED", "deferrable" : false, "validated" : "VALIDATED", "sysGeneratedName" : true }, { "name" : "SYS_C0038432", "constraintType" : "PRIMARY KEY", "columns" : [ { "name" : "C6" } ], "status" : "ENABLED", "deferrable" : false, "validated" : "VALIDATED", "sysGeneratedName" : true } ], "segmentCreated" : "N/A", "dependencies" : "DISABLED" }, "etag" : "1783FD9E69EBC7196DED7576A09394AF"}
Herein lies the inherent limitation of using DBMS_DEVELOPER as a source for getting DDL. The required components are simply not present. You can probably think of the relationship between DBMS_METADATA and DBMS_DEVELOPER as being similar to the DDL versus the ‘describe’ command in SQLcl or SQLPlus. ‘describe’ gives you a look at the structure of the table but is certainly not enough to recreate the table exactly as it was originally defined.
Leave a Reply