Home

Description

The rule states that the number of variables in a FETCH statement should match the number of columns in the cursor. This means that when a FETCH statement is used to retrieve data from a cursor, the number of variables used to store the data must be equal to the number of columns in the cursor. This ensures that all the data in the cursor is retrieved and stored in the correct variables. Failure to do so may result in data loss or incorrect data retrieval.

Key Benefits

• Faster Execution: The number of variables in a FETCH statement should match the number of columns in the cursor, which helps to reduce the amount of time needed to execute the query.
• More Efficient: By ensuring that the number of variables in the FETCH statement matches the number of columns in the cursor, the query is more efficient and will run faster.
• More Accurate: By ensuring that the number of variables in the FETCH statement matches the number of columns in the cursor, the query will be more accurate and will return the correct results.

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; --Compliant code (The number of variables in a FETCH statement is 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;