PLSQL script to Register Oracle Views from One Instance to Another automatically

PLSQL SCRIPT TO REGISTER ORACLE VIEWS 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 Oracle Views registration details 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 views for this and now you want to register these 40 views in other instance and it’s 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 Views 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 Views and execute this file in sql plus for that instance and your Views will be register there in few seconds.





For Views



declare

a1 LONG;

b LONG;

C1 LONG;

TEST_FILE Utl_File.File_Type;

FILE_NAME varchar2(400):='VIEW_NAME _REGISTRATION_FILE';

BEGIN

TEST_FILE := Utl_File.FOPEN('ODPDIR',FILE_NAME||'.txt','W',32767); -- ODPDIR is the Directory

FOR j in ( select OBJECT_NAME from dba_objects

 where object_type='VIEW'

 AND OBJECT_NAME like 'XX_%AP%')

loop

for i IN (SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS

WHERE TABLE_NAME=j.object_name

ORDER BY COLUMN_ID)

loop

a1:=a1||i.COLUMN_NAME||',';

end loop;

C1:=NULL;

SELECT TEXT INTO C1 FROM DBA_VIEWS

WHERE VIEW_NAME=j.object_name;

b:='create or replace view '||j.object_name||'R'||'('||rtrim(a1,',')||') AS '||C1||';'||chr(10);



a1:=null;

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);

B:=NULL;



--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;