This content has been marked as final. Show 7 replies
I'm having the same issue. The issues seems to also exist in the newly release 3.1.
Yes it seems to be related to the instead of trigger.
Is this a known bug?
Here is a testcase to reproduce the issue.
create table mytab as select * from tab;
create table myobjects as select * from user_objects;
create or replace view test_view as
select myobjects.object_id, mytab.tname, mytab.tabtype from mytab, myobjects where mytab.tname=myobjects.object_name group by myobjects.object_id, mytab.tname, mytab.tabtype;
create or replace trigger io_test_view_trg
INSTEAD OF INSERT or UPDATE or DELETE
FOR EACH ROW
if updating then
update mytab set tname=:NEW.tname, TABTYPE=:NEW.TABTYPE where tname=:OLD.tname;
update myobjects set object_name=:NEW.tname where object_name=:OLD.tname;
select * from test_view;
update test_view set tname='1TEST_TABLE' where rownum < 2;
-- Attempting to view TEST_VIEW from SQL Developper fails with
-- ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
drop table mytab;
drop table myobjects;
drop view test_view;
The ORA error is ORA-01445. You have it listed as ORA-01446.
I get this when I connect to an 22.214.171.124.0 database with SQL Developer v 3.1.07 Build MAIN-07.42. I'm connecting as sys / SYSDBA; Open up Views; Select either V_SQL or GV_SQL (it's a rac cluster) view; Select the "Data" tab. Is there a fix for this?