ADO Parameters and OracleType Issues

[LadyDragon]

I am experiencing problems using ADO to run my Oracle function within a C# program. I am able to run the function just fine in oracle, but when running it via ADO, I consistently get the following msg:

Error MSG:
ORA-06550: line 1, column 18:\nPLS-00306: wrong number or types of arguments in call to 'POP_MO_ACCR'\n
ORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n

I do not know whether it is because I have incorrectly set the parameters or not - this function was working fine prior to the addition of the "percentage" parameter. I have verified that all the passed parameters actually have values and appear to be the correct type.

I'm going a bit nuts on this! I've looked through the forums, but wasn't able to find a specific enough issue that helped me, so I'm hoping someone out there can help me on this.

Thanks a bunch in advance!
Juls

PS - I've tried this with and without the p_edate parameter which has a default of NULL in the PLSQL function. Same error either way.

//Call from aspx.cs page:
//declarations
string edt = "1/31/2004";
DateTime x = Convert.ToDateTime(edt);
string node_id="274";
string scenarioName="My Scenario";
int pct = 50;
//call
ans.RecalculateAccrual(x,node_id,scenarioName.ToUpper(), pct);

//C# Function using ADO:

		public void RecalculateAccrual(DateTime beginning_date, string node_id, string scenario_name, int percentage)
		{
			OracleConnection sqlcon = null;
			try
			{
				sqlcon = new OracleConnection(m_connectionString);
				sqlcon.Open();
				OracleCommand cnCommand = new OracleCommand();
				cnCommand.Connection = sqlcon;
				cnCommand.CommandText ="ves.ves_monthly.POP_MO_ACCR";
				cnCommand.CommandType = CommandType.StoredProcedure;

				OracleParameter[] parms = 
				{
					new OracleParameter("P_BDATE",OracleType.DateTime),
					new OracleParameter("P_NODE_ID",OracleType.VarChar,15),
					new OracleParameter("P_SNAME",OracleType.VarChar,30),
					new OracleParameter("P_PCT",OracleType.Number),
					new OracleParameter("retVal",OracleType.VarChar,255)
				};

				parms[0].Direction = ParameterDirection.Input;
				parms[0].Value = beginning_date;
				parms[1].Direction=ParameterDirection.Input; 
				parms[1].Value = node_id;
				parms[2].Direction = ParameterDirection.Input;
				parms[2].Value = scenario_name;
				parms[3].Direction = ParameterDirection.Input;
				parms[3].Value = percentage;
				parms[4].Direction = ParameterDirection.ReturnValue;

				cnCommand.Parameters.Add(parms[0]);
				cnCommand.Parameters.Add(parms[1]);
				cnCommand.Parameters.Add(parms[2]);
				cnCommand.Parameters.Add(parms[3]);
				cnCommand.Parameters.Add(parms[4]);

				cnCommand.ExecuteNonQuery();

//PL/SQL Function 
FUNCTION pop_mo_accr (p_bdate DATE, p_node_id VARCHAR2
                      , p_sname VARCHAR2 			 ,p_accr_adj_pct NUMBER DEFAULT 100.00
, p_edate DATE DEFAULT NULL
) RETURN VARCHAR2

 

[cappmgr]

You have 6 parameters in you SQL counting the return VARCHAR2, It looks like you forget to add the parm for p_edate,
hth
Marty

 

[LadyDragon]

Actually, I'm modifying already existing code where the p_edate was not declared. The p_edate is defaulted in Oracle, so it should be ok not having a parameter passed to it (it was OK prior to my tinkering with it). I've tried adding the following:

new OracleParameter("P_EDATE",OracleType.DateTime),

and

parms[4].Direction = ParameterDirection.Input;
parms[4].Value = System.DBNull.Value;

but I get the same error with or without the p_edate declared. The only change I've made is the addition of the percentage field which was also added in Oracle.

Thanks,
Juls

 

[cappmgr]

I might be wrong but Oracle wants 6 parms 0-5.

 

[LadyDragon]

I've commented out the percentage parameter and it executes fine - the percentage parameter has a default set up as 100. I want to be able to pass something other than that, however, which is where the problem arises.

This runs fine with only 4 parameters set and calculates using the default date and pct set in the Oracle Proc:

parms[0].Direction = ParameterDirection.Input;
parms[0].Value = beginning_date;
parms[1].Direction=ParameterDirection.Input; 
parms[1].Value = node_id;
parms[2].Direction = ParameterDirection.Input;
parms[2].Value = scenario_name;
parms[3].Direction = ParameterDirection.ReturnValue;

cnCommand.Parameters.Add(parms[0]);
cnCommand.Parameters.Add(parms[1]);
cnCommand.Parameters.Add(parms[2]);
cnCommand.Parameters.Add(parms[3]);

I think I'm not setting the percentage field correctly. It is a NUMBER in Oracle and declared as an int in C#. Is there another required Parameter setting I need to use?

Thanks!
Juls

 

[LadyDragon]

After beating my head against the wall more than a few times and wasting a couple of hours, I realized I put P_PCT in the ADO function instead of p_accr_adj_pct which is the Oracle variable name.

***DOH!!!!***

Thanks all!

 

[cappmgr]

DragonLady,
I am glad you found it.
Here is how I populate my parms. It is less typing and no indices to keep track of. All info for a parm is on one line. It might help spot typos easier. No direction is needed for input, and I have never had to declare the length.

		OracleCommand myCommand = new OracleCommand("PKG_OMHCBR.DBF_GetPropertyWorkSheetReport",myConnection);
		myCommand.CommandType = CommandType.StoredProcedure;
		myCommand.Parameters.Add(new OracleParameter("a_i_YearNo", OracleType.Number)).Value = 2003;
		myCommand.Parameters.Add(new OracleParameter("a_s_YearType", OracleType.Char)).Value = "LFYD";
		myCommand.Parameters.Add(new OracleParameter("a_s_Provider", OracleType.Char)).Value = "19340";
		myCommand.Parameters.Add(new OracleParameter("a_s_OMHCountyCode", OracleType.Char)).Value = "31";
		myCommand.Parameters.Add(new OracleParameter("r_o_ReturnCursor", OracleType.Cursor)).Direction = ParameterDirection.Output;

Marty