Monday, July 22, 2013

UTL File Read a file, UTL Write a file , UTL Move the file, UTL Remove the file in Oracle Applications

CREATE OR REPLACE PROCEDURE apps.xx_file_copy_test (
   errbuf    OUT   VARCHAR2
  ,retcode   OUT   NUMBER
)
AS
   msg                 VARCHAR2 (4000)    := NULL;
   ufilehndl           UTL_FILE.file_type;
   ufilehnd2           UTL_FILE.file_type;
   sfilename           VARCHAR2 (30)      := 'FND_USER.txt';
   l_source_dir        VARCHAR2 (500);
   l_destination_dir   VARCHAR2 (500);
   i                   NUMBER             := 0;
   desfilename         VARCHAR2 (500)
                              := TO_CHAR (SYSDATE, 'MMDDYYHH24MISS')
                                 || '.txt';
BEGIN
   desfilename := 'UTLFILE' || TO_CHAR (SYSDATE, 'MMDDYYHH24MISS') || '.txt';
   ufilehndl := UTL_FILE.fopen ('/u005/data/XXX/appl/temp', sfilename, 'r');
   ufilehnd2 :=
            UTL_FILE.fopen ('/u004/XXXX/XXX/appl/data/in', desfilename, 'w');

   LOOP
      BEGIN
         i := i + 1;
         UTL_FILE.get_line (ufilehndl, msg);
         fnd_file.put_line (fnd_file.LOG, 'Line no ' || i || ' ' || msg);
         UTL_FILE.put_line (ufilehnd2, 'Line no ' || i || ' ' || msg);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;
   END LOOP;

   fnd_file.put_line (fnd_file.LOG, 'After done all lines');
   fnd_file.put_line (fnd_file.LOG
                     ,'************************************************'
                     );
-- Close the output file
   UTL_FILE.fclose (ufilehndl);
   UTL_FILE.fclose (ufilehnd2);
   retcode := 0;
   sfilename := 'UNIXCommands.pdf';
   desfilename := 'FileCopy' || TO_CHAR (SYSDATE, 'MMDDYYHH24MISS') || '.txt';
   fnd_file.put_line (fnd_file.LOG, 'Started');
   --DBMS_FILE_TRANSFER.copy_file
   UTL_FILE.fcopy ('/u005/data/XXXX/appl/temp', sfilename, '/u004/XXXX/XXX/appl/data/in', TO_CHAR (SYSDATE, 'MMDDYYHH24MISS') ||sfilename);
   fnd_file.put_line (fnd_file.LOG, 'file moved');
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      UTL_FILE.fclose (ufilehndl);
      fnd_file.put_line (fnd_file.LOG, 'Error ' || SQLERRM);
      errbuf := 'Error ' || SQLERRM;
      retcode := 2;
END;

No comments:

Post a Comment