Using Parameters with Oracle Packages. ODP .NET

Question

I have an Oracle function that returns a record set. I introduced parameters to the Oracle function and this is causing the front-end code to go haywire.

Here's my front-end code.

 OracleCommand od = oc.CreateCommand();
            od.CommandType = System.Data.CommandType.Text;
            od.CommandText = " select * from table(pkg_fetchPOInfo.getPORowsTable(:1,:2))";
            //od.CommandText = "pkg_fetchPOInfo.getPORowsTable";
            //od.CommandType = System.Data.CommandType.TableDirect;

            OracleParameter op1 = new OracleParameter();
            op1.ParameterName = "1";
            op1.OracleDbType = OracleDbType.Varchar2;
            op1.Direction = System.Data.ParameterDirection.Input;
            op1.Size = 6;
            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);

If I execute the query in the front-end SQLPLUS, I get a recordset. This code works if I remove the parameters from the package and the front-end code.

select * from table(pkg_fetchPOInfo.getPORowsTable('1007446','US')); --works in SQLPLUS.

select * from table(pkg_fetchPOInfo.getPORowsTable()); --works in both places.

Am I assigning the parameters incorrectly?

Answer 1

--create or replace type varcharTableType as table of varchar2 (4000); create or replace PACKAGE TESTP AS function TESTPIPE(nr in number, nr2 in number) return varchartabletype pipelined; END TESTP;

CREATE OR REPLACE PACKAGE BODY TESTP AS

function TESTPIPE(nr in number, nr2 in number) return varchartabletype pipelined AS CURSOR TESTPIPE_cur IS SELECT (level + 1) datam FROM dual connect by level < nr; vtt varchartabletype ;

BEGIN OPEN TESTPIPE_cur;

       LOOP
          FETCH testpipe_cur
          BULK COLLECT INTO vtt LIMIT nr2;

          FOR indx IN 1 .. vtt.COUNT
          LOOP
              Pipe Row ( vtt( indx ) )  ;
          END LOOP;

          EXIT WHEN testpipe_cur%NOTFOUND;
       END LOOP;

END TESTPIPE;

END TESTP;

... public static void pipeTest() { String conString = GetConnectionString(); OracleConnection _conn = new OracleConnection(conString); _conn.Open(); OracleCommand oCmd = new OracleCommand(); oCmd.CommandText = "begin open :crs for Select * from table(testp.testpipe(:nr,:nr2)); end;";

        oCmd.CommandType = CommandType.Text ;
        oCmd.Connection = _conn;

        OracleParameter crs = new OracleParameter();
        crs.OracleDbType = OracleDbType.RefCursor;
        crs.Direction = ParameterDirection.Output;
        crs.ParameterName = "crs";
        oCmd.Parameters.Add(crs);

        OracleParameter nr = new OracleParameter();
        nr.OracleDbType = OracleDbType.Int64;
        nr.Direction = ParameterDirection.Input ;
        nr.ParameterName = "nr";
        nr.Value = 25;
        oCmd.Parameters.Add(nr);

        OracleParameter nr2 = new OracleParameter();
        nr2.OracleDbType = OracleDbType.Int64;
        nr2.Direction = ParameterDirection.Input;
        nr2.ParameterName = "nr2";
        nr2.Value = 10;
        oCmd.Parameters.Add(nr2);

        using (OracleDataReader MyReader = oCmd.ExecuteReader())
        {
            int ColumnCount = MyReader.FieldCount;
            // get the data and add the row
            while (MyReader.Read())
            {
                String s = MyReader.GetOracleValue(0).ToString();
                Console.WriteLine(string.Format("i={0}", s));
            }
        }
        Console.ReadLine();
    }