PLSQL SCRIPT TO REGISTER PACKAGES FROM ONE INSTANCE TO ANOTHER INSTANCE AUTOMATICALLY IN ORACLE APPS

In this post , I will share you one method in which you can extract the PLSQL package body and definition of hundreds of packages in few seconds using this below script.

As an example , you are working on some project in development instance and you have created around 40 packages for this and now you want to register this 40 packages in other instance and its a time consuming activity.

By this below query , you just need to execute this below script in test instance with proper values as per your requirement and that will create one .sql file in you server will all the 40 packages registration details.

Copy this file from server and put this file in your system and then open sql plus with targeted instance where you want to register these packages and execute this file in sql plus for that instance and your packages will be register there in few seconds.

For Package Definition
declare
a1 LONG;
b LONG;
C1 LONG;
TEST_FILE Utl_File.File_Type;
FILE_NAME varchar2(400):='PACKAGE_NAME_PKG_DEF';
BEGIN
TEST_FILE := Utl_File.FOPEN('ODPDIR',FILE_NAME||'.txt','W',32767); -----ODPDIR  is the directory name in the server where this file will create
FOR j in ( select OBJECT_NAME from dba_objects
 where object_type='PACKAGE'
 AND OBJECT_NAME LIKE 'XXAP_%')
loop
for i IN (SELECT CASE WHEN LINE=1 THEN CHR(10)||'CREATE OR REPLACE '||REPLACE(TEXT,CHR(10)) ELSE REPLACE(TEXT,CHR(10)) END TEXT FROM DBA_SOURCE
WHERE TYPE='PACKAGE'
AND NAME=j.object_name
ORDER BY LINE)
loop
B:=NULL;
B:=I.TEXT;
Utl_File.PUT_LINE(FILE     => TEST_FILE,
     buffer   => b,
     autoflush => TRUE);
--dbms_output.put_line(rtrim(b));
end loop;
C1:=NULL;

a1:=null;

end loop;
if Utl_File.is_open(TEST_FILE) then
Utl_File.FCLOSE(TEST_FILE);
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
end;



For Package Body

declare
a1 LONG;
b LONG;
C1 LONG;
TEST_FILE Utl_File.File_Type;
FILE_NAME varchar2(400):='PACKAGE_NAME_PKG_BODY';
BEGIN
TEST_FILE := Utl_File.FOPEN('ODPDIR',FILE_NAME||'.sql','W',32767); -----ODPDIR  is the directory name in the server where this file will create
FOR j in ( select OBJECT_NAME from dba_objects
 where object_type='PACKAGE'
 AND OBJECT_NAME LIKE 'XXAP_%')
loop
for i IN (SELECT CASE WHEN LINE=1 THEN CHR(10)||'CREATE OR REPLACE '||REPLACE(TEXT,CHR(10)) ELSE REPLACE(TEXT,CHR(10)) END TEXT FROM DBA_SOURCE
WHERE TYPE='PACKAGE BODY'
AND NAME=j.object_name
ORDER BY LINE)
loop
B:=NULL;
B:=I.TEXT;
Utl_File.PUT_LINE(FILE     => TEST_FILE,
     buffer   => b,
     autoflush => TRUE);
--dbms_output.put_line(rtrim(b));
end loop;
C1:=NULL;
--b:='create or replace view '||j.object_name||'R'||'('||rtrim(a1,',')||') AS '||C1;
a1:=null;

--TEST_FILE := Utl_File.FOPEN('ECX_UTL_XSLT_DIR_OBJ',FILE_NAME||'.txt','W',32767);
--Utl_File.PUT_LINE(TEST_FILE ,b);
--Utl_File.PUT_LINE(FILE     => TEST_FILE,
   --  buffer   => b,
   --  autoflush => TRUE);
--UTL_FILE.NEW_LINE(TEST_FILE ,1);

--dbms_output.put_line('1');
end loop;
if Utl_File.is_open(TEST_FILE) then
Utl_File.FCLOSE(TEST_FILE);
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
end;