Oracle读写文件bfilename的实例.doc

Oracle读写文件bfilename的实例Create directory让我们可以在Oracle数据库中灵活的对文件进行读写操作，极大的提高了Oracle的易用性和可扩展性，下面就让我们来了解一下它的语法。Oracle读写文件操作相信大家都不陌生，下面就为您介绍Oracle读写文件bfilename的实例，希望对您学习Oracle读写文件方面能有所帮助。Create directory让我们可以在Oracle数据库中灵活的对文件进行读写操作，极大的提高了Oracle的易用性和可扩展性。其语法为:CREATE OR REPLACE DIRECTORY directory AS pathname;本案例具体创建如下:create or replace directory exp_dir as /tmp;目录创建以后，就可以把读写权限授予特定用户，具体语法如下:GRANT READ,WRITE ON DIRECTORY directory TO username;例如:grant read, write on directory exp_dir to eygle;此时用户eygle就拥有了对该目录的读写权限。让我们看一个简单的测试:1. SQLcreateorreplacedirectoryUTL_FILE_DIRas/opt/oracle/utl_file; 2. Directorycreated.SQLdeclare 3. fhandleutl_file.file_type; 4. begin 5. fhandle:=utl_file.fopen(UTL_FILE_DIR,example.txt,w); 6. utl_file.put_line(fhandle,eygletestwriteone); 7. utl_file.put_line(fhandle,eygletestwritetwo); 8. utl_file.fclose(fhandle); 9. end; 10. / 11. PL/SQLproceduresuccessfullycompleted. 12. SQL! 13. oraclejumper9.2.0$more/opt/oracle/utl_file/example.txt 14. eygletestwriteone 15. eygletestwritetwo 16. oraclejumper9.2.0$类似的我们可以通过utl_file来读取文件:1. SQLdeclare 2. fhandleutl_file.file_type; 3. fp_buffervarchar2(4000); 4. begin 5. fhandle:=utl_file.fopen(UTL_FILE_DIR,example.txt,R); 6. 7. utl_file.get_line(fhandle,fp_buffer); 8. dbms_output.put_line(fp_buffer); 9. utl_file.get_line(fhandle,fp_buffer); 10. dbms_output.put_line(fp_buffer); 11. utl_file.fclose(fhandle); 12. end; 13. / 14. eygletestwriteone 15. eygletestwritetwo 16. PL/SQLproceduresuccessfullycompleted.可以查询dba_directories查看所有directory.1. SQLselect*fromdba_directories; 2. OWNERDIRECTORY_NAMEDIRECTORY_PATH- 3. SYSUTL_FILE_DIR/opt/oracle/utl_file 4. SYSBDUMP_DIR/opt/oracle/admin/conner/bdump 5. SYSEXP_DIR/opt/oracle/utl_file可以使用drop directory删除这些路径.1. SQLdropdirectoryexp_dir;DirectorydroppedSQLselect*fromdba_directories;OWNERDIRECTORY_NAMEDIRECTORY_PATH-SYSUTL_FILE_DIR/opt/oracle/utl_fileSYSBDUMP_DIR/opt/oracle/admin/conner/bdump 2. 3. createorreplacedirectoryUSER_DIRasE:PLSQL310; 4. 5. DECLARE 6. v_contentVARCHAR2(1800); 7. v_bfileBFILE; 8. amountINT; 9. offsetINT:=1; 10. 11. BEGIN 12. v_bfile:=bfilename(USER_DIR,test.TXT);-注意这里的User_dir对应上面已经创建好啦的目录 13. amount:=DBMS_LOB.getlength(v_bfile); 14. DBMS_LOB.OPEN(v_bfile); 15. DBMS_LOB.R