Oracle Type Mapping

[d0r1]

Hello! I am facing the issue that I get “ORA-00902: invalid datatype” when trying to map String to LONGVARCHAR in the DB Writer.
I am working with an Oracle 12c database and jdbc8 driver, KNIME version 4.5.7

[takbb]

Hi @d0r1 , welcome to the KNIME community.

Firstly, just as an aside, I’ve requested that your post be moved out into a new topic because whilst related to the existing one, it is a new issue (the previous issue was resolved although not marked as such) and the original post is also from a few years ago. This will give your issue better visibility. When there is an old, but related post, it is generally better to create a totally new one and refer to it (e.g. by pasting a link)

Anyway, I agree with you that there seems to be a problem in terms of type mapping here. I tried my own test on an Oracle database and found the same problem as you when attempting to write to a table that doesn’t already exist. It appears that the type mapping information doesn’t get handled correctly, or at least I couldn’t find a way of persuading it to use the mapping information properly.

I also attempted to use the “Mapping by Name” options at the top panel, but the results were similar.
For my tests I had data called “longcomment” consisting of a 32767 character string.

If I attempted Mapping by Name like this (with no table already existing):

The result was:

If I attempted to map to a CLOB, it worked

and Oracle correctly created a clob field.

Experimenting further, I found that the data type mappings for creating tables in this way are of limited use. Attempting to leave String mapped to a regular varchar column, and having a string containing just 300 characters failed because it created a destination column as a varchar2(255 byte) which of course was too small.

I found though that if the table already existed on the database with the columns defined as required (including long varchar), then without specifying any special mappings in the DB Writer, it inserted the data correctly.

So from brief experimentation, I agree with you that there is an issue with the type mappings functionality in the DB Writer, making them of potentially limited use (and in your case not usable) when creating a table dynamically. I would generally advise that it is best to have pre-defined tables correctly scripted rather than attempting to create them through the DB Writer, although I realise there can be use cases where dynamic table creation has benefits for quick tasks.

Are you able to create the table first using a DB Table Creator node, or even from a script (in a SQL DB Executor), or must you have it being created “on the fly” directly from the data table via the DB Writer?

Going slightly off topic, my further comment would be questioning the use of a LONG VARCHAR for an Oracle database. As far as I know, Oracle doesn’t strictly have a LONG VARCHAR datatype as such. If I try to create one, it produces a LONG which is not a very friendly datatype (difficult to manipulate and search on in normal sql), and is in fact now deprecated (since Oracle 8.0). On an Oracle 12c database, possibly CLOB is slightly more friendly, within certain bounds, if you have to store large character data.