Stored procedure is not able to write into File in oracle

Question

I have a stored procedure where I'm trying to write a records into files and able to write into files but problem is when my condition is like this ELSIF REC.REC_STAT = 'DUPL' then stored procedure is not able to write there and getting error below on other hand In the case is like this IF REC.REC_STAT = 'NEW' stored procedure is able to write there.

Error

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "PS_ADMIN.IRE_DE_DUP_PROC7", line 31
ORA-06512: at line 9

SP is:

CREATE OR REPLACE PROCEDURE TEST_SP(Dy_File_Name    IN     VARCHAR2,
                                          SU_CD     IN      VARCHAR2,
                                          EX_ID     IN     VARCHAR2,
                                          DUP_FILE_NAME IN VARCHAR2  )

AS


CLREF            SYS_REFCURSOR;    
UNQIUE_REC_FILE  UTL_FILE.FILE_TYPE;
DE_DUP_FILE      UTL_FILE.FILE_TYPE;
OUT_DIR          VARCHAR2 (200) := 'DE_DUP_OUTPUT_NAME';
OUT_DE_DUP_DIR   VARCHAR2 (300) := 'DE_DUP_DUPLICATES_NAME';



TYPE REC_TYP IS RECORD

   (

        A    varchar2(10),
        B    Varchar2(20), 
        C    varchar2(10),
        D    varchar2(30),
        E    varchar2(30),

    )

    REC   REC_TYP;
BEGIN
    SELECT ABS(DBMS_RANDOM.RANDOM) INTO RNDOM_VAL FROM DUAL;
    OUTPT_FILE := 'KF_OUTFILE_'||RNDOM_VAL||'.TXT';    
    UNQIUE_REC_FILE := UTL_FILE.FOPEN (OUT_DIR, OUTPT_FILE, 'W');

    UTL_FILE.PUT_LINE (UNQIUE_REC_FILE, 'this is test');

    OPEN CLREF FOR ('SELECT

    s.AB AS A,
    P.AB1 AS B,
    s.AB2 AS C,
    S.AB3 AS D,
    S.AB4 AS E

    CASE WHEN S.CK = 1 AND H.CK = 0 THEN ''DEL''
               WHEN S.CK = 1 AND H.CK IS NULL THEN ''DEL_IGNR''
               WHEN S.DETL_CLMNS_HASH <> H.DETL_CLMNS_HASH THEN ''UPDT''
               WHEN S.DETL_CLMNS_HASH = H.DETL_CLMNS_HASH THEN ''DUPL''
               WHEN H.DETL_CLMNS_HASH IS NULL THEN ''NEW''
               ELSE ''ERR'' END AS REC_STAT,
          S.REC_TYP                       AS RE_TYP,     
          S.DETL_CLMNS_HASH               AS DT_CLMNS_HASH,
          S.KEY_CLMNS_HASH                AS KY_CLMNS_HASH     


        FROM
          (SELECT stg.*,
            row_number() over ( partition BY key_clmns_hash, rx_dspnsd_dt, del_ind ORDER BY 1) AS RN
          FROM  '   || Dy_File_Name || '   stg
          ) s
        LEFT JOIN ps_pha p ON s.EX = p.EX
        LEFT JOIN ps_rx_hist H
        ON h.key_clmns_hash        = s.key_clmnS_hash
        AND h.rx_dspnsd_dt         = s.rx_dspnsd_dt
        AND s.sU = h.SU
        WHERE s.RN                 = 1');

    LOOP

            FETCH CLREF  INTO REC;
            EXIT WHEN CLREF%NOTFOUND;  --CLREF%COUNT

                IF REC.REC_STAT = 'NEW'
            THEN


            UNIQUE_RECORDS:='"'|| REC.A || '"'||REC.B || '"|"' || REC.C || '"|'  || substr(REC.D) || '|' || REC.E || '"';
            UTL_FILE.PUT_LINE (UNQIUE_REC_FILE,UNIQUE_RECORDS);

        ELSIF REC.REC_STAT = 'DUPL'  -- DUPLICATE
            THEN

                    DE_DUP_FILE := UTL_FILE.FOPEN (OUT_DE_DUP_DIR, DUP_FILE_NAME, 'W');                            

                    DUP_RECORDS :='"'|| REC.A || '"'||REC.B || '"|"' || REC.C || '"|'  || substr(REC.D) || '|' || REC.E || '"';

                    UTL_FILE.PUT_LINE (DE_DUP_FILE, DUP_RECORDS);

        END IF;

    END LOOP;

    UTL_FILE.FCLOSE (UNQIUE_REC_FILE);
        UTL_FILE.FCLOSE (DE_DUP_FILE);
END;

Answer 1

Here are a few pointers.

• Variables OUTPT_FILE and DUP_FILE_NAME are not declared. Ideally this will give you a compilation error. If you have declared the variable then check the value for variable DUP_FILE_NAME. If it is being passed as empty it may cause an issue.
• You are opening the DE_DUP_FILE in a loop. This may be possibly causing the issue if it is trying to open it multiple times.
• Variable OUT_DE_DUP_DIR is not being used anywhere in the code.

Here are some comments considering your revised code.

• Since you are passing the DUP_FILE_NAME as an input parameter would like to know what value you are passing to this parameter. In fact, can you share the entire script for invoking this procedure with details for all parameters.
• Can you check if the DE_DUP_DUPLICATES_NAME directory exists. You can check it in user_directories data dictionary view.
• Since you unique file is getting created can you try changing DE_DUP_FILE creating to OUT_DIR instead of OUT_DE_DUP_DIR. This might help understand if there is an issue with the OUT_DE_DUP_DIR directory.
• You are still opening the file in the loop. Can you try opening it outside the loop.