Table-valued functions in Oracle are the functions, returning a collection of rows and can be queried like usual database tables by calling the function in the FROM clause of SELECT statements. Pipelined table-valued functions are the functions that iteratively return rows as soon as they are produced instead of returning all of the rows after the end of the function execution.

In this topic we will show how you can execute such functions with the OracleCommand Class.

We will use the following two functions in our samples:

CREATE OR REPLACE TYPE t_tvf_row AS OBJECT (
  id           NUMBER,
  description  VARCHAR2(50)
);
/
CREATE OR REPLACE TYPE t_tvf_table IS TABLE OF t_tvf_row;
/
CREATE OR REPLACE FUNCTION tvf_table_func (p_rows IN NUMBER)
  RETURN t_tvf_table
AS
  l_tab  t_tvf_table := t_tvf_table();
BEGIN
  FOR i IN 1 .. p_rows LOOP
    l_tab.extend;
    l_tab(l_tab.last) := t_tvf_row(i, 'Description for ' || i);
  END LOOP;

  RETURN l_tab;
END;
/
CREATE OR REPLACE FUNCTION tvf_pipelined_table_func (p_rows IN NUMBER)
  RETURN t_tvf_table PIPELINED
AS
BEGIN
  FOR i IN 1 .. p_rows LOOP
    PIPE ROW(t_tvf_row(i, 'Description for ' || i));   
  END LOOP;

  RETURN;
END;
/ 

The tvf_table_func is a non-pipelined table function, and tvf_pipelined_table_func is a pipelined table function.

The following example executes the non-pipelined tvf_table_func function, however this code can be used for both for pipelined and non-pipelined functions.

    private static List<object[]> ExecuteTableValuedFunctionWithoutDescribe() {

      using (OracleConnection conn = new OracleConnection("Data Source=ORA; User Id=scott; Password=tiger; DescribeStoredProcedure=false;")) {
        conn.Open();

        using (OracleCommand cmd = conn.CreateCommand("tvf_table_func", CommandType.StoredProcedure)) {
          cmd.IsTableValuedFunction = true;
          cmd.Parameters.Add("p_rows", 3);

          List<object[]> resultSet = new List<object[]>();
          using (OracleDataReader reader = cmd.ExecuteReader()) {
            while (reader.Read())
              resultSet.Add(reader.GetValues());
            return resultSet;
          }
        }
      }
    }

Private Shared Function ExecuteTableValuedFunctionWithoutDescribe() As List(Of Object())
    Using conn As New OracleConnection("Data Source=ORA; User Id=scott; Password=tiger; DescribeStoredProcedure=false;")
        conn.Open()
        Using cmd As OracleCommand = conn.CreateCommand("tvf_table_func", CommandType.StoredProcedure)
            cmd.IsTableValuedFunction = True
            cmd.Parameters.Add("p_rows", 3)
            Dim resultSet As New List(Of Object())()
            Using reader As OracleDataReader = cmd.ExecuteReader()
                While reader.Read()
                    resultSet.Add(reader.GetValues())
                End While
                Return resultSet
            End Using
        End Using
    End Using
End Function

When performing the ExecuteReader() call, only one call is sent to the database - the stored function call. For this we set the DescribeStoredProcedure connection string parameter to false and the IsTableValuedFunction property of OracleCommand to true. The latter is required for execution of non-pipelined table-valued functions.

The next sample executes the pipelined table-valued function tvf_pipelined_table_func. When performing the ExecuteReader() call, two database calls are performed. The first selects the stored function metadata to determine whether the function is pipelined, and the second executes the procedure. This code cannot be used for executing a non-pipelined table-valued function, such as tvf_table_func.

    private List<object[]> ExecuteTableValuedFunctionWithDescribe() {

      using (OracleConnection conn = new OracleConnection("Data Source=ORA; User Id=scott; Password=tiger;")) {
        conn.Open();

        using (OracleCommand cmd = conn.CreateCommand("tvf_pipelined_table_func", CommandType.StoredProcedure)) {
          cmd.Parameters.Add("p_rows", 3);

          List<object[]> resultSet = new List<object[]>();
          using (OracleDataReader reader = cmd.ExecuteReader()) {
            while (reader.Read())
              resultSet.Add(reader.GetValues());
            return resultSet;
          }
        }
      }
    }

Private Function ExecuteTableValuedFunctionWithDescribe() As List(Of Object())
    Using conn As New OracleConnection("Data Source=ORA; User Id=scott; Password=tiger;")
        conn.Open()
        Using cmd As OracleCommand = conn.CreateCommand("tvf_pipelined_table_func", CommandType.StoredProcedure)
            cmd.Parameters.Add("p_rows", 3)
            Dim resultSet As New List(Of Object())()
            Using reader As OracleDataReader = cmd.ExecuteReader()
                While reader.Read()
                    resultSet.Add(reader.GetValues())
                End While
                Return resultSet
            End Using
        End Using
    End Using
End Function

See Also

Working with Oracle Stored Procedures  | Stored Procedures - General Information  | Using Package Procedures  | Using Stored Procedures in DataSets