The Job Script search functionality fails to return all matching records. (Oracle 12c)

Current Situation:

• The Job Script search functionality in the Workflows and Configuration Tab does not return results for all the matched records with the Oracle 12c database.
• General Notes
  • A Workflow Configuration is internally split into JSON pieces (e.g., Job names, Job Scripts) that are stored in separate NCLOB (Oracle) columns in the SEARCH_WORKFLOW table.
  • The advanced configuration search is based on JSON search and uses JSON-specific functions of the respective DBMS product.
  • For Oracle, the functions JSON_QUERY and JSON_VALUE are used:
    • Oracle 12c:
      • JSON_QUERY Documentation
      • JSON_VALUE Documentation
      • Oracle 12c has a limitation: it can only process JSON content up to the maximum length of a VARCHAR2, i.e., 4000 bytes (if extended data types are not enabled).
        • Note: Starting with Oracle 18c, support for CLOB and BLOB types was introduced.
      • If the column content length exceeds 4000 bytes, JSON_QUERY and JSON_VALUE will return NULL by default, regardless of whether the requested content exists or not.
• Conclusion
  • JSON search in Oracle 12c may fail due to the 4k RETURNING limitation.
  • This issue may occur if a workflow contains many jobs with "large" script content.

Desired Behaviour:

• The Job script search functionality should return all the results with the matched records.

Maintainer Note

• The advanced configuration search for Oracle (all supported versions) has been updated to use the JSON_EXISTS function instead of JSON_QUERY or JSON_VALUE.
  • Note: The handling of the advanced configuration search for all other DBMS products remains unchanged.
  • Note on Oracle 12c Compatibility:
    • 12.1.x (e.g., 12.1.0.2.0) — JSON_EXISTS functionality is rudimentary and does not support advanced filtering, which was added in 12.2.x (e.g., 12.2.0.1.0).
      • As a workaround, REGEXP_LIKE has been implemented.

Test Instructions

• Use Oracle 12c.
• Deploy the attached workflow w-JOC-1988.workflow.json.
• Manually identify "problematic" workflows.
  • Execute the following query using a database tool (e.g., Oracle SQL Developer):

    select ic."PATH"                      as WORKFLOW_PATH, 
        LENGTH(sw."JOBS")                 as SEARCH_JOB_NAMES_LENGTH
        ,LENGTH(sw."ARGS")                as SEARCH_ARGS
        ,LENGTH(sw."JOBS_SCRIPTS")        as SEARCH_JOB_SCRIPTS_LENGTH
        ,LENGTH(sw."INSTRUCTIONS")        as SEARCH_INSTRUCTIONS_LENGTH
        ,LENGTH(sw."INSTRUCTIONS_ARGS")   as SEARCH_INSTRUCTION_ARGS_LENGTH
    from SEARCH_WORKFLOWS sw
    join INV_CONFIGURATIONS ic ON sw."INV_CID" = ic."ID"
    where ic."TYPE"=1
        and ic."DEPLOYED"=1
        and (
                LENGTH(sw."JOBS")                 > 4000
                or LENGTH(sw."ARGS")              > 4000
                or LENGTH(sw."JOBS_SCRIPTS")      > 4000 
                or LENGTH(sw."INSTRUCTIONS")      > 4000
                or LENGTH(sw."INSTRUCTIONS_ARGS") > 4000
            )

• • • The query will return columns like SEARCH_JOB_NAMES_LENGTH and others.
    • If any SEARCH...LENGTH value exceeds 4000, the corresponding search will fail or not return results without the fix:
      • SEARCH_JOB_NAMES_LENGTH -> corresponds to a Job name search.
      • SEARCH_JOB_SCRIPTS_LENGTH -> corresponds to a Job Script search.
      • etc
• Test the advanced search using:
  • Return Workflow
  • Advanced - Job Script
  • Perform searches based on other input fields and return types(e.g. Exact match for Job Name ...).  
  • Expected Result: No errors; correct matches should be returned.
• Repeat tests with newer Oracle versions (e.g., 18c, 21c).
• Repeat tests with other DBMS products (e.g., MySQL).