Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need help with UTL_FILE taking out double quotes from .txt

user11089573Nov 24 2014 — edited Nov 24 2014

I am trying to open a .txt file in where each row is enclosed by double quotes. The objective is to open this file, remove the quotes and save file back without the quotes. Below is what I've written so far but it seems like the REPLACE function is not working. I've made a small test and the file isn't getting updated without the quotes. Can someone let me know what am I missing?

CREATE OR REPLACE PROCEDURE TEST_PROC(ERRBUF             OUT VARCHAR2,
                                                          RETCODE            OUT VARCHAR2,
                                                          FILE_NAME       IN     VARCHAR2,
                                                          FILE_NAME_OUT   IN     VARCHAR2)
AS
   --DECLARE
   P_FILE        VARCHAR2(50);
   P_FILE_OUT    VARCHAR2(50);
   L_FILE        UTL_FILE.FILE_TYPE;
   L_FILE_OUT    UTL_FILE.FILE_TYPE;
   INPUT_LINE    VARCHAR2(30000);
   OUTPUT_LINE   VARCHAR2(30000);
BEGIN
   --   Fnd_File.Put_Line(Fnd_File.LOG, 'Start Date ' || startdate);

   P_FILE := FILE_NAME;
   L_FILE := UTL_FILE.FOPEN('MY_DIR',
                            P_FILE,
                            'r',
                            32767);


   P_FILE_OUT := FILE_NAME_OUT;
   L_FILE_OUT := UTL_FILE.FOPEN('MY_DIR',
                                P_FILE_OUT,
                                'w',
                                32767);
IF UTL_FILE.IS_OPEN(L_FILE)
THEN
   LOOP
      BEGIN
         UTL_FILE.GET_LINE(L_FILE, INPUT_LINE);
         --DBMS_OUTPUT.PUT_LINE(INPUT_LINE);
         OUTPUT_LINE := REPLACE(INPUT_LINE, '"', '');
        -- DBMS_OUTPUT.PUT_LINE(OUTPUT_LINE);
         UTL_FILE.PUT_LINE(L_FILE_OUT, OUTPUT_LINE);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
            WHEN OTHERS THEN
                  errbuf := 'Process failed with the following error: '||SQLERRM;
      retcode := 2;
      END;
   END LOOP;
   END IF;

   UTL_FILE.FCLOSE(L_FILE);
   UTL_FILE.FCLOSE(L_FILE_OUT);
END TEST_PROC;
/

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 22 2014
Added on Nov 24 2014
1 comment
558 views