Informix Error -720

-720 The number of returned values and of SPL variables do not match.

The list of SPL variables that follows the INTO keyword has a different cardinality from the number of values in each row of the active set. This error can occur during the FOREACH statement of SPL, or during the FETCH statement of dynamic SQL.

In a FOREACH statement with an embedded SELECT statement, the variable list that follows the INTO keyword in the SELECT statement does not match the number of columns or expressions in each row that the SELECT statement retrieves. In a FOREACH statement with an embedded EXECUTE FUNCTION or EXECUTE PROCEDURE statement, the INTO clause that follows the routine argument list specifies a list of variables whose number does not match the number of values that the routine returns.

In a FETCH statement, the variable list that follows the INTO keyword does not match the number of columns or expressions in each row that a SELECT statement retrieves, or does not match the number of values returned by a function call. A DECLARE statement in the same SPL routine associated the cursor that FETCH specifies with a SELECT statement or with an EXECUTE FUNCTION or EXECUTE PROCEDURE statement.

For example, the following dynamic SQL statements inside a procedure body returns this error when the procedure is executed, because fetching from the cursor returns two values (c1, c2) but has only one local variable (l_out1) to receive those two column values:

create procedure proc() define qry_str varchar(120); define l_out1 char(10);

let qry_str = "select c1,c2 from t1";

prepare stmt_id from qry_str;

declare my_cur cursor for stmt_id;

open my_cur;

fetch my_cur into l_out1;

.... end procedure;

To avoid this error, review the logic of the FOREACH or FETCH statement to verify that the list of variables in the INTO clause matches the number of values in the projection list of the SELECT statement, or matches the number of values returned by the routine. Both FOREACH and FETCH require a 1-to-1 correspondence in number, order, and data type between the list of SPL variables in the INTO clause and the values returned by a call or in each row returned by a query.

If more than one cursor is open when a FETCH statement receives this error, make sure that the FETCH statement references the correct cursor.

FOREACH SELECT order_id, status, ship_date INTO v_id, v_status FROM orders
  ...
END FOREACH;
-- -720: 3 columns selected, 2 variables in INTO

FOREACH SELECT order_id, status, ship_date INTO v_id, v_status, v_ship_date FROM orders
  ...
END FOREACH;