CODEWITHSUNDEEP
oracle-databasesql-insert
Vijay Jagdale
Vijay Jagdale

Reputation: 2649

oracle subquery insert gives ora937 and ora 979

I am running into a strange errors with insert (Oracle 12.2), when the subquery has an aggregate function. The subquery itself runs fine, but when I put it in the insert statement oracle gives error. Also I discovered that it has something to do with the identity column in the inserting table (TESTLOGEVENT.evID in the example below), because if I drop that column, all insert queries work just fine!

create table testitems as
   (select 1 itemid, 'x001' trackingid from dual union all select 2,'x001' from dual);

create table testlogevent(
  evType varchar(50), evDesc varchar2(1000), userID varchar2(30), evDate date,
  evID  NUMBER(8) Generated as Identity);

insert into testlogevent(evType, evDesc, userid, evDate) 
  select 'testevent', max(itemid), :UserID, sysdate
   from testitems where trackingid='x001';

>>>ORA-00937: not a single-group group function

The select subquery by itself works fine! I tried to rewrite this by adding an unnecessary GROUP BY in the subquery to see if that works.

insert into testlogevent(evType, evDesc, userid, evDate) 
 select 'testevent', max(itemid), :UserID,sysdate
   from testitems where trackingid='x001' group by 'x001'

>>>ORA-00979: not a GROUP BY expression

Now Oracle hit me with ORA-00979. However yet again, the subquery by itself works fine.

Finally when I rewrote (the first insert query) using a CTE, this time Oracle didn't complain and the insert worked! What is going on here?

insert into testlogevent(evType, evDesc, userid, evDate) 
  with x as (
    select 'testevent', max(itemid), :UserID,sysdate
      from testitems where trackingid='x001')
  select * from x;

>>>1 row inserted

Upvotes: 1

Views: 220

Answers (1)

Vijay Jagdale
Vijay Jagdale

Reputation: 2649

After some research, it seems like when there is a subquery insert, Oracle cannot deduce the data type, and some internal bug related to identity column causes this cryptic error message (it works fine if there is no identity column or in Oracle 11)

when I changed the bind variable :UserID in the insert of the first query to: cast(:UserID as varchar2(30)) or simply: :UserID||'', the insert works because the bind variable datatype compiles to varchar2. That seems to also be the reason why the third insert works.

Hope Oracle fixes this issue in the next iteration.

Upvotes: 0

Related Questions