鍒╃敤pl/sql璇绘枃浠?绠?鍗

聽聽聽聽 浠ュ墠鍚??oracle涓?彲浠ヤ娇鐢╱tl_file璇诲啓鏂囦欢锛屼竴鐩存病鏈轰細鎺ヨЕ鍒帮紝鍐嶇湅浜唘tl_file璇绘枃浠剁殑渚嬪瓙鍚庯紝鎰熻?鍜宩ava涓?殑璇绘枃浠跺緢鍍忥紝涓?涓嬫槸涓?涓?畝鍗曠殑浣跨敤utl_file璇绘枃浠剁殑渚嬪瓙锛屽ぇ绁炶?缁曡繃锛屽洜涓轰緥瀛愮湡鐨勫緢绠?鍗曪紝鎴戜娇鐢ㄧ殑oracle鐗堟湰鏄?0.2銆?/span>

聽聽聽 棣栧厛锛屾櫘閫氱敤鎴疯?鎯充娇鐢╱tl_file锛屽繀椤昏?鏈夋潈闄愶紝棣栧厛浣跨敤dba鐧诲綍锛岀粰鐢ㄦ埛鎺堟潈锛屽?涓嬫墍绀猴細

聽聽聽

D:\oracle\app\oracle\product\10.2.0\server\BIN>sqlplus "connect as sysdba"SQL*Plus: Release 10.2.0.1.0 - Production on 鏄熸湡鏃?1鏈?26 10:25:40 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.杈撳叆鍙ｄ护:杩炴帴鍒?Oracle Database 10g Express Edition Release 10.2.0.1.0 - ProductionSQL> grant execute on utl_file to testuser;鎺堟潈鎴愬姛銆?/pre>
聽聽聽 鏈変簡鏉冮檺涔嬪悗锛岃?鎯宠?鍙栨枃浠讹紝闇?鏂板缓涓?涓?洰褰?span>directory锛岃繕寰楀?璇ョ洰褰曟湁璇诲彇鏉冮檺锛?/span>
聽聽聽 鏂板缓鐩?綍鍛戒护濡備笅锛?/span>
聽聽聽
SQL> create or replace directory ORADIR_MY_FILE as 'f:/saveFile/oracle/';鐩?綍宸插垱寤恒??/pre>
聽聽聽 瀵圭敤鎴锋巿浜堣?鐩?綍璇诲啓鏉冮檺
聽聽聽 
SQL> grant write,read on directory ORADIR_MY_FILE to testuser;鎺堟潈鎴愬姛銆?/pre>
聽聽聽 濡傛灉闄愬埗鐢ㄦ埛鍙?湁璇绘潈闄愬彲浠ユ妸write鍘绘帀銆?/span>
聽聽聽 鑳芥巿浜堟潈闄愬氨鑳芥挙閿?鐢ㄦ埛鏉冮檺锛屾挙閿?鐢ㄦ埛鏉冮檺鍛戒护濡備笅锛?/span>
聽聽聽
revoke write,read on directory ORADIR_MY_FILE from testuser;
聽聽聽 鏌ョ湅绯荤粺鎵?鏈夌洰褰曞懡浠ゅ?涓嬶細
聽聽聽
select * from dba_directories;
聽聽 濡傛兂鍒犻櫎鐩?綍锛屽彲浠ヨ繖鏍峰仛锛?/span>
聽聽
SQL> drop directory ORADIR_MY_FILE;鐩?綍宸插垹闄ゃ??/pre>
聽聽聽 鐩?綍鏂板缓瀹屼簡锛屾潈闄愪篃鏈変簡锛屽彲浠ヨ瘯涓嬭?鍙栨枃浠朵簡锛?/span>
聽聽聽
---璇绘枃浠舵祴璇?DECLARE  f_file utl_file.file_type;  c_line VARCHAR2(2048);BEGIN  --鎵撳紑鏂囦欢  f_file := utl_file.fopen('ORADIR_MY_FILE',                           'testsql.sql',                           'R');    IF utl_file.is_open(f_file) THEN    LOOP      BEGIN        utl_file.get_line(f_file, c_line);      EXCEPTION        WHEN no_data_found THEN          EXIT;      END;      dbms_output.put_line(c_line);    END LOOP;  END IF;  IF utl_file.is_open(f_file) THEN    utl_file.fclose(f_file);  END IF;EXCEPTION  WHEN OTHERS THEN    IF utl_file.is_open(f_file) THEN      utl_file.fclose(f_file);    END IF;    dbms_output.put_line('寮傚父:' || SQLERRM);END;
聽聽
聽聽 utl_file.fopen('ORADIR_MY_FILE','testsql.sql','R');绗?竴涓?弬鏁版槸鍒氭墠鏂板缓鐨勭洰褰曪紝绗?簩涓?弬鏁版槸鏂囦欢鍚嶏紝鍙?互浣跨敤鍏ㄨ矾寰勫?锛?f:/saveFile/oracle/testsql.sql'锛涙枃浠朵笉涓?瀹氭槸sql鏂囦欢锛屼粈涔坖ava,txt,xml閮藉彲浠ワ紝鍒?瘯鐫?鎵撳紑doc涔嬬被鐨勬枃浠讹紝杩欐牱浣犲緱鍒扮殑灏嗘槸涔辩爜銆?/span>
聽聽聽 缁忔垜娴嬭瘯锛屾枃浠跺悕涓嶈兘浣跨敤涓?枃锛岃繕鏈?f:/saveFile/oracle/'鍜?f:/saveFile/oracle/test/'鏄?笉鍚岀殑2涓?洰褰曪紝瀵?f:/saveFile/oracle/'鎺堟潈骞朵笉璇存槑瀵?f:/saveFile/oracle/test/'鐩?綍涔熸湁鏉冮檺锛屼笂闈㈢殑缁撹?鏄?垜娴嬭瘯鐨勭粨鏋滐紝濡傛灉鏈夎?锛岃?鎸囨?锛岃阿璋?紒
聽聽聽 鍏ㄦ枃瀹屻??/span>
聽聽聽
聽聽 聽
聽