No question at this time
DBA Top 10
1 B. Vroman 16200
2 M. Cadot 10100
3 T. Boles 8150
4 J. Schnackenberg 7700
5 A. Kavsek 7000
6 M. Hidayathullah ... 3000
7 G. Lambregts 1100
7 P. Wisse 1100
9 T. P 1000
10 B. Derous 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48261
Total active users1615
Act. users last 24h3
Act. users last hour0
Registered user hits last week315
Registered user hits last month1260
Go up

Error loading timestamp data using sqlldr
Next thread: Not able to backup the database using RMAN
Prev thread: ORA-01281 when starting logical apply

Message Score Author Date
While direct loading data through sqlldr, I am get...... gr am77 Nov 23, 2009, 16:49
I have created a function that converts varchar2 i...... gr am77 Nov 23, 2009, 16:50
************** create table: ************** CRE...... gr am77 Nov 23, 2009, 17:02
Hi, It's not necessary to create a function, yo...... Chantal Walschaerts Nov 23, 2009, 17:03
Hi Grammy, Why don't you use the to_timestamp f...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse Nov 23, 2009, 17:13
set NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts gr am77 Nov 24, 2009, 07:17
Hi Grammy, The syntax I suggested seems not val...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse Nov 24, 2009, 11:25

Follow up by mail Click here


Subject: Error loading timestamp data using sqlldr
Author: gr am77, United Kingdom
Date: Nov 23, 2009, 16:49, 3223 days ago
Oracle info: 10g
Message: While direct loading data through sqlldr, I am getting an error loading timestamp data into the table.
LOAD_TS , <--TIMESTAMP(6)

Seems direct loading does not load timestamp data


SQLLDR USERID=abc/def@ghi,CONTROL=C:\load.ctl,DATA=C:load.dat,LOG=C:\load.log,BAD=C:load.bad,DISCARD=C:\load.dsc,ERRORS=100000,DIRECT=TRUE


LOAD DATA
INFILE 'C:\load.dat'
REPLACE INTO TABLE EQUITY_TIMESERIES
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(ID_BB_UNIQUE,
EQY_PRIM_EXCH_SHRT,
PX_VOLUME,
PX_INST_PRC,
REGION_ID,
PX_SRC_CD,
PX_BID,
PX_MID,
PX_ASK,
PX_OPEN,
PX_HIGH,
PX_LOW,
PX_LAST,
EQY_BETA,
CUR_MKT_CAP,
LAST_UPDATE_DT "validate_date(:LAST_UPDATE_DT)",
EQY_SH_OUT_REAL,
CRNCY,
AS_OF_DT "validate_date(:AS_OF_DT)",
LOAD_TS , <--TIMESTAMP(6)
LAST_UPDATE_SRC_ID,
CP_ADJ)

Record 13: Rejected - Error on table EQUITY_TIMESERIES, column LOAD_TS.
ORA-26041: DATETIME/INTERVAL datatype conversion error
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error loading timestamp data using sqlldr
Author: gr am77, United Kingdom
Date: Nov 23, 2009, 16:50, 3223 days ago
Message: I have created a function that converts varchar2 into timestamp values,
create or replace
FUNCTION validate_date_timestamp(test_date IN VARCHAR2)
RETURN DATE IS
v_return_date TIMESTAMP;
BEGIN
BEGIN
IF test_date IS NOT NULL
THEN
v_return_date := TO_TIMESTAMP(test_date, 'YYYY-MM-DD HH24:MI:SS.FF');
ELSE
v_return_date := NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN v_return_date := NULL;
END;
RETURN v_return_date;
END validate_date_timestamp;

I call this function like this:
LOAD DATA
INFILE 'C:\load.dat'
REPLACE INTO TABLE AMAN_BLG_EQUITY_PX_TIMESERIES
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(ID_BB_UNIQUE,
EQY_PRIM_EXCH_SHRT,
PX_VOLUME,
PX_INST_PRC,
REGION_ID,
PX_SRC_CD,
PX_BID,
PX_MID,
PX_ASK,
PX_OPEN,
PX_HIGH,
PX_LOW,
PX_LAST,
EQY_BETA,
CUR_MKT_CAP,
LAST_UPDATE_DT "validate_date(:LAST_UPDATE_DT)",
EQY_SH_OUT_REAL,
CRNCY,
AS_OF_DT "validate_date(:AS_OF_DT)",
LOAD_TS "validate_date_timestamp(:LOAD_TS)",
LAST_UPDATE_SRC_ID,
CP_ADJ)

Now the error I get is:
SQL*Loader-951: Error calling once/load initialization
ORA-26052: Unsupported type 180 for SQL expression on column LOAD_TS.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error loading timestamp data using sqlldr
Author: gr am77, United Kingdom
Date: Nov 23, 2009, 17:02, 3223 days ago
Message: **************
create table:
**************
CREATE TABLE EQUITY_TIMESERIES
(
ID_BB_UNIQUE VARCHAR2(30 BYTE),
EQY_PRIM_EXCH_SHRT VARCHAR2(20 BYTE),
PX_VOLUME NUMBER,
PX_INST_PRC NUMBER,
REGION_ID VARCHAR2(3 BYTE),
PX_SRC_CD VARCHAR2(12 BYTE),
PX_BID NUMBER,
PX_MID NUMBER,
PX_ASK NUMBER,
PX_OPEN NUMBER,
PX_HIGH NUMBER,
PX_LOW NUMBER,
PX_LAST NUMBER,
EQY_BETA NUMBER,
CUR_MKT_CAP NUMBER,
LAST_UPDATE_DT DATE,
EQY_SH_OUT_REAL NUMBER,
CRNCY VARCHAR2(8 BYTE),
AS_OF_DT DATE,
LOAD_TS TIMESTAMP(6),
LAST_UPDATE_SRC_ID VARCHAR2(30 BYTE),
CP_ADJ NUMBER
)


**************
ctrl file:
**************
LOAD DATA
INFILE 'C:\load.dat'
REPLACE INTO TABLE EQUITY_TIMESERIES
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(ID_BB_UNIQUE,
EQY_PRIM_EXCH_SHRT,
PX_VOLUME,
PX_INST_PRC,
REGION_ID,
PX_SRC_CD,
PX_BID,
PX_MID,
PX_ASK,
PX_OPEN,
PX_HIGH,
PX_LOW,
PX_LAST,
EQY_BETA,
CUR_MKT_CAP,
LAST_UPDATE_DT "validate_date(:LAST_UPDATE_DT)", <--similar to function described above
EQY_SH_OUT_REAL,
CRNCY,
AS_OF_DT "validate_date(:AS_OF_DT)",
LOAD_TS "validate_date_timestamp(:LOAD_TS)",<--function described above
LAST_UPDATE_SRC_ID,
CP_ADJ)

**************
SqlLdr:
**************
SQLLDR USERID=abc/def@ghi,CONTROL=C:\load.ctl,DATA=C:load.dat,LOG=C:\load.log,BAD=C:load.bad,DISCARD=C:\load.dsc,ERRORS=100,DIRECT=TRUE

**************
Data:
**************
ID_BB_UNIQUE|EQY_PRIM_EXCH_SHRT|PX_VOLUME|PX_INST_PRC|REGION_ID|PX_SRC_CD|PX_BID|PX_MID|PX_ASK|PX_OPEN|PX_HIGH|PX_LOW|PX_LAST|EQY_BETA|CUR_MKT_CAP|LAST_UPDATE_DT|EQY_SH_OUT_REAL|CRNCY|AS_OF_DT|LOAD_TS|LAST_UPDATE_SRC_ID|CP_ADJ
EQ0014886700001000|XJAS|29300|656|AS1|JP|656|662.5|669|651|690|650|656|1.039|5103.68|10/7/2008|7780000|JPY|10/7/2008|10/8/2008 4:42:03.392235 PM||
EQ0011378500001000|XTKS|40000|398|AS1|JP|398|403|408|391|400|385|398|1.02|10218.92|10/7/2008|25675675|JPY|10/7/2008|10/8/2008 4:42:03.392603 PM||
EQ0011344400001000|XTKS|24700|745|AS1|JP|745|748|751|721|784|711|745|0.939|14006|10/7/2008|18800000|JPY|10/7/2008|10/8/2008 4:42:03.392821 PM||
EQ0014871000001000|XJAS|5000|410|AS1|JP||409|409|380|410|380|410|0.418|4724.02|10/7/2008|11522000|JPY|10/7/2008|10/8/2008 4:42:03.392988 PM||
EQ0014897300001000|XJAS|12000|221|AS1|JP|221|233.5|246|254|254|221|221|0.7|1337.05|10/7/2008|6050000|JPY|10/7/2008|10/8/2008 4:42:03.393191 PM||
EQ0014879300001000|XTKS|17800|463|AS1|JP|465|466.5|468|475|490|460|463|0.729|8824.13|10/7/2008|19058587|JPY|10/7/2008|10/8/2008 4:42:03.393417 PM||
EQ0012650300001000|XNGO|5000|280|AS1|JP||280|280||||280|0.561|6029.49|10/3/2008|21533890|JPY|10/7/2008|10/8/2008 4:42:03.393953 PM||
EQ0011411800001000|XTKS|424000|45|AS1|JP|45|45.5|46|43|47|43|45|1.109|3314.55|10/7/2008|73656731|JPY|10/7/2008|10/8/2008 4:42:03.394280 PM||
EQ0011407400001000|XTKS|1685000|11|AS1|JP|11|11.5|12|9|12|9|11|1.056|4960.93|10/7/2008|450993208|JPY|10/7/2008|10/8/2008 4:42:03.394688 PM||
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error loading timestamp data using sqlldr
Author: Chantal Walschaerts, Belgium
Date: Nov 23, 2009, 17:03, 3223 days ago
Message: Hi,

It's not necessary to create a function, you can specify
set NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
to tell to SQL LOADER the format you want to use. You don't need to call the function.

Best Regards,
Chantal
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error loading timestamp data using sqlldr
Author: Philip Wisse, Netherlands
Date: Nov 23, 2009, 17:13, 3223 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Grammy,

Why don't you use the to_timestamp function, like:
LOAD_TS "to_timestamp(:LOAD_TS,'DD/MM/YYYY HH:MI:SS.FF AM')"

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error loading timestamp data using sqlldr
Author: gr am77, United Kingdom
Date: Nov 24, 2009, 07:17, 3222 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: set NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF

Q How can i set NLS_TIMESTAMP_FORMAT in sqlldr control file?

As a workaround, i am loading into the table EQUITY_TIMESERIES without LOAD_TS column.

Does oracle have a bug converting timestamp data?
I read somewhere that oracle internally stores date in numbers, and so requires to convert the date stored in
numbers into to_char to to_timestamp each time we use the column in a query?

I guess here Oracle has problems making this convertions using a functin to_timestamp() in sqlldr.

Is this a oracle bug?

PS:
As sqlldr does not work in sqlplus setting NLS_TIMESTAMP_FORMAT in sqlplus does not make sense.

and i do not have permission to set NLS_TIMESTAMP_FORMAT at system level.

So i can do alter session but not alter system.


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error loading timestamp data using sqlldr
Author: Philip Wisse, Netherlands
Date: Nov 24, 2009, 11:25, 3222 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Grammy,

The syntax I suggested seems not valid. I normally code a date as something like:
,AS_OF_DT date "DD/MM/YYYY"

So maybe the following will be working:
,LOAD_TS timestamp "DD/MM/YYYY HH:MI:SS.FF AM"

At least it works in my test environment.

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here