Home

Description

The rule states that when writing PL/SQL code for a pipelined function, the function must contain at least one PIPE ROW statement and should not return an expression. A PIPE ROW statement is used to return a row from a pipelined function, and an expression is a statement that evaluates to a single value. This rule ensures that the pipelined function is able to return multiple rows, as opposed to just a single value.

Key Benefits

• Ensure Result Set: This ensures that the function will return a result set, and not just a single row or expression. This makes it easier to use the function in other queries and applications.
• Avoid Unpredictable Results: Returning an expression from a pipelined function can lead to unpredictable results, as the expression may not be evaluated in the same context as the rest of the query. By not returning an expression, the pipelined function can guarantee consistent results regardless of the context in which it is used.

Non-compliant Code Example

CREATE FUNCTION GetCustomers(cust CUSTOMER_T) RETURN CUSTOMERS_T  --Non compliant code (Pipelined functions is not having at-least one PIPE ROW statement)
PIPELINED IS
  out_cust CUSTOMERS_T := CUSTOMERS_T(NULL);
  in_cust cust%ROWTYPE;
BEGIN
  LOOP
    FETCH cust INTO in_cust; 
    EXIT WHEN cust%NOTFOUND;
    
    out_cust.first_name := in_cust.first_name;
    out_cust.last_name := in_cust.last_name;
    out_cust.credit_limit := in_cust.credit_limit;
    
    out_cust.first_name := in_cust.first_name;
    out_cust.last_name := in_cust.last_name;
    out_cust.credit_limit := in_cust.credit_limit;
    
  END LOOP;
  CLOSE cust;
  RETURN;
END;

Compliant Code Example

CREATE FUNCTION GetCustomers(cust CUSTOMER_T) RETURN CUSTOMERS_T  --Compliant code (Pipelined functions is having more than one PIPE ROW statement)
PIPELINED IS
  out_cust CUSTOMERS_T := CUSTOMERS_T(NULL);
  in_cust cust%ROWTYPE;
BEGIN
  LOOP
    FETCH cust INTO in_cust; 
    EXIT WHEN cust%NOTFOUND;
    
    out_cust.first_name := in_cust.first_name;
    out_cust.last_name := in_cust.last_name;
    out_cust.credit_limit := in_cust.credit_limit;
    PIPE ROW(out_cust);
    
     out_cust.first_name := in_cust.first_name;
    out_cust.last_name := in_cust.last_name;
    out_cust.credit_limit := in_cust.credit_limit;
    PIPE ROW(out_cust);
  END LOOP;
  CLOSE cust;
  RETURN;
END;