This is package is used to take the backup of the PLSQL objects.
create or replace PACKAGE XX_WRITE_FILES_PKG
IS
PROCEDURE XX_WRITE_FILES_PRC(P_OBJECT_NAME IN VARCHAR2);
PROCEDURE MAIN;
END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/
create or replace PACKAGE BODY XX_WRITE_FILES_PKG
IS
-- | |
-- |Description : XX_WRITE_FILES_PKG is used to take the backup of |
-- | Database objects like PROCEDURE,PACKAGE BODY,PACKAGE |
-- | TYPE BODY,TRIGGER,FUNCTION,TYPE. |
PROCEDURE Debug( p_message IN VARCHAR2
) IS
lv_message VARCHAR2(200);
BEGIN
lv_message := SUBSTR(p_message,1,240);
fnd_file.put_line(fnd_file.log, lv_message);
-- dbms_output.put_line(lv_message);
END Debug;
-- +====================================================================+
-- | Name : write_out |
-- | Description : To write to the Output file of a concurrent Prog|
-- | Parameters : pv_mesg - Message String |
-- | |
-- +====================================================================+
PROCEDURE write_out(pv_mesg IN VARCHAR2) IS
BEGIN
FND_FILE.PUT_LINE( FND_FILE.OUTPUT, substr(pv_mesg,1,500));
-- dbms_output.put_line(substr(pv_mesg,1,500));
END write_out;
PROCEDURE XX_WRITE_FILES_PRC (P_OBJECT_NAME IN VARCHAR2)
IS
CURSOR lcu_file_name (cv_object_name VARCHAR2)
IS
SELECT text
FROM user_source
WHERE NAME = cv_object_name;
l_file UTL_FILE.FILE_TYPE;
BEGIN
Debug('XX_WRITE_FILES_PRC => Begining of Procedure');
l_file := UTL_FILE.FOPEN('/usr/tmp',P_OBJECT_NAME||'.TXT','W');
Debug('XX_WRITE_FILES_PRC => After opening file '||P_OBJECT_NAME||'.TXT');
FOR lr_file_name_rec IN lcu_file_name(P_OBJECT_NAME) LOOP
UTL_FILE.PUT_LINE(l_file,lr_file_name_rec.TEXT);
END LOOP;
Debug('XX_WRITE_FILES_PRC => After closing the for-loop lr_file_name_rec');
UTL_FILE.FCLOSE(l_file);
Debug('XX_WRITE_FILES_PRC => End of procedure');
EXCEPTION
WHEN OTHERS THEN
Debug('XX_WRITE_FILES_PRC error at processing backup file for object '||P_OBJECT_NAME);
Debug('XX_WRITE_FILES_PRC => Error: '||SQLCODE ||','||SQLERRM);
END XX_WRITE_FILES_PRC;
PROCEDURE MAIN (ERRBUF OUT VARCHAR2
,RETCODE OUT VARCHAR2)
IS
CURSOR lcu_object_name
IS
SELECT xbon.OBJECT_NAME
FROM XX_BKUP_OBJECT_NAMES xbon;
BEGIN
Debug('MAIN => Begining of PROCEDURE');
FOR lr_object_name IN lcu_object_name LOOP
Debug('MAIN => Entered into for-loop lr_object_name');
XX_WRITE_FILES_PRC(lr_object_name.OBJECT_NAME);
write_out('Backup file created for object '||lr_object_name.OBJECT_NAME);
END LOOP;
Debug('MAIN => End of PROCEDURE');
EXCEPTION
WHEN OTHERS THEN
Debug('MAIN => Error: '||SQLCODE ||','||SQLERRM);
END MAIN;
END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/
create or replace PACKAGE XX_WRITE_FILES_PKG
IS
PROCEDURE XX_WRITE_FILES_PRC(P_OBJECT_NAME IN VARCHAR2);
PROCEDURE MAIN;
END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/
create or replace PACKAGE BODY XX_WRITE_FILES_PKG
IS
-- | |
-- |Description : XX_WRITE_FILES_PKG is used to take the backup of |
-- | Database objects like PROCEDURE,PACKAGE BODY,PACKAGE |
-- | TYPE BODY,TRIGGER,FUNCTION,TYPE. |
PROCEDURE Debug( p_message IN VARCHAR2
) IS
lv_message VARCHAR2(200);
BEGIN
lv_message := SUBSTR(p_message,1,240);
fnd_file.put_line(fnd_file.log, lv_message);
-- dbms_output.put_line(lv_message);
END Debug;
-- +====================================================================+
-- | Name : write_out |
-- | Description : To write to the Output file of a concurrent Prog|
-- | Parameters : pv_mesg - Message String |
-- | |
-- +====================================================================+
PROCEDURE write_out(pv_mesg IN VARCHAR2) IS
BEGIN
FND_FILE.PUT_LINE( FND_FILE.OUTPUT, substr(pv_mesg,1,500));
-- dbms_output.put_line(substr(pv_mesg,1,500));
END write_out;
PROCEDURE XX_WRITE_FILES_PRC (P_OBJECT_NAME IN VARCHAR2)
IS
CURSOR lcu_file_name (cv_object_name VARCHAR2)
IS
SELECT text
FROM user_source
WHERE NAME = cv_object_name;
l_file UTL_FILE.FILE_TYPE;
BEGIN
Debug('XX_WRITE_FILES_PRC => Begining of Procedure');
l_file := UTL_FILE.FOPEN('/usr/tmp',P_OBJECT_NAME||'.TXT','W');
Debug('XX_WRITE_FILES_PRC => After opening file '||P_OBJECT_NAME||'.TXT');
FOR lr_file_name_rec IN lcu_file_name(P_OBJECT_NAME) LOOP
UTL_FILE.PUT_LINE(l_file,lr_file_name_rec.TEXT);
END LOOP;
Debug('XX_WRITE_FILES_PRC => After closing the for-loop lr_file_name_rec');
UTL_FILE.FCLOSE(l_file);
Debug('XX_WRITE_FILES_PRC => End of procedure');
EXCEPTION
WHEN OTHERS THEN
Debug('XX_WRITE_FILES_PRC error at processing backup file for object '||P_OBJECT_NAME);
Debug('XX_WRITE_FILES_PRC => Error: '||SQLCODE ||','||SQLERRM);
END XX_WRITE_FILES_PRC;
PROCEDURE MAIN (ERRBUF OUT VARCHAR2
,RETCODE OUT VARCHAR2)
IS
CURSOR lcu_object_name
IS
SELECT xbon.OBJECT_NAME
FROM XX_BKUP_OBJECT_NAMES xbon;
BEGIN
Debug('MAIN => Begining of PROCEDURE');
FOR lr_object_name IN lcu_object_name LOOP
Debug('MAIN => Entered into for-loop lr_object_name');
XX_WRITE_FILES_PRC(lr_object_name.OBJECT_NAME);
write_out('Backup file created for object '||lr_object_name.OBJECT_NAME);
END LOOP;
Debug('MAIN => End of PROCEDURE');
EXCEPTION
WHEN OTHERS THEN
Debug('MAIN => Error: '||SQLCODE ||','||SQLERRM);
END MAIN;
END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/
No comments:
Post a Comment