Solved

a question of sqlldr

Posted on 2003-10-28
4
1,928 Views
Last Modified: 2010-08-05
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
Comment
Question by:tjli
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 20 total points
ID: 9632998
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
 
LVL 4

Expert Comment

by:Vinay_dba
ID: 9635087
SQL loader is expecting data as a|a|a. If last column/columns is null you have to use trailing nullcols.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question