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!