Oninit
Talk to an expert →

Informix Error -675

-675 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.