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,274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

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 77

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 77

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 77

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
 

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 77

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 77

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

752 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