INSERT returning IDENTITY generating ORA 00933

Question

I'm trying to INSERT a single record into a table and return the sequence number that was added to the record via ASP.net/Visual Studio. However, I'm receiving the above referenced error. Initially I thought my error was because it thought I might return more than one record, but even after rewriting several ways, the error continues. Multiple posts exist on this topic, but they all seem to revolve around the possibility of multiple records being inserted.

I suspect because I'm using a "select... from dual" that it still thinks I could insert more than one record. I obviously don't need the "select...from dual" except that I'd like to use a WHERE clause to guarantee that the record doesn't already exist in the destination table.

Any help or suggestions would be greatly appreciated. Thank you.

INSERT INTO blatchildren
  (blatranscriptid, childactivityid, enrollmentDate, enrollmentStatus)
  SELECT 2,
                 'cours000000000004981',
                 to_date('1/1/2015 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
                 'E'
    from dual
   where 'cours000000000004981' not in (select childactivityid from blatchildren) 
   returning id
    into :identity

To test the code, I've been running the following in PL/SQL Developer:

declare identity number(2);
begin
INSERT INTO blatchildren
  (blatranscriptid, childactivityid, enrollmentDate, enrollmentStatus)
  VALUES( 2,
         'cours000000000004981',
         to_date('1/1/2015 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
         'E')
  returning id
    into identity;
end;

INSERT..SELECT FROM DUAL..WHERE dont mix,you dont want duplicates use an unique key,even without from dual still doenst make sense — Mihai, Sep 19 '15 at 15:22
@LukasEder - I'm testing from a PL/SQL tool, but using in Visual Studio. — Trebor, Sep 19 '15 at 15:25
possible duplicate of [PLSQL Insert into with subquery and returning clause (Oracle)](http://stackoverflow.com/questions/5325033/plsql-insert-into-with-subquery-and-returning-clause-oracle) — Lukas Eder, Sep 19 '15 at 15:34
You are willing to run this SQL (select childactivityid from blatchildren) with no where clause. What if instead you ran select childiactivity from blatchchildren where childactivityid='someid' and then used the sql rowcnt of 0 to decide to do the insert, or 1 - to not. — Robert Dupuy, Sep 19 '15 at 18:26
@RobertDupuy - That would still mean two separately ran queries wouldn't it? My goal was to try and do it in one single query. If you have something else in mind, maybe you could give an example. — Trebor, Sep 19 '15 at 20:44
OK, I'll put out an answer, but you won't like it ;) see below — Robert Dupuy, Sep 20 '15 at 05:35

score 2 · Answer 1 · answered Sep 19 '15 at 15:25

2

You cannot use the RETURNING clause with INSERT .. SELECT in PL/SQL:

insert_into_clause
{ values_clause [ returning_clause ]
| subquery 
} [ error_logging_clause ]

The returning_clause can only be supplied with the values_clause

See: https://docs.oracle.com/database/121/SQLRF/statements_9014.htm#SQLRF55051

A better way forward might be to add a UNIQUE constraint to blatchildren(childactivityid)

answered Sep 19 '15 at 15:25

Lukas Eder

211,314
129
689
1,509

Thanks, but I'm not sure how the constraint would prevent the duplication before the INSERT occurred. I looked at the reference to the "possible duplicate" and followed a link out of it to _forall_. That looks like it might be a possible answer but seems fairly complex for a simple query that needs a conditional where clause. – Trebor Sep 19 '15 at 15:56
*"before the INSERT occurred"* why does it matter? Or rather: why is your approach more desireable? – Lukas Eder Sep 19 '15 at 16:12
Maybe I'm misunderstanding your suggestions, but I'd rather prevent the error than have to write an error handler to trap it. Am I misunderstanding your suggestion? – Trebor Sep 19 '15 at 16:26
@Trebor: It's common practice. Do you worry about performance, perhaps? – Lukas Eder Sep 19 '15 at 18:15
Thank you for the clarification about the VALUES clause. That clarified the syntax for me! – Trebor Sep 21 '15 at 16:08

Robert Dupuy · Accepted Answer · 2015-09-20T05:52:12.673

I completely agree with Luka Eder's answer, but if you must use a single query with a return clause, it would look like this:

variable identity number;
BEGIN
INSERT INTO blatchildren
  (blatranscriptid, childactivityid, enrollmentDate, enrollmentStatus)
  VALUES ((SELECT 2 from dual where 'cours000000000004981' not in (select childactivityid from blatchildren)),
          (SELECT 'cours000000000004981' from dual where 'cours000000000004981' not in (select childactivityid from blatchildren)), 
          (SELECT to_date('1/1/2015 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') from dual where 'cours000000000004981' not in (select childactivityid from blatchildren)), 
          (SELECT 'E'  from dual where 'cours000000000004981' not in (select childactivityid from blatchildren)))
   returning blatranscriptid
    into :identity;
END;
/

The logic here, is, as Luka said, you must use a VALUES clause to get RETURNING to work. Notice the number of parenthesee I've put in there. Doing a select inside a values clause is possible, I've personally never written something this way, I believe I would just query the database twice.

p.s. this doesn't solve much, you'd still have an insert attempt. But maybe it saves adding another constraint, probably you have a not null constraint on an id column.

I said "p.s. this doesn't solve much, you'd still have an insert attempt" - the short answer is Lukas' answer is correct concerning limitations in oracle. — Robert Dupuy, Sep 20 '15 at 08:27
Robert, thanks. I hadn't even considered a SELECT inside of the values clause. Although it's not pretty, it prevents the error from occurring rather than just coding around a possible error. Though I haven't tried it, my guess is that a single select query for all of the values inside of the values clause might work too. Thank you for the ideas. — Trebor, Sep 21 '15 at 16:06

INSERT returning IDENTITY generating ORA 00933

2 Answers2