Oracle SQL: How to INSERT a SELECT statement with a GROUP BY clause on a table with IDENTITY column?

Question

In an application, I intend on truncating and inserting on an Oracle 12c database, but have found this issue with an IDENTITY column. Even though the INSERT... SELECT statement works on most SELECT uses I have tried, when this statement also has a GROUP BY clause, it fails to work, issuing a "ORA-00979: not a GROUP BY expression" complaint. Below is some example code:

create table aux (
  owner_name varchar2(20),
pet varchar2(20) );

insert into aux values ('Scott', 'dog');
insert into aux values ('Mike', 'dog');
insert into aux values ('Mike', 'cat');
insert into aux values ('John', 'turtle');

create table T1 (
  id number generated always as identity,
  owner_name varchar2(20),
  pet_count number
);
insert into T1 (owner_name, pet_count) 
  select owner_name, count(*) as pet_count from aux group by owner_name;
select owner_name, count(*) as pet_count from aux group by owner_name;

It works on the first insert, but fails on the next.

EDIT: I've changed the code so the issue is easier to understand while still reproducible.

Appreciate the help!

Answer 1

In the Oracle Community, this question has been answered. https://community.oracle.com/message/13227544#13227544

insert into T1 (owner_name, pet_count)
with t as (select /*+ materialize */ owner_name, count(*) as pet_count from aux group by owner_name)
select owner_name, pet_count from t

Quoting the original answer, keep in mind the materialize hint is not documented. Thanks to all for your help!