Oninit
Talk to an expert →

Informix Error -669

Cause and resolution

Variable variable-name redeclared.

A variable has been declared twice in the same scope. See the IBM Informix Guide to SQL: Tutorial for an explanation of the scope of procedural variables.

This error can also be seen when a DECLARE (or PREPARE) uses identifier same as one of the declared procedural variables. Choose a different cursor name or statement id instead.

For example, following DECLARE statement incorrectly uses cursor name same as procedural variable:

DEFINE spl_var INTEGER; ... DECLARE spl_var CURSOR FOR stmt_id1;

Following code fixes the issue in above sample code:

DEFINE spl_var INTEGER; ... DECLARE cur_id CURSOR FOR stmt_id1;

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-669 fires when a variable is declared more than once in the same scope — per the official guidance, this includes not just a duplicate DEFINE, but also a DECLARE (cursor) or PREPARE (statement) using an identifier that collides with an already-declared procedural variable's name.

  1. The same variable name given to two separate DEFINE statements in the same scope — the direct, common cause.
  2. A DECLARE cursor-name or PREPARE statement-id reusing a name already taken by a procedural variable, per the official guidance — cursor names and statement IDs share the same namespace as variable names within a routine.
  3. Copy-pasted declaration code carrying over a variable name that's already declared elsewhere in the same routine.
Solutions / Resolution
  1. Rename one of the two colliding declarations, per the official guidance, whichever is more appropriate given their distinct purposes.
  2. Choose a different cursor name or statement id, per the official guidance's specific correction, if the collision is between a DECLARE/PREPARE identifier and a procedural variable name.
  3. Review procedural-variable scope rules (per the official guidance's pointer to the IBM Informix Guide to SQL: Tutorial) if the collision isn't obvious — two declarations that look like they're in different blocks may still share the same scope.
Examples
A duplicate DEFINE
CREATE PROCEDURE process_order(p_order_id INT)
  DEFINE v_status CHAR(10);
  DEFINE v_status INT;
  -- -669: v_status already declared
END PROCEDURE;
A cursor name colliding with a variable name
CREATE PROCEDURE process_orders()
  DEFINE order_cursor INT;
  DECLARE order_cursor CURSOR FOR SELECT * FROM orders;
  -- -669: order_cursor is already a procedural variable
END PROCEDURE;
Diagnostic Checks
  1. Scan the routine for every DEFINE, DECLARE, and PREPARE, checking all three for name collisions against each other, not just against other DEFINEs.
Related Errors / Related Topics
  • -667 — "Variable variable-name not declared." The mirror-image situation: a variable referenced without ever being declared, rather than declared more than once.

Checks three kinds of declarations for collisions, not just DEFINE against DEFINE — cursor names and statement IDs share the same namespace as procedural variables.