Skip to Main Content
Forums

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

721872Jun 23 2010 — edited Apr 29 2011
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.
This post has been answered by gdarling - oracle on Jun 23 2010
Jump to Answer

Comments

gdarling - oracle
Answer
Hi,

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,
Greg
/*

create or replace package mypack5 as
TYPE v2array is table of varchar2(4000) index by binary_integer;
PROCEDURE test_it(thearray out v2array);
END;
/
CREATE or replace PACKAGE BODY MYPACK5 AS
PROCEDURE test_it(thearray out v2array) IS
 begin
    thearray(1):='hello';
    thearray(2):='world'; 
    thearray(3):='from the stored procedure';
 END; 
END;
/
*/

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;"))
        {
            con.Open();

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

                cmd.ExecuteNonQuery();
                for (int i = 0; i < 3; i++)
                    Console.WriteLine("Param1[{0}] = {1} ", i,
                      (cmd.Parameters[0].Value as Array).GetValue(i));
            }
        }
    }
}
Marked as Answer by 721872 · Sep 27 2020
721872
Greg:

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?

Thanks
Andy
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[].

http://download.oracle.com/docs/html/E15167_01/OracleParameterClass.htm#CHDJHDGE

Hope it helps,
Greg
1 person found this helpful
721872
Greg:

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.

Thanks
Andy
858659
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.

Kathleen
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 27 2011
Added on Jun 23 2010
#odp-net
5 comments
4,849 views