Home » RDBMS Server » Server Utilities » sql loader question (merged)
| sql loader question (merged) [message #329270] |
Tue, 24 June 2008 21:17  |
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'
|
|
|
|
|
|
| sql loader question [message #329272 is a reply to message #329270] |
Tue, 24 June 2008 21:35  |
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 #329569 is a reply to message #329272] |
Wed, 25 June 2008 21:38 |
 |
Barbara Boehmer
Messages: 9101
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.
|
|
|
|
Goto Forum:
Current Time: Tue Dec 24 10:12:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
