Home » RDBMS Server » Server Utilities » Unable to upload "SYSDATE" using SQL*Loader
| Unable to upload "SYSDATE" using SQL*Loader [message #170748] |
Fri, 05 May 2006 01:17  |
blazingrock4u
Messages: 30
Registered: March 2006
Location: India
|
Member |
|
|
OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'data.txt'
TRUNCATE INTO TABLE "TABLE1"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( Col1, Col2, Col3, LOAD_DATE "SYSDATE")
For sysdate if i remove double quotations it is working fine in all the environments...but if i give quotes, it is working only for some environments
|
|
|
|
|
|
| Re: Unable to upload "SYSDATE" using SQL*Loader [message #170751 is a reply to message #170748] |
Fri, 05 May 2006 01:23  |
blazingrock4u
Messages: 30
Registered: March 2006
Location: India
|
Member |
|
|
anthr update is that the data.txt has got col1,col2,col3..col9 and i am trying to insert the SYSDATE for LOAD_DATE field of table TABLE1
anthr update is that the data.txt has got col1,col2,col3,col4,col5,col6,col7,col8,col9 and i am trying to insert the SYSDATE for LOAD_DATE field of table TABLE1
***MY TABLE****
desc TABLE1
Name Null? Type
----------------- -------- ----------------------------
COL1 VARCHAR2(10)
COL2 VARCHAR2(10)
COL3 VARCHAR2(10)
COL4 VARCHAR2(10)
COL5 VARCHAR2(10)
COL6 VARCHAR2(10)
COL7 VARCHAR2(10)
COL8 VARCHAR2(10)
COL9 VARCHAR2(10)
LOAD_DATE DATE
****MY CTL FILE****
OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'data.txt'
TRUNCATE INTO TABLE "TABLE1"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, LOAD_DATE "SYSDATE")
***MY SQLLDR CMND****
sqlldr userid=brock/brock control=brockc.ctl direct=true errors=1 log=brockl.log bad=brockb.bad discard=brockd.discard
****OUTPUT OF BROCKL.LOG****
SQL*Loader: Release 9.2.0.4.0 - Production on Fri May 5 12:23:23 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Control File: brockc.ctl
Data File: data.txt
Bad File: brockb.bad
Discard File: brockd.discard
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 1
Continuation: none specified
Path used: Direct
Table "TABLE1", loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
---------------------------- ----- ---- ---- ---------------------
COL1 FIRST * , O(") CHARACTER
COL2 NEXT * , O(") CHARACTER
COL3 NEXT * , O(") CHARACTER
COL4 NEXT * , O(") CHARACTER
COL5 NEXT * , O(") CHARACTER
COL6 NEXT * , O(") CHARACTER
COL7 NEXT * , O(") CHARACTER
COL8 NEXT * , O(") CHARACTER
COL9 NEXT * , O(") CHARACTER
LOAD_DATE NEXT * , O(") CHARACTER
SQL string for column : "SYSDATE"
Table "TABLE1":
32 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 32
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Fri May 05 12:23:23 2006
Run ended on Fri May 05 12:23:25 2006
Elapsed time was: 00:00:01.58
CPU time was: 00:00:00.37
****************
In TABLE1 everything is uploaded except for the default "SYSDATE".
Now if i run the same cmnd with only one change i.e., in brockc.ctl:-
....LOAD_DATE SYSDATE)
Note: I have removed the quotations from SYSDATE.
This time around it runs fine and uploads the sysdate in LOAD_DATE column.
Also i found that if i REMOVE "DIRECT=TRUE" from my sqlldr cmnd then "SYSDATE" (sysdate WITH quotes) works fine. But when i put "DIRECT=TRUE", "SYSDATE" (sysdate WITH quotes)doesnot work. I ran catldr.sql also (juz in case) but in vain.
Can someone explain what is going on here? 
[Updated on: Fri, 05 May 2006 07:19] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sat Jun 29 09:45:39 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
