Skip to Main Content
  • Questions

  • How to get rid of double quotes from get_ddl

Breadcrumb

Question and Answer

Connor McDonald

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 10K+ times! This question is

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

Comments

A safer way

fnds, May 08, 2020 - 5:33 pm UTC

It would be safer to remove the double quotes only in cases where the identifier doesn't contain lowercase, spaces, etc.
It can be done using a regex.
select 
  case when object_type in ('FUNCTION','PROCEDURE',...) then
      dbms_metadata.get_ddl(object_type,object_name) 
  else
      regexp_replace(dbms_metadata.get_ddl(object_type,object_name),'"([A-Z0-9_$#]+)"','\1')
  end
from user_objects;

SQL> select regexp_replace('CREATE TABLE "ABC"','"([A-Z0-9_$#]+)"','\1') ddl from dual;

DDL             
----------------
CREATE TABLE ABC

SQL> select regexp_replace('CREATE TABLE "Abc"','"([A-Z0-9_$#]+)"','\1') ddl from dual;

DDL               
------------------
CREATE TABLE "Abc"

SQL> select regexp_replace('CREATE TABLE "A c"','"([A-Z0-9_$#]+)"','\1') ddl from dual;

DDL               
------------------
CREATE TABLE "A c"

Connor McDonald
May 11, 2020 - 5:13 am UTC

nice touch
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library