dyarosh
asked on
How to return the Primary Key after an INSERT using Oracle 11.g
Here is the information on our version of Oracle:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I have a table, EMP_Division, that contains the following fields:
DivsionID (PrimaryKey, NUMBER(10,0))
Division (VARCHAR2(30 Byte))
ActiveStatusFlag (CHAR(1 Bye))
LastUpdateTimeStamp (TIMESTAMP (6))
The LastUpdateTimeStamp is defaulted to the SYSDATE. The DivsionID is an auto increment field implemented using a Trigger and Sequence table.
What I need is to duplicate the MS SQL SERVER Select Scope_Identity which would return the value of DivisionID after the insert.
How do I do this in Visual Studio 2010 C#, using command.CommandText and command.Parameters?
Here is what I have so far but I keep getting 0 returned.
Any help is greatly appreciated!
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I have a table, EMP_Division, that contains the following fields:
DivsionID (PrimaryKey, NUMBER(10,0))
Division (VARCHAR2(30 Byte))
ActiveStatusFlag (CHAR(1 Bye))
LastUpdateTimeStamp (TIMESTAMP (6))
The LastUpdateTimeStamp is defaulted to the SYSDATE. The DivsionID is an auto increment field implemented using a Trigger and Sequence table.
What I need is to duplicate the MS SQL SERVER Select Scope_Identity which would return the value of DivisionID after the insert.
How do I do this in Visual Studio 2010 C#, using command.CommandText and command.Parameters?
Here is what I have so far but I keep getting 0 returned.
public Int32 InsertDivision(OracleConnection connectString, String division, Char activestatusflag)
{
// Inserts the Division record into the Division Table. Returns DivisionID on success otherwise returns 0.
// Check that the database has been opened
if (connectString == null || connectString.State != ConnectionState.Open)
{
ErrorMessage = ErrorMessage + "<br />Database is not opened.";
return 0;
}
// Create Command object
OracleCommand command = connectString.CreateCommand();
command.Connection = connectString;
Int32 retVal = 0;
Int32 retPK = 0;
try
{
// Insert Division information and return DivisionID
command.CommandText =
"BEGIN INSERT INTO EMP_OWNER.EMP_DIVISION " +
"(DIVISION, ACTIVESTATUSFLAG) " +
"VALUES " +
"(:Division, :ActiveStatusFlag) " +
"RETURNING DIVISIONID INTO :retPK;END;";
// Remove any previously set Parameters
command.Parameters.Clear();
command.Parameters.Add("Division", OracleDbType.Varchar2, 30, division, ParameterDirection.Input);
command.Parameters.Add("ActiveStatusFlag", OracleDbType.Char, 1, activestatusflag, ParameterDirection.Input);
command.Parameters.Add("retPK", OracleDbType.Int32, 10, retPK, ParameterDirection.ReturnValue);
retVal = (Int32)command.ExecuteNonQuery();
retVal = retPK;
}
catch (Exception ex)
{
ErrorMessage = ErrorMessage + "<br />ERROR Inserting Division Data: " + ex.Message;
retVal = 0;
}
return retVal;
}
Any help is greatly appreciated!
ASKER
If I move the return value parameter first I get the following error:
ORA-12899: value too large for column "EMP_OWNER"."EMP_DIVISION" ."ACTIVEST ATUSFLAG" (actual: 12, maximum: 1) ORA-06512: at line 1
It looks like it is trying to put the value of Division in ActiveStatusFlag.
ORA-12899: value too large for column "EMP_OWNER"."EMP_DIVISION"
It looks like it is trying to put the value of Division in ActiveStatusFlag.
OK, let me see if I can cobble together a test case.
It will be in VB.Net but you should be able to port it.
It will be in VB.Net but you should be able to port it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I believe I have copied what you have but I am getting the following error:
ORA-00911: invalid character
Here is my code:
ORA-00911: invalid character
Here is my code:
command.CommandText =
"INSERT INTO EMP_OWNER.EMP_DIVISION " +
"(DIVISION, ACTIVESTATUSFLAG) " +
"VALUES " +
"(:Division, :ActiveStatusFlag) " +
"RETURNING DIVISIONID INTO :retPK;";
// Remove any previously set Parameters
command.Parameters.Clear();
command.Parameters.Add("Division", OracleDbType.Varchar2, 30, division, ParameterDirection.Input);
command.Parameters.Add("ActiveStatusFlag", OracleDbType.Char, 1, activestatusflag, ParameterDirection.Input);
command.Parameters.Add("retPK", OracleDbType.Int32, ParameterDirection.Output);
retVal = (Int32)command.ExecuteNonQuery();
retVal = (Int32)command.Parameters["retPK"].Value;
>>"RETURNING DIVISIONID INTO :retPK;";
Remove the trailing semi-colon:
"RETURNING DIVISIONID INTO :retPK";
Remove the trailing semi-colon:
"RETURNING DIVISIONID INTO :retPK";
ASKER
Thanks you for your help! For those that need the solution in C#, here is my code:
// Create Command object
OracleCommand command = connectionObj.CreateComman d();
command.Connection = connectionObj;
Int32 retVal = 0;
Int32 retPK = 0;
try
{
// Insert Division information and return DivisionID
command.CommandText =
"INSERT INTO EMP_OWNER.EMP_DIVISION " +
"(DIVISION, ACTIVESTATUSFLAG) " +
"VALUES " +
"(:Division, :ActiveStatusFlag) " +
"RETURNING DIVISIONID INTO :retPK";
// Remove any previously set Parameters
command.Parameters.Clear() ;
command.Parameters.Add("Di vision", OracleDbType.Varchar2, 30, division, ParameterDirection.Input);
command.Parameters.Add("Ac tiveStatus Flag", OracleDbType.Char, 1, activestatusflag, ParameterDirection.Input);
command.Parameters.Add("re tPK", OracleDbType.Int32, ParameterDirection.Output) ;
retVal = (Int32)command.ExecuteNonQ uery();
if (retVal == 1) // Insert Successful
retPK = Convert.ToInt32(command.Pa rameters[" retPK"].Va lue.ToStri ng());
// Create Command object
OracleCommand command = connectionObj.CreateComman
command.Connection = connectionObj;
Int32 retVal = 0;
Int32 retPK = 0;
try
{
// Insert Division information and return DivisionID
command.CommandText =
"INSERT INTO EMP_OWNER.EMP_DIVISION " +
"(DIVISION, ACTIVESTATUSFLAG) " +
"VALUES " +
"(:Division, :ActiveStatusFlag) " +
"RETURNING DIVISIONID INTO :retPK";
// Remove any previously set Parameters
command.Parameters.Clear()
command.Parameters.Add("Di
command.Parameters.Add("Ac
command.Parameters.Add("re
retVal = (Int32)command.ExecuteNonQ
if (retVal == 1) // Insert Successful
retPK = Convert.ToInt32(command.Pa
Glad to help!
>>Convert.ToInt32(command. Parameters ["retPK"]. Value.ToSt ring());
Do you need to convert Value to a string then back to int32?
Do you need to convert Value to a string then back to int32?
Never tried an insert into returning with .Net but when calling a function the returned value needs to be the first parameter.
I've also never set a returnvalue with an actual variable.
Going from memory here I think it went something like (I mainly return CLOBs):
retPK = retVal.Value;
Without changing anything else, try rearranging the parmaters:
Open in new window