eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Jonathan Lewis: Can we have a sensible debate ? | Blog首页 | Solaris Open File Limit »

Using Create directory & UTL_FILE in 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就拥有了对该目录的读写权限。

让我们看一个简单的测试:

SQL> create or replace directory UTL_FILE_DIR as '/opt/oracle/utl_file'; Directory created. SQL> declare 2 fhandle utl_file.file_type; 3 begin 4 fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w'); 5 utl_file.put_line(fhandle , 'eygle test write one'); 6 utl_file.put_line(fhandle , 'eygle test write two'); 7 utl_file.fclose(fhandle); 8 end; 9 / PL/SQL procedure successfully completed. SQL> ! [oracle@jumper 9.2.0]$ more /opt/oracle/utl_file/example.txt eygle test write one eygle test write two [oracle@jumper 9.2.0]$

类似的我们可以通过utl_file来读取文件:

SQL> declare 2 fhandle utl_file.file_type; 3 fp_buffer varchar2(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 / eygle test write one eygle test write two PL/SQL procedure successfully completed.

可以查询dba_directories查看所有directory.

SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ ------------------------------ SYS UTL_FILE_DIR /opt/oracle/utl_file SYS BDUMP_DIR /opt/oracle/admin/conner/bdump SYS EXP_DIR /opt/oracle/utl_file

可以使用drop directory删除这些路径.

SQL> drop directory exp_dir; Directory dropped SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ ------------------------------ SYS UTL_FILE_DIR /opt/oracle/utl_file SYS BDUMP_DIR /opt/oracle/admin/conner/bdump



历史上的今天...
    >> 2016-04-22文章:
    >> 2013-04-22文章:
    >> 2009-04-22文章:
    >> 2008-04-22文章:
    >> 2006-04-22文章:
           域名真的很重要么?

无觅

By eygle on 2005-04-22 11:57 | Comments (5) | SQL.PLSQL | 267 |

5 Comments

建议把您作为语法的
create or replace directory exp_dir as '/tmp';
grant read, write on directory exp_dir to eygle;

这两句用标准语法格式给出吧,或许我比较愚钝,但的确有些感觉不清晰。建议换成:

CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
GRANT READ[,WRITE] ON DIRECTORY directory TO username;

此外,您在给出的测试中,并没有对 UTL_FILE_DIR 进行授权,当然您当时测试肯定没有问题,但作为HowTo,最好完整示例您给出的语法。

浅见,请包涵!

谢谢指出,修改了一下原文:)

是不是只有用sys用户创建目录,在把权限授予其他用户?

I had a lot of time finding a soluction

So good you note

Thanks you

Best regards

Antonio

directory名称 只能是大写 UTL_FILE_DIR


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com