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