2 Replies Latest reply: Apr 26, 2013 4:26 AM by DK2010 RSS

    Directory Create

    asif.maqbool
      Hello:

      I want to use UTL_FILE package to create OS file. Please look on script and suggest how to resolve this error. Oracle11g under XP.


      SQL> create directory my_dir as 'c:\temp';

      Directory created.


      1 create or replace procedure test_1(md in varchar2)
      2 is
      3 file utl_file.file_type;
      4 begin
      5 file := utl_file.fopen(md,'abc.log','w');
      6 utl_file.put_line(file,'EMPLOYE REPORT');
      7 utl_file.fclose(file);
      8* end;
      SQL> /

      Procedure created.

      SQL> execute test_1('MY_DIR');
      BEGIN test_1('MY_DIR'); END;


      RROR at line 1:
      RA-06510: PL/SQL: unhandled user-defined exception
      RA-06512: at "SYS.UTL_FILE", line 98
      RA-06512: at "SYS.UTL_FILE", line 157
      RA-06512: at "SCOTT.TEST_1", line 5
      RA-06512: at line 1
        • 1. Re: Directory Create
          DK2010
          Hi,

          What about the grant
          grant read,write on directory  my_dir to <username>;
          • 2. Re: Directory Create
            JohnWatson
            Works for me:
            c:\users\john\home>sqlplus system/oracle
            
            SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 26 10:29:43 2013
            
            Copyright (c) 1982, 2011, Oracle.  All rights reserved.
            
            
            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
            With the Partitioning, Oracle Label Security, OLAP, Data Mining,
            Oracle Database Vault and Real Application Testing options
            
            
            Session altered.
            
            orcl> create directory my_dir as 'c:\temp';
            
            Directory created.
            
            orcl> create or replace procedure test_1(md in varchar2)
              2  is
              3  file utl_file.file_type;
              4  begin
              5  file := utl_file.fopen(md,'abc.log','w');
              6  utl_file.put_line(file,'EMPLOYE REPORT');
              7  utl_file.fclose(file);
              8  end;
              9  /
            
            Procedure created.
            
            orcl> execute test_1('MY_DIR')
            
            PL/SQL procedure successfully completed.
            
            orcl> exit
            Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
            With the Partitioning, Oracle Label Security, OLAP, Data Mining,
            Oracle Database Vault and Real Application Testing options
            
            c:\users\john\home>dir c:\temp\abc.log
             Volume in drive C has no label.
             Volume Serial Number is 2E3F-D952
            
             Directory of c:\temp
            
            26/04/2013  10:30                16 abc.log
                           1 File(s)             16 bytes
                           0 Dir(s)  44,309,999,616 bytes free