ORACLE DB: insert a row and return a variable to a scalar query

Asked Apr 21 '17 at 08:37

Active Apr 21 '17 at 15:05

Viewed 501 times

Environment:

Oracle DB (12c)
C# application to call the db

The expected outcome is to add new row into a table and return an id column which is auto-incremented using a sequence.

The caller is a c# application which uses executeScalar to execute the sql command.

I use this sql script:

DECLARE
  NEW_ID NUMBER;

BEGIN
  INSERT INTO Users
  (username)
  VALUES ('newName')
  RETURNING userId INTO NEW_ID;
END;

which seems to be executing and does insert the new row.

The issue

The issue is that when this script is executed from the c# application using executeScalar method, it should return the first column of the first row of the returned result. Instead of receiving a value, i receive null (not DBNull).

Is the 'RETURNING' keyword not meant to be used for scalar queries or does the issue creeps somewhere else?

EDIT:

The trigger that is used to increment the column:

edited Apr 21 '17 at 15:05

asked Apr 21 '17 at 08:37

Mario

"an id column which is auto-incremented using a sequence". How is `userid` populated? With a SEQUENCE or with the new 12c IDENTITY column? – APC Apr 21 '17 at 10:21
with a SEQUENCE – Mario Apr 21 '17 at 13:50
And there's a trigger on the table which populates the column with the sequence nextval? – APC Apr 21 '17 at 13:56
yes exactly. I can see both by selecting 'user_sequences' and 'user_triggers' – Mario Apr 21 '17 at 14:06
Post your trigger. – Nick Apr 21 '17 at 14:44
I've updated the question with the trigger. i think that there is no problem with the trigger since it is working fine and is incrementing the id as expected. – Mario Apr 21 '17 at 15:07
Seems to me that one should wrap the PL/SQL script in a function or procedure and call that from C# – David Faber Apr 21 '17 at 15:07

ORACLE DB: insert a row and return a variable to a scalar query

0 Answers0