ORACLE Directory directory--Reprint

Create directory gives us the flexibility to read and write files in an Oracle database, greatly improving the ease of use and scalability of Oracle.
Its syntax is:
CREATE [OR REPLACE] Directory directory as 'pathname';

This case is specifically created as follows:

Create or replace directory Exp_dir as '/tmp ';

After the directory is created, you can grant read and write permissions to specific users, with the following syntax:
GRANT Read[,write] on the directory directory to username;

For example:

Grant read, write on directory exp_dir to Eygle;

At this point, the user eygle has read and write access to the directory.

Let's look at a simple test:

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 '); 7 Utl_file.fclose (fhandle); 8 end; 9 / PL/SQL procedure successfully completed. Sql>! [[email protected] 9.2.0]$ more/opt/oracle/utl_file/example.txt eygle Test write one eygle test write Two [[email protected] 9.2.0]$

Similarly we can read the file via 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); Ten Dbms_output.put_line (fp_buffer); Utl_file.fclose (fhandle); The end; / eygle Test write one eygle test write, PL/ SQL procedure successfully completed.

You can query dba_directories to see all your 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

You can use drop directory to delete these paths.

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

ORACLE Directory directory--Reprint