오라클] OS의 물리파일 읽기 쓰기(UTL_FILE)

2017.09.07 07:00



오라클 UTL_FILE 패키지는 OS 파일을 read/write 할 수 있게 해준다. 


몇 가지 UTL_FILE 패키지를 사용하기 위한 전제 조건이 있다. 

(이걸 잘 몰라서 엄청 헤맸다. ㅠㅠ)




 UTL_FILE 패키지 사용 조건


1 오라클의 DIRECTORY 오브젝트를 생성해야 한다. 

   - OS의 물리적인 디렉토리를 미리 생성해야 한다.

   - 오라클의 CREATE DIRECTORY 명령어로 디렉토리 오브젝트를 생성한다. 


2. 오라클 일반 계정이 DIRECTROY 오브젝트를 사용하기 위해서는

   - SYS/SYSTEM 계정이 DIRECTORY를 생성하고 사용자에게 read/write 권한을 주거나

   - 사용자가 CREATE ANY DIRECTORY 권한을 받아야 한다. 


3. DIRECTORY 오브젝트 이름은 대문자를 사용한다.

   - 소문자로 적어서 생성하더라도 실제 사용할 때 대문자여야 한다. 


4. 하위 디렉토리와 하위 디렉토리의 폴더는 접근할 수 없다

   - /aaa 디렉토리에 대해 오라클 디렉토리 오브젝트를 생성한 경우
     /aaa 디렉토리의 파일은 접근 가능하지만 /aaa/bbb/*.txt 파일은 접근 불가능하다
    ( /aaa/bbb 디렉토리에 대한 오브젝트를 별도로 생성해야 한다)


5. 당연한 얘기지만 OS쪽으로 접근하려는 디렉토리에 대해서 오라클 프로세스가 접근권한을 가지고 있어야 한다.

   - 오라클 실행계정이 oracle이라면 디렉토리 소유주가 oracle이거나 적어도 oracle과
     같은 그룹이어야 한다.

   - 디렉토리에 대해서 오라클이 읽기/쓰기 권한을 가지고 있어야 한다





디렉토리 오브젝트 관련 기본 명령어


-- 디렉토리 오브젝트 생성

SQL> CREATE DIRECTORY 디렉토리_오브젝트이름 AS '/OS디렉토리_이름';


-- 디렉토리 오브젝트 조회(사용자)

SQL> SELECT * FROM ALL_DIRECTORIES;


-- 디렉토리 오브젝트 조회(DBA)

SQL> SELECT * FROM DBA_DIRECTORIES;


-- 디렉토리 오브젝트 삭제

SQL> DROP DIRECTORY 디렉토리 이름;




이제 명령어랑 실제 사용 방법에 대해서 좀 더 자세히 알아보자. 



1. 물리 디렉토리 생성


- 사용자 계정 oracle의 홈 디렉토리 아래 ora_utl이라는 디렉토리를 만든다


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
[root@OEL73:~]$ su - oracle
마지막 로그인: 일  9월  3 17:59:44 KST 2017 일시 pts/3
 
[oracle@OEL73:~]$ pwd
/home/oracle
 
[oracle@OEL73:~]$ mkdir ora_utl
 
[oracle@OEL73:~]$ chmod 764 ora_utl
 
[oracle@OEL73:~]$ ls -alrt ora_utl
합계 8
drwxrw-r--.  2 oracle oinstall 4096  9월  5 22:28 .
drwx------15 oracle oinstall 4096  9월  5 22:28 ..
cs


-- oracle계정이 아닌 root계정으로 디렉토리 생성시 oracle이 접속할 수 있어야 하고 필요에 따라 write권한도 부여해야 한다. 여기서는 764(rwx rw- r--)로 설정했다.



2. 디렉토리 오브젝트 생성


디렉토리 오브젝트는 sys/system 계정이 만들어서 사용자에게 read/write 권한을 주거나 

사용자 계정이 직접 디렉토리 오브젝트를 만들 수 있도록 권한을 준다. 


-- sys계정으로 생성후 scott계정에게 권한 부여


1
2
3
4
5
6
7
SYS @ SQL > CREATE DIRECTORY TEST_DIR AS '/home/oracle/ora_utl';
 
디렉토리가 생성되었습니다.
 
SYS @ SQL > GRANT READWRITE ON DIRECTORY TEST_DIR TO scott;
 
권한이 부여되었습니다.
cs



-- 또는 scott 계정에게 CREATE ANY DIRECTORY  권한 부여


1
2
3
4
5
6
7
8
SYS @ SQL > GRANT CREATE ANY DIRECTORY TO scott;
 
권한이 부여되었습니다.
 
SYS @ SQL > conn scott/tiger
연결되었습니다.
 
SCOTT @ SQL > CREATE DIRECTORY TEST_DIR AS '/home/oracle/ora_utl';
cs




3. 파일쓰기


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SCOTT @ SQL> declare
      fhandle utl_file.file_type;
        begin
          fhandle := utl_file.fopen('TEST_DIR''example.txt''w'); -- 반드시 디렉토리명 대문자
          utl_file.put_line(fhandle , '파일쓰기 테스트');
          utl_file.put_line(fhandle , 'write!! write!!');
          utl_file.fclose(fhandle);
      end;
    /
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
SCOTT @ SQL > ! ls -alrt /home/oracle/ora_utl
합계 12
drwx------. 15 oracle oinstall 4096  9월  5 22:28 ..
drwxrw-r--.  2 oracle oinstall 4096  9월  6 23:13 .
-rw-r--r--.  1 oracle oinstall   39  9월  6 23:13 example.txt
 
SCOTT @ SQL > ! cat /home/oracle/ora_utl/example.txt
파일쓰기 테스트
write!! write!!
cs



4. 파일읽기


-- 위에서 저장한 파일을 읽기


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SCOTT @ SQL > set serveroutput on  -- 화면으로 출력
 
SCOTT @ SQL > declare
      fhandle utl_file.file_type;
      fp_buffer varchar2(4000);
  
      begin
        fhandle := utl_file.fopen('TEST_DIR''example.txt''R'); -- 반드시 디렉토리명 대문자
        utl_file.get_line (fhandle , fp_buffer );
        dbms_output.put_line(fp_buffer );
        utl_file.get_line (fhandle , fp_buffer );
        dbms_output.put_line(fp_buffer );
        utl_file.fclose(fhandle);
      end;    
  /
파일쓰기 테스트
write!! write!!
 
PL/SQL 처리가 정상적으로 완료되었습니다.
cs



* 오라클에 데이터 입력하기 

- 오라클] ORACLE SQL*LOADER


또루아빠 SQL(오라클,mysql) , , , , , , , ,