Aveksa SQL Node Job Variable Data Length Limitation.

Aveksa  SQL Node columns creates run time Job variable  that has a data length limitation of 4000 bytes

SQL Node

Example:

select change_description from pv_change_request_detail

Run time Job variable is created with name $(JobUserData_CHANGE_DESCRIPTION}

Now lets assume that this change_description column is of CLOB data type and it returns more than 4000 bytes.  Then this would result in error during the workflow run time.

Note: The above query will execute successfully in any sql client like SQL Developer. It fails only in Aveksa workflow

Solution

split the CLOB datatype columns to 4000 bytes chuncks. You can do this using dbms_lob.substr().

Example

select dbms_lob.substr(change_description,3999,1) as chunck_1 , dbms_lob.substr(change_description,3999,4000) as chunck_2, dbms_lob.substr(change_description,3999,7999) as chunck_3 from pv_change_request_detail

Now you have 3 columns holding the same information $(JobUserData_CHANGE_DESCRIPTION} was holding.

${JobUserData_chunck_1}

${JobUserData_chunck_2}

${JobUserData_chunck_3}

---------------------------------------------------------------------------------------------------------------------------------------------

Disclaimer: Content posted here worked for me and may not guarantee success, should be used as reference only and please use it cautiously.