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
, 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.
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?
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!
According to the documentation, the default value in a schema is only used by certain components:
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.
thanks all, with all this information I can indeed get it working.
finally I'll handle it as described in this post:
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!