community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Clob Data poor performance

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.

8 Comments
Atom

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.

Meteoroid

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. 

Alteryx Partner

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.

Atom

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.

 

Clob.jpg

Meteoroid

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. 

Asteroid

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

Asteroid

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.

Meteoroid

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