RE: Dbms_metadata experts?

Below SQL will set some of the transform parameters to control your output, and then will replace double quotes with NULL, change CHAR to VARCHAR (protecting any existing VARCHAR), and will rename the table all at once (also pulls index ddl):

BEGIN

  dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'DEFAULT');
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',FALSE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER',TRUE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE);

SELECT REPLACE(REPLACE(REPLACE(REPLACE(dbms_metadata.get_ddl('TABLE', table_name,owner),'VARCHAR','CHAR'),'CHAR','VARCHAR'),chr(34),NULL),table_name,table_name   ||'_CPY') AS ddl_create
FROM dba_tables
WHERE TABLE_NAME = 'x'
AND OWNER = 'y'
UNION ALL
SELECT REPLACE(REPLACE(REPLACE(dbms_metadata.get_ddl('INDEX', INDEX_NAME,OWNER),chr(34),NULL),index_name,index_name||'_CPY'),table_name,table_name||'_CPY') AS ddl_indexes FROM dba_INDEXES
WHERE TABLE_NAME = 'x' and TABLE_OWNER = 'y'

I’m sure there’s a better way to write this than having table_name and table_owner hard-coded twice.

Thanks,
Scott

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andrew Kerber Sent: Wednesday, March 18, 2015 2:38 PM To: Steve Harville
Cc: Stefan Knecht; <oracle-l_at_freelists.org> Subject: Re: Dbms_metadata experts?

Below is what I have so far. I would like to have a neater way of changing char to varchar2, and I would like to change the table names to tablename_int.

select 'select replace(dbms_metadata.get_ddl(object_type=>'||''''||'TABLE'||''''||',name=>'||''''||table_name||''''||',schema=>'|| ''''||owner||''''||')'||'||'||''''||';'||''''|| ','||''''||'CHAR('||''''||','||''''||'varchar2('||''''||') from dual;' from dba_tables where owner='SCOTT';

Sent from my iPad

Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**

--
http://www.freelists.org/webpage/oracle-l