Pipelined Table Functions & Object-Oriented PL/SQL

Master high-throughput streaming Pipelined Table Functions (PIPE ROW), object-oriented PL/SQL types, member methods, and type inheritance.

🌊 Pipelined Table Functions (`PIPE ROW`)

A Pipelined Table Function is a high-performance PL/SQL function that returns a collection result set as a virtual database table stream (TABLE()), returning rows one-by-one as they are produced rather than waiting for the entire collection to complete processing in memory!

Analogy: An Assembly Line Water Conveyor Hose vs A Giant Bucket.
- Standard Function: Filling a 10,000-gallon water tank completely before dumping the entire bucket onto the floor (High PGA RAM consumption, slow initial response).
- Pipelined Function (PIPE ROW): Opening a continuous water hose stream. As fast as each water drop is processed (PIPE ROW), it streams out to the consumer immediately!

                       Standard vs. Pipelined Table Functions
                                          │
        ┌─────────────────────────────────┴─────────────────────────────────┐
        ▼                                                                   ▼
Standard Function (Batched Return)                                 Pipelined Function (PIPE ROW Streaming)
Processes all 1,000,000 rows in PGA memory                         Streams Row 1 ──► SQL Query Consumer
Waits for full completion ──► Returns collection                   Streams Row 2 ──► SQL Query Consumer
High PGA memory footprint; high initial latency                    Sub-second time-to-first-row latency!

🛠️ Implementing a Pipelined Table Function

To build a pipelined function, declare the function with the PIPELINED keyword and emit individual tuples using PIPE ROW(record):

-- 1. Create Schema Object Types
CREATE OR REPLACE TYPE t_num_array IS TABLE OF NUMBER;
/

-- 2. Create Pipelined Function
CREATE OR REPLACE FUNCTION generate_series (
    p_start IN NUMBER,
    p_end   IN NUMBER
) RETURN t_num_array PIPELINED AS
BEGIN
   FOR i IN p_start..p_end LOOP
      -- Emits single tuple row to SQL caller immediately!
      PIPE ROW(i);
   END LOOP;

   -- Empty RETURN statement concludes the pipeline stream
   RETURN;
END;
/

Querying Pipelined Functions in SQL

Pipelined functions can be queried directly inside standard SQL FROM clauses using the TABLE() operator:

-- Querying pipelined function stream like a physical database table!
SELECT column_value AS generated_number 
FROM TABLE(generate_series(1, 5));

🤖 Object-Oriented PL/SQL (`CREATE TYPE`)

PL/SQL supports object-oriented programming (OOP) paradigms through Object Types. An Object Type encapsulates data attributes, constructor methods, member functions, and inheritance hierarchies:

-- 1. Declare Object Type Specification (NOT FINAL allows sub-type inheritance!)
CREATE OR REPLACE TYPE person_obj AS OBJECT (
   person_id   NUMBER,
   first_name  VARCHAR2(50),
   last_name   VARCHAR2(50),

   -- Member Method Header
   MEMBER FUNCTION get_full_name RETURN VARCHAR2
) NOT FINAL;
/

-- 2. Declare Object Type Body
CREATE OR REPLACE TYPE BODY person_obj AS
   MEMBER FUNCTION get_full_name RETURN VARCHAR2 IS
   BEGIN
      RETURN SELF.first_name || ' ' || SELF.last_name;
   END get_full_name;
END;
/

Object Inheritance (`UNDER` Clause) & Method Overriding

-- Sub-type inheriting from person_obj parent type
CREATE OR REPLACE TYPE employee_obj UNDER person_obj (
   salary     NUMBER(10,2),
   job_title  VARCHAR2(50),

   -- Overriding Parent Member Function
   OVERRIDING MEMBER FUNCTION get_full_name RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY employee_obj AS
   OVERRIDING MEMBER FUNCTION get_full_name RETURN VARCHAR2 IS
   BEGIN
      RETURN SELF.last_name || ', ' || SELF.first_name || ' (' || SELF.job_title || ')';
   END get_full_name;
END;
/

-- Instantiating Object Types in PL/SQL
DECLARE
   v_emp employee_obj;
BEGIN
   -- Automatic Constructor Call
   v_emp := employee_obj(101, 'Alice', 'Smith', 95000, 'Lead Architect');

   DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp.get_full_name());
END;
/

❓ Conceptual Quizzes

Knowledge Check

What is the primary architectural advantage of a Pipelined Table Function (PIPE ROW) over a standard collection function?

Knowledge Check

Which keyword is required when creating an Object Type specification to allow child sub-types to inherit from it?

💻 Practice Problems

Problem: ETL Streaming Pipelined Function

Create a pipelined function stream_dept_salaries(p_dept_id INT) that reads employees in p_dept_id and pipes records containing employee_id and double salary values.