学步园

--login system
CREATE DIRECTORY | as '/appl/appldev2/dev2appl/ofc/11.5.0';
grant read,write on directory test_dir to apps;
--grant execute on utl_file to apps;

-- login apps
DECLARE
  FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
  FILE_HANDLE := UTL_FILE.FOPEN('TEST_DIR', SYSDATE||'.txt', 'w',32767);
  UTL_FILE.put_line(FILE_HANDLE, 'Test all directories!');
  UTL_FILE.FCLOSE(FILE_HANDLE);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
END;
------------------------------------------------------------------
........
open c_gl_data;
   loop
      fetch c_gl_data into l_effective_date,l_currency_code,l_attribute
                      ,l_proc_unit,l_business,l_cost_center
                      , l_acct, l_je_number, l_ref, l_desc1
                      , l_desc2, l_curr1, l_amt1, l_curr2, l_amt2
                      , l_posting_period, l_logonid, l_src, l_filter
                      , l_doc_sequence_value, l_header_name, l_je_line_num,  l_batch_name,  l_description;
      EXIT WHEN c_gl_data%NOTFOUND;                    
       if  l_attribute != 'CNY' and l_attribute !=  'USD' then
           l_attribute := l_currency_code;
       end if;
      if substrb(l_desc2||l_desc1||'`',73,1) != '`' then
              FND_FILE.PUT_LINE(FND_FILE.LOG,'');                  
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Warning: Wrong encoding of descrption for this line:    '
                                        ||'Account:'||l_acct
                                        ||'    Document num:'||l_doc_sequence_value
                                        ||'    Header Name:'||l_header_name
                                        ||'    Line num:'||l_je_line_num
                                        ||'    Batch name:'||l_batch_name
                                        ||'    Description:'||l_description);                                       
       end if;       
            utl_file.put_line(fileHandler,
            l_proc_unit ||l_business ||l_cost_center ||
            l_acct ||l_je_number || l_ref || l_desc2 ||
            l_desc1 || l_curr1 ||l_amt1 || l_curr2 || l_amt2 ||
            l_posting_period ||l_logonid ||l_src ||l_filter);
------------------------------------------------------------------------------------         

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]$ 
 

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.

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

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