CODEWITHSUNDEEP
sqloracle-databaseoracle10gsql-loader
gaurav
gaurav

Reputation: 21

SQL Loader : Not enough values

Need help debugging. My table structure is below :

RESPOND_BY_DATE                TIMESTAMP(6)   ,
DESCRIPTION                    VARCHAR2(300)  ,
AXBCRULE               NUMBER(4)      NOT NULL,
SEGMENT                        VARCHAR2(100)  ,
ASSIGN_UNIQUE_BALCON           VARCHAR2(1)    ,
BALCON_OFFER_TYPE              VARCHAR2(3)    ,
APR                            VARCHAR2(10)   ,
FEE_YN                         VARCHAR2(1)    ,
FEE_PERCENT                    VARCHAR2(10)   ,
FEE_AMOUNT_MAX                 VARCHAR2(10)   ,
BALCON_DURATION_TYPE           VARCHAR2(7)    ,
BALCON_DURATION                VARCHAR2(3)    ,
BALCON_CAP_AMOUNT              VARCHAR2(10)   ,
INTERIM_APR                    VARCHAR2(10)   ,
INTERIM_DURATION_TYPE          VARCHAR2(7)    ,
INTERIM_DURATION               NUMBER(3)      ,
BALCON_ID                      NUMBER(7)      ,
CHANNEL_ID              VARCHAR2(7)   NOT NULL ,
SUBCHANNEL_ID           VARCHAR2(7)   NOT NULL ,
PROGRAM_NAME            VARCHAR2(45)   NOT NULL,
DATE_INITIATED          TIMESTAMP(6)   NOT NULL,
ERROR_MESSAGE                  VARCHAR2(1024) ,
STATUS                         NUMBER(2)      ,
USER_CODE                      VARCHAR2(10)   ,
WARNING_MESSAGE                VARCHAR2(1024) ,
BALCON_OFFER_SEQUENCE   NUMBER(2)      NOT NULL,
CAMPAIGN_TAG                   VARCHAR2(3)

It has total of 27fields and I used the below ctl file to load it:

OPTIONS (LOAD=1)
load data 
        infile *
    replace into table test_table  
    (RESPOND_BY_DATE        SYSDATE,
DESCRIPTION            CONSTANT   'test',          
AXBCRULE               CONSTANT   '011',        
SEGMENT                CONSTANT   '12',         
ASSIGN_UNIQUE_BALCON   CONSTANT   '1',          
BALCON_OFFER_TYPE      CONSTANT   '1',          
APR                    CONSTANT   '12',         
FEE_YN                 CONSTANT   'Y',          
FEE_PERCENT            CONSTANT   '009',        
FEE_AMOUNT_MAX         CONSTANT   '12',          
BALCON_DURATION_TYPE   CONSTANT   '1',          
BALCON_DURATION        CONSTANT   '12',         
BALCON_CAP_AMOUNT      CONSTANT   '0123',       
INTERIM_APR            CONSTANT   '1',         
INTERIM_DURATION_TYPE  CONSTANT   'N',          
INTERIM_DURATION       CONSTANT   '1',          
BALCON_ID              CONSTANT   '1',          
CHANNEL_ID       CONSTANT '1',          
SUBCHANNEL_ID          CONSTANT   '1',          
PROGRAM_NAME           CONSTANT   '1',          
DATE_INITIATED         SYSDATE,          
ERROR_MESSAGE          CONSTANT   '1',          
STATUS                 CONSTANT   '1' ,         
USER_CODE              CONSTANT   '1',          
WARNING_MESSAGE        CONSTANT   '1',          
BALCON_OFFER_SEQUENCE  CONSTANT   '1',
CAMPAIGN_TAG CONSTANT   '1'
)

When I try to load this test data into the table, I am facing the below error :

Record 1: Rejected - Error on table "schema"."TEST_TABLE".
 ORA-00947: not enough values

Kindly help me out. Thanks in advance.

Upvotes: 0

Views: 2310

Answers (1)

Gary_W
Gary_W

Reputation: 10360

Change your control file entries for the timestamp columns to look like:

RESPOND_BY_DATE EXPRESSION "CAST(SYSDATE AS TIMESTAMP)"

A DATE and a TIMESTAMP are not the same datatypes. You just need to convert SYSDATE to a TIMESTAMP.

ORA-00947 is being raised when the process tries to put a SYSDATE into the TIMESTAMP. SYSDATE does not have the same fractional seconds component that the TIMESTAMP requires.

Upvotes: 2

Related Questions