| NEED HELP regarding DBMS_METADATA.GET_DDL [message #394249] |
Thu, 26 March 2009 05:25  |
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 #394337 is a reply to message #394259] |
Thu, 26 March 2009 08:45  |
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 |
 |
Mahesh Rajendran
Messages: 10707
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 #394436 is a reply to message #394347] |
Fri, 27 March 2009 01:51 |
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 #394750 is a reply to message #394457] |
Mon, 30 March 2009 02:27 |
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 |
|
Mahesh Rajendran
Messages: 10707
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 >
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
