We encountered an issue with clob data. Looks like alteryx is very slow performing while loading clob data to Oracle database. We need to figure a better way to handle the clob data.
Hi ,
Any one encountered this issue, while loading clob data.
ORA-24801: illegal parameter value in OCI lob function
while loading csv file to clob datatype type table, we are encountering this issue. We tried loading manually using toad it worked but through alteryx it fails always. Any sugesstions.
To further Sugus discussion around CLOBs it would be nice to see Alteryx fix up a bug in the Oracle database connection's handling on CLOBS. Currently, CLOB fields are randomly truncated when read in using the OCI Oracle reader. This can be fixed using an ODBC connection, however, the workaround adds complexity for users. I would like to see CLOBs handled natively in the Oracle connection.
Running v 10.6, I'm encountering this: illegal parameter value in OCI lob function... Each time an error is thrown, I'm seeing at least one 'EMPTY_CLOB' reference in the error output.
Daniel,
This occurs when there are weird 'blanks' in the column values. Try using dummy delimiter(to check for the unidentifiable value in CLOB column) and this will fix the issue. This worked for us.
Also, our workflow which was running for 4 hours completed in 30 min after adding this delimiter function.
Hi All,
Another workaround to this that we have found is to use an odbc connection instead of the oci connection and it fixed the problem too.
@smbourke Can you please help with some screenshots? I am trying to load data into Oracle DB using OCI connection but facing the same error as @Sugu
Error: Output Data (142): DataWrap2OCI:: SendBatch: ORA-24801: illegal parameter value in OCI lob function
Appreciate it. TIA!
I was able to figure out the solution for this. This error comes up when we are trying to load empty cells of data into Oracle table. Instead, we can use a Multi-Field formula tool and have this formula to replace null with 0.
IIF(IsNull([_CurrentField_]),0,[_CurrentField_])
Once replaced, it should be good to go and load into table without any issues.
Hi neither the multi field tool or the data cleansing tool have resolved the issue and I believe the error I am getting is because I am attempting to insert (ORA-24801) empty clobs into an oracle database via an OCI connection. Has anyone found a method to identify and populate an empty clob read in from an oracle datasource?
Thanks,
Susan
The status of this idea has been changed to 'Inactive'. This status indicates that:
1. The idea has not had activity in the form of likes or comments in over a year.
2. The idea has not reached ten likes.
3. The idea is still in the 'New Idea' status.
However, this doesn't mean your idea won't be implemented! The Community can still like and comment on this idea. With enough renewed interest, this idea can be brought back into the 'New Idea' status.
Thank you for contributing to the Alteryx Community and the Alteryx Product Idea Boards!
Yes when loading string values to our Oracle database, we first load into a TEMP table and then merge it into our FACT table, then drop the temp table.
When adding new fields to the workflow, sometime the workflow fails after running for a long time without writing any data. When we check the temp tablet that was created, NCLOB data types are seen and whenever these are present no data is written.
To fix this, usually we manually set the precision types in a Select tool right before the output tool. This happens when a field is too big, and when I check the precision that was automatically set by Alteryx when running, it is something huge. Manually setting it to 255 usually fixes this issue.
Sometimes, however, even with a precision set to 255 in Alteryx, the workflow will fail with an error stating that it is trying to insert something that is 256 length when the max field length in oracle is 255.
This has been very frustrating and only have found random solutions that sometimes work.
This is very important for DB's. Alteryx crashes when we use CLOB or takes huge time to load the data even when we have just 2 records. This is core issue of the product.
