5 Replies Latest reply: Apr 29, 2011 10:46 AM by 858659 RSS

    PLS-00306 - wrong number or types of arguments in call......

      Hello All:

      I know the technical reason as to why I am getting this error (my arguments are of the wrong type), but I do not know why it is occuring. It has something to do with my pPramArray array / Varchar2 value I am trying to set.

      public void GetAppParams(string Family, string Application, string Param, ref string[] ParamArray)

      Basically, I have the above web-service function header, that is going to call a Stored Procedure. ParamArray is an output parameter from the Stored Procedure, but is always passed to the function as NULL. I can’t set OracleDBType.Array as the ParameterType, as Oracle doesn’t have a native type of Array (I get a binding issue if I try). I found some literature on-line to do the following instead (which I am not overly pleased with, as what I am to do if the parameter list is above 100 long, or less than 100):

      const int numToFetch = 100;
      oCommand.Parameters.Add("pPramArray", OracleDbType.Varchar2).Value = System.DBNull.Value;
      oCommand.Parameters["pPramArray"].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      oCommand.Parameters["pPramArray"].Size = 100;
      oCommand.Parameters["pPramArray"].ArrayBindSize = new int[numToFetch];
      oCommand.Parameters["pPramArray"].Direction = System.Data.ParameterDirection.Output;

      However: this is giving me the following error: PLS-00306 - wrong number or types of arguments in call.....

      pPramArray is defined as following in the Oracle Stored Procedure:

      pPramArray OUT OutParamArray
      The OutParamArray is defined as below:
      TYPE OutParamArray IS TABLE OF VARCHAR2(200);

      Oracle doesn't have a native type of Array, so if I just set OracleDBType.Array as the parameter type in my .Net code, I get a binding error. If I try this method, that I have found online that others say is supposed to work, I get the error posted above.

      How can I simply get an array returned from an oracle stored procedure (or technically, a collection because it doesn't have a native type array) to a .Net array in my code?

      Any help is greatly appreciated.
        • 1. Re: PLS-00306 - wrong number or types of arguments in call......
          gdarling - oracle

          Have you already taken a look at the example on your hard drive.. %ORACLE_HOME%\ODP.NET\samples\2.x\AssocArray . It has IN, IN OUT, and OUT Associative Array Parameters.

          I'd think your problem probably though is that you've failed to include "INDEX BY BINARY_INTEGER" in the type declaration. Try changing it to
          TYPE OutParamArray IS TABLE OF VARCHAR2(200) index by binary_integer;
          Here's a small complete sample I had laying around, and I can easily reproduce your complaint if I leave off " index by binary_integer"

          Hope it helps,
          create or replace package mypack5 as
          TYPE v2array is table of varchar2(4000) index by binary_integer;
          PROCEDURE test_it(thearray out v2array);
          CREATE or replace PACKAGE BODY MYPACK5 AS
          PROCEDURE test_it(thearray out v2array) IS
              thearray(3):='from the stored procedure';
          using System;
          using System.Data;
          using Oracle.DataAccess.Client;
          using System.Text;
          public class out_v2_tab
              public static void Main()
                  using (OracleConnection con = new OracleConnection("data source=orcl;user id=scott;password=tiger;"))
                      using (OracleCommand cmd = new OracleCommand("mypack5.test_it", con))
                          cmd.CommandType = CommandType.StoredProcedure;
                          OracleParameter Param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
                          Param1.Direction = ParameterDirection.Output;
                          Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                          Param1.Size = 3;
                          Param1.ArrayBindSize = new int[]{4000,4000,4000};
                          for (int i = 0; i < 3; i++)
                              Console.WriteLine("Param1[{0}] = {1} ", i,
                                (cmd.Parameters[0].Value as Array).GetValue(i));
          • 2. Re: PLS-00306 - wrong number or types of arguments in call......

            Thank You. That is what I needed in the stored procedure.

            But one thing I cannot find, is how to convert an OracleString[] to a .Net string[] once this Stored Procedure is returned.

            I mean, I know I could technically loop through the OracleString[] and do it one at a time, but I would think there is a more efficient way to accomplish this. The example doesn't have this in it, and unable to find something online (as of yet, but still looking).

            Any efficient way of doing this without a massive loop?

            • 3. Re: PLS-00306 - wrong number or types of arguments in call......
              gdarling - oracle
              Param1.OracleDbTypeEx = OracleDbType.Varchar2;
              works when I add it to my example above at least, and causes Param1.Value to return string[] instead of OracleString[].


              Hope it helps,
              • 4. Re: PLS-00306 - wrong number or types of arguments in call......

                Thank you so much. I would never have found that without your help. I can't tell you how much I appreciate your help, knowledge and willingness to share it.

                • 5. Re: PLS-00306 - wrong number or types of arguments in call......
                  I wanted to thank you guys for this posting as I have just spent the last few days trying to figure out the disconnect between Oracle and my .NET application.

                  You see I have a stored procedure that has two OUT params. One was a ref cursor and the other was an plsqlassocitative array. While the cursor results were being pumped into the dataset, the array was not. I still do not know why this is the case but the fact that I found all my array results pumped back into the array parameter, I was able to manually access the parameter value and put it in my dataset manually with my other table.

                  As one solution sometimes present another obstruction, your post also helped me with the needed syntax for iterating through the array parameter Values.

                  Thanks for the time you took assisting with this post. Its a gift that keeps on giving.