Thanks for the question, Ravi.
Asked: April 20, 2017 - 11:47 am UTC
Last updated: May 11, 2020 - 5:13 am UTC
Version: 11GR2
Viewed 1000+ times
You Asked
Hi Team,
Is there any way to get rid of the double quotes from the output of dbms_metadata.get_ddl. Following is the sample output of dbms_metadata.get_ddl('TABLE','TEST') :
e.g., CREATE TABLE "TEST"
( "F1" NUMBER(4,0) NOT NULL ENABLE,
"F2" NUMBER(6,0) NOT NULL ENABLE,
"F3" NUMBER(6,0),
PRIMARY KEY ("F1", "F2")
..............
...............
I should get output like
CREATE TABLE TEST
( F1 NUMBER(4,0) NOT NULL ENABLE,
F2 NUMBER(6,0) NOT NULL ENABLE,
F3 NUMBER(6,0),
PRIMARY KEY (F1, F2)
..............
...............
Note : Need to get DDL for entire schema with out double quotes. can not use Replace function as there might be some double quotes used in the codes
inside package/procedure/trigger etc
Is there any session level settings or any other way to sort out this issue?
Regards
P.B.Ravi Kumar
and Connor said...
There are no dbms_metadata transforms (not even in 12.2) that can do this. A compromise perhaps would be by object type ?
select
case when object_type in ('FUNCTION','PROCEDURE',...) then
dbms_metadata.get_ddl(object_type,object_name)
else
replace(dbms_metadata.get_ddl(object_type,object_name),'"')
end
from user_objects;
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment