Clob Data poor performance

[Sugu]

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.

[Sugu]

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.

[smbourke]

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. 

[DanielUpton]

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.

[Sugu]

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.

[smbourke]

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. 

[timewaste]

@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!

[timewaste]

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.

[suemc]

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

[Community_Admin]

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!