Pipelined Table returning function (NON-Linq)

[loquin]

I can't locate documentation on how to get table returned from pipelined table functions.

(Am using dotConnect for Oracle 8.2)

The Oracle function I'm using is called as

Code: Select all

Select * from Table(GET_BOM_QTYS('ITEM_NUM', 'REV', QTY)

WHERE ITEM_NUM and REV are VARCHAR2, and QTY is Numeric (either integer or real)

The return table (TAB_BOM_QTYS) is table of BOM_QTYS records, defined as

Code: Select all

CREATE OR REPLACE TYPE BOM_QTYS
  AS
  OBJECT (ROW_NUM NUMBER,
          LVL NUMBER,         -- INDENT LEVEL
          ITEM_NO VARCHAR2(15),
          REV VARCHAR2(6),
          DESCRIPTION VARCHAR2(80),
          ITEM_TYPE VARCHAR2(3),
          PARENT_QTY NUMBER,
          QTY_PER NUMBER,
          TOT_QTY NUMBER,
          UOM VARCHAR2(6));

The VB (2012) code in question is

Code: Select all

        Dim localCmd As New OracleCommand("GET_BOM_QTYS", Me.con) [*]
        localCmd.CommandType = CommandType.StoredProcedure
        ' localCmd.istablevaluedfunction = True
        ' prepare parameters manually
        Dim paramITEM As OracleParameter = localCmd.Parameters.Add("ITEM", sItem)
        Dim paramREV As OracleParameter = localCmd.Parameters.Add("REV", sRev)
        Dim paramQTY As OracleParameter = localCmd.Parameters.Add("QTY", iQty)
        ' Need to specifically define return parameter
        Dim paramReturn As OracleParameter = New OracleParameter
        paramReturn.Direction = System.Data.ParameterDirection.ReturnValue
        paramReturn.DbType =  DbType.Object

        localCmd.Parameters.Add(paramReturn)

        localCmd.CommandText = SQL_BOM_QTYS '  query w parameters

        Me.DC_IP_BOMQ.Connection = Me.con

        Me.DT_IP_BOMQ.Connection = Me.con

        Me.DT_IP_BOMQ.SelectCommand = localCmd

        iRecs = Me.DT_IP_BOMQ.Fill()           ' <<< Error here

The error is

Code: Select all

Object SELECT * FROM TABLE(PROD10.GET_BOM_QTYS(ITEM,REV,QTY))  does not exist.

[Pinturiccio]

Your sample is not complete, it doesn't contain the pipelined function definition and we need the value of the SQL_BOM_QTYS variable.

If we understood you correctly, your pipelined function GET_BOM_QTYS returns the table (TAB_BOM_QTYS) which is a table of BOM_QTYS records. And you want this function to be the SelectCommand for OracleDataTable.

Here is our example of this situation:
We have used the BOM_QTYS type you specified in your post.
SQL Script:

Code: Select all

-- TAB_BOM_QTYS type
CREATE OR REPLACE TYPE TAB_BOM_QTYS AS TABLE OF BOM_QTYS;

--  Pipelined function GET_BOM_QTYS
CREATE OR REPLACE FUNCTION GET_BOM_QTYS (ITEM_NUM IN VARCHAR2, REV IN VARCHAR2, QTY IN NUMBER) RETURN TAB_BOM_QTYS PIPELINED AS
BEGIN
  FOR i IN 1 .. 10 LOOP
    PIPE ROW(BOM_QTYS(i,i, 'ITEM_NO', REV, 'DESCRIPTION', '333', i, QTY, QTY, ITEM_NUM));   
  END LOOP;

  RETURN;
END;

Visual Basic code:

Code: Select all

Sub Main()
    Dim conn As New OracleConnection("host=orcl1120;uid=scott;pwd=tiger;")
    conn.Open()
    Dim localCmd As New OracleCommand("GET_BOM_QTYS", conn)
    localCmd.CommandType = CommandType.StoredProcedure
    Dim paramITEM As OracleParameter = localCmd.Parameters.Add("ITEM", OracleDbType.VarChar)
    paramITEM.Value = "sItem"
    Dim paramREV As OracleParameter = localCmd.Parameters.Add("REV", OracleDbType.VarChar)
    paramREV.Value = "sRev"
    Dim paramQTY As OracleParameter = localCmd.Parameters.Add("QTY", OracleDbType.Number)
    paramQTY.Value = 5

    Dim dt As New OracleDataTable(localCmd)
    Dim iRecs As Integer = dt.Fill()
    Console.WriteLine(iRecs)

    For Each row As DataRow In dt.Rows
        For Each col As DataColumn In dt.Columns
            Console.Write(row(col))
            Console.Write("   ")
        Next
        Console.WriteLine()
    Next
End Sub