OraFAQ Forum: Server Utilities » SQL loader issure with direct='Y'

Home » RDBMS Server » Server Utilities » SQL loader issure with direct='Y'

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: SQL loader issure with direct='Y' [message #334776 is a reply to message #334774]

Fri, 18 July 2008 00:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have two issues with sqlldr when I use direct=y
> column with default value does not get populated.
TWO?
How can anyone reproduce your purported problem with what you have & have not posted.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated above

Report message to a moderator

Re: SQL loader issure with direct='Y' [message #334792 is a reply to message #334774]

Fri, 18 July 2008 00:51

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

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