UTL_FILE 테스트 방법
출처 : http://www.oratable.com/utl_file/
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 | SQL> col owner for a24 SQL> col directory_name for a30 SQL> col directory_path for a80 SQL> SQL> select owner, directory_name, directory_path from dba_directories ; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------ ------------------------------ -------------------------------------------------------------------------------- SYS EXP /u01/exp SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [TGDB]TGSVR:/app/oracle> cd /u01 [TGDB]TGSVR:/u01> cd exp [TGDB]TGSVR:/u01/exp> ls expdp_jat2bravo_180511.dmp expdp_jat2bravo_180511.dmp.zip impdp_jat2bravo_180511.log impdp_jat2bravo_180511.par [TGDB]TGSVR:/u01/exp> ss SQL*Plus: Release 11.2.0.4.0 Production on Wed May 16 17:52:12 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set serveroutput on SQL> -- Simple PLSQL to open a file, SQL> -- write two lines into the file, SQL> -- and close the file SQL> declare 2 fhandle utl_file.file_type; 3 begin 4 fhandle := utl_file.fopen( 5 'EXP' -- File location 6 , 'test_file.txt' -- File name 7 , 'w' -- Open mode: w = write. 8 ); 9 10 utl_file.put(fhandle, 'Hello world!'|| CHR(10)); 11 utl_file.put(fhandle, 'Hello again!'); 12 13 utl_file.fclose(fhandle); 14 exception 15 when others then 16 dbms_output.put_line('ERROR: ' || SQLCODE 17 || ' - ' || SQLERRM); 18 raise; 19 end; 20 / PL/SQL procedure successfully completed. SQL> !ls -lrt total 9198424 -rw-r--r-- 1 oracle oinstall 26 May 16 17:52 test_file.txt SQL> !cat test_file.txt Hello world! Hello again! SQL> CREATE OR REPLACE DIRECTORY TG_DIR AS '/u01/exp/tg' ; Directory created. SQL> GRANT READ, WRITE ON DIRECTORY TG_DIR TO PUBLIC ; Grant succeeded. -- OS Path 가 없지만, DB DIRECTORY 는 문제 없이 생성됨 !!! SQL> !ls -al /u01/exp/tg ls: cannot access /u01/exp/tg: No such file or directory SQL> SQL> -- Simple PLSQL to open a file, SQL> -- write two lines into the file, SQL> -- and close the file SQL> declare 2 fhandle utl_file.file_type; 3 begin 4 fhandle := utl_file.fopen( 5 'TG_DIR' -- File location 6 , 'test_file.txt' -- File name 7 , 'w' -- Open mode: w = write. 8 ); 9 10 utl_file.put(fhandle, 'Hello world!' 11 || CHR(10)); 12 utl_file.put(fhandle, 'Hello again!'); 13 14 utl_file.fclose(fhandle); 15 exception 16 when others then 17 dbms_output.put_line('ERROR: ' || SQLCODE 18 || ' - ' || SQLERRM); 19 raise; 20 end; 21 / ERROR: -29283 - ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation declare * ERROR at line 1: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation ORA-06512: at line 19 SQL> | cs |