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