SQL loader issure with direct='Y' [message #334774] |
Fri, 18 July 2008 00:02  |
toshidas2000
Messages: 120 Registered: November 2005
|
Senior Member |
|
|
I have two issues with sqlldr when I use direct=y
a column with default value does not get populated. Following is my ctl file
OPTIONS( ERRORS=10, READSIZE=5000000, BINDSIZE=5000000, SKIP=0)
LOAD DATA
INFILE FILENAME'
BADFILE FILE.bad'
DISCARDFILE FILE.dsc'
APPEND
INTO TABLE stage
when (AA != ' ') and (BB!=' ') and (CC!=' ')
TRAILING NULLCOLS
(
AA CHAR terminated by "|" NULLIF AA=BLANKS,
BB terminated by "|" NULLIF BB='0',
CC CHAR terminated by "|" NULLIF CC='0',
DD CHAR terminated by WHITESPACE NULLIF DD=BLANKS,
SEQNUM CHAR "'FILENAME_'||to_char(SYSDATE,'YYYYMMDD')||'_'||AA_SEQ.nextval",
SENDTIME "LOCALTIMESTAMP",
CID CONSTANT "070001",
PID CONSTANT "0001",
CREATE SYSDATE,
FILE_NAME CONSTANT "FILENAME")
Following is the table
STAGE
SEQNUM VARCHAR2(65 BYTE),
SENDTIME TIMESTAMP(6),
CID VARCHAR2(15 BYTE),
PID VARCHAR2(10 BYTE),
CREAT DATE,
AA VARCHAR2(9 BYTE),
BB NUMBER(3) DEFAULT 0,
CC NUMBER(3) DEFAULT 0,
DD VARCHAR2(15 BYTE),
FILE_NAME VARCHAR2(100 BYTE),
PROCESS VARCHAR2(1 BYTE) DEFAULT 'N'
)
The columns SEQNUM, SENDTIME, PROCESS does not get populated, but when I use convention path, everything get populated.
Please help
|
|
|
|
Re: SQL loader issure with direct='Y' [message #334792 is a reply to message #334774] |
Fri, 18 July 2008 00:51   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Database Utilities
Chapter 11 Conventional and Direct Path Loads
Section Direct Path Load
Paragraph Field Defaults on the Direct Path
Quote: | Default column specifications defined in the database are not available when you use direct path loading. Fields for which default values are desired must be specified with the DEFAULTIF clause. If a DEFAULTIF clause is not specified and the field is NULL, then a null value is inserted into the database.
|
Regards
Michel
[Edit: fix link]
[Updated on: Fri, 18 July 2008 00:53] Report message to a moderator
|
|
|
|
|
|