Can I create a "table-valued function" in an Oracle package?

I’m relatively new to the Oracle world. Most of my experience is with SQL Server.

I am writing code that would benefit from a "parameterized view", aka a "table-valued function" (tvf) in SQL Server.

I found a good example here that I’m trying to follow: Oracle: Return a «table» from a function

But I need mine to be inside a package, and I’m having a devil of a time with it.

Here’s an example of what I’m trying:

CREATE OR REPLACE PACKAGE pkg_test_oracle_tvfs IS

    TYPE t_tvf_row IS RECORD(
        i NUMBER,
        n VARCHAR2(30));

    TYPE t_tvf_tbl IS TABLE OF t_tvf_row INDEX BY BINARY_INTEGER;

    FUNCTION fn_get_tvf(p_max_num_rows INTEGER) RETURN t_tvf_tbl;

END pkg_test_oracle_tvfs;

CREATE OR REPLACE PACKAGE BODY pkg_test_oracle_tvfs IS

    FUNCTION fn_get_tvf(p_max_num_rows INTEGER) RETURN t_tvf_tbl IS
        v_tvf_tbl t_tvf_tbl;
    BEGIN
    
        SELECT pkg_test_oracle_tvfs.t_tvf_row(rownum,
                                              uo.object_name)
          BULK COLLECT
          INTO v_tvf_tbl
          FROM user_objects uo
         WHERE rownum <= p_max_num_rows;
    
        RETURN v_tvf_tbl;
    
    END;
END pkg_test_oracle_tvfs;

With the intent that I can do something like:

SELECT * FROM pkg_test_oracle_tvfs.fn_get_tvf(5);

Or

SELECT * FROM TABLE(pkg_test_oracle_tvfs.fn_get_tvf(5));

(I’m unclear if the TABLE() is required.)

But when I compile the package I get:

Compilation errors for PACKAGE BODY XXX.PKG_TEST_ORACLE_TVFS

Error: PL/SQL: ORA-00913: too many values
Line: 11
Text: FROM user_objects uo

Error: PL/SQL: SQL Statement ignored
Line: 7
Text: SELECT pkg_test_oracle_tvfs.t_tvf_row(rownum,

What am I doing wrong here? Why does this syntax seem to work fine outside of a package but not inside one?

Do I need to use the "pipeline" style of constructing the table as described in Oracle: Pipelined PL/SQL functions If so, why is this example different than the one I’ve been trying to follow?

Thanks!

>Solution :

There is a fundamental flaw; both RECORDs and associative arrays (TABLE OF ... INDEX BY ...) are PL/SQL only data types and cannot be used in SQL statements.

If you want to use a record-like and array-like data structure in an SQL statement then you will need to define it in the SQL scope which means that you cannot define it in a package and would need to use an OBJECT type and a nested-table collection type:

CREATE TYPE t_tvf_row IS OBJECT(
  i NUMBER,
  n VARCHAR2(30)
);

CREATE TYPE t_tvf_tbl IS TABLE OF t_tvf_row;

Then:

CREATE OR REPLACE PACKAGE pkg_test_oracle_tvfs IS
  FUNCTION fn_get_tvf(
    p_max_num_rows INTEGER
  ) RETURN t_tvf_tbl;
END pkg_test_oracle_tvfs;
/

CREATE OR REPLACE PACKAGE BODY pkg_test_oracle_tvfs IS
  FUNCTION fn_get_tvf(
    p_max_num_rows INTEGER
  ) RETURN t_tvf_tbl
  IS
    v_tvf_tbl t_tvf_tbl;
  BEGIN
    SELECT t_tvf_row(
             rownum,
             object_name
           )
    BULK COLLECT INTO v_tvf_tbl
    FROM   (
      SELECT object_name
      FROM   user_objects
      ORDER BY object_name
    )
    WHERE rownum <= p_max_num_rows;

    RETURN v_tvf_tbl;
  END;
END pkg_test_oracle_tvfs;
/

fiddle

why is this example different than the one I’ve been trying to follow?

Because you are defining data-types in a PL/SQL scope (a package) that can only be used in PL/SQL (because records and associative arrays are PL/SQL-only data types) and then trying to use them in an SQL scope (a SELECT statement). The example you are following defines the data types as an OBJECT and a non-associative array and defines them in the SQL scope (outside of a package) and then using them in an SQL statement is allowable.