Oracle connection error with EntLib - Help

Topics: Data Access Application Block, Enterprise Library Core
Jan 23, 2008 at 4:48 PM
Hello,
I am using EntLib connecting to Oracle back-end. I tried to insert to db with the C# code and procedure in package as followed:

C#:
--***********************************************************************************8
Database db = DatabaseFactory.CreateDatabase();
String sqlCommand = "PKGUSER.SPINSERT";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

db.AddInParameter(dbCommand, "USERNAME", DbType.String, NewUser.UserName);
db.AddInParameter(dbCommand, "USERDESCR", DbType.String, NewUser.UserDescr);
db.AddInParameter(dbCommand, "LASTNAME", DbType.String, NewUser.LastName);
db.AddInParameter(dbCommand, "FIRSTNAME", DbType.String, NewUser.FirstName);
db.AddInParameter(dbCommand, "EMAILADDRESS", DbType.String, NewUser.EmailAddress);
db.AddInParameter(dbCommand, "BID", DbType.Int32, NewUser.BID);
db.AddInParameter(dbCommand, "USERSTATUS", DbType.String, NewUser.UserStatus);
db.AddInParameter(dbCommand, "USERROLE", DbType.String, NewUser.UserRole);
db.AddInParameter(dbCommand, "CREATEDBY", DbType.Int32, NewUser.CreatedBy);
db.AddInParameter(dbCommand, "CREATE_DATE", DbType.DateTime, NewUser.CreatedDate);


db.AddOutParameter(dbCommand, "USER_ID", DbType.Int32, 4);
try
{
db.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
ex.Message.ToString();
}
**********************************************************************************************
My package code:

create or replace
PACKAGE BODY "PKGUSER"
IS

--Insert a user and return User ID
PROCEDURE SPINSERT(
pUserID OUT NUMBER,
pUserName IN VARCHAR2,
pUSERDESCR IN VARCHAR2 DEFAULT NULL,
pLastName IN VARCHAR2,
pFirstName IN VARCHAR2,
pEmailAddress IN VARCHAR2,
pBID IN NUMBER,
pUserStatus IN VARCHAR2,
pUserRole IN VARCHAR2 DEFAULT NULL,
pCreatedBY IN NUMBER,
pCreate_Date IN TIMESTAMP
)
IS
--Define variables here
BEGIN

INSERT INTO CASUSER (USERID, USERNAME, USERDESCR, BID, USERSTATUS, USERROLE, CREATEDBY, CREATEDATE, LASTNAME, FIRSTNAME, EMAILADDRESS)
VALUES (USERSSEQ.NEXTVAL, pUserName, pUSERDESCR, pBID, pUserStatus, pUserRole, pCreatedBY, pCreateDate, pLastName, pFirstName, pEmailAddress);

--SELECT USERS_SEQ.CURRVAL INTO pUserID FROM DUAL;

EXCEPTION
WHEN OTHERS THEN
raiseapplicationerror(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END SPINSERT;


END;
**************************************************************************************************************************************

When executing the C# code, I got this error:

System.Data.OracleClient.OracleException was caught
Message="ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'SPINSERT'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"
Source="System.Data.OracleClient"
ErrorCode=-2146232008
Code=6550
StackTrace:
at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command)
at User.AddUser(User NewUser, Int32 UserGroupID) in C:\Documents and Settings\CASSecurity\BLL\UserProfile.cs:line 252



Am I missing something? It seems the db.AddInParameter doesn't seem to be working. I tried to debug the package and it works just fine.
Your help is truly appreciated.

Thanks.
le9569
Jan 23, 2008 at 7:48 PM
I found a problem and it is fixed.
Thanks all.