Home » RDBMS Server » Server Utilities » SQL loader issure with direct='Y'
SQL loader issure with direct='Y' [message #334774] Fri, 18 July 2008 00:02 Go to next message
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 #334776 is a reply to message #334774] Fri, 18 July 2008 00:12 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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
Re: SQL loader issure with direct='Y' [message #334792 is a reply to message #334774] Fri, 18 July 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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

Re: SQL loader issure with direct='Y' [message #334879 is a reply to message #334792] Fri, 18 July 2008 09:03 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
even sequsene is not use to generate the seqnum
Re: SQL loader issure with direct='Y' [message #334895 is a reply to message #334879] Fri, 18 July 2008 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this a question or an information?

Regards
Michel
Re: SQL loader issure with direct='Y' [message #334897 is a reply to message #334879] Fri, 18 July 2008 10:09 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>even sequsene is not use to generate the seqnum
For direct loads use SEQUENCE parameter.
But it comes with its own cost Smile
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref1281

[Updated on: Fri, 18 July 2008 10:10]

Report message to a moderator

Previous Topic: Exporting two tables
Next Topic: export failed due to ORACLE error 20000 encountered
Goto Forum:
  


Current Time: Sat Dec 03 09:55:45 CST 2016

Total time taken to generate the page: 0.07780 seconds