Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

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.

12 Comments
Sugu
5 - 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.

smbourke
6 - 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. 

DanielUpton
9 - Comet

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
5 - 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

smbourke
6 - 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. 

timewaste
8 - 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!

timewaste
8 - 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.

suemc
6 - 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

Community_Admin
Alteryx
Alteryx
Status changed to: Inactive
 
Community_Admin
Alteryx
Alteryx

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!