OracleClient and pls-00306 error message

D

Darren Guy

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
 
P

Paul Clement

¤ 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)
¤ /*

<snip>

¤ 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);

<snip>

¤ 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
¤

From what I can see none of the parameter names defined (using CreateParameter) match those defined
in your Oracle sp (e.g. none of them are appended with _in or _out).


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
D

Darren Guy

From what I can see none of the parameter names defined (using
CreateParameter) match those defined
in your Oracle sp (e.g. none of them are appended with _in or _out).


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...

Changed the code to include _in, or _out in the parameter names, still
received the same error.
 
P

Paul Clement

¤ > From what I can see none of the parameter names defined (using
¤ CreateParameter) match those defined
¤ > in your Oracle sp (e.g. none of them are appended with _in or _out).
¤ >
¤ >
¤ > Paul ~~~ (e-mail address removed)
¤ > Microsoft MVP (Visual Basic)
¤
¤ Changed the code to include _in, or _out in the parameter names, still
¤ received the same error.
¤

Assuming the data types in the table are as specified and match the parameter data types, I don't
see any other obvious problems.

What you could do is create a new (empty) sp and add one parameter at a time until the error is
generated.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
P

Paul Clement

¤ > From what I can see none of the parameter names defined (using
¤ CreateParameter) match those defined
¤ > in your Oracle sp (e.g. none of them are appended with _in or _out).
¤ >
¤ >
¤ > Paul ~~~ (e-mail address removed)
¤ > Microsoft MVP (Visual Basic)
¤
¤ Changed the code to include _in, or _out in the parameter names, still
¤ received the same error.
¤

Assuming the data types in the table are as specified and match the parameter data types, I don't
see any other obvious problems.

What you could do is create a new (empty) sp and add one parameter at a time until the error is
generated.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

insert with stored procedure - no errors, but no insert 6
Dissapearing Validators 1
MS Oracle .NET Provider and REF CURSOR Error 0
Possible bugs in using [Flags] and Interfaces with Web Applications 1

Top