The resulting SQL query in Oracle database is up to dynamically influence/generate a so called pipelined FUNCTION. Their use, we will see the following example:

[Česká verze článku – PIPELINED FUNCTION aneb jak na data dynamicky]

Create new data types PersonType and PersonTypeSet

Within these data types, we store our data.

CREATE TYPE PersonType AS OBJECT
(
  id number,
  first_name varchar2(2000),
  last_name varchar2(2000),
  description varchar2(2000)
)
/

CREATE TYPE PersonTypeSet AS TABLE OF PersonType
/

Create a pipelined FUNCTION

CREATE OR REPLACE FUNCTION GET_PERSONS RETURN PersonTypeSet
PIPELINED
IS
    l_one_row PersonType := PersonType(NULL, NULL, NULL, NULL);

BEGIN

    FOR i IN 1..10 LOOP
        l_one_row.id := i;
        l_one_row.first_name := 'Johnny (' || i || ')';
        l_one_row.last_name := 'English';
        l_one_row.description := 'British Super Agent';
        PIPE ROW(l_one_row);
    END LOOP;

    RETURN;
END GET_PERSONS;
/

Looking at the data (SQL query over the pipelined function!)

SQL> SELECT * FROM TABLE(GET_PERSONS());

Pipelined function result (SQL query)

UPDATE 14.09.2010 – If Oracle exception occured “ORA-22905: cannot access rows from a non-nested table item” it’s necessary to CAST table object like this: “SELECT * FROM TABLE(CAST(GET_PERSONS() AS PersonTypeSet));”

Post to Twitter

Leave a comment

Your email address will not be published. Required fields are marked *