Solved

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

Posted on 2013-05-14
9
4,092 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

920 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

16 Experts available now in Live!

Get 1:1 Help Now