Home
Pipelined functions should have at least one PIPE ROW statement and not return an expression
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 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;