Oracle / Plsql do I need to check that the file is saved?

Question

ORA-29280: ORA-06512: "SYS.UTL_FILE", line 270 ORA-06512: "SYS.UTL_FILE", line 1243 ORA-06512: line 9

declare
  fhandle      UTL_FILE.FILE_TYPE;
begin


/*********************************************/
-- if statement  ( file1.txt check ???? )  

  utl_file.fcopy(src_location  => 'location1',
                 src_filename  => 'file1.txt',
                 dest_location => 'location2',
                 dest_filename => 'file1_backup.txt');
/*********************************************/

  fhandle := UTL_FILE.FOPEN('location1', 'file1.txt', 'W');

  utl_file.put_line(fhandle, 'some text');

  UTL_FILE.FCLOSE(fhandle);

end;

The error says the directory is missing. Are you sure you have created it with `CREATE DIRECTORY` statement? — Pavel Smirnov, Jun 21 '19 at 07:26

Dmitry Demin · Answer 1 · 2019-06-21T07:41:19.303

You must write the name of the directory in uppercase.

for example

select * from dba_directories
where directory_name='DIR_VIKS'
-------------------------------------------------
SYS DIR_VIKS    /home/trs/viks/00001/XML/03_KRW


declare
  fhandle      UTL_FILE.FILE_TYPE;
begin


/*********************************************/
-- if statement  ( file1.txt check ???? )  

  utl_file.fcopy(src_location  => 'DIR_VIKS',
                 src_filename  => '120910X-001.xml',
                 dest_location => 'DIR_VIKS',
                 dest_filename => '120910X-001.xml.bak');
/*********************************************/

  /*fhandle := UTL_FILE.FOPEN('location1', 'file1.txt', 'W');

  utl_file.put_line(fhandle, 'some text');

  UTL_FILE.FCLOSE(fhandle);
  */

end;



before run script  
oracle@esmd:/home/trs/viks/00001/XML/03_KRW> ls -l
total 64
-rw-r--r-- 1 viks00001 users 29425 2012-09-11 12:43 120910X-001.xml
-rw-r--r-- 1 viks00001 users 29337 2012-11-08 08:26 120910X-002.xml
after run script  
esmd:/home/trs/viks/00001/XML/03_KRW # ls -l
total 96
-rw-r--r-- 1 viks00001 users    29425 Sep 11  2012 120910X-001.xml
-rw-r--r-- 1 oracle    oinstall 29425 Jun 21 10:17 120910X-001.xml.bak
-rw-r--r-- 1 viks00001 users    29337 Nov  8  2012 120910X-002.xml

If the directory name is lowercase, we get an error.

begin
/*********************************************/
-- if statement  ( file1.txt check ???? )  

  utl_file.fcopy(src_location  => 'dir_viks',
                 src_filename  => '120910X-001.xml',
                 dest_location => 'dir_viks',
                 dest_filename => '120910X-001.xml.bak');
/*********************************************/

  /*fhandle := UTL_FILE.FOPEN('location1', 'file1.txt', 'W');

  utl_file.put_line(fhandle, 'some text');

  UTL_FILE.FCLOSE(fhandle);
  */

end;

10:13:01  line 1: ORA-29280: invalid directory path
10:13:01  ORA-06512: at "SYS.UTL_FILE", line 270
10:13:01  ORA-06512: at "SYS.UTL_FILE", line 1243
10:13:01  ORA-06512: at line 9

The file name must be in the same register as in the operating system. If the name in the procedure does not match the operating system, then we get an error.

declare
  fhandle      UTL_FILE.FILE_TYPE;
begin


/*********************************************/
-- if statement  ( file1.txt check ???? )  

  utl_file.fcopy(src_location  => 'DIR_VIKS',
                 src_filename  => '120910X-001.XML',
                 dest_location => 'DIR_VIKS',
                 dest_filename => '120910X-001.xml.bak2');
/*********************************************/

end;

10:36:13  line 1: ORA-29283: invalid file operation
10:36:13  ORA-06512: at "SYS.UTL_FILE", line 270
10:36:13  ORA-06512: at "SYS.UTL_FILE", line 1243
10:36:13  ORA-06512: at line 9

score 0 · Answer 2 · answered Jun 21 '19 at 08:40

I solved the problem

declare
  l_file      utl_file.file_type;
  l_file_name varchar2(20) := 'file1.txt';
  l_exists    boolean;
  l_length    number;
  l_blksize   number;
begin

  utl_file.fgetattr('location1',
                    l_file_name,
                    l_exists,  --OUT
                    l_length,
                    l_blksize);

  if (l_exists) then
    utl_file.fcopy(src_location  => 'location1',
                   src_filename  => 'file1.txt',
                   dest_location => 'location2',
                   dest_filename => 'file1_backup.txt');
  end if;

  fhandle := UTL_FILE.FOPEN('location1', 'file1.txt', 'W');

  utl_file.put_line(fhandle, 'some text');

  UTL_FILE.FCLOSE(fhandle);

end;

Oracle / Plsql do I need to check that the file is saved?

2 Answers2