Have you worked with Oracle Pipelined functions?

[abhijit]

I am facing an issue trying to use variable binding with a pipeline function in Oracle. I am using ODP .NET for connecting to the database.

If you want to be familiar with PIPELINED functions, you can read this blog.

I have very similar code with a difference. My function takes in two parameters that I need to pass to get the table. This is working in SQLPLUS without any issues.

In my C# code, however things change. My function no longer returns a recordset (data reader), if I use the standard method of assigning the parameters.

The code will work if I concat the variables in a string.

Here is the example that doesn't work.

static OracleDataReader fetchData(OracleConnection oc, string strPONumber)
        {
            try
            {
                OracleCommand od = oc.CreateCommand();
                od.CommandType = System.Data.CommandType.Text;
                od.CommandText = "select * from table(pkg_fetchPOInfo.getPORowsTable(:1,:2))";
                OracleParameter op1 = new OracleParameter();
                op1.ParameterName = "1";
                op1.OracleDbType = OracleDbType.Varchar2;
                op1.Direction = System.Data.ParameterDirection.Input;
                op1.Size = 7;
                op1.Value = strPONumber;
                od.Parameters.Add(op1);
                
                OracleParameter op2 = new OracleParameter();
                op2.ParameterName = "2";
                op2.OracleDbType = OracleDbType.Varchar2;
                op2.Direction = System.Data.ParameterDirection.Input;
                op2.Size = 3;
                op2.Value = "US";
                od.Parameters.Add(op2);
 
                OracleDataReader or = od.ExecuteReader();
                return or;
            }
            catch (Exception e)
            {
                Console.WriteLine("Error " + e.ToString());
                return null;
            }
        }

Here is the example that does.

static OracleDataReader fetchData(OracleConnection oc, string strPONumber)
        {
            try
            {
                OracleCommand od = oc.CreateCommand();
                string formSQL = "Select * from table(pkg_fetchPOInfo.getPORowsTable('"+strPONumber+"','US'))";
                od.CommandType = System.Data.CommandType.Text;
                od.CommandText = formSQL;
                OracleDataReader or = od.ExecuteReader();
                return or;
            }
            catch (Exception e)
            {
                Console.WriteLine("Error " + e.ToString());
                return null;
            }
        }

[leinad31]

No error message written to console?

[abhijit]

No error message written to console?

Yes, there is an error code being returned.

The error being returned is ORA-22905: cannot access rows from a non-nested table item.

[leinad31]

Might as well post code of pipeline function

[abhijit]

The function is part of a package. The function looks something like this.

create or replace package pkg_fetchPOInfo is
TYPE PO_HEADER_ROW IS RECORD
(      PO_ID INT, --Reference field
       PO_NUMBER VARCHAR2(22), --Reference field
       BRANCH_ID VARCHAR2(3), -- Contains the Operating Unit Id.
       PARTY_NAME VARCHAR2(200), --Could contain VENDOR or MANUFACTURER
       DATE_ORDERED DATE,
       UNIT_NUMBER VARCHAR2(6), --reference to unit number.
       UNIT_HEIGHT NUMBER(15),
       UNIT_WIDTH NUMBER(15),
       UNIT_LENGTH NUMBER(15),
       AMOUNT NUMBER(22,2)
);
TYPE PO_ROWS_TAB IS TABLE OF PO_HEADER_ROW;
function getPORowsTable(iPO_NUMBER IN VARCHAR2, iCountryCode IN VARCHAR2) return PO_ROWS_TAB pipelined;
end pkg_fetchPOInfo;
/
CREATE OR REPLACE PACKAGE BODY pkg_fetchPOInfo
IS
function getPORowsTable(iPO_NUMBER IN VARCHAR2, iCountryCode IN VARCHAR2) return PO_ROWS_TAB
  pipelined
  is
  v_row PO_HEADER_ROW;
  cursor curGetPOInfo is
  SELECT * FROM  get_POMANU_VW_US WHERE po_number = iPO_NUMBER;
begin  
if iCountryCode = 'US' then  
begin
  for x_rec in curGetPOInfo
  loop
      v_row.PO_ID := x_rec.po_id;
      v_row.PO_NUMBER := x_rec.po_number;
      v_row.PARTY_NAME := x_rec.MFG_NAME;   
      v_row.DATE_ORDERED := x_rec.ordered_date;
      v_row.BRANCH_ID := x_rec.gl_branch; --Fin Org Unit code.
      v_row.UNIT_NUMBER := x_rec.unit_number;
      v_row.UNIT_HEIGHT := x_rec.unit_length;
      v_row.UNIT_LENGTH := x_rec.unit_length;
      v_row.UNIT_WIDTH := x_rec.unit_WIDTH;
      v_row.AMOUNT := x_rec.po_amount;
    pipe row ( v_row );
  end loop;
  return;
end;
else 
begin
       --Stub for other countries.
       null;
end;
end if;
end getPORowsTable;
end; /*Always mark the package end*/
/

[leinad31]

What's strange is that it works in some scenarios, it shouldn't work at all... you didn't use sys_refcursor, nor database scope types (you declared within package, type not in all_types view).

It may be client-side SQL parsing related since it runs in sqlplus... check version of libraries you are using to connect to oracle database. Make sure it is the latest version.

[abhijit]

A variation works for me. The variation is to wrap the function in a stored proc that returns a REFCURSOR. The I use the SYSREFCURSOR datatype to fetch the output.

I suppose, the only option for me is to use wrap this function with a stored procedure.