058 - [Oracle PL/SQL] Oracle package - UTL_FILE

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

 

 

 

 

 

 

• 사전에 필요한 작업들, 서버 환경에 따라서 폴더 옵션은 다름

to read/write file , we need to create directory
create directory only for sys and system user
1- open sqlplus
2- conn as sysdba
3- alter session set container=orclpdb ( the plug db name )
4- create directory mydir as '/home/oracle'
5- grant READ, WRITE on DIRECTORY MYDIR to public;
6- CREATE THE DIRECTORY ON your computer ( the server ) 
7- put empty file sample.txt
8- 폴더와 파일은 서버환경에 따라 맞게 설장하면 된다.

 

 

 

• 샘플코드

declare
    l_file UTL_FILE.file_type;
    l_location     VARCHAR2(100) := 'MYDIR'; --CAPITAL
    l_filename     VARCHAR2(100) := 'sample.txt';
    l_text         VARCHAR2(32767);
begin
    -- Open file.
    l_file := UTL_FILE.fopen(l_location, l_filename, 'r');
    
    --Read and output first line.
    UTL_FILE.get_line(l_file, l_text);
    DBMS_OUTPUT.put_line('1st Line:' || l_text);
    UTL_FILE.get_line(l_file, l_text);
    DBMS_OUTPUT.put_line('2nd Line:' || l_text);
    --close the file
    UTL_FILE.fclose(l_file);

end;
---------------------------------------------------------
1st Line:100	Steven	King	SKING	515.123.4567	17-JUN-03	AD_PRES	24100			90
2nd Line:101	Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000		100	90

 

 

 

• loop를 이용한 파일 읽기

declare
    l_file          UTL_FILE.file_type;
    l_location      VARCHAR2(100) := 'MYDIR'; --CAPITAL
    l_filename      VARCHAR2(100) := 'sample.txt';
    l_text          VARCHAR2(32767);
begin
    --Open file.
    l_file := UTL_FILE.fopen(l_location, l_filename, 'r');
    
    begin
        loop
            UTL_FILE.get_line(l_file, l_text);
            DBMS_OUTPUT.put_line( l_text);
        end loop;
        --데이터 없음 오류 예외처리.
        exception when no_data_found then
        DBMS_OUTPUT.put_line('end file');
    end;
    --Close the file
    UTL_FILE.fclose(l_file);

end;
----------------------------------------
100	Steven	King	SKING	515.123.4567	17-JUN-03	AD_PRES	24100			90
101	Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000		100	90

...

205	Shelley	Higgins	SHIGGINS	515.123.8080	07-JUN-02	AC_MGR	12008		101	110
206	William	Gietz	WGIETZ	515.123.8181	07-JUN-02	AC_ACCOUNT	8300		205	110
end file

 

 

 

• for문을 이용한 file 쓰기

# UTL_FILE.put_line

declare
    l_file UTL_FILE.file_type;
    l_location     VARCHAR2(100) := 'MYDIR'; --CAPITAL
    l_filename     VARCHAR2(100) := 'sample2.txt';
begin
    -- Open file.
    l_file := UTL_FILE.fopen(l_location, l_filename, 'w');--새로 생성. 기존 내용 삭제.

    for i in (select * from DEPARTMENTS)
    loop
        UTL_FILE.put_line(l_file, i.DEPARTMENT_name);      
    end loop;

    --close the file
    UTL_FILE.fclose(l_file);

    l_file := UTL_FILE.fopen(l_location, l_filename, 'A');--추가가능.
    UTL_FILE.put_line(l_file, 'ADDITIONAL LINES');
    UTL_FILE.fclose(l_file);
end;

 

 

 

 

 

 

create or replace procedure read_any_file
    (P_dir in varchar2, p_file_name in varchar2)
is
    l_file UTL_FILE.file_type;
    l_text         VARCHAR2(32767);
begin
    l_file := UTL_FILE.fopen(P_dir, p_file_name, 'r');
    begin
        loop
            UTL_FILE.get_line(l_file, l_text);
            DBMS_OUTPUT.put_line( l_text);
        end loop;
        exception 
        when no_data_found then
            DBMS_OUTPUT.put_line('The end of the file');
            UTL_FILE.fclose(l_file);
    end;

    exception 
        when UTL_FILE.invalid_operation then
            DBMS_OUTPUT.put_line('can not open the file, invalid file name');
        when UTL_FILE.read_error then
            DBMS_OUTPUT.put_line('can not be read');
end read_any_file;
-------------------------------------------------------
Procedure READ_ANY_FILE compiled

 

 

 

# 파일을 읽고 정상적으로 출력
execute read_any_file('MYDIR','sample2.txt');
-------------------------------------------------------
test
Administration
Marketing
...
Payroll
ADDITIONAL LINES
The end of the file



# 파일이 없어서 발생하는 오류를 예외처리한 경우
execute read_any_file('MYDIR','sampdddle.txt');
-------------------------------------------------------
can not open the file,invalid file name