OracleParameter Output using straight SQL not a stored procedure

[Kevin Schneider]

I am trying to insert a new row into a table in Oracle. The table
generates the ID during the insert through the use of a trigger.

How can I pull back the generated ID when I do the insert with the
following InsertCommand?

OracleInsertCommand1.CommandText = "INSERT INTO WIDGETS(WIDGETID,
NAME) VALUES WIDGETID, :NAME)"
OracleInsertCommand1.Connection = OracleConnection1
OracleInsertCommand1.Parameters.Add(New OracleParameter(":NAME",
OracleType.VarChar, 50, "NAME"))

I figured I needed to add an Output parameter so I added the something
like the following:

OracleInsertCommand1.Parameters.Add(New
OracleParameter(":WidgetIDOUT", OracleType.Number, 0,
ParameterDirection.Output, False, CType(38, Byte), CType(0, Byte),
"WIDGETID", DataRowVersion.Current, Nothing))

But when I try to do the insert, I get the Oracle error "ORA-01036:
illegal variable name/number".

I ran across the Oracle clause "RETURNING" for the INSERT command, is
this the place to use it?

 

[Kevin Yu [MSFT]]

Hi Kevin,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to return the generated ID for
an inserted record from Oracle server. If there is any misunderstanding,
please feel free to let me know.

I think the problem has something to do with the parameter names. When
writing the SQL statement, we add a colon before the parameter. However, it
is not necessary to add it when creating the parameters in your VB code. I
have made some changes to your code.

OracleInsertCommand1.Parameters.Add(New OracleParameter("NAME",
OracleType.VarChar, 50, "NAME"))
OracleInsertCommand1.Parameters.Add(New
OracleParameter("WidgetIDOUT", OracleType.Number, 0,
ParameterDirection.Output, False, CType(38, Byte), CType(0, Byte),
"WIDGETID", DataRowVersion.Current, Nothing))

Also, the output parameter didn't appear in your command text. Please add
assign value to the output parameter in SQL statement, so that it can be
added to the insert command parameter collection.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Schneider]

Your assement of question was correct. I appreciate your quick response.
After I posted the message I did the following and got it to work (only
the relevant lines are below):

Me.OracleInsertCommand1.CommandText = "INSERT INTO WIDGETS(WIDGETID,
NAME, DESCRIPTION, WIDGETTYPEID, QTY, PRICE) VALUES WIDGETID, :NAME)
RETURNING WIDGETID INTO :WidgetIDOUT"

OracleInsertCommand1.Parameters.Add(New OracleParameter(":WidgetIDOUT",
OracleType.Number, 0, ParameterDirection.Output, False, CType(38, Byte),
CType(0, Byte), "WIDGETID", DataRowVersion.Current, Nothing))

The key was to use the RETURNING clause with the bind variable
:WidgetIDOUT in the Insert statement.

There may be other ways to do this, but this works really well for what
I want to do.

Kevin

 

[Kevin Yu [MSFT]]

Hi Kevin,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."