hai,
i am facing one problem i.e,my source is sequential file .one of my column is in 'date' type it is in "YYMMDD" format.i am inserting this values into oracle table using oracle(database) stage.oracle internal format is 'dd-mon-yyyy'.in oracle stage insert command like
insert into a1(date1) values('to_date(:1,'yymmdd'))
here the error is:
date001..Transformer_2: At row 1, link "DSLink4", while processing column "date1"
Value treated as NULL
Attempt to convert String value "61/08/13" to Date type unsuccessful
just i am giving example with one column.i laso tried with oconv functions in transformer to change the input format into dd/mm/yyyy
and also changed the insert command in output stage like
insert into a1(date1) values('to_date(:1,'dd/mm/yyyy'))
but it is also not working.
please help me.
problem while inserting date into oracle
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 36
- Joined: Mon Jul 16, 2007 3:37 am
- Location: USA
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Re: problem while inserting date into oracle
Output the result from transformer to a sequential file and make sure that your date conversion is happening correct, if this is not right then here is where your problem is and you might have to correct this first.
If you got that right (ie. Your output in dd/mm/yyyy) then you don't have to use 'to_date' function in your insert statement because already the input column is converted into date.
If you got that right (ie. Your output in dd/mm/yyyy) then you don't have to use 'to_date' function in your insert statement because already the input column is converted into date.
reddy.vinod wrote: i also tried with oconv functions in transformer to change the input format into dd/mm/yyyy
and also changed the insert command in output stage like
insert into a1(date1) values('to_date(:1,'dd/mm/yyyy'))
but it is also not working.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
No, that is not the 'Oracle internal format'. That is an external format.reddy.vinod wrote:i am inserting this values into oracle table using oracle(database) stage. oracle internal format is 'dd-mon-yyyy'.
Not quite true. It's only 'converted to a date' if you use the TO_DATE function. Otherwise, you end up attempting to match the NLS_DATE setting of the current database, this allows Oracle to do the conversion 'behind the scenes' with default values. All in all, a Bad Idea even when it does work. Your solution should work on any instance, not just one where you've managed to match the NLS_DATE format.JoshGeorge wrote:you don't have to use 'to_date' function in your insert statement because already the input column is converted into date.
I've posted over and over here how to handle Oracle DATE fields. It involves generated sql and proper conversion of the incoming field. It's not rocket science nor all that mysterious and a standard set of Oracle date handling routines will go a long way towards making your DATE issues go away forever.
One such example here, scroll down for a couple of Christmas time posts from me on the subject. Other examples abound.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers