3

I try to read the same Oracle external table multiple times with the different underlying file content.

I use the java app with the code fragment like:

int sptepn = 1;
for (File file: files) {
    log_step(stepn++);
    copy_file_to_oracle_directory(file); // copy myfileN.txt to myfile.txt
    call_oracle_sp_read_file();
}

In the pl/sql procedure I use code fragment like:

procedure read_file() is
    cursor ext_cu is
      select * from ext_table;
begin
    for ext_rec in ext_cu loop
      -- do something with ext_rec.*
    end loop;
end read_file;

On the one group of db-servers this pl/sql procedure works properly. But on the other group of db-servers the call of this pl/sql procedure on the step 2 (of the java procedure) raises the error:

29913-ORA-29913: error in executing ODCIEXTTABLEFETCH callout: ORA-29400: data cartridge error: KUP-05011: Size of file myfile.txt in directory /mydir has changed from 12345 to 67890.

Where the myfile1.txt has the size 12345 and the myfile2.txt has the size 67890.

What is the problem?

The affected server has the version:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE   12.2.0.1.0  Production"
TNS for IBM/AIX RISC System/6000: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
igortche
  • 115
  • 1
  • 8
  • 2
    What DDL did you use to create the external table? Oracle tracks metadata about the files in an external table and you can't usually swap them out without altering the table to read the new files. – pmdba May 01 '20 at 03:03
  • The DDL is: `CREATE TABLE "EXT_TABLE" ( "FLDA" VARCHAR2(100 BYTE), "FLDB" VARCHAR2(100 BYTE), "FLDC" VARCHAR2(100 BYTE) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "LOGDIR" ACCESS PARAMETERS ( RECORDS DELIMITED BY newline SKIP 1 FIELDS TERMINATED BY "\t" MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ) LOCATION ( "MYDIR":'myfile.txt' ) ) REJECT LIMIT UNLIMITED ;` Swapping file for external tables works normally on most of our servers. – igortche May 04 '20 at 09:35

1 Answers1

0

How about telling Oracle that you've changed the file? Instead of copy myfileN.txt to myfile.txt I'd try to keep the number in the filenames and alter the external table:

ALTER TABLE ext_table LOCATION ("MYDIR":'myfile1.txt');
read_file();

ALTER TABLE ext_table LOCATION ("MYDIR":'myfile2.txt');
read_file();
...
wolφi
  • 8,091
  • 2
  • 35
  • 64