Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-05-14
9
4,227 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
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.

 
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
 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

856 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