I am getting the following error message when trying to execute an oracle
stored procedure with in and out parameters only.
ora-06550 line 1, column 7:
pls-00306 wrong number or types of aurguments in call to 'GETUSERDETAILS'
pl/sql statement ignored.
The code for the sp is a follows:
create or replace procedure GetUserDetails (
LoginName_in in security.loginname%TYPE,
UserPassword_in in security.UserPassword%TYPE,
UserID out security.userid%TYPE,
FullName out security.fullname%TYPE,
HelpDesk_out out security.helpdesk%TYPE,
MenuNo_out out security.menuno%TYPE,
PasswordExpires_out out security.PasswordExpires%TYPE,
Expires_out out security.Expires%TYPE,
Change_out out security.Change%TYPE,
DesignForms_out out security.DesignForms%TYPE,
LoginDateTime_out out security.LoginDateTime%TYPE,
WebLoginDateTime_out out security.WebLoginDateTime%TYPE,
UserType_out out security.UserType%TYPE,
PWExpiry_out out security.PWExpiry%TYPE,
LastPWChangeDate_out out security.LastPWChangeDate%TYPE,
ADsPath_out out security.ADsPath%TYPE,
LastMachineLogOn_out out security.LastMachineLogOn%TYPE,
result out number)
/*
-- Purpose of Stored Procedure
Check to make sure that the loginname and password supplied
match a valid user in the database. If do match
then return the user details
*/
as
begin
result := 0;
-- get the record
select UserID, FullName, HelpDesk, MenuNo, PasswordExpires,
Expires, Change, DesignForms, LoginDateTime, WebLoginDateTime,
UserType, PWExpiry, LastPWChangeDate, ADsPath, LastMachineLogOn
into Userid, FullName, HelpDesk_out, MenuNo_out, PasswordExpires_out,
Expires_out, Change_out, DesignForms_out, LoginDateTime_out,
WebLoginDateTime_out,
UserType_out, PWExpiry_out, LastPWChangeDate_out, ADsPath_out,
LastMachineLogOn_out
from security
where LoginName = LoginName_in
and UserPassword = UserPassword_in;
exception
when NO_DATA_FOUND then result := -1;
end GetUserDetails;
The table definition of security is as follows:
CREATE TABLE Security(
UserID NUMBER(10,0) NOT NULL,
LoginName Varchar(30) NULL,
FullName Varchar(40) NULL,
Description Varchar(40) NULL,
UpdatedBy Varchar(30) NULL,
DBAlias Varchar(100) NULL,
HelpDesk NUMBER(10,0) NULL,
MenuNo NUMBER(10,0) NULL,
PasswordExpires DATE NULL,
Expires NUMBER(10,0) NULL,
Change NUMBER(10,0) NULL,
DesignForms NUMBER(10,0) NULL,
LoginDateTime DATE NULL,
WebLoginDateTime DATE NULL,
UserPassword Varchar(50) NULL,
dbUID varchar(25) NULL ,
dbPWD varchar(25) NULL,
UserType NUMBER(10,0) NULL,
PRIMARY KEY (UserID)
)
/
I am using a factory, and the relevant code to create the procedures is as
follows:
public void ExecuteNonQuery()
{
m_oCommand.ExecuteNonQuery();
}
/// <summary>
/// Create a parameter and add it to the command ParameterCollection
/// </summary>
/// <param name="paramName">The parameter name</param>
/// <param name="dbType">the DATATYPES enum</param>
/// <param name="size">If required, the parameter size</param>
/// <param name="precision">If required the parameter number value
precision</param>
/// <param name="direction">The parameter direction</param>
/// <param name="value">If input, or InputOutput parameter, then the value
to be added </param>
public void CreateParameter(string paramName, DATATYPES dbType, int size,
byte precision, ParameterDirection direction, object value)
{
// TODO convert this to using IDataParameter only
IDataParameter param;
switch (this.PROVIDER)
{
case PROVIDER_TYPE.PROVIDER_SQLCLIENT:
{
param = doSqlParameter(paramName, dbType, size, precision);
param.Direction = direction; // had to do this here else it
would not compile
if ( value != null )
param.Value = value; // had to do this here else it would
not compile
m_oCommand.Parameters.Add(param);
break;
}
case PROVIDER_TYPE.PROVIDER_ORACLE:
{
param = doOracleParameter(paramName, dbType, size, precision);
param.Direction = direction; // had to do this here else it
would not compile
if ( value != null )
param.Value = value; // had to do this here else it would
not compile
m_oCommand.Parameters.Add(param);
break;
}
default: break;
}
}
IDataParameter doSqlParameter(string paramName, DATATYPES dbType, int size,
byte precision)
{
SqlParameter Param = new SqlParameter();
Param.ParameterName = paramName;
switch (dbType)
{
case DATATYPES.VARCHAR:
Param.SqlDbType = SqlDbType.VarChar;
break;
case DATATYPES.INT:
Param.SqlDbType = SqlDbType.Int;
break;
case DATATYPES.DATETIME:
Param.SqlDbType = SqlDbType.DateTime;
break;
default: break;
}
if ( size > 0 )
Param.Size = size;
if ( precision > 0 )
Param.Precision = precision;
return Param;
}
/// <summary>
/// Create an oracle specific parameter
/// </summary>
/// <param name="paramName">The parameter name</param>
/// <param name="dbType">the DATATYPES enum</param>
/// <param name="size">If required, the parameter size</param>
/// <param name="precision">If required the parameter number value
precision</param>
/// <returns>OracleParameter()</returns>
IDataParameter doOracleParameter(string paramName, DATATYPES dbType, int
size, byte precision)
{
OracleParameter Param = new OracleParameter();
Param.ParameterName = paramName.Substring(1); // for oracle, strip out
the @ character
switch (dbType)
{
case DATATYPES.VARCHAR:
Param.OracleType = OracleType.VarChar;
break;
case DATATYPES.INT:
Param.OracleType = OracleType.Number;
size = 10;
break;
case DATATYPES.DATETIME:
Param.OracleType = OracleType.DateTime;
break;
default: break;
}
if ( size > 0 )
Param.Size = size;
if ( precision > 0 )
Param.Precision = precision;
return Param;
}
and the code that I am using is as follows:
private void button1_Click(object sender, System.EventArgs e)
{
AssistDataFactory oSecDB = null; //
oSecDB = AssistDataFactory.GetADONETWrapper();
oSecDB.ConnectionString = CONN_STR;
oSecDB.PrepareStoredProcedure("GetUserDetails");
oSecDB.CreateParameter("@LoginName",
AssistDataFactory.DATATYPES.VARCHAR, 30, 0, ParameterDirection.Input,
edtLoginName.Text);
oSecDB.CreateParameter("@UserPassword",
AssistDataFactory.DATATYPES.VARCHAR, 50, 0, ParameterDirection.Input,
Encrypt(tbUserPassword.Text));
oSecDB.CreateParameter("@UserID", AssistDataFactory.DATATYPES.INT, 0, 0,
ParameterDirection.Output, null);
oSecDB.CreateParameter("@FullName", AssistDataFactory.DATATYPES.VARCHAR,
40, 0, ParameterDirection.Output, null);
oSecDB.CreateParameter("@HelpDesk", AssistDataFactory.DATATYPES.INT, 0,
0, ParameterDirection.Output, null);
oSecDB.CreateParameter("@MenuNo", AssistDataFactory.DATATYPES.INT, 0, 0,
ParameterDirection.Output, null);
oSecDB.CreateParameter("@PasswordExpires",
AssistDataFactory.DATATYPES.DATETIME, 0, 0, ParameterDirection.Output,
null);
oSecDB.CreateParameter("@Expires", AssistDataFactory.DATATYPES.INT, 0,
0, ParameterDirection.Output, null);
oSecDB.CreateParameter("@Change", AssistDataFactory.DATATYPES.INT, 0, 0,
ParameterDirection.Output, null);
oSecDB.CreateParameter("@DesignForms", AssistDataFactory.DATATYPES.INT,
0, 0, ParameterDirection.Output, null);
oSecDB.CreateParameter("@LoginDateTime",
AssistDataFactory.DATATYPES.DATETIME, 0, 0, ParameterDirection.Output,
null);
oSecDB.CreateParameter("@WebLoginDateTime",
AssistDataFactory.DATATYPES.DATETIME, 0, 0, ParameterDirection.Output,
null);
oSecDB.CreateParameter("@UserType", AssistDataFactory.DATATYPES.INT, 0,
0, ParameterDirection.Output, null);
oSecDB.CreateParameter("@PWExpiry", AssistDataFactory.DATATYPES.INT, 0,
0, ParameterDirection.Output, null);
oSecDB.CreateParameter("@LastPWChangeDate",
AssistDataFactory.DATATYPES.DATETIME, 0, 0, ParameterDirection.Output,
null);
oSecDB.CreateParameter("@ADsPath", AssistDataFactory.DATATYPES.VARCHAR,
255, 0, ParameterDirection.Output, null);
oSecDB.CreateParameter("@LastMachineLogOn",
AssistDataFactory.DATATYPES.VARCHAR, 255, 0, ParameterDirection.Output,
null);
oSecDB.CreateParameter("@result", AssistDataFactory.DATATYPES.INT, 0, 0,
ParameterDirection.Output, null);
try
{
oSecDB.ExecuteNonQuery();
}
catch (Exception ex)
{
string errMessage = "";
for( Exception tempException = ex; tempException != null ;
tempException = tempException.InnerException )
{
errMessage += tempException.Message + Environment.NewLine +
Environment.NewLine;
}
MessageBox.Show( string.Format( "There are some problems while
trying to use the Assist Data Factory: {0}",
errMessage ), "Application error", MessageBoxButtons.OK,
MessageBoxIcon.Error );
}
}
I have tested the procedure in sql*plus and it works correctly.
The factory that I am using does correct create an OracleCommand object. I
am sure that it is something simple that I have done wrong. I have tested
the factory using sqlserver and experienced no problems
Thanks in advance
Darren
Similar Threads
