Home

Rule description

• Pipelined functions should have at least one PIPE ROW statement and not return an expression (PLS-00633)

Non-compliant Code Example

CREATE FUNCTION GetCustomers(cust CUSTOMER_T) RETURN CUSTOMERS_T      --Non complaint 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       --Complaint 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;