I have a workflow that is retrieving data from a SQL Server 2000 table and writing it to an Oracle 12c database. When attempting to write some large string data to Oracle, Alteryx designer was returning the following error:
Output Data (2) DataWrap2OCI::SendBatch: ORA-24801: illegal parameter value in OCI lob function¶¶ Insert into "IR_TESTVARCHAR"("LargeTextData","SmallTextData","BadLargeText") Values (EMPTY_CLOB(),:A2,EMPTY_CLOB());
I found a couple of posts related to this error and similar issues with writing CLOB data, but hadn't found a workflow that resolved it, other than suggestions of using different drivers such as ODBC.
My investigation revealed that whenever Alteryx attempts to create an oracle table and insert character data that exceeds a length of 1023 characters, rather than write it as a varchar2 column, it writes it as a clob. I could not find any way to control this behaviour. Oracle itself can handle varchar2 data up to 4000 (and in some cases, depending on version and settings, 8000) characters.
It turns out that this error occurs when an attempt is made to write an EMPTY STRING to a CLOB in Oracle. NB, an empty string is different to a NULL. If we try to write a NULL, the error does not occur.
To show this error occurring, I produced the following simplified demonstration workflow:
In the above demo workflow, a text input contain fields with some arbitrary data which would not cause any issues, and then a formula tool has been added to create a field called BadLargeText set to '' (an empty string). The data type for BadLargeText has been defined in the formula tool as V_String (2000):
When the workflow is run, the output to Oracle (in this case using OCI) fails with the error shown above.
If the formula tool were instead re-coded as follows:
[so that instead of an empty string, the field were set to null() ], the workflow runs without error. So the error is the direct result of the treatment of writing an empty string to a CLOB in Oracle.
For my purposes, I am happy to store NULL in my database in place of an empty string. (In Oracle, there is no such thing as an empty string anyway, when writing to varchar2 columns: an attempt to place an empty string into a varchar2 column will result in Oracle writing a null there anyway), so I am of the opinion that it won't matter to me if I use the same treatment for CLOB data.
Of course, this is a demo workflow and in reality, I don't have the luxury of simply changing a formula tool to "null"
The trick then is how to handle this both gracefully and generically? I wanted a section of workflow that will handle this situation with any large string (clob) field thrown at it, without any prior knowledge of the field name. At some point, I'll probably create this as a "handleEmptyClobs" macro for use elsewhere.
The following workflow is my solution:
The workflow obtains the field info for the dataset, and extracts NAME, TYPE and SIZE. If there is a String field of 1K in length or greater, it performs a dynamic replace on it. If the value is an empty string, it replaces it with null, otherwise it just leaves it as the current value. The data can then be written to Oracle.
I have uploaded the workflow, in case others might find it useful. [Obviously you'd need your own Oracle db to test against... ]
Of course if anybody knows of any alternative (simpler) ways of handling this, or spots any flaws in this approach, please feel free to share!
