Oracle PL/SQL の UTL_FILEによる操作がしたいです

Question

###前提・実現したいこと
Oracle database 12c と、SQL Developerを使用してPL/SQLの勉強中です。
UTL_FILEを使用して、ファイルの読み書きをしたいのですが、うまくいきませんでした。

作業&実行ユーザ：SCOTT（一般ユーザ）
OS：Windows7
作業：
SQL Developer(PL/SQL作成と実行)
コマンドプロンプトよりSQL*Plus(sysdba権限で権限付与)

エンジニア経験も浅い初心者ですが、
よろしくお願い致します。

###発生している問題・エラーメッセージ
データベースorcl.domainに接続中です。
ORA-29283: 無効なファイル操作です。
ORA-06512: "SYS.UTL_FILE", 行536
ORA-29283: 無効なファイル操作です。
ORA-06512: "SCOTT.FILE_UNLOAD_DEPT", 行9
ORA-06512: 行2
プロセスが終了しました。
データベースorcl.domainから切断中です。。

###UTL_FILE_DIR使用の場合

create or replace 
PROCEDURE UTL_FILE_DIR_WRITE_SAMPLE
AS
  file_hand UTL_FILE.FILE_TYPE;
  dirname  VARCHAR2(250);
  name     VARCHAR2(250);
BEGIN
  name := 'dept.txt';
  dirname  := 'C:\Users\ymrkm\Desktop\SCOTT'; 
  file_hand   := UTL_FILE.FOPEN(dirname ,name,'w', 32767);
  UTL_FILE.PUT_LINE(file_hand, 'hoge');
  UTL_FILE.FCLOSE(file_hand);
END;

**実行時のエラーメッセージ**
データベースorcl.domainに接続中です。
ORA-29283: 無効なファイル操作です。
ORA-06512: "SYS.UTL_FILE", 行536
ORA-29283: 無効なファイル操作です。
ORA-06512: "SCOTT.FILE_UNLOAD_DEPT", 行9
ORA-06512: 行2
プロセスが終了しました。
データベースorcl.domainから切断中です。

###DIRECTORYオブジェクト使用の場合

CREATE OR REPLACE PROCEDURE file_unload_dept
IS
  CURSOR dept_cur IS SELECT dname FROM dept;
  file_hand       UTL_FILE.FILE_TYPE;
  name            VARCHAR2(20);
BEGIN
  SELECT sysdate INTO day FROM dual;
  name:='dept.txt';
  file_hand:=UTL_FILE.FOPEN('UTL_DATA',name,'a');
  FOR dept_rec IN dept_cur LOOP
    UTL_FILE.PUT_LINE(file_hand,dept_rec.dname);
  END LOOP;
  UTL_FILE.FCLOSE(file_hand);
END;

**実行時のエラーメッセージ**
データベースorcl.domainに接続中です。
ORA-29283: 無効なファイル操作です。
ORA-06512: "SYS.UTL_FILE", 行536
ORA-29283: 無効なファイル操作です。
ORA-06512: "SCOTT.FILE_UNLOAD_DEPT", 行9
ORA-06512: 行2
プロセスが終了しました。
データベースorcl.domainから切断中です。

###試したこと
・CREATE DIRECTORY -> SCOTTにディレクトリの権限付与
・SQLPlusでSCOTTに接続し、SQLPlusでfile_unload_deptを実行
・システム権限を再度付与（CREATE SESSION,ALTER SESSION,CREATE PROCEDURE,CREATE TRIGGER,CREATE SEQUENCE）
・'dept.txt'をあらかじめ用意してみた

###（追記）試したこと
UTL_FILE_DIRにディレクトリをセットし、
GRANT SELECT ON V_$PARAMETER2 TO SCOTT;もやってみたのですが、同様でした。
'dept.txt'が存在している状態で、FOPEN()の引数を'a'->'ｗ'に変更したところ、
ORA-06512: "SYS.UTL_FILE", 行41 がなくなったのですが、他はそのままエラーとなっています。

###確認したこと(DIRECTORYオブジェクト)
(1)UTL_FILE.OPENの第一引数は「DIRECTORYオブジェクト」

"select * from all_directories;"より
OWNER: SYS
DIRECTORY_NAME: UTL_DATA
DIRECTORY_PATH: C:\Users\ymrkm\Desktop\SCOTT
ORIGIN_CON_ID: 0

(2)UTL_FILE.OPENの第ニ引数は「DIRECTORYオブジェクト」配下となるファイル名

C:\Users\ymrkm\Desktop\SCOTT\dept.txtのdept.txt

(3)上記DIRECTORYオブジェクトに対してスキーマが読み書きの権限があるか

GRANTEE:	   	SCOTT
OWNER:			   SYS
TABLE_NAME:		  UTL_DATA
GRANTOR:		     SYS
PRIVILEGE:		   READ
GRA:		     	NO
HIE:			     NO
COM:			     NO
TYPE:			    DIRECTORY

GRANTEE:     		SCOTT
OWNER:		   	SYS
TABLE_NAME:	  	UTL_DATA
GRANTOR:	     	SYS
PRIVILEGE:		   WRITE
GRA:			     NO
HIE:		     	NO
COM:	     		NO
TYPE:			    DIRECTORY

以上です。
よろしくお願い致します。

Answer 1

未検証ですので外しているかもしれませんが、以下の条件が満たされているか確認して見てください。

• UTL_FILE.OPENの第一引数は「DIRECTORYオブジェクト」
• UTL_FILE.OPENの第ニ引数は「DIRECTORYオブジェクト」配下となるファイル名
• 上記DIRECTORYオブジェクトに対してスキーマが読み書きの権限があるか
• UTL_FILE.OPENで利用するファイルに対して、Oracle実行ユーザにOSレベルで読み書き権限があるか

また、Oracle12cではUTL_FILE_DIRの利用よりもDIRECTORYオブジェクトの利用が推奨されているようです。

http://docs.oracle.com/cd/E49329_01/appdev.121/b71281/u_file.htm
セキュリティ・モデル

---

※質問文のソースコードはコードタグでくくると読みやすくなりますよ。

CREATE OR REPLACE ....
  // hogehoge

Comments

[ymrkm]

回答ありがとうございます。
教えていただいた確認事項は本分にて追記させていただきました。
ただし、1点だけ、
>UTL_FILE.OPENで利用するファイルに対して、Oracle実行ユーザにOSレベルで読み書き権限があるか
申し訳ありません、こちらについてはよくわからなかったのですが……、
下記のものとは別でしょうか……？

SQL> SELECT * FROM dba_sys_privs WHERE GRANTEE='SCOTT'

GRANT PRIVILEGE ADM COM
----- ---------------------------------------- --- ---
SCOTT CREATE TABLE NO NO
SCOTT UNLIMITED TABLESPACE NO NO
SCOTT ALTER SESSION NO NO
SCOTT DEBUG CONNECT SESSION NO NO
SCOTT CREATE TRIGGER NO NO
SCOTT CREATE SESSION NO NO
SCOTT DEBUG ANY PROCEDURE NO NO
SCOTT CREATE PROCEDURE NO NO
SCOTT CREATE SEQUENCE NO NO

[takyafumin]

>UTL_FILE.OPENで利用するファイルに対して、Oracle実行ユーザにOSレベルで読み書き権限があるか

「OSレベルで」とはOracle上ではなく、通常のファイルへの権限を意味しました。
Oracleサーバプロセスが該当ファイル（やディレクトリ）にアクセスできないと同様のエラーが発生するかと思います。

確認ですが、Oracleデータベースはご自身のPCにインストールして実行していますか？それともWindows上に仮想環境を構築して実行していますか？それともWindowsとは異なる別のサーバに構築して実行していますか？
※Oracleデータベースサーバがどこにあるか、という意味合いです。
　tnsnames.oraに指定されている「HOST」はどこを指していますか？

Answer 2

UTL_FILE パッケージの使い方 (UTL_FILE_DIR 編) とぱっと見比べると、ALTER SYSTEM SET UTL_FILE_DIR ... が抜けています。
上記のサンプルを参考に1ステップずつきちんと実行してみては？

Comments

[ymrkm]

回答ありがとうございます。
UTL_FILE_DIRにディレクトリをセットし、
GRANT SELECT ON V_$PARAMETER2 TO SCOTT;もやってみたのですが、同様でした。
'dept.txt'が存在している状態で、FOPEN()の引数を'a'->'ｗ'に変更したところ、
ORA-06512: "SYS.UTL_FILE", 行41 がなくなったのですが、他はそのままエラーとなっています。

[Orlofsky]

>上記のサンプルを参考に1ステップずつきちんと実行してみては？
って書いたのですが。

UTL_FILE.FOPENの部分で
ymrkmさんのコードは
>file_hand:=UTL_FILE.FOPEN('UTL_DATA',name,'a');
ですが、
わたしが提示したコードは
> vHandle := UTL_FILE.FOPEN(vDirname ,vFilename,'r', 32767);
とvDirnameでディレクトリのパスを指定しています。

UTL_FILE.FOPEN をマニュアルで確認されましたか？
http://docs.oracle.com/cd/E57425_01/121/ARPLS/u_file.htm#i1003526

[ymrkm]

追記の足りない箇所がございました。
失礼いたしました。
create directoryしたUTL_DATAを使用するのをやめて、
設定したUTL_FILE_DIRと同じpathを'vDirname'と同様に変数に入れて、FOPENの変数としています。

[Orlofsky]

コードは必要最小限の内容をきちんと載せないで省かれてもわかりません。エラー・メッセージも表示されたものをそのまま載せてください。

[ymrkm]

コードをそのまま全部載せるようにさせていただきました。
練習問題の内容そのままなので、短いですがこれで全文です。
エラーコードも全文です。
utl_file_dirとcreate directoryの2パターンです。

[Orlofsky]

UTL_FILE の使い方 (DIRECTORY 編)
http://www.shift-the-oracle.com/plsql/utl_file/create-directory.html

tnsnames.oraの HOST = localhost となっていますか？
tns1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxxxxxxx)
)
)

テキストファイルに出力するだけなら、
cd C:\Users\ymrkm\Desktop\SCOTT
sqlplus / as sysdba
set は省略
spool dept.txt
SELECT dname FROM dept;
spool off