Home » SQL & PL/SQL » SQL & PL/SQL » How to get ddls for objects in DB and write them to a file (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
How to get ddls for objects in DB and write them to a file [message #601790] Mon, 25 November 2013 15:12 Go to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
I want to create a procedure to write the DDLs of database objects in a file. Please help.
Re: How to get ddls for objects in DB and write them to a file [message #601791 is a reply to message #601790] Mon, 25 November 2013 15:33 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
when all else fails, Read The Fine Manual

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_metada.htm#ARPLS66866
Re: How to get ddls for objects in DB and write them to a file [message #602268 is a reply to message #601791] Mon, 02 December 2013 11:28 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
I have created a table to save the ddl's of objects. Below is the structure to save the ddl:

CREATE TABLE scott.DDL_SAVE_TBL
(
  LOAD_DT  CHAR(8 BYTE),
  OWNER        VARCHAR2(30 BYTE),
  OBJECT_NAME  VARCHAR2(30 BYTE),
  DDL          CLOB,
  OBJECT_TYPE  VARCHAR2(30 BYTE)
);


There is one table from which I am picking up the date(This table is updated everyday with the current date)

CREATE TABLE scott.load_DATES
(
  LOAD_DT            CHAR(8 BYTE)           NOT NULL,
)



Below is the code for my procedure:

create or replace procedure scott.save_objects_ddls
is
v_ddl          scott.ddl_save_tbl.ddl%type;

begin

for rec in (select object_type ,object_name, owner, load_dt from all_objects, scott.load_dates where owner in ('SCOTT')
and object_name  in ('EMP', 'DEPT'))

loop 

dbms_output.put_line(rec.object_type || rec.object_name || rec.owner);

select dbms_metadata.get_ddl(rec.object_type,rec.object_name,rec.owner) into v_ddl from dba_objects;

insert into scott.ddl_save_tbl values (rec.object_name, rec.owner, rec.object_type, rec.load_dt, v_ddl );

end loop;

exception
when others then
dbms_output.put_line (SQLCODE|| SQLERRM|| 'review the logs');

end;


I am getting the below error:
-31603ORA-31603: object "EMP" of type TABLE not found in schema
"SCOTT"
ORA-06512: at "SYS.DBMS_METADATA", line 2625
ORA-06512: at
"SYS.DBMS_METADATA", line 2668
ORA-06512: at "SYS.DBMS_METADATA", line
2983
ORA-06512: at "SYS.DBMS_METADATA", line 3897
ORA-06512: at
"SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1review the logs


Please help.
Re: How to get ddls for objects in DB and write them to a file [message #602269 is a reply to message #602268] Mon, 02 December 2013 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'SCOTT';

post results from SQL above
Re: How to get ddls for objects in DB and write them to a file [message #602270 is a reply to message #602269] Mon, 02 December 2013 12:17 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
BlackSwan wrote on Mon, 02 December 2013 17:40
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'SCOTT';

post results from SQL above



Actually my actual schema and table names are different from the one I mentioned in the procedure posted here. But the table_name I was using in my original procedure do exist in all_tables and that was what the error I got.

Please let me know what could be reason of the error.
Re: How to get ddls for objects in DB and write them to a file [message #602271 is a reply to message #602270] Mon, 02 December 2013 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
1) Either Oracle is correct & you are mistaken
or
2) you are correct & Oracle is incorrectly reporting an error.

If #1 is true, then fix your problem.
If #2 is true, then submit bug report to Oracle.

I can't say more since you repeatedly obfuscated reality.
Re: How to get ddls for objects in DB and write them to a file [message #602274 is a reply to message #602270] Mon, 02 December 2013 12:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
gupta27 wrote on Mon, 02 December 2013 13:17
Please let me know what could be reason of the error.


Reason is simple. DBMS_METADATA always shows you own objects, but shows other schema objects only if you are SYS or are granted SELECT_CATALOG_ROLE role. And your SP is created with definer rights which means rolls are ignored. Create SP with invoker rights. Make sure user who runs SP has SELECT_CATALOG_ROLE role. For example:

SCOTT@orcl > select dbms_metadata.get_ddl('TABLE','TBL','U1') from dual;

DBMS_METADATA.GET_DDL('TABLE','TBL','U1')
--------------------------------------------------------------------------------

  CREATE TABLE "U1"."TBL"
   (    "N" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"


SCOTT@orcl > create or replace
  2    procedure save_objects_ddls
  3    is
  4      v_ddl clob;
  5    begin
  6        select dbms_metadata.get_ddl('TABLE','TBL','U1') into v_ddl from dual;
  7        dbms_output.put_line (v_ddl);
  8  end;
  9  /

Procedure created.

SCOTT@orcl > exec save_objects_ddls
BEGIN save_objects_ddls; END;

*
ERROR at line 1:
ORA-31603: object "TBL" of type TABLE not found in schema "U1"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1
ORA-06512: at "SCOTT.SAVE_OBJECTS_DDLS", line 5
ORA-06512: at line 1


SCOTT@orcl > create or replace
  2    procedure save_objects_ddls
  3    authid current_user
  4    is
  5      v_ddl clob;
  6    begin
  7        select dbms_metadata.get_ddl('TABLE','TBL','U1') into v_ddl from dual;
  8        dbms_output.put_line (v_ddl);
  9  end;
 10  /

Procedure created.

SCOTT@orcl > exec save_objects_ddls

  CREATE TABLE "U1"."TBL"
   (    "N" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING
  TABLESPACE "USERS"

PL/SQL procedure successfully completed.

SCOTT@orcl >


SY.

[Updated on: Mon, 02 December 2013 12:56]

Report message to a moderator

Re: How to get ddls for objects in DB and write them to a file [message #602276 is a reply to message #602274] Mon, 02 December 2013 13:35 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
Thanks SY for the wonderful concept. Just one more thing . Is it possible to store package body's ddl using dbms_output.get_ddl , because I am getting error for package body.

-31600ORA-31600: invalid input value PACKAGE BODY for parameter OBJECT_TYPE in
function GET_DDL


What could be the reason for the above error ?

Thanks in advance for your prompt help.
Re: How to get ddls for objects in DB and write them to a file [message #602277 is a reply to message #602276] Mon, 02 December 2013 13:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
PACKAGE_BODY

SY.
Re: How to get ddls for objects in DB and write them to a file [message #602278 is a reply to message #602276] Mon, 02 December 2013 13:43 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
gupta27 wrote on Mon, 02 December 2013 11:35
Thanks SY for the wonderful concept. Just one more thing . Is it possible to store package body's ddl using dbms_output.get_ddl , because I am getting error for package body.

-31600ORA-31600: invalid input value PACKAGE BODY for parameter OBJECT_TYPE in
function GET_DDL


What could be the reason for the above error ?

Thanks in advance for your prompt help.


http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_metada.htm#ARPLS66895

"PACKAGE_BODY" not "PACKAGE BODY"
Re: How to get ddls for objects in DB and write them to a file [message #602279 is a reply to message #602278] Mon, 02 December 2013 13:48 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
Yes , But that's what it is picking from all_objects table.

PACKAGE BODY
INDEX PARTITION
TABLE PARTITION
etc.

Should I change my column type from varchar2() to something else. What should I change it to ?
Re: How to get ddls for objects in DB and write them to a file [message #602281 is a reply to message #602279] Mon, 02 December 2013 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
1) the source code is already stored inside the DB so why are you reinventing the the wheel & duplicating what already exists?
2) some, many, most professional software development teams store their source code on code repository; like subversion. Why don't you do the same?
Re: How to get ddls for objects in DB and write them to a file [message #602282 is a reply to message #602281] Mon, 02 December 2013 14:02 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
BlackSwan wrote on Mon, 02 December 2013 19:57
1) the source code is already stored inside the DB so why are you reinventing the the wheel & duplicating what already exists?


I want to keep a track of changes (and store them in table) in the object definition which could happen over a period of time.
Re: How to get ddls for objects in DB and write them to a file [message #602285 is a reply to message #602279] Mon, 02 December 2013 14:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
gupta27 wrote on Mon, 02 December 2013 14:48
Yes , But that's what it is picking from all_objects table.


And what prevents you from using something like:

CASE rec.object_type
  WHEN 'PACKAGE' THEN 'PACKAGE_SPEC'
  ELSE REPLACE(rec.object_type,' ','_')
END


SY.
Re: How to get ddls for objects in DB and write them to a file [message #602286 is a reply to message #602285] Mon, 02 December 2013 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>And what prevents you from using something like:
unable to produce original thought & can only do what is specifically directed.

http://en.wikipedia.org/wiki/Not_invented_here
Re: How to get ddls for objects in DB and write them to a file [message #602292 is a reply to message #602286] Mon, 02 December 2013 16:07 Go to previous message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
SY& Black Swan,

I just saw that package includes body and spec, so no need to use package body option. Hence I excluded it from my query.

Though I thought of using decode only to accomodate the '_' .

Once again thanks for your time help and patience.

Previous Topic: Comma Delimited String
Next Topic: query to work fast
Goto Forum:
  


Current Time: Fri Aug 29 21:24:38 CDT 2014

Total time taken to generate the page: 0.18053 seconds