Oninit
Talk to an expert →

Informix Error -675: Illegal SQL statement in SPL routine.

Cause and resolution

Illegal SQL statement in SPL routine.

An SQL statement that is not allowed in an SPL routine was executed. This error occurs when a routine is called from an SQL data manipulation statement.

Example of error:

CREATE PROCEDURE testproc (arg INT, id INT) RETURNING INT; UPDATE tab SET col = arg WHERE key = id; -- error RETURN id; END PROCEDURE;

SELECT col FROM tab WHERE testproc(tab.col, tab.key) = 10;

Do not use statements such as the preceding UPDATE statement in SPL routines.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-675 fires when an SPL routine, invoked from within a SELECT (or another data-retrieval SQL statement), contains a statement that isn't allowed in that calling context — per the official guidance's example, a routine containing an UPDATE statement, called from a SELECT.

  1. A routine that modifies data (UPDATE/INSERT/DELETE) invoked from within a SELECT, per the official guidance's example — data-modifying side effects aren't allowed when a routine is called this way.
  2. A routine written assuming it would only ever be called standalone (via EXECUTE PROCEDURE), later reused inside a SELECT's expression list without accounting for the different rules that context imposes.
Solutions / Resolution
  1. Don't use data-modifying statements (UPDATE/INSERT/DELETE) in a routine that will be called from a SELECT, per the official guidance's explicit instruction.
  2. Split the routine into two: a read-only function safe to call from SELECT, and a separate procedure containing the data-modifying logic, invoked standalone via EXECUTE PROCEDURE instead.
  3. Call the data-modifying routine directly, not from within a SELECT, if the modification is genuinely needed as part of the same workflow.
Examples
The disallowed pattern
CREATE PROCEDURE mark_reviewed(p_order_id INT)
  UPDATE orders SET status = 'reviewed' WHERE order_id = p_order_id;
END PROCEDURE;

SELECT mark_reviewed(order_id) FROM orders WHERE status = 'pending';
-- -675: mark_reviewed contains UPDATE, called from a SELECT
Corrected — call it standalone instead
FOREACH SELECT order_id INTO v_order_id FROM orders WHERE status = 'pending'
  EXECUTE PROCEDURE mark_reviewed(v_order_id);
END FOREACH;
Diagnostic Checks
  1. Check whether the routine contains any data-modifying statement, and whether it's being invoked from within a SELECT rather than standalone.
Related Errors / Related Topics

No closely related error codes are cross-referenced for -675 in this set yet.

Data-modifying statements aren't allowed in a routine called from SELECT — invoke it standalone via EXECUTE PROCEDURE instead, or split read and write logic into separate routines.