Oracle SQL Developer report to make DDL of DB object

Question

I've wrote a report for SQL Developer to generate the DDL of a DB object.

SELECT DBMS_METADATA.GET_DDL(UPPER(:OBJECT_TYPE),UPPER(:OBJECT_NAME),UPPER(:OBJECT_SCHEMA)) DDL FROM DUAL
UNION ALL
select DBMS_METADATA.GET_DDL (UPPER('CONSTRAINT'),UPPER(CONSTRAINT_NAME),UPPER(:OBJECT_SCHEMA)) AS "DDL OGGETTI DIPENDENTI" from DBA_CONSTRAINTS where OWNER like UPPER(:OBJECT_SCHEMA) and TABLE_NAME like UPPER(:OBJECT_NAME) and CONSTRAINT_TYPE not like 'R'
UNION ALL
select DBMS_METADATA.GET_DDL (UPPER('REF_CONSTRAINT'),UPPER(CONSTRAINT_NAME),UPPER(:OBJECT_SCHEMA)) from DBA_CONSTRAINTS where OWNER like UPPER(:OBJECT_SCHEMA) and TABLE_NAME like UPPER(:OBJECT_NAME) and CONSTRAINT_TYPE like 'R'
UNION ALL
select DBMS_METADATA.GET_DDL (UPPER('INDEX'),UPPER(INDEX_NAME),UPPER(:OBJECT_SCHEMA)) from DBA_INDEXES where OWNER like UPPER(:OBJECT_SCHEMA) and TABLE_NAME like UPPER(:OBJECT_NAME) 
UNION ALL
select DBMS_METADATA.GET_DEPENDENT_DDL (UPPER('COMMENT'),UPPER(TABLE_NAME),UPPER(:OBJECT_SCHEMA)) from DBA_COL_COMMENTS where OWNER like UPPER(:OBJECT_SCHEMA) and TABLE_NAME like UPPER(:OBJECT_NAME) AND COMMENTS is not null GROUP BY TABLE_NAME
UNION ALL
select DBMS_METADATA.GET_DEPENDENT_DDL (UPPER('OBJECT_GRANT'),UPPER(TABLE_NAME),UPPER(:OBJECT_SCHEMA)) from DBA_TAB_PRIVS where OWNER like UPPER(:OBJECT_SCHEMA) and TABLE_NAME like UPPER(:OBJECT_NAME) GROUP BY TABLE_NAME
UNION ALL
select DBMS_METADATA.GET_DDL (UPPER('SYNONYM'),UPPER(TABLE_NAME),UPPER('PUBLIC')) from DBA_SYNONYMS where TABLE_OWNER like UPPER(:OBJECT_SCHEMA) and TABLE_NAME like UPPER(:OBJECT_NAME) GROUP BY TABLE_NAME
UNION ALL
select DBMS_METADATA.GET_DEPENDENT_DDL (UPPER('TRIGGER'),UPPER(TABLE_NAME),UPPER(:OBJECT_SCHEMA)) from DBA_TRIGGERS where TABLE_OWNER like UPPER(:OBJECT_SCHEMA) and TABLE_NAME like UPPER(:OBJECT_NAME) GROUP BY TABLE_NAME;

The result type of function DBMS_METADATA.GET_DDL is CLOB.

The problem is when i copy the strings output in notepad or word:

If I choose TABLE for report style every row of the output is quoted with " ". for example: " CREATE TABLE "MWPROD"."ORDINI" ... TABLESPACE "MWPROD_TBSDAT" ;"
If I choose SCRIPT for report style some rows are truncate.

I want found a solution for generate a clean output with complete rows without quotes.

thatjeffsmith · Accepted Answer · 2016-07-07 14:15:22Z

1

the DDL is quoted by the database - it's to account for idiot developers - sorry, developers who name tables with reserved words, like "TABLE", or for folks that need case sensitive object names

For the truncated script output, use the SET LONG command in your script.

SET LONG {80 | n} Sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.

answered Jul 7, 2016 at 14:15

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Developer report to make DDL of DB object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related