Home

The number of variables in a FETCH statement should match the number of columns in the cursor

Rule description

  • The number of variables in a FETCH statement should match the number of columns in the cursor

 

Non-compliant Code Example

DECLARE 
   id customers.id%type; 
   name customer.name%type; 
   address customers.address%type; 
   CURSOR customers_c is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN customers_c; 
   FETCH customers_c into id, name;         --Non compliant code (The number of variables in a FETCH statement is not matching with the number of columns in the cursor)
      EXIT WHEN customers_c%notfound; 
      dbms_output.put_line(id || ' ' || name || ' ' || address); 
   CLOSE customers_c; 
END;

Compliant Code Example

DECLARE 
   id customers.id%type; 
   name customer.name%type; 
   address customers.address%type; 
   CURSOR customers_c is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN customers_c; 
   FETCH customers_c into id, name, address;         --Non compliant code (The number of variables in a FETCH statement is not matching with the number of columns in the cursor)
      EXIT WHEN customers_c%notfound; 
      dbms_output.put_line(id || ' ' || name || ' ' || address); 
   CLOSE customers_c; 
END;
Visual Expert 2020
 VEPLSQLRULE188