oracle os file delete ( 오라클에서 os상 file 삭제 )

UTL_FILE Package 중 FREMOVE(PATH,FILE_NM) 를 사용해 봅니다.
아마 9i r2부턴가 지원된다고 합니다.

 

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 28 14:51:02 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> CREATE DIRECTORY TEST_DIR AS 'D:\\oracle_files';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY TEST_DIR TO 사용자;

Grant succeeded.

SQL>SELECT * FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME='TEST_DIR';

OWNER            DIRECTORY_NAME	           DIRECTORY_PATH
---------------- ------------------------- -------------------------------
SYS              TEST_DIR                  D:\\oracle_files

SQL> SELECT NAME FROM V$PARAMETER WHERE NAME = 'utl_file_dir';

NAME
----------------
utl_file_dir

EXCEPTION은 추가 테스트가 필요할 수 있습니다.

CREATE OR REPLACE PROCEDURE TEST_REMOVE(V_MSG OUT VARCHAR2) IS
START_TIME  NUMBER;
END_TIME    NUMBER;

INVALID_OPERATION EXCEPTION;
INVALID_FILEHANDLE EXCEPTION;
INVALID_FILENAME EXCEPTION;
INVALID_PATH EXCEPTION;
DELETE_FAILED EXCEPTION;

PRAGMA EXCEPTION_INIT(INVALID_OPERATION,-29283);
PRAGMA EXCEPTION_INIT(INVALID_FILEHANDLE,-29282);
PRAGMA EXCEPTION_INIT(INVALID_FILENAME,-29288);
PRAGMA EXCEPTION_INIT(INVALID_PATH,-29280);
PRAGMA EXCEPTION_INIT(DELETE_FAILED,-29291);

CURSOR FILE_NMS IS
SELECT FILE_NM FROM FILE_REMOVE_TEST;

/******************************************************************************
   NAME:       TEST_REMOVE
   PURPOSE:    OS상의 파일 삭제 테스트

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2017/06/28   ROOKIE       1. Created this procedure.
******************************************************************************/
BEGIN

    START_TIME := DBMS_UTILITY.GET_TIME;
   
    V_MSG := '';
    
    FOR FILE_LIST IN FILE_NMS LOOP
        BEGIN
            DBMS_OUTPUT.PUT_LINE('파일명 : ' || FILE_LIST.FILE_NM);
            UTL_FILE.FREMOVE('TEST_DIR',FILE_LIST.FILE_NM);
        EXCEPTION 
            WHEN INVALID_OPERATION OR INVALID_FILEHANDLE OR INVALID_FILENAME OR INVALID_PATH OR DELETE_FAILED THEN
                V_MSG:=V_MSG||CHR(10)||FILE_LIST.FILE_NM||'------>'||SQLERRM;
                CONTINUE;
            WHEN OTHERS THEN
                RAISE;
            
        END;
    END LOOP;                 
   
    END_TIME := DBMS_UTILITY.GET_TIME;
           
    V_MSG := 'TEST_REMOVE ELAPSED: '||TO_CHAR( (END_TIME-START_TIME)/100,'FM9,990.990')||' '||V_MSG;
           
    COMMIT;
       
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       ROLLBACK;
       DBMS_OUTPUT.PUT_LINE('TEST_REMOVE ERROR: '||SQLERRM);
       V_MSG := 'TEST_REMOVE ERROR: '||SQLERRM;
       RAISE;
END TEST_REMOVE;
/