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

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);

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!

CodePudding user response：

Your initial error is because you're selecting into a record type, not an object type, so you don't need the constructor:

        SELECT rownum, uo.object_name
          BULK COLLECT
          INTO v_tvf_tbl

fiddle, which shows it now compiles, but you can't call it from SQL for the reason's MTO already explained.

As an alternative to creating an object type, you can as you suggested use a pipelined function, if you modify the collection type:

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;

    FUNCTION fn_get_tvf(p_max_num_rows INTEGER) RETURN t_tvf_tbl PIPELINED;

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 PIPELINED IS
        v_tvf_tbl t_tvf_tbl;
    BEGIN
    
        SELECT rownum, uo.object_name
          BULK COLLECT
          INTO v_tvf_tbl
          FROM user_objects uo
         WHERE rownum <= p_max_num_rows;

        FOR i IN 1..v_tvf_tbl.COUNT LOOP
          PIPE ROW (v_tvf_tbl(i));
        END LOOP;

        RETURN;
    
    END;
END pkg_test_oracle_tvfs;
/

SELECT * FROM pkg_test_oracle_tvfs.fn_get_tvf(5);

I	N
1	PKG_TEST_ORACLE_TVFS
2	PKG_TEST_ORACLE_TVFS

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

I	N
1	PKG_TEST_ORACLE_TVFS
2	PKG_TEST_ORACLE_TVFS

fiddle

CodePudding user response：

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.