topic tOracleOutput - Null values and defaults in Talend Studio

tOracleOutput - Null values and defaults

Anonymous — Wed, 12 Apr 2017 20:11:50 GMT

Hi all,

I'm trying to load a table through the tOracleOutput component. I have a table which has a column defined as:

CREATE TABLE TEST_NULL
(
COLUMN1 varchar2(200)
, DEFAULT_VALUE NUMBER DEFAULT '-1' NOT NULL
)

now I can't seem to be able to find a working combination, together with the tOracleOutput component, which is able to use this table definition.

I either get nullpointer exceptions in my job, or "cannot insert null value" oracle errors.

Alternatively I see that the tOracleOutput component has got a "default" column, but also from that option I can't seem to find any documentation, or get it to work.

Anyone has got some advice?

thanks in advance for your help.

Re: tOracleOutput - Null values and defaults

TRF — Wed, 12 Apr 2017 20:20:13 GMT

If DEFAULT_VALUE column is in your tOracleOutput schema, you MUST have a value for it to avoid oracle error.
The null pointer exception comes from elsewhere.
I think so.

Regards,

Re: tOracleOutput - Null values and defaults

Anonymous — Wed, 12 Apr 2017 20:24:36 GMT

hi TRF,

thanks for the quick reply.

well, the weird thing is, if I define my column as "MY_COLUMN NUMBER DEFAULT '-1' " (without the "NOT NULL") then the job works fine, but it actually inserts a NULL, instead of using the default value which I specify in my Oracle DDL (or in the "Default" field of the tOracleOutput component). Is there any way to have null's not explicity written?

regards

Christoph

Re: tOracleOutput - Null values and defaults

TRF — Wed, 12 Apr 2017 20:33:44 GMT

With oracle, default value is set to the associated column only if this column doesn't participate to the insert operation.
As soon as the field is in Talend schema, the column participate to the insert and the default doesn't apply.

Re: tOracleOutput - Null values and defaults

Anonymous — Wed, 12 Apr 2017 20:39:26 GMT

OK, in that case what would be the cleanest solution to make sure that my null values get replaced by another value (-1 in this case) I would presume that the "Default" option in the tOracleOutput component is used for that, right? Or is that for something completely different? in which case is this "default" value used, because I can't get it working neither.

thanks!

Re: tOracleOutput - Null values and defaults

TRF — Wed, 12 Apr 2017 20:49:59 GMT

Do it in tMap
row1.myColumn == null ? -1 : row1.myColumn

Re: tOracleOutput - Null values and defaults

Anonymous — Wed, 12 Apr 2017 21:10:41 GMT

That's indeed a solution, but that would be the functionality I would expect from that "Default" column:

But I'll need to search a bit further then to get that working. thanks!

Re: tOracleOutput - Null values and defaults

TRF — Wed, 12 Apr 2017 21:16:05 GMT

Not sure it works with tOracleOutput. Some topic about that in the ancient forum.
Maybe the default defined in the schema can be accessed in Java, but looks a little complicated.

Re: tOracleOutput - Null values and defaults

cterenzi — Thu, 13 Apr 2017 14:16:12 GMT

According to the documentation, the default value in a schema is only used by certain components:

How to set default values in the schema of a component

The database output components won't add a default value for you when you write data to Oracle. You'll either need to handle missing values in a tMap or you can potentially handle them on input, depending on what your input source is.

Re: tOracleOutput - Null values and defaults

Anonymous — Thu, 13 Apr 2017 16:28:36 GMT

thanks all, with all this information I can indeed get it working.

finally I'll handle it as described in this post:

https://community.talend.com/t5/Design-and-Development/resolved-How-to-set-Float-default-values-in-the-schema/td-p/77334

Re: tOracleOutput - Null values and defaults

Anonymous — Thu, 13 Apr 2017 19:29:21 GMT

First of all, the default value in the schema will only be applied while creating the table and does not have any effect while inserting/updating data.

Second, default values in the database will only be applied if you do not have this column in your insert statement!

If you simply send null values, the database takes this null value and this will e.g. violate against a not-null-constraint.

The NullPointerException comes typically from the fact, you have in your source a null value (any numeric value) and your table output schema column is a primitive data type like int or long (etc) and now Java tries to unbox the given numeric object to the primnitive data type. This leads to the NullPointerException.

Conclusion: Check where you apply a Integer or Long or Double to a int, long or double typed column and take care your source does not send null values here!