Hi,
I have a Flat file and I created one External Table that shows flat file data.
I defined Bad File and Log files too while created the above external table and files are getting created while executing the Proc to load external table.
Please note I am using same FILE names for Ex. "MY_FILE.BAD" and "MY_FILE.LOG" (Not generating separate files for each new run) , so data is getting appended in this scenario.
I created two external table one each for BAD file and LOG file However
What I am looking for:
-
To overwrite Log File after every fresh Run.
-
Is it possible to capture metadata information as information column i.e . DATE_LOADED, LEADED_BY etc...
-
The External table created using BAD and LOG files are not properly formatted, is it possible to show the record appropriately formatted. below is code snippet of my external table created for Log File content in tabular format:
(Please correct it for Proper formatting or to capture meta data information, as working on External table and its formatting first time )
CREATE TABLE "ABC"."ALL_APPS_LOG_FILE_EXT"
(
"TEXT1" VARCHAR2(4000 BYTE),
"FILENAME" VARCHAR2(4000 BYTE),
"DATE_LOADED" VARCHAR2(4000 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "MY_DIRECOTRY"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS
MISSING FIELD VALUES ARE NULL
( text1 position(1:4000),
FILENAME position(4001:8000),
DATE_LOADED position(8001:12000) ) )
LOCATION ( "MY_DIRECOTRY":'MY_FILE.LOG' ) );
See how the Log File Table Outcome Looks like :
