dileepav
asked on
Getting "ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call" in ASP.NET Web Applications
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'\nO RA-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.
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'\nO
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;
}
}
Make sure the parameters match in numer, direction and data type.
Can you post the code for BuildParameters.
also describe the Oracle Procedure RETRIEVECURRENTGROUPS.
Can you post the code for BuildParameters.
also describe the Oracle Procedure RETRIEVECURRENTGROUPS.
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?
Still would like the code for BuildParameters.
Did you create t_cursor as a global type or is it created inside the package?
ASKER
This procedure has been created in side a package:
Build Parameter Code:
=================
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;
}
}
wow.... that's some modular code. Check the XML for that procedure and see what is there as far as parameters.
ASKER
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.
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.
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.
So whereever you are calling that, there's a problem with the number, datatype or direction of the parameters you are passing it.
ASKER
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_Gro up_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_I D)
order by USER_GROUP_NAME;
END RetrieveCurrentGroups;
=====================
PROCEDURE RetrieveCurrentGroups
(i_USER_ID IN VARCHAR2,
CURRENT_GROUPS OUT t_cursor)
IS
BEGIN
OPEN CURRENT_GROUPS FOR
select ugm.User_Group_Id,User_Gro
from RDA_SUPPMAINT_USER_GROUPS ug,RDA_SUPPMAINT_USER_GRP_
where ug.USER_GROUP_ID=ugm.USER_
order by USER_GROUP_NAME;
END RetrieveCurrentGroups;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
XML file is a common one and is working for other procedures.
thx
Dil.
thx
Dil.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thx, Experts, the issue has been resolved.
ASKER
==========================
Open in new window