473,837 Members | 1,759 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

PLS-00306 Wrong number of types of arguments in call to 'Stored Procedure' {Pls help}

3 New Member
I am getting this error when I call a stored procedure from my C# code. It is a simple stored procedure with six params that inserts data :

PROCEDURE LHD_SUR_ADMNEXP _HDR_INS (
p_ORG_ID Varchar,
p_YEAR Varchar,
p_DATA_NOT_APPL ICABLE_ADMNEXP Number,
p_DATA_NOT_APPL ICABLE_OPENENR Number,
P_VALDN_EXCEPN_ MSG varchar,
p_RETVAL OUT NUMBER
)
IS

REQUIRED EXCEPTION;
BEGIN
If (p_ORG_ID IS NULL) OR Length(p_ORG_ID ) = 0 Then
Raise REQUIRED;
End If;

If (p_YEAR IS NULL) OR Length(p_YEAR) = 0 Then
Raise REQUIRED;
End If;

insert into lhd_sur_admn_op nenr_exp_hdr
(org_id,
year,
data_not_applic able_admexp,
data_not_applic able_opnenr,
valdn_excepn_ms g)
values
(p_org_id,
p_year,
p_DATA_NOT_APPL ICABLE_ADMNEXP,
p_DATA_NOT_APPL ICABLE_OPENENR,
p_valdn_excepn_ msg
);

p_retval := 1;

EXCEPTION
WHEN REQUIRED THEN --params missing
p_RETVAL := -999;
WHEN NO_DATA_FOUND THEN --data not found
p_RETVAL := -100;

END LHD_SUR_ADMNEXP _HDR_INS;
*************** *************** *************** *********
And here is the C# code
Cmd.Parameters. Clear() 'Initialize Parameters collection
Cmd.Parameters. Add(New OracleParameter ("p_ORG_ID", OracleType.VarC har, 22)).Value = clsLHSurveyComm on.gOrgid '"11568" '
Cmd.Parameters( 0).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("p_YEAR", OracleType.VarC har, 4)).Value = clsLHSurveyComm on.gPlanYear '"2009" '
Cmd.Parameters( 1).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("p_DATA_NOT_AP PLICABLE_ADMNEX P", OracleType.Int1 6, 1)).Value = Convert.ToInt16 (chkDataNotAppl icableAdmnExp.C hecked)
Cmd.Parameters( 2).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("p_DATA_NOT_AP PLICABLE_OPENEN RL", OracleType.Int1 6, 1)).Value = Convert.ToInt16 (chkDataNotAppl icableOpenEnrl. Checked)
Cmd.Parameters( 3).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("P_EXCEPTION_M ESSAGE", OracleType.VarC har, 500)).Value = IIf(Not (String.IsNullO rEmpty(txtExcep tionMessage.Tex t)), txtExceptionMes sage.Text, System.DBNull.V alue)
Cmd.Parameters( 4).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("p_RETVAL", OracleType.Floa t, 4)).Direction = ParameterDirect ion.Output

Cmd.ExecuteNonQ uery()

*************** *************** *************** ********
I am not sure if the column types of the destination table matter, but here is that info as well.
"ORG_ID" VARCHAR2(22) NOT NULL ENABLE,
"YEAR" VARCHAR2(4) NOT NULL ENABLE,
"DATA_NOT_APPLI CABLE_ADMEXP" NUMBER(1,0),
"VALDN_EXCEPN_M SG" VARCHAR2(500),
"DATA_NOT_APPLI CABLE_OPNENR" NUMBER(1,0),

Any help would be really appreciated, as I have spent several hours on this error and cannot seem to figure it out.

This question has also been asked on this website before, but it does not have any solution to it.
http://bytes.com/topic/c-sharp/answers/260973-problem-when-accessing-oracle-procedure-varchar2#
Jan 4 '10 #1
5 8595
Plater
7,872 Recognized Expert Expert
Cmd is an oracle command object with the command type set to stored procedure?
Jan 4 '10 #2
kaushal30
3 New Member
Yes, ... Command is a System.Data.Ora cleClient.Oracl eCommand object, and the type is set to Stored procedure.
Jan 4 '10 #3
Plater
7,872 Recognized Expert Expert
I am not sure what to suggest, I have heard of this trouble before.

You could try making a new stored procedure that only takes one parameter and see if you can get that working, then keeping adding parameters to the storedproc untill you can no longer make it work in code?
Jan 5 '10 #4
kaushal30
3 New Member
Plater, Your suggestion was brilliant. Thanks so much.

It helped me narrow down the problem to two parameters that were the cause of the issue. You would not believe, it was the name of those two parameters (3rd and 4th parameters are not named exactly as in the signature of the Stored Procedure). Oracle 10i is definitely throwing a wrong description of the error, partly to blame.

The second conclusion is also that Oracle.DataAcce ss.Client is not sensitive to parameter names. Initially we were using this version and I was not getting these errors and saving successfully to the DB. Only after changing to System.Data.Ora cleClient, this issue surfaced. Thanks again.
Jan 5 '10 #5
Sourabh4u
1 New Member
@Plater
I also had same problem. I have tried following code to create parameter to command object. Its working.. i guess there are few incompatibility between .net & oracle. Oracle sometime cant recognize the parameters..


OleDbParameter param = new OleDbParameter( );
param.Parameter Name = "TPSG_ID1";
param.Value = Int32.Parse(trv Mnu.SelectedNod e.Value);
param.Direction = ParameterDirect ion.Input;
param.OleDbType = OleDbType.Integ er;
DBCmd.Parameter s.Add(param);
Jan 4 '12 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
8038
by: Java Guy | last post by:
I'm trying to view a web page. IE tells me there are (Java?) errors on the page. Here they are: Line: 15 Char: 7 Error: Wrong number of arguments or invalid propert assignment Code: 0 URL: http://(address.of.my.webcam):port/LiveView.html and
6
20535
by: tinareed | last post by:
Please help out a newbie. I've copied a complicated query and am trying to modify it for mailing labels. First, last name and primary address are the fields. Primary address would be either a home or work address. The prior query was made to list primary phone number of which the number was just one field (ex: 213-300-0000). However, the primary address would include street address, city, state and zip for a total of 4 fields. I receive...
1
3060
by: alhalayqa | last post by:
hi guys, I have XML node object o,and I am trying to know if getAttribute is a function in the object o, by using if(typeof o.getAttribute == 'function'), now firefox returns "function", but IE returns it as "unknown", when I tried to access the property as o.getAttribute , it through an exception as : "Wrong number of arguments or invalid property assignment".
3
9051
by: vunet | last post by:
Hello, I've just installed ASPXMLRPC library and testing their main function: xmlRPC ("URL", "command_name", params) The function converts all parameters to XML, sends a request to third- party server and receives XML response. It seems to be getting response fine. But when I call the function like this: myresp = xmlRPC ("http://someurl.com", "get", paramList)
2
1258
by: Deven Oza | last post by:
Create PROCEDURE NextGen_ConvertDate_Deven AS declare @DLSStart smalldatetime set @DLSStart = (select dbo.fn_GetDaylightSavingsTimeStart(convert(varchar,datepart(year,getdate())))) /* please Note this funcution (fn_GetDaylightSavingsTimeStart) returns values is 2008-03-09 02:00:00 */ update NexGen_New_Losses_Deven set F37_CLAIM_ENTERED_S_DT_conv =
3
7700
akashazad
by: akashazad | last post by:
Hi Friends My question is "can we call Stored Procedure from a User Defined Function in SQL" If yes then pl tell me how? If No then pl tell me why not ? While I was trying finding about the this question on internet I m getting both kind of answers hence I am confused . Pl help,Thanx in advance
2
3570
by: Steven Hayes | last post by:
Thanks for any help I'm new at this. I have the following excel statement: =IF(B131="", "Input UFMIP", IF((B131-B133)<0, 0, IF(B131>0, B131-B133, 0))) I tried unsuccessfully to convert to access: =iff(=" ","Input UFMIP", iif(-,0,0, iff(-,0))) I rec'd this error message:
2
4874
by: Dave Smith | last post by:
Hi everyone again. I’m working on making my database roll with each month that changes. Below is what I have so far, and was hoping someone could help me with the rest. The error message I’m getting is: The expression you entered has a function containing the wrong number of arguments. Jan_Loan_Amount: Sum(IIf( Between DateSerial(Year(Date()),Month(Date())-13,1) And DateSerial(Year(Date()),Month(Date())-12,0),)And Trim()>"?9?",1,0)))
0
4288
by: Peter Moh | last post by:
I get wrong number of arguments (0 for 1) error. The create action works with regular validates but authenticate/salt processing. Please point out what does the message mean? The create action works fine without calling and authenticate(user_id, password). require 'digest/sha1' class Ctkuser < ActiveRecord::Base
0
9846
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10579
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10633
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10276
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9412
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7007
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5674
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4479
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4050
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.