ORACLE 10gR2 Read UTL_FILE範例說明

create or replace directory UPLOAD_FILE as '/orafs01/upload';
--請以有設定權限的ID設定如system等 ''內為您主機內檔案的絕對路徑
select * from dba_directories s order by s.directory_path;
--請確認你的設定有成功
grant read on directory UPLOAD_FILE to dev;
--設定成功後 grant read 權限給dev這個帳號

DECLARE
fhandle utl_file.file_type;
fp_buffer VARCHAR2(4000);
p_msg_desc VARCHAR2(2000);
BEGIN
fhandle := utl_file.fopen('UPLOAD_FILE', 'test_1.sql', 'R');
LOOP
BEGIN
utl_file.get_line(fhandle, fp_buffer);
EXECUTE IMMEDIATE fp_buffer;
--因為讀取的檔案內容就是sql指令 , 故以 EXECUTE IMMEDIATE 來執行
--如果讀取的內容就是sql指令，檔案內不能有 ; 以避免錯誤
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Over');
COMMIT;
EXIT;
WHEN OTHERS THEN
p_msg_desc := SQLCODE || ' : ' || SQLERRM;
dbms_output.put_line('error message = ' || p_msg_desc);
EXIT;
END;
END LOOP;
utl_file.fclose(fhandle);
END;