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!

Sql Loader - Comma separated dat file leaving out the first digit...

Discussion in 'SQL PL/SQL' started by Alex Amirtharaj, Aug 27, 2009.

  1. Alex Amirtharaj

    Alex Amirtharaj Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Chennai
    Hi,

    I'm using the following control file to upload data from a comma separated dat file (shown below the ctl file). The problem is that the first digit of PRD_ID is not getting loaded into the table. ie., instead of 365744932, only 65744932 is getting loaded. Kindly help. Thanks.

    OPTIONS(errors=5000)LOAD DATA
    INFILE a001i.dat
    APPEND
    INTO TABLE1
    TRAILING NULLCOLS
    (RNWL_LD_ID "LOAD_SEQ.NEXTVAL",
    RNW_LD_TYP_CD EXPRESSION "to_char('RMX')",
    PRD_ID CHAR TERMINATED BY ',',
    FRM_ID CHAR TERMINATED BY ',',
    LST_PRCSS_DT SYSDATE,
    LCK_NMBR CONSTANT 9900,
    TRMNTN_CD CHAR TERMINATED BY ',')

    Data File: a001i.dat

    365744932,431381067,DTRM
    365745224,591531281,DTRM
    365745758,264310844,DTRM
    365746048,365746048,DTRM

    Regards,
    Alex A.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You should really consider altering your table and writing a trigger to deal with the constants you're expecting SQL*Loader to handle:

    Code (SQL):
    SQL> CREATE TABLE table1(
      2  RNWL_LD_ID NUMBER,
      3  RNW_LD_TYP_CD varchar2(5) DEFAULT 'RMX',
      4  PRD_ID NUMBER,
      5  FRM_ID NUMBER,
      6  LST_PRCSS_DT DATE DEFAULT sysdate,
      7  LCK_NMBR NUMBER DEFAULT 9900,
      8  TRMNTN_CD varchar2(20)
      9  );

    TABLE created.

    SQL>
    SQL> CREATE SEQUENCE load_seq
      2  START WITH 1 INCREMENT BY 1 nomaxvalue nocycle nocache;

    SEQUENCE created.

    SQL>
    SQL> CREATE TRIGGER table1_ld_id_trg
      2  BEFORE INSERT ON table1
      3  FOR each ROW
      4  BEGIN
      5          SELECT load_seq.NEXTVAL
      6          INTO :NEW.rnwl_ld_id
      7          FROM dual;
      8
      9  END;
     10  /

    TRIGGER created.

    SQL>
    SQL>
    SQL> $"sqlldr bing/bong a001i.ctl"

    SQL*Loader: Release 10.2.0.3.0 - Production ON Thu Aug 27 07:54:58 2009

    Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.

    Commit point reached - logical record COUNT 4

    SQL>
    SQL> SELECT *
      2  FROM table1;

    RNWL_LD_ID RNW_L     PRD_ID     FRM_ID LST_PRCSS_DT           LCK_NMBR TRMNTN_CD
    ---------- ----- ---------- ---------- -------------------- ---------- --------------------
             1 RMX    365744932  431381067 27-AUG-2009 07:54:58       9900 DTRM
             2 RMX    365745224  591531281 27-AUG-2009 07:54:58       9900 DTRM
             3 RMX    365745758  264310844 27-AUG-2009 07:54:58       9900 DTRM
             4 RMX    365746048  365746048 27-AUG-2009 07:54:58       9900 DTRM

    SQL>
    Your control file would then look this way:

    OPTIONS(errors=5000)LOAD DATA
    INFILE a001i.dat
    APPEND
    INTO table TABLE1
    fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
    (PRD_ID ,
    FRM_ID ,
    TRMNTN_CD )

    and, as you can see from the example, it works as expected.
     
  3. Alex Amirtharaj

    Alex Amirtharaj Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Chennai
    Thank you zargon. I see that you've put in a lot of effort. But I have resolved it just by adding a comma at the start of each row in the data file as shown below. Now its working fine.

    ,365744932,431381067,DTRM
    ,365745224,591531281,DTRM
    ,365745758,264310844,DTRM
    ,365746048,365746048,DTRM

    Regards,
    Alex A.
     
  4. Alex Amirtharaj

    Alex Amirtharaj Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Chennai
    The following control file gives the required result. Here, I have just added the key word 'EXPRESSION' after RNWL_LD_ID.

    OPTIONS(errors=5000)LOAD DATA
    INFILE a001i.dat
    APPEND
    INTO TABLE1
    TRAILING NULLCOLS
    (RNWL_LD_ID EXPRESSION "LOAD_SEQ.NEXTVAL",
    RNW_LD_TYP_CD EXPRESSION "to_char('RMX')",
    PRD_ID CHAR TERMINATED BY ',',
    FRM_ID CHAR TERMINATED BY ',',
    LST_PRCSS_DT SYSDATE,
    LCK_NMBR CONSTANT 9900,
    TRMNTN_CD CHAR TERMINATED BY ',')

    Thanks,
    Alex A.