-1

I need to update some code and as part of this I need to insert a row into a table and obtain the id (primary key) of the row just entered.

Have researched this and I believe I should be using RETURNING INTO and Oracle Parameters. I have used parameters in the past successfully to Insert values.

I have an INSERT statement that runs perfectly from VB.NET, but as soon as I add the text "" RETURNING id INTO :myId" I get ORA-00933 Command Not Properly Ended.

Here is a version of the code.

sql = "INSERT ... RETURNING id INTO :myId"
Connect()
Dim intRecsAffected As Integer = 0

Dim comm As OracleCommand = New OracleCommand(sql, _conn)
Dim param As OracleParameter
param = New OracleParameter()

param.ParameterName = ":myId"
param.OracleDbType = OracleDbType.Int32
param.Direction = ParameterDirection.Output ' Tried ReturnValue

comm.Parameters.Add(param)

intRecsAffected = comm.ExecuteNonQuery()
id = comm.Parameters(":myId").Value

Disconnect()

Any ideas?

sstan
  • 35,425
  • 6
  • 48
  • 66
TobyKenobi
  • 1
  • 2
  • There is something wrong with your SQL, but since you are only showing it partially, can't really help. Please post the full sql. – sstan May 27 '16 at 12:37
  • RETURNING INTO is PL/SQL syntax, not SQL so you need to use an anonymous PL/SQL block (wrap in BEGIN and END) – Christian Shay May 28 '16 at 16:26

2 Answers2

1

I believe that your syntax is incorrect:

 sql = "INSERT ... RETURNING id INTO myId"

Example below: https://oracle-base.com/articles/misc/dml-returning-into-clause

coder32
  • 191
  • 3
  • 12
0

Actually, realised what was going on. I cut my full SQL as it's quite long and there's some sensitive stuff in there.

The INSERT was using a SELECT rather than VALUES to get the values for the fields. That won't work - I am guessing because an INSERT with SELECT can add multiple rows even though in this case it won't.

Have re-written the SQL to use VALUES and the VB.Net code works fine.

Thanks to all who replied.

TobyKenobi
  • 1
  • 2