Page 1 of 1
NUll to oracle date
Posted: Thu Jun 21, 2007 1:12 pm
by edward_m
I am trying to insert null value to oracled nullable date,somehow its generating error called
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
I am not putting any condition in transformer,i am simply inserting this into oracle nullable date.
Please let me know how to insert null value to oracle nullable date.
Thanks in advance..
Posted: Thu Jun 21, 2007 1:25 pm
by chulett
A nullable date should take a null without issue. Doesn't sound like you are inserting what you think you are inserting. Why not try replacing the OCI stage with a Sequential File stage and see what ends up in the file?
Posted: Thu Jun 21, 2007 3:59 pm
by ray.wurlod
Make sure you really are sending it a NULL, not "" (which is not the same thing) or some default value where the year is out of range.
Re: NUll to oracle date
Posted: Fri Jun 22, 2007 8:05 am
by sachin1
i agree with chulett their should be no problem.
Posted: Fri Jun 22, 2007 8:30 am
by crouse
i also agree with chulett their should be no problem.
(Hey, just trying to get my post count up. Trying to catch up with chulett. I have 11,000 to go.)
(Just a little levity on a Friday morning.)
Ray, actually inserting '' (no space) into a nullable field does insert a null, for Oracle.
CREATE TABLE HDR_005001.Craig_test (
EFFECTIVE_START_DT DATE NULL
)
INSERT INTO HDR_005001.CRAIG_TEST(EFFECTIVE_START_DT)
VALUES('')
GO
select * from HDR_005001.CRAIG_TEST
EFFECTIVE_START_DT
---------------------
(null)
1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 6/22/07 8:25:02 AM MDT ] [Execution: 125/ms]
-Craig
Posted: Fri Jun 22, 2007 8:45 am
by ray.wurlod
Learned something today. Not a wasted day. Thanks, Craig.