Home » RDBMS Server » Server Utilities » sql loader question (merged)
sql loader question (merged) [message #329270] Tue, 24 June 2008 21:17 Go to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
CREATE TABLE test1
(
SEQNUM VARCHAR2(65 BYTE),
SENDTIME TIMESTAMP(6),
CID VARCHAR2(15 BYTE),
PID VARCHAR2(10 BYTE),
ADDED DATE,
SID VARCHAR2(9 BYTE),
ID1 NUMBER(3),
ID2 NUMBER(3),
FILE_NAME VARCHAR2(100 BYTE),
PROCESSED VARCHAR2(1 BYTE)
)

Following is the control file of sqlldr

OPTIONS( ERRORS=10, READSIZE=5000000, BINDSIZE=5000000, SKIP=0)
LOAD DATA
INFILE 'test.dat' "fix 310"
BADFILE test.bad
DISCARDFILE test.dsc
APPEND
INTO TABLE test1
fields terminated by ","
TRAILING NULLCOLS
(
SEQNUM CHAR "'test.dat_'||to_char(SYSDATE,'YYYYMMDD')||'_'||SEQNUM_SEQ.nextval",
SENDTIME "LOCALTIMESTAMP",
CID CONSTANT "067000",
PID CONSTANT "0040",
ADDED SYSDATE,
FILE_NAME CONSTANT "test.dat",
SID CHAR NULLIF SID=BLANKS,
ID1 CHAR NULLIF ID1=BLANKS,
ID2 CHAR NULLIF ID2=BLANKS)

Following is the datafile
123456789,345,432
234567890,245,123
345678901,301,456

According to it SID, ID1 and ID2 should get popultaed. But only SID gets populated with 432 123 and 456 last fields. Following is in the log file.

TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SEQNUM FIRST * , CHARACTER
SQL string for column : "'test.dat_'||to_char(SYSDATE,'YYYYMMDD')||'_'||TRANSEQNUM_SEQ.nextval"
SENDTIME NEXT * , CHARACTER
SQL string for column : "LOCALTIMESTAMP"
CID CONSTANT
Value is '067000'
PID CONSTANT
Value is '0040'
ADDED SYSDATE
SID NEXT * , CHARACTER
NULL if SID = BLANKS
ID1 NEXT * , CHARACTER
NULL if ID1 = BLANKS
ID2 NEXT * , CHARACTER
NULL if ID2 = BLANKS
FILE_NAME CONSTANT
Value is 'test.dat'
Re: sql loader question [message #329271 is a reply to message #329270] Tue, 24 June 2008 21:21 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

[Updated on: Tue, 24 June 2008 21:24] by Moderator

Report message to a moderator

sql loader question [message #329272 is a reply to message #329270] Tue, 24 June 2008 21:35 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
How do we use when and fields terminated by "|" together. I got bunch of errors.
OPTIONS( ERRORS=10, READSIZE=5000000, BINDSIZE=5000000, SKIP=0)
LOAD DATA
INFILE 'test.dat' "fix 310"
BADFILE test.bad
DISCARDFILE test.dsc
APPEND
INTO TABLE test1
fields terminated by ","
TRAILING NULLCOLS
WHEN (SID != ' ')
(
SEQNUM CHAR "'test.dat_'||to_char(SYSDATE,'YYYYMMDD')||'_'||SEQNUM_SEQ.nextval",
SENDTIME "LOCALTIMESTAMP",
CID CONSTANT "067000",
PID CONSTANT "0040",
ADDED SYSDATE,
FILE_NAME CONSTANT "test.dat",
SID CHAR NULLIF SID=BLANKS,
ID1 CHAR NULLIF ID1=BLANKS,
ID2 CHAR NULLIF ID2=BLANKS)

I get this error
SQL*Loader-350: Syntax error at line 10.
Expecting "(", found keyword when.
when (SID != ' ')
^
Re: sql loader question [message #329277 is a reply to message #329272] Tue, 24 June 2008 22:05 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

toshidas2000,

As a "Senior Member" your total disregard for not following Posting Guidelines is worthy to be added to everyone's IGNORE List.

You're On Your Own YOYO)!

[Updated on: Tue, 24 June 2008 22:08] by Moderator

Report message to a moderator

Re: sql loader question [message #329569 is a reply to message #329272] Wed, 25 June 2008 21:38 Go to previous message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
Sid is an Oracle reserved word. If you change the column name to something else, like sid_col or enclose it within double quotes, "SID", that solves one problem.

The "when" clause belongs between your "into table" clause and your "fields terminated by" clause.

Your "fix" clause does not match your data.

Previous Topic: SQL LOADER... URGENT HELP !!!
Next Topic: creating export of 9i db from 10g db
Goto Forum:
  


Current Time: Tue Dec 06 00:08:45 CST 2016

Total time taken to generate the page: 0.24262 seconds