Five Stars

tOracleOutput - Null values and defaults

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. 

  • Data Integration
Tags (3)
10 REPLIES
Eleven Stars TRF
Eleven Stars

Re: tOracleOutput - Null values and defaults

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,

TRF
Five Stars

Re: tOracleOutput - Null values and defaults

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

Eleven Stars TRF
Eleven Stars

Re: tOracleOutput - Null values and defaults

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.

TRF
Five Stars

Re: tOracleOutput - Null values and defaults

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!
Eleven Stars TRF
Eleven Stars

Re: tOracleOutput - Null values and defaults

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

TRF
Five Stars

Re: tOracleOutput - Null values and defaults

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

 

Default.JPG

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

Eleven Stars TRF
Eleven Stars

Re: tOracleOutput - Null values and defaults

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.

TRF
Ten Stars

Re: tOracleOutput - Null values and defaults

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.

Five Stars

Re: tOracleOutput - Null values and defaults

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

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

 

https://www.talendforge.org/forum/viewtopic.php?id=46873

 

 

Seventeen Stars

Re: tOracleOutput - Null values and defaults

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!