Solved

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

Posted on 2013-05-14
9
4,340 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
[X]
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
  • 6
  • 3
9 Comments
 
LVL 77

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 77

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 77

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 77

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 77

Expert Comment

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

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

726 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