Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1944
  • Last Modified:

a question of sqlldr

there is a table  tmp(a varchar2(3),b varchar2(3),rmk varchar2(6))

the content of tmp.txt  is:  
a|a
b|b

the content of tmp.ctl  is:
LOAD DATA
        INFILE 'tmp.txt'
        INTO TABLE tmp append
        FIELDS TERMINATED BY '|'
        (sn,
         agent,
         rmk "to_char(sysdate,'yyyymm')")

Run "sqlldr ltj/intelL control=tmp log=log.txt " ,then show :

“SQL string for column : "to_char(sysdate,'yyyymm')"

Record 1: Rejected - Error on table TMP, column RMK.
Column not found before end of logical record (use TRAILING NULLCOLS)”

why??
0
tjli
Asked:
tjli
1 Solution
 
catchmeifuwantCommented:
Use trailing nullcols in your control file...

LOAD DATA
INFILE 'tmp.txt'
INTO TABLE tmp append
FIELDS TERMINATED BY '|'
trailing nullcols
(a,b,rmk "to_char(sysdate,'yyyymm')")
0
 
Vinay_dbaCommented:
SQL loader is expecting data as a|a|a. If last column/columns is null you have to use trailing nullcols.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now