Hi all,
I have a problem using a stored procedure that insert a new record and
set the value of an output parameter to the new ID.
The insert works fine but the output parameter is always 0.
Here is the sp code :
PROCEDURE AddRole(RoleSite_ID OUT Number, Site_ID in Number,
Default_Role in NUMBER, Created_By in Number) AS
BEGIN
INSERT INTO D_RoleSite(Rolesiteid, SiteID, IsDefaultRole,
CreationDate, CreatedBy, LastUpdate, UpdatedBy)
VALUES (Seq_RoleSite.Nextval, Site_ID, Default_Role, (SELECT
CURRENT_DATE FROM DUAL), created_By, (SELECT CURRENT_DATE FROM DUAL),
created_By);
SELECT Seq_RoleSite.CurrVal INTO RoleSite_ID FROM DUAL;
COMMIT;
End AddRole;
and my .net code :
OracleCommand oCmd = new
OracleCommand("ASPortal.PermissionCtl.AddRole", oConn);
oCmd.CommandType = CommandType.StoredProcedure;
OracleParameter oParam = new OracleParameter("Site_ID",
OracleDbType.Int32, siteID, ParameterDirection.Input);
oCmd.Parameters.Add(oParam);
oParam = new OracleParameter("Default_Role", OracleDbType.Int32,
defaultRole, ParameterDirection.Input);
oParam = new OracleParameter("Created_By", OracleDbType.Int32,
createdBy, ParameterDirection.Input);
oCmd.Parameters.Add(oParam);
oParam = new OracleParameter("RoleSite_ID", OracleDbType.Int32,
ParameterDirection.Output);
oCmd.Parameters.Add(oParam);
try
{
oConn.Open();
oCmd.ExecuteNonQuery();
roleID = (int)oCmd.Parameters["RoleSite_ID"].Value;
...
roleID's value is always 0
when I test my sp in PL/SQL developer the output parameter is well
assigned with the new ID.
Is use ODP .NET
Any help will be appreciated.
Kind regards.