Pipelined Table returning function (NON-Linq)
Posted: Thu 17 Apr 2014 19:58
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 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
The VB (2012) code in question is
The error is
(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)
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));
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.