Table Functions in Oracle PL/SQL | mrcaption49

📘 What is a Table Function?

In Oracle, a table function is a type of function that returns a collection type (like a nested table or a VARRAY), and it can be queried just like a regular table using the TABLE() operator. This allows you to encapsulate complex logic inside PL/SQL functions and return multiple rows of data — just like a table would.

---

📝 Five-liner Paragraph Explanation

A table function is a PL/SQL function that returns a collection (like a nested table), allowing you to select from it using the TABLE() keyword. These functions enable dynamic or complex result generation at runtime, which can be treated as rows in SQL queries. Table functions are especially useful in ETL, reporting, and data transformation use cases. They allow better modularity and cleaner separation of logic. Unlike regular functions returning a single value, table functions behave like row sources.

---

🚀 Understanding Table Functions in Oracle PL/SQL — A Deep Dive with Example

In Oracle PL/SQL development, table functions offer a powerful way to return multiple rows from a function and use them as if they were regular database tables. If you’ve ever wished a function could return a result set directly into a SELECT query, this is how you do it.

• This article explains what table functions are, how they differ from regular functions, and demonstrates their usage with a practical employee example.

---

🧠 What Are Table Functions?

A table function is a PL/SQL function that returns a collection (such as a nested table or varray), and whose results can be queried in SQL’s FROM clause.

• Instead of returning a single scalar value like NUMBER or VARCHAR2, table functions return sets of rows. These rows are unpacked by Oracle so you can interact with them as though they were stored in a real database table.

---

🛠 Example Scenario

Listing Employee Names Using Table Function
Let’s build a complete working example from scratch.

🔸 Step 1: Create a Sample Employee Table

CREATE TABLE emp123 (
  empno    NUMBER(4),
  ename    VARCHAR2(30),
  job      VARCHAR2(20),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7, 2),
  deptno   NUMBER(2)
);

---

🔸 Step 2: Populate Sample Data

INSERT INTO emp123 VALUES (7369, 'SMITH', 'CLERK', 
7902, TO_DATE('17-DEC-80', 'DD-MON-YY'), 800, 20);
INSERT INTO emp123 VALUES (7499, 'ALLEN', 'SALESMAN', 
7698, TO_DATE('20-FEB-81', 'DD-MON-YY'), 1600, 30);
INSERT INTO emp123 VALUES (7521, 'WARD', 'SALESMAN', 
7698, TO_DATE('22-FEB-81', 'DD-MON-YY'), 1250, 30);
INSERT INTO emp123 VALUES (7566, 'JONES', 'MANAGER', 
7839, TO_DATE('02-APR-81', 'DD-MON-YY'), 2975, 20);
INSERT INTO emp123 VALUES (7698, 'BLAKE', 'MANAGER', 
7839, TO_DATE('01-MAY-81', 'DD-MON-YY'), 2850, 30);
INSERT INTO emp123 VALUES (7782, 'CLARK', 'MANAGER', 
7839, TO_DATE('09-JUN-81', 'DD-MON-YY'), 2450, 10);
INSERT INTO emp123 VALUES (7788, 'SCOTT', 'ANALYST', 
7566, TO_DATE('09-DEC-82', 'DD-MON-YY'), 3000, 20);
COMMIT;

---

📦 Step 3: Define a SQL Collection Type

A table function must return a SQL-level collection that Oracle understands:

CREATE OR REPLACE TYPE t_emp_list IS TABLE OF VARCHAR2(30);
/

This creates a nested table type that can hold a list of employee names (VARCHAR2(30)).

🧩 Step 4: Create the Table Function

Now let’s create the function that returns the employee names as a collection:

CREATE OR REPLACE FUNCTION fn_get_emp_list
RETURN t_emp_list
AS
  lv_emp_list t_emp_list := t_emp_list();  -- Initialize empty list
BEGIN
  FOR emp_rec IN (SELECT ename FROM emp123) LOOP
    lv_emp_list.EXTEND;
    lv_emp_list(lv_emp_list.LAST) := emp_rec.ename;
  END LOOP;

  RETURN lv_emp_list;
END;
/

✅ What This Function Does:

• Initializes a local variable of type t_emp_list.
• Iterates through each row in emp123.
• Adds each ename to the list.
• Returns the final list.

---

🔍 Step 5: Query the Function as a Table

Here comes the magic:

SELECT * FROM TABLE(fn_get_emp_list);

🧾 Output:
COLUMN_VALUE
SMITH
ALLEN
WARD
JONES
BLAKE
CLARK
SCOTT

• Oracle has expanded the returned collection into a result set. Each element in the collection becomes one row in the query result.

---

🔍 Explanation of the Table Function

• emp_type is an object type representing a single row of employee data.
• emp_tab_type is a nested table (collection) of emp_type objects.
• The function fn_get_emp_list initializes the nested table and fills it using a loop over the EMP table rows.
• The TABLE(fn_get_emp_list) syntax lets you treat the returned collection like a relational table — each object becomes a row.

---

🏎 Bonus: PIPELINED Version (Streaming Rows)

For better performance in large datasets, use the PIPELINED feature to stream rows without materializing the full collection in memory:

CREATE OR REPLACE FUNCTION fn_get_emp_list_pipe
RETURN t_emp_list PIPELINED
AS
BEGIN
  FOR emp_rec IN (SELECT ename FROM emp123) LOOP
    PIPE ROW(emp_rec.ename);
  END LOOP;
  RETURN;
END;
/

🎯 Use Cases for Table Functions

• 💡 Transforming or filtering input data row-wise
• 🧾 Reading files into rows (CSV/XML/JSON)
• 📊 Generating dynamic reports
• 🌐 Wrapping API data into SQL queries
• 🔁 Expanding comma-separated lists to rows

🧠 Conclusion

Table functions are a powerful feature of Oracle PL/SQL that bridge procedural logic and set-based SQL operations. If you're working with dynamic data sources, external systems, or complex row logic, table functions let you return rowsets directly into SQL — offering performance, flexibility, and clean integration.

---

📎 Sample Source Code Recap

-- Type
CREATE OR REPLACE TYPE t_emp_list IS TABLE OF VARCHAR2(30);

-- Function
CREATE OR REPLACE FUNCTION fn_get_emp_list
RETURN t_emp_list
AS
  lv_emp_list t_emp_list := t_emp_list();
BEGIN
  FOR i IN (SELECT ename FROM emp123) LOOP
    lv_emp_list.EXTEND;
    lv_emp_list(lv_emp_list.LAST) := i.ename;
  END LOOP;
  RETURN lv_emp_list;
END;

-- Query
SELECT * FROM TABLE(fn_get_emp_list);