Handle Timestamp null when inserting in OCI

CLOPES · Post by **CLOPES** » Thu Mar 15, 2007 12:09 pm

Hi all,

Got a problem with Timestamp.
I read data from a dataset then join with datasets then go throught a transformer and then make an upsert into an Oracle table.

I have some columns that i have to check in the transformer.

If data_from_the_beginning is NULL
then
take_the_data_from_joiner (timestamp)
OR
stringtotimestamp(take_the_data_from_the_beginning) (varchar)

The problem is that sometimes i don't have data in these both columns and the OCI drop the rows telling me that take_the_data_from_joiner is NULL.
I want to insert into Oracle table even if there's no value and it seems that OCI stage want to convert null to timestamp even if there's no data.
How can i insert without losing my rows and put null into these timestamp columns if the columns are null ??

DSguru2B · Post by **DSguru2B** » Thu Mar 15, 2007 12:23 pm

Does your table accept NULL for the timestamp field? If you get rid of the database stage and load it to a flat file with the timestamp field set to null, can you load the data into a file?

chulett · Post by **chulett** » Thu Mar 15, 2007 12:24 pm

What is the actual error that you get? And is your target Oracle field nullable or not?

CLOPES · Post by **CLOPES** » Thu Mar 15, 2007 12:44 pm

[quote="chulett"]What is the [i]actual error[/i] that you get? And is your target Oracle field [b]nullable[/b] or not?[/quote]

My error is something (because i'm at home) like the column from node 0 is NULL row is dropped.
The target is nullable yes

CLOPES · Post by **CLOPES** » Thu Mar 15, 2007 12:46 pm

[quote="DSguru2B"]Does your table accept NULL for the timestamp field? If you get rid of the database stage and load it to a flat file with the timestamp field set to null, can you load the data into a file?[/quote]

if i put a flat file with link reject i can see these rows and there's nothing in these columns.
The column in the Oracle table is nullable.

chulett · Post by **chulett** » Thu Mar 15, 2007 1:04 pm

But is the field nullable in your metadata?

And make sure you haven't checked the 'Disable BBCode in this post' option, that makes all your quoting / etc go a little wonky.

CLOPES · Post by **CLOPES** » Thu Mar 15, 2007 1:38 pm

chulett wrote:But is the field nullable in your metadata?

And make sure you haven't checked the 'Disable BBCode in this post' option, that makes all your quoting / etc go a little wonky.

Yes the field is nullable.
Thanks for the BBCode

DSguru2B · Post by **DSguru2B** » Thu Mar 15, 2007 2:42 pm

Thats odd. If you have it defined as nullable all accross and it still rejects it then that means something is missing. Double, triple check in all the input/output tabs of all the stages.

CLOPES · Post by **CLOPES** » Thu Mar 15, 2007 2:52 pm

The field is nullable everywhere in the stages and in Oracle too.
I have to check if fields are null or not and sometimes they are but the transformer reject the rows because the field is null.
I'm going crazy with this problem. Server Edition is easier to work with.

the message is that the column is null and there's a conversion problem.
I do not do conversion type for this column because it's already timestamp from a dataset. so the rows are dropped !!

DSguru2B wrote:Thats odd. If you have it defined as nullable all accross and it still rejects it then that means something is missing. Double, triple check in all the input/output tabs of all the stages.

DSguru2B · Post by **DSguru2B** » Thu Mar 15, 2007 3:09 pm

Can you post the exact error message by copy pasting from the log.

CLOPES · Post by **CLOPES** » Sat Mar 17, 2007 1:54 am

Resolved !

My problem was => too many stages and a trsfmr at the end that gone crazy.
Explication:

My job was
DS => colImport => 2 JOINs => TSFMR => OCI

The conversions type gone crazy in transformer so i create 3 jobs
One for the Joins
One for the tranformations
One for the Insert in Oracle Table

It work fine and last job deals fine with with null timestamps.

Thank you guys !!

CLOPES wrote:Hi all,

Got a problem with Timestamp.
I read data from a dataset then join with datasets then go throught a transformer and then make an upsert into an Oracle table.

I have some columns that i have to check in the transformer.

If data_from_the_beginning is NULL
then
take_the_data_from_joiner (timestamp)
OR
stringtotimestamp(take_the_data_from_the_beginning) (varchar)

The problem is that sometimes i don't have data in these both columns and the OCI drop the rows telling me that take_the_data_from_joiner is NULL.
I want to insert into Oracle table even if there's no value and it seems that OCI stage want to convert null to timestamp even if there's no data.
How can i insert without losing my rows and put null into these timestamp columns if the columns are null ??

chulett · Post by **chulett** » Sat Mar 17, 2007 8:14 am

Thanks for posting the resolution. And I'll keep an eye out for the video... 'Transformers Gone Wild!'.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 17, 2007 1:44 pm

CLOPES wrote:conversions type gone crazy in transformer

That was a delightful piece of "Franglais"; thinking in French (or maybe Spanish, but that would make it "Spanglish") but communicating in English.

Aucune critique.

DSguru2B · Post by **DSguru2B** » Sat Mar 17, 2007 3:26 pm

Great. Modularization always helps, especially for debuggin purposes.

DSXchange

Handle Timestamp null when inserting in OCI

Handle Timestamp null when inserting in OCI

Re: Handle Timestamp null when inserting in OCI