Resolving ORA-01858 in Automic SQLI Preview

Products

• Automic Automation
• CA Automic Workload Automation - Automation Engine

Issue/Introduction

When using the Preview button in an Automic SQLI (VARA.SQLI) object, the execution fails with the following error:

Wrong Wrong SQL Statement in 'SQLI.FORKB.438545'. DB error: 'U00003754 Database-error in SQLI-variable: 'U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR', '', 'ORA-01858: a non-numeric character was found where a numeric was expected

 

Environment

Automic Workload Automation v24.x

 

Cause

This error occurs when a string is passed into Oracle and it does not match the expected format, or when a required bind parameter is missing/null during the preview.

 

Resolution

1. Missing or Null Bind Parameters

Scenario: The SQLI query contains a bind parameter (e.g., &date#) that is expected to be populated by a script at runtime. During a manual Preview, the variable has no value.

Solution:

• Ensure the bind parameter is correctly defined in the Variables & Prompts tab or the SQL tab,
• Or, use from an SCRI that defines the variable being used in the SQLI Bind Parameter
  Note: Variable must be set in the SCRI before calling the SQLI. 
• Or, if appropriate, use a System Property that has a value (ex: &$PHYS_DATE_DD_MON_RR_d#).

 

2. Incorrect/Unexpected Format

Scenario: The query is passing a string to oracle, but the input string is not in an expected format.

Solution:

• Figure out the expected format and then consider using a function to ensure it is in that format (ex: to_date(?, 'DD-MON-RR'))