Closing a file in Oracle External table

Question

I use external table to read a file from a folder in the system.

After reading the file,when i try to move the read file to archive,i get FILE IN USE error.

Is there a mechanism to close the file or something in external tables.

Answer 1

I cannot reproduce your experience. Please provide a more detailed test case, including pertinent details such as operating system and complete Oracle error message, including number.

Given read write access to an OS directory ...

SQL> select * from all_directories
  2  where directory_name like 'DATA%'
  3  /

OWNER  DIRECTORY_NAME  DIRECTORY_PATH
------ --------------- ------------------------
SYS    DATA_DROP       /home/oracle/drop_zone


SQL> 

... I create this table ...

create table t42_ext  (
    field1   number,
    field2   varchar2(20)
)
organization external
(
        type oracle_loader
        default directory data_drop
        access parameters 
        (
                records delimited by newline
                fields terminated by ','
                missing field values are null
        )
        location ('data20140923.txt')
)
reject limit unlimited;

... which I can query:

SQL> select * from t42_ext;

    FIELD1 FIELD2
---------- --------------------
        23  'some data'

SQL>

Then, in a separate OS session I move the file:

[oracle@localhost ~]$ cd drop_zone
[oracle@localhost drop_zone]$ ls
data20140923.txt  T42_EXT_3295.log
[oracle@localhost drop_zone]$ mv data20140923.txt ../data_archive
[oracle@localhost drop_zone]$ ls
T42_EXT_3295.log
[oracle@localhost drop_zone]$ 

Back in the database I can no longer query the table:

SQL> r
  1* select * from t42_ext
select * from t42_ext
              *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file data20140923.txt in DATA_DROP not found

SQL>

---

Alternate scenario, file manipulation from within the database, still not reproduced.

Reset:

SQL> select * from t42_ext;

    FIELD1 FIELD2
---------- --------------------
        23  'some data'

SQL> begin
    utl_file.frename(
           src_location     => 'DATA_DROP',
           src_filename     => 'data20140923.txt', 
           dest_location    => 'DATA_ARCH',
           dest_filename    => 'data20140923.txt',
           overwrite        => FALSE);
end;
/
  2    3    4    5    6    7    8    9  
PL/SQL procedure successfully completed.

SQL> select * from t42_ext;
select * from t42_ext
              *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file data20140923.txt in DATA_DROP not found


SQL>