Table Function return a result set that mimics what we would normally expect from a traditional SQL SELECT statement
Table functions are a new feature in Oracle9i that allow you to define a set of PL/SQL statements that will, when queried, behave just as a regular query to table would. The added benefit to having a table function is that you can perform transformations to the data in question before it is returned in the result set.
CREATE OBJECT
Create our own object type called PERSON_DETAILS.Then we create a table of PERSON_DETAILS called PERSON_DETAILS_TABLE.
CREATE TYPE PERSON_DETAILS AS OBJECT (USER_NAME VARCHAR2(50), ADDRESS VARCHAR2(50), LOCATION VARCHAR2(50)); / CREATE TYPE PERSON_DETAILS_TABLE AS TABLE OF PERSON_DETAILS; /
PIPELINED Clause
Within the CREATE FUNCTION clause, there is a new option called PIPELINED. This option tells Oracle to return the results of the function as they are processed, and not wait for a complete execution or completion of the result set. This pipelining of the result set to one row at a time has the immediate advantage of not requiring excessive memory or disk staging resources.
PIPE ROW(out_rec)
The PIPE ROW statement is the interface or mechanism to send a piped row through the PIPELINED option through to the caller of the function.
Working with a simple pipelined function requires 2 things
- collection type
- pipelined function
CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;
CREATE FUNCTION row_generator(rows_in IN P_INTEGER) RETURN number_ntt PIPELINED IS BEGIN FOR i IN 1 .. rows_in LOOP PIPE ROW(i); END LOOP; RETURN; END; /
The CSV_TABLE is a collection which has rows of comma separated value.
CREATE OR REPLACE TYPE "CSV_TABLE" as table of varchar2(32767) CSV_TABLE
FUNCTION CSV_TO_TABLE(p_delimted_string VARCHAR2, p_delimter VARCHAR2 := ',') RETURN CSV_TABLE PIPELINED IS indexCount PLS_INTEGER; csvString VARCHAR2(32767) := p_delimted_string; BEGIN LOOP indexCount := instr(csvString, p_delimter); IF indexCount > 0 THEN PIPE ROW(substr(csvString, 1, indexCount - 1)); csvString := substr(csvString, indexCount + length(p_delimter)); ELSE PIPE ROW(csvString); EXIT; END IF; END LOOP; RETURN; END CSV_TO_TABLE;
Input
A,B,C,D
Output
A B C D
The above output is a collection.
SELECT PACKAGE_NAME.CSV_TO_TABLE('A,B,C,D') FROM DUAL;