1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Add Field Not in Flat File in External Table Load

Discussion in 'SQL PL/SQL' started by CTOROCK, Jan 7, 2011.

  1. CTOROCK

    CTOROCK Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi I'm trying import from a flat file using an external table load, however I'm adding a field that's not in the flat file, FILE_DATE and want us sysdate, but am getting an error, this is what I have:

    CONN MSUSER/MSUSER@M310;
    --DEFINE varFILEPATH = '\\csdowflir001\Legacy\CO\REGISTRIES'
    DEFINE varDATASET = 'WC_SUBMIT_CLAIMS'
    DEFINE varSCRIPT_PATH = '/share/code001_code/SQL/ETL/M310/LOADER'
    DEFINE varFLATFILE = 'SUBCLAIM_TEST'

    SPOOL \\csdowcode001\code$\SQL\ETL\EXT_TABLE\&varDATASET.\LOG\&varDATASET.LOG

    CREATE OR REPLACE DIRECTORY DIR_&varDATASET AS '/share/flir001_g/NCRS/WORKERS_COMP/CLAIMS/INBOUND';
    CREATE OR REPLACE DIRECTORY DIR_&varDATASET._LOG AS '&varSCRIPT_PATH./LOG/';
    CREATE OR REPLACE DIRECTORY DIR_&varDATASET._BAD AS '&varSCRIPT_PATH./BAD/';
    CREATE OR REPLACE DIRECTORY DIR_&varDATASET._DSC AS '&varSCRIPT_PATH./DSC/';

    DROP TABLE EXT_&varDATASET;
    CREATE TABLE EXT_&varDATASET
    (
    FILE_DT DATE,
    DOB_DT DATE,
    DOI_DT DATE,
    PRICE_NR NUMBER(10,4)
    )
    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY DIR_&VARDATASET
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY '\r\n'
    BADFILE DIR_&varDATASET._BAD : '&varDATASET..BAD'
    DISCARDFILE DIR_&varDATASET._DSC : '&varDATASET..DSC'
    LOGFILE DIR_&varDATASET._LOG : '&varDATASET..LOG'
    FIELDS TERMINATED BY '|'
    LRTRIM
    MISSING FIELD VALUES ARE NULL
    (
    FILE_DT DATE_UPDATE DATE,
    DOB_DT CHAR DATE_FORMAT DATE MASK "YYYYMMDD",
    DOI_DT CHAR DATE_FORMAT DATE MASK "YYYYMMDD",
    PRICE_NR
    )
    )
    LOCATION (DIR_&VARDATASET :'&VARFLATFILE..TXT')
    )
    REJECT LIMIT UNLIMITED;
    CLEAR COLUMNS;
    COMMIT;

    SET SERVEROUTPUT ON

    SPOOL OFF




    But I get: table column not found in external source:

    Thanks in advance for any help!
     
  2. CTOROCK

    CTOROCK Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I figured it out. I appended it later.