OutputParameter does not return valid value from Oracle StoredProcedure

  • Thread starter Thread starter Seb
  • Start date Start date
S

Seb

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.
 
Seb said:
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.
Click to expand...
Try the following instead in your stored proc

PROCEDURE AddRole(RoleSite_ID OUT Number, Site_ID in Number,
Default_Role in NUMBER, Created_By in Number) AS
BEGIN
SELECT Seq_RoleSite.Nextval INTO RoleSite_ID OUT FROM DUAL;
INSERT INTO D_RoleSite(Rolesiteid, SiteID, IsDefaultRole,
CreationDate, CreatedBy, LastUpdate, UpdatedBy)
VALUES (RoleSite_ID, Site_ID, Default_Role, (SELECT
CURRENT_DATE FROM DUAL), created_By, (SELECT CURRENT_DATE FROM DUAL),
created_By);
COMMIT;
End AddRole;

Selim
 
Sélim said:
Seb said:
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.
Click to expand...

Try the following instead in your stored proc

PROCEDURE AddRole(RoleSite_ID OUT Number, Site_ID in Number,
Default_Role in NUMBER, Created_By in Number) AS
BEGIN
SELECT Seq_RoleSite.Nextval INTO RoleSite_ID OUT FROM DUAL;
INSERT INTO D_RoleSite(Rolesiteid, SiteID, IsDefaultRole,
CreationDate, CreatedBy, LastUpdate, UpdatedBy)
VALUES (RoleSite_ID, Site_ID, Default_Role, (SELECT
CURRENT_DATE FROM DUAL), created_By, (SELECT CURRENT_DATE FROM DUAL),
created_By);
COMMIT;
End AddRole;

Selim
Click to expand...
Sorry, you need to remove the OUT in the SELECT => SELECT
Seq_RoleSite.Nextval INTO RoleSite_ID FROM DUAL;
 
Back
Top