Solved

Getting "ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call" in ASP.NET Web Applications

Posted on 2010-11-30
14
2,135 Views
Last Modified: 2012-05-10
Hi Experts,

I am getting the following error when I try to retrieve some values in the Data Reader.

{"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'RETRIEVECURRENTGROUPS'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n" }

Please help me to get rid of this error. Also Please do get back to me if you need any more details abut this.

thx
Dil.
In Page_Load()

==============

SpecialUserCheck(Session["user"].ToString());



Member Functions

================

private void SpecialUserCheck(string rUserId)

		{	

			DoCheck("RetrieveCurrentGroups","USER_GROUP_NAME","USER_GROUP_ID",rUserId);		

		}

		private void DoCheck(string spName,string TextField,string ValueField,string UserId)

		{

			ArrayList spArrayList = new ArrayList();

			bool spUser;

			spArrayList.Add(UserId);

			spArrayList.Add("");

			System.Data.OracleClient.OracleDataReader vcdr;

			vcdr = objDataAccess.GetDataReader(spName,spArrayList);		

			while (vcdr.Read())

			{

				string specialUsers = "VENDOR CATEGORY";

				string tempSpecialUser = vcdr[""].ToString();

				if (specialUsers == tempSpecialUser)

				{

					spUser = true;

					Session["specialuser"] = specialUsers;

					break;

				}

				else

				{

					spUser = false;

				}

			}

			vcdr.Close();

		}



Data Access

===========

public OracleDataReader GetDataReader

			(string ProcName , ArrayList objAL)

		{

			//*********************************************************************

			//* Purpose: returns DataReader for one or multi datatables           *

			//* Input parameters:                                                 *

			//*         ProcName ---StoredProcedures name                         *

			//*         objAL---- parameteres in ArrayList						  *

			//* returns :                                                         *                          *

			//*                    DataReader Object contains data                   *

			//* *******************************************************************                                          DataSet dstEorder ;

			try

			{

				String strConn = ConfigurationSettings.AppSettings["strConnOracleDB"];

				conn = new OracleConnection(strConn);

				//OraPkgName = "RDA_SUPPLIER_MAINT_UTILITY_PKG";

				cmd = new OracleCommand(OraPkgName + ProcName,conn);

				cmd.CommandType = CommandType.StoredProcedure;

				

				if (objAL.Count > 0)

					BuildParameters(ref cmd, ref objAL, ref ProcName);

				conn.Open();

				//OracleDataReader dr;

				dr = cmd.ExecuteReader();



				return dr;

			}

			catch ( Exception objError)

			{

				//write error to the windows event log                  

				WriteToEventLog(objError);

				throw;                                       

			}

		}

Open in new window

0
Comment
Question by:dileepav
  • 7
  • 6
14 Comments
 

Author Comment

by:dileepav
ID: 34240779
Here is the Procedure Code I am using for this.
=======================================
Declaration

===========

PROCEDURE RetrieveCurrentGroups

          (i_USER_ID IN VARCHAR2,

           CURRENT_GROUPS OUT t_cursor);



Body

====

PROCEDURE RetrieveCurrentGroups

          (i_USER_ID IN VARCHAR2,

           CURRENT_GROUPS OUT t_cursor)

IS

BEGIN

          OPEN CURRENT_GROUPS FOR

          select ugm.User_Group_Id,User_Group_Name

          from RDA_SUPPMAINT_USER_GROUPS ug,RDA_SUPPMAINT_USER_GRP_MEMBERS ugm

          where ug.USER_GROUP_ID=ugm.USER_GROUP_ID and ugm.USER_ID=UPPER(i_USER_ID)

          order by USER_GROUP_NAME;



END RetrieveCurrentGroups;

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34240801
Make sure the parameters match in numer, direction and data type.

Can you post the code for BuildParameters.

also describe the Oracle Procedure RETRIEVECURRENTGROUPS.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34240815
Caught typing.

Still would like the code for BuildParameters.

Did you create t_cursor as a global type or is it created inside the package?
0
 

Author Comment

by:dileepav
ID: 34240857
This procedure has been created in side a package:

Build Parameter Code:
=================
public void BuildParameters(ref OracleCommand Command , ref ArrayList ParmValues, ref string SPName)

		{

			//*********************************************************************

			//* Purpose:Build the parameter collection to be passed to Stored Proc*

			//* Input parameters:  Command---Oracle Command	by reference          *

			//*                    ParmValues-----ArrayList containing parm values*

			//*                    SPName----String Stored Proc Name              *

			//* returns :          nothing                                        *

			//* *******************************************************************  

			XmlDocument objDOM = new XmlDocument();

			XmlNodeList objParameterNodes;

			OracleParameter objParameter;

			OracleType objOracleDataType;

			ParameterDirection objParameterDirection;



			object objParameterValue;

			string strXPathQuery;

			string strParameterName;

			int intParameterSize;

			int intParamCounter = 0;

			bool blnIsNullable;

			byte bytPrecision;

			byte bytScale;



			try

			{

			    mstrSPConfigXMLFile = ConfigurationSettings.AppSettings["strSPConfigXMLFile"];

				objDOM.Load(mstrSPConfigXMLFile);

			}

			catch (XmlException objXMLException)

			{

				//write error to the windows event log                  

				WriteToEventLog(objXMLException);

				throw;                                       

			}

			

			try

			{

				Command.Parameters.Clear();

				strXPathQuery = "/StoredProcedures/StoredProcedure[@name='"

					+ SPName + "']/Parameters/Parameter";

				objParameterNodes = objDOM.SelectNodes(strXPathQuery);





				if (objParameterNodes.Count == 0)

				{

					strXPathQuery = "/StoredProcedures/StoredProcedure[@altname='"

						+ SPName + "']/Parameters/Parameter";

					objParameterNodes = objDOM.SelectNodes(strXPathQuery);

					

				}

				//Loop through the stored procedure <Parameter> elements

				foreach (XmlNode objNode in objParameterNodes)

				{

					//Get the attribute values for the <Parameter> element.

					//name

					try

					{

						strParameterName = objNode.Attributes.GetNamedItem("name").Value;

					}

					catch

					{

						throw new Exception("Error getting the 'name' attribute for the <Parameter> element.");

					}



					//size

					try

					{

						intParameterSize = int.Parse((objNode.Attributes.GetNamedItem("size").Value));

					}

					catch

					{

						throw new Exception("Error getting the 'size' attribute for the <Parameter> element.");

					}

					//datatype

					try

					{

						string dtype = objNode.Attributes.GetNamedItem("datatype").Value;

						objOracleDataType = GetOracleDataType(ref dtype );

					}

					catch

					{

						throw new Exception("Error getting the 'datatype' attribute for the <Parameter> element.");

					}

					//direction

					try

					{	

						string dir = objNode.Attributes.GetNamedItem("direction").Value;

						objParameterDirection = GetParamDirection(ref dir);

					}

					catch

					{

						throw new Exception("Error getting the 'direction' attribute for the <Parameter> element.");

					}

					//Get the optional attribute values for the <Parameter> element



					//isNullable

					try

					{

						blnIsNullable = bool.Parse((objNode.Attributes.GetNamedItem("isNullable").Value));

					}

					catch

					{

						blnIsNullable = false;

					}



					//precision

					try

					{

						bytPrecision = byte.Parse((objNode.Attributes.GetNamedItem("precision").Value));

					}

					catch

					{

						bytPrecision = 0;

					}



					//scale

					try

					{

						bytScale = byte.Parse(objNode.Attributes.GetNamedItem("scale").Value);

					}

					catch

					{

						bytScale = 0;

					}



					//Get the value of the parameter.  This could be passed in from 

					//one of the public methods, or it could be an attribute value 

					//in the XML <Parameter> element.

					try

					{

						//Check the XML first

						objParameterValue = objNode.Attributes.GetNamedItem("value").Value;

					}

					catch

					{

						//Now check the ParamValues ArrayList

						try

						{

							objParameterValue = ParmValues[intParamCounter];

						}

						catch

						{

							throw new Exception("Error getting the corresponding value for the "

								+ strParameterName + " <Parameter> element.");

						}

					}



					//Create the parameter object.  Pass in the name, datatype,

					//and size to the constructor.

					objParameter = new OracleParameter(strParameterName, objOracleDataType, intParameterSize);



					//Set the direction of the parameter.

					objParameter.Direction = objParameterDirection;



					//If the optional attributes have values, then set them.

					if (bytPrecision > 0) 

						objParameter.Precision = bytPrecision;

					

					if (bytScale > 0 )

						objParameter.Scale = bytScale;

					



					//Set the value of the parameter

					objParameter.Value = objParameterValue;



					//Add the parameter to the SQLCommand's parameter collection

					Command.Parameters.Add(objParameter);



					//Increment the counter

					intParamCounter = intParamCounter + 1;



        

				}

			}

			catch ( Exception objError)

			{

				//write error to the windows event log                  

				WriteToEventLog(objError);

				throw;                                       

			}

		}

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34240890
wow....  that's some modular code.  Check the XML for that procedure and see what is there as far as parameters.
0
 

Author Comment

by:dileepav
ID: 34240937
I am getting the mentioned exception / error here in the following function:

public OracleDataReader GetDataReader

exactly in the following line:
dr = cmd.ExecuteReader();

throwing an exception and the control is going here:
catch ( Exception objError)
                  {
                        //write error to the windows event log                  
                        WriteToEventLog(objError);
                        throw;                                      
                  }

not returning the data reader.

thx
Dil.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34240960
The PLS-00306 message is coming from calling the RETRIEVECURRENTGROUPS procedure.

So whereever you are calling that, there's a problem with the number, datatype or direction of the parameters you are passing it.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dileepav
ID: 34240995
here is the package body:
=====================
PROCEDURE RetrieveCurrentGroups
          (i_USER_ID IN VARCHAR2,
           CURRENT_GROUPS OUT t_cursor)
IS
BEGIN
          OPEN CURRENT_GROUPS FOR
          select ugm.User_Group_Id,User_Group_Name
          from RDA_SUPPMAINT_USER_GROUPS ug,RDA_SUPPMAINT_USER_GRP_MEMBERS ugm
          where ug.USER_GROUP_ID=ugm.USER_GROUP_ID and ugm.USER_ID=UPPER(i_USER_ID)
          order by USER_GROUP_NAME;
END RetrieveCurrentGroups;
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 34241022
I saw that above.  is t_cursor declared inside the package or as a global database type and how is it declared?

Also verify the parameters in the XML file to make sure they match.
0
 

Author Comment

by:dileepav
ID: 34241026
Here is the body,
PROCEDURE RetrieveCurrentGroups
          (i_USER_ID IN VARCHAR2,
           CURRENT_GROUPS OUT t_cursor);

Wondering hwat is the mistake with the procedure body and declaration.

regards
Dil.
0
 

Author Comment

by:dileepav
ID: 34241053
XML file is a common one and is working for other procedures.

thx
Dil.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34241088
The error message is pretty specific.

Again: Check the declared data types, number of parameters in there and direction for this specific procedure.

There has to be a mis-match between what you are passing Oracle and what it is expecting.
0
 
LVL 4

Accepted Solution

by:
pepepaco earned 300 total points
ID: 34241246
the name for the cursor paramenter CURRENT_GROUPS must match the XML. check the XML again to see whats the name of the ref cursor type.

regards
0
 

Author Closing Comment

by:dileepav
ID: 34308113
Thx, Experts, the issue has been resolved.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now