Link to home
Create AccountLog in
Avatar of dyarosh
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.

        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;
        }

Open in new window


Any help is greatly appreciated!
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

From my dealings with .Net and Oracle,
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:

command.Parameters.Add("retPK", OracleDbType.Int32, 10, retPK, ParameterDirection.ReturnValue);
command.Parameters.Add("Division", OracleDbType.Varchar2, 30, division, ParameterDirection.Input);
command.Parameters.Add("ActiveStatusFlag", OracleDbType.Char, 1, activestatusflag, ParameterDirection.Input);

Open in new window

Avatar of dyarosh

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"."ACTIVESTATUSFLAG" (actual: 12, maximum: 1) ORA-06512: at line 1

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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dyarosh

ASKER

I believe I have copied what you have but I am getting the following error:

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;

Open in new window

>>"RETURNING DIVISIONID INTO :retPK;";

Remove the trailing semi-colon:
"RETURNING DIVISIONID INTO :retPK";
Avatar of dyarosh

ASKER

Thanks you for your help!  For those that need the solution in C#, here is my code:

            // Create Command object
            OracleCommand command = connectionObj.CreateCommand();
            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("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();
                if (retVal == 1)        // Insert Successful
                    retPK = Convert.ToInt32(command.Parameters["retPK"].Value.ToString());
>>Convert.ToInt32(command.Parameters["retPK"].Value.ToString());

Do you need to convert Value to a string then back to int32?