Home » RDBMS Server » Server Administration » NEED HELP regarding DBMS_METADATA.GET_DDL  () 1 Vote
NEED HELP regarding DBMS_METADATA.GET_DDL [message #394249] Thu, 26 March 2009 05:25 Go to next message
aditya.garg@accenture.com
Messages: 8
Registered: March 2009
Junior Member
In my project i need to extract around 1000's of packages from dev environment & put in production environment.
I tried doing this way using DBMS_METADATA.GET_DDL command, but i am facing a small error which is that i am able to copy all the packages but while compiling there is an error occuring. after testing came to know that there is no "/" backslash between package body & spec.

So can you please guide me if there is any alternative way or we can do something with this query itself. where we can differentiate between spec & body part

set long 500000
set linesize 1000
SET HEAD off
set trimspool on
set verify off
set feed off;
define name='&1'
spool '&&name'
column XXXX format a300
SELECT DBMS_METADATA.GET_DDL('PACKAGE','&&name','APPS')||'/' as xxxx from dual;
spool off
set head on


Thanks
AADI
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394252 is a reply to message #394249] Thu, 26 March 2009 05:28 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Welcome to OraFaq !!!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

1/ What's your database version (4 digit)

2/ You can use Toad right to extract script.??

Babu
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394259 is a reply to message #394252] Thu, 26 March 2009 05:38 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

SQL> SET DEFINE ON
SQL> set long 500000
SQL> set linesize 1000
SQL> SET HEAD off
SQL> set trimspool on
SQL> set verify off
SQL> set feed off;
SQL> column XXXX format a300
SQL> SELECT DBMS_METADATA.GET_DDL('PROCEDURE','&ONAME','BABU')||'/' as xxxx from dual;
Enter value for oname: TEST_PRO


  CREATE OR REPLACE PROCEDURE "BABU"."TEST_PRO" as
begin
dbms_output.put_line('Success');
end;
 /


>> but i am facing a small error which is that i am able to copy all the packages but while compiling there is an error

What error your getting?? Cpoy and paste all details from your session.

Babu
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394337 is a reply to message #394259] Thu, 26 March 2009 08:45 Go to previous messageGo to next message
aditya.garg@accenture.com
Messages: 8
Registered: March 2009
Junior Member
Hi babu,

Actually we are migrating from 11.5.8 to 11.5.10 version of database & while testing we were not able to copy around 4000 standard packages from dev env to prod env.
If we start doing it manually it will take around 100's of man hours so meanwhile we are trying to locate some generic idea or code which can be used to take all the packages from dev to prod.
That's why we tried writing the code for DBMS_METADATA utility.


And the error which we are getting is after running code. i.e. once the package's are extracted and we try to validate & compile it we get a compilation error. Which is due to missing of back slash "/" between package body & package Spec.

Hope i have communicated the issue well if you still require any further info please let me know.

Thanks in Advance.

Please let me know if there is some tool which can do such kind off job.
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394345 is a reply to message #394337] Thu, 26 March 2009 09:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Actually, the process you are trying to do is what we call 'manual'. It is very time consuming, requires a lot of intervention and understanding.

What happens to other 'objects', its dependents, tables and data?
Easiest way to duplicate to test environment, upgrade there.
You can use RMAN duplication, RMAN cold restore, exp/imp or
better expdp/impdp.
If you duplicate, it is the whole database that is been duplicated.
You can also selectively export/import a particular schema, so that all objects are exported and imported.
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394347 is a reply to message #394345] Thu, 26 March 2009 09:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I just re-read your post and see that you are moving from dev to prod.
Export/import is the easiest way.
There is nothing wrong with dbms_metadata. It just works for me. NOt sure what your problem is.
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394436 is a reply to message #394347] Fri, 27 March 2009 01:51 Go to previous messageGo to next message
aditya.garg@accenture.com
Messages: 8
Registered: March 2009
Junior Member
Due to constraints we can't use RMAN commands...

This is the error i am encountering while compiling the package in the putty...
SQL> @ECO_ERROR_HANDLER.lst

Warning: Package created with compilation errors.

SQL> show err
No errors.

the process is i willcall the dbms.metadata utility from unix sqlpls prompt and in return it will save a file in the backend of the following package in this case ECO_ERROR_HANDLER.lst.And when i will call the package for compilation i will be able to run it without error. but in this case it is ending up with errors. and the error is missing of breakpoint or backslash b/w package Spec & package body
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394445 is a reply to message #394436] Fri, 27 March 2009 03:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
dbadmin@chum > get t1
  1  set long 500000
  2  set linesize 1000
  3  SET HEAD off
  4  set trimspool on
  5  set verify off
  6  set feed off;
  7  spool dummy.lst
  8  column XXXX format a300
  9  SELECT 	replace(DBMS_METADATA.GET_DDL('PACKAGE','PKG1','SCOTT'),'CREATE OR REPLACE PACKAGE BODY','/'||
 10  	chr(10)||'CREATE OR REPLACE PACKAGE BODY') ||chr(10)||'/' as xxxx from dual;
 11  spool off
 12* set feed on
dbadmin@chum > @t1


  CREATE OR REPLACE PACKAGE "SCOTT"."PKG1"
as
procedure p1;
procedure p2;
end;
/
CREATE OR REPLACE PACKAGE BODY "SCOTT"."PKG1"
as
procedure p1 is
begin
DBMS_OUTPUT.PUT_LINE ('from p1');
end;
procedure p2 is
begin
DBMS_OUTPUT.PUT_LINE ('from p2');
end;
procedure p3 is
begin
DBMS_OUTPUT.PUT_LINE ('from p3');
end;
end;

/

dbadmin@chum > @dummy.lst

Package created.


Package body created.
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394457 is a reply to message #394445] Fri, 27 March 2009 04:27 Go to previous messageGo to next message
aditya.garg@accenture.com
Messages: 8
Registered: March 2009
Junior Member
Thanks for such a quick response Smile

Thanks very much
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394750 is a reply to message #394457] Mon, 30 March 2009 02:27 Go to previous messageGo to next message
aditya.garg@accenture.com
Messages: 8
Registered: March 2009
Junior Member
Hi After the sucessfull implimnetation of the process the next thing i want to do is the automate the process of extracting definations of DB objects as the above discussed process is time consuming.
So in this context i started working on combining UTL_FILE utility & the DBMS_METADATA.get_ddl query and i am stuck at one point.
--The Below UTL_FILE procedure will be having a file name object.csv which will be having NAMES & TYPES of the DB objects. ex.
XXPPP_REF_CODES,TABLE
XX_SWITCH_LOGON_TRG,TRIGGER
XXPO_TEST_WMS_22_V,VIEW
----------------------------------------------------------------
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := '/sptec51/app/SPDEV1/product/10.2.0//appsutil/outbound/SPDEV1_gbhpdr02';
l_filename VARCHAR2(100) := 'object.csv';
l_text VARCHAR2(32767);
BEGIN
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'r', 32767);

BEGIN
LOOP
UTL_FILE.get_line(l_file, l_text, 32767);
DBMS_OUTPUT.put_line(l_text);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
UTL_FILE.fclose(l_file);
END;
/
----------------------------------------------------------------
Now i was thinking of providing the NAME & TYPE of objects to the DBMS_METADATA query automatically. I am stuck at this point how to pass the values of object.csv to &&name, &&type in DBMS_METADATA query.
It will of great help if you can provide your inputs.
----------------------------------------------------------------
set long 5000000
set linesize 30000
SET HEAD off
set trimspool on
set verify off
set feed off
define name='&1'
define type='&2'
spool '&&name'
column XXXX format a5000
SELECT replace(DBMS_METADATA.GET_DDL('&&type','&&name','APPS'),'CREATE OR REPLACE PACKAGE BODY','/'||chr(10)||'CREATE OR REPLACE PACKAGE BODY') ||chr(10)||'/' as xxxx from dual;
spool off
SET HEAD on
-----------------------------------------------------------------
Re: NEED HELP regarding DBMS_METADATA.GET_DDL [message #394841 is a reply to message #394750] Mon, 30 March 2009 09:00 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
query the user_objects.
for object_type use an IN filter.
object_type in ('PACKAGE','PROCEDURE')

You do not want to give PACKAGE_BODY as we already deal with that within PACKAGE.
dbadmin@chum > @t1


  CREATE OR REPLACE PACKAGE "DBADMIN"."PKG1"
as
procedure p1;
procedure p2;
end;
/
CREATE OR REPLACE PACKAGE BODY "DBADMIN"."PKG1"
as
procedure p1 is
begin
DBMS_OUTPUT.PUT_LINE ('from p1');
end;
procedure p2 is
begin
DBMS_OUTPUT.PUT_LINE ('from p2');
end;
procedure p3 is
begin
DBMS_OUTPUT.PUT_LINE ('from p3');
end;
end;

/


  CREATE OR REPLACE FUNCTION "DBADMIN"."FOO"
return boolean
as
begin
null;
end;

/

dbadmin@chum > get t1
  1  set long 500000
  2  set linesize 1000
  3  SET HEAD off
  4  set trimspool on
  5  set verify off
  6  set feed off;
  7  spool dummy.lst
  8  column XXXX format a300
  9  sELECT 	replace(DBMS_METADATA.GET_DDL(u.object_type,u.object_name),'CREATE OR REPLACE PACKAGE BODY','/'||
 10		chr(10)||'CREATE OR REPLACE PACKAGE BODY') ||chr(10)||'/' as xxxx from user_objects u where object_type in ('PACKAGE','FUNCTION');
 11  spool off
 12* set feed on
dbadmin@chum > 

Previous Topic: IPC Send timeout detected. Receiver ospid 24104
Next Topic: ORA-12571: TNS:packet writer failure
Goto Forum:
  


Current Time: Thu Dec 08 04:13:39 CST 2016

Total time taken to generate the page: 0.16555 seconds