Solved

How to return the Primary Key after an INSERT using Oracle 11.g

Posted on 2013-05-14
9
3,978 Views
Last Modified: 2013-05-14
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!
0
Comment
Question by:dyarosh
  • 6
  • 3
9 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39165542
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

0
 

Author Comment

by:dyarosh
ID: 39165641
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39165660
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.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39165741
Try this:

<%@ import namespace = "system.data" %>
<%@ import namespace = "Oracle.DataAccess.Client" %>
<%@ import namespace = "Oracle.DataAccess.Types" %>


<html>
<title>Test page</title>
<body>
<script language="VB" runat="server">




sub doInsert(sender as object,e as eventargs)
		Dim con As New OracleConnection() 
		Dim retVal As Int32

		con = New OracleConnection("User Id=BUD;Password=BUD;Data Source=BUD;")

		Dim cmd as OracleCommand = new OracleCommand()
		cmd.Connection	= con

		Dim param1 as OracleParameter = cmd.Parameters.Add("mytext", OracleDbType.varchar2, _
			50, someId.text, ParameterDirection.Input)

		Dim param2 as OracleParameter = cmd.Parameters.Add("myid", OracleDbType.Int32, _
			ParameterDirection.Output)

		try
			con.open()

			cmd.commandText	= " insert into tab1(col1) values(:sometext) returning id into :myval "

			cmd.executenonquery()

			response.write("Got: " & param2.Value)


		catch ex as exception
			ErrMsg.text = ex.tostring()

		finally
			cmd.Dispose()
			con.Close()
			con.Dispose()
		end try


end sub
</script>

<form id="myForm" runat="server">

	<asp:label id="ErrMsg" runat="server"/>
	<br/>

	String to insert: <asp:textbox id="someId" textmode="SingleLine" columns=20 maxlength=20 runat="server"/>
	<br/>

	<asp:Button id="SubmitButton" height="22" width="60" OnClick="doInsert" runat="server" Text="Go"/>

</form>

</body>
</html>

Open in new window

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:dyarosh
ID: 39165838
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

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39165879
>>"RETURNING DIVISIONID INTO :retPK;";

Remove the trailing semi-colon:
"RETURNING DIVISIONID INTO :retPK";
0
 

Author Closing Comment

by:dyarosh
ID: 39166206
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());
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39166245
Glad to help!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39166251
>>Convert.ToInt32(command.Parameters["retPK"].Value.ToString());

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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now