Sunday, 24 June 2018

How to take backup of PLSQL objects programatically in Oracle Apps R12 ?

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
/

No comments:

Post a Comment

Query to find request set and its responsibility

  SELECT FA.application_name,        fr.responsibility_name program_attached_to,        frg.request_group_name,        fcp.request_set_name,...