Tags

, ,

Another cool feature of 12.2 is the possibility to override defined parametrs of an external table when accessing it. The following properties may be overwritten:

  • default directory
  • location (file-name)
  • access parameters like badfile, logfile and discardfile
  • reject limit

This enables us to change e.g. the name of the file to be read without having to do an ALTER TABLE statement (and therefore doing an impicit commit). Furthermore the modification done this way is effective for the current statement only.

So imagine we have an external table defined like this:

CREATE TABLE employee_import (    
   department_id  NUMBER(2,0)  
  ,first_name     VARCHAR2(20 BYTE)  
  ,last_name      VARCHAR2(20 BYTE)  
  ,email          VARCHAR2(25 BYTE)  
  ,phone_number   VARCHAR2(20 BYTE)  
  ,hire_date      DATE
  ,job_id         VARCHAR2(10 BYTE)
  ,salary         NUMBER(8,2)  
  ,commission_pct NUMBER(2,2)
) 
ORGANIZATION EXTERNAL (    
   TYPE ORACLE_LOADER   
   DEFAULT DIRECTORY LTROT_VM_DIR   
   ACCESS PARAMETERS (       
      RECORDS DELIMITED BY NEWLINE      
      LOGFILE  LTROT_VM_DIR:'emp20_wrong.log'      
      BADFILE  LTROT_VM_DIR:'emp20_wrong.bad'      
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
      MISSING FIELD VALUES ARE NULL (         
         department_id
        ,first_name
        ,last_name
        ,email
        ,phone_number
        ,hire_date date 'DD.MM.YYYY'
        ,job_id
        ,salary
        ,commission_pct
      )
   )
   LOCATION('emp20.txt')
)
REJECT LIMIT UNLIMITED ;

Now, if we would like to access a different os file (having the same structure) than “emp20.txt” we can easily achieve this with the following query:

SELECT *
 FROM employee_import 
 EXTERNAL MODIFY (LOCATION ('em10.txt'));

The parameters that can be changed this way are:

  • Default Directory
  • Location
  • Access Parameters
  • Reject Limit

If the external is partitioned only table level (not partition level) clauses may be overwritten.

Use-Case

Now, looking at the list of parameters that can be changed it should be possible to change the name of the bad-/log-file when loading the emp-os file and afterwards having an external table where i can read the bad-/log-file passing the names that i used before….

Something like this:

Generic External Table to read log/bad files

CREATE TABLE generic_file (
    message VARCHAR2(4000 BYTE)
) 
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
   DEFAULT DIRECTORY LTROT_VM_DIR
   ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      NOLOGFILE
      NOBADFILE
      NODISCARDFILE
      FIELDS (
         message CHAR(4000)
      )
   )
   LOCATION('emp20.bad')
)
REJECT LIMIT UNLIMITED;

So when I read the emp os-file I alter the name of the bad and the log file using the external modify clause:

SELECT * 
  FROM employee_import
EXTERNAL MODIFY (
   ACCESS PARAMETERS (
      BADFILE 'abadfile.bad'
      LOGFILE 'alogfile.log'));

And after that I should be able to access the bad-/log-file using my generic external table.

SELECT * 
  FROM generic_file 
EXTERNAL MODIFY (
   LOCATION('abadfile.bad'));

Unfortunately I cannot see any data, as the log-/bad-files are locked and empty (Linux Environment) so I need to commit first to release the locks. Afterwards I can read the log-file as well as the bad-file.

SELECT * 
 FROM generic_file 
EXTERNAL MODIFY (
 LOCATION('alogfile.log'))
 WHERE message IS NOT NULL;

MESSAGE
---------------------------------------------------------------------------
 LOG file opened at 07/26/17 07:25:18
Field Definitions for table EMPLOYEE_IMPORT
 Record format DELIMITED BY NEWLINE
 Data in file has same endianness as the platform
 Rows with all null fields are accepted
 Fields in Data Source:
 DEPARTMENT_ID CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 FIRST_NAME CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 LAST_NAME CHAR (255)
 Terminated by ","
...

Conclusion

Nice!