CODEWITHSUNDEEP
sqloracle-databaseplsql
Trebor
Trebor

Reputation: 813

INSERT returning IDENTITY generating ORA 00933

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;

Upvotes: 1

Views: 804

Answers (2)

Robert Dupuy
Robert Dupuy

Reputation: 857

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.

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 221096

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)

Upvotes: 2

Related Questions