Error creating row on form

Question

After I try to create a new row in one of my pages on APEX, the following error appears:

ORA-02291: integrity constraint (BD43015.SYS_C001194280) violated - parent key not found ORA-06512: at "BD43015.INSERT_STUDENT", line 4 ORA-04088: error during execution of trigger 'BD43015.INSERT_STUDENT' ORA-06512: at "SYS.WWV_DBMS_SQL", line 549 ORA-06512: at "APEX_040000.WWV_FLOW_DML", line 1121 ORA-22816: unsupported feature with RETURNING clause Error Unable to process row of table V_ALLSTUDENTS. OK

NOTE: I'm trying to use this trigger:

create or replace trigger insert_student
instead of insert on v_allstudents
for each row
begin
  insert into members values(:new.memberID, :new.name, :new.birth, :new.regist_date, :new.address, :new.sex, :new.phone);
  insert into students values(:new.memberID, :new.guardian, :new.rank, :new.plan_name, :new.plan_value);
end;
/

And this view:

create or replace view v_allstudents(memberID, name, birth, regist_date, address, sex, phone, guardian, rank, plan_name, plan_value) AS
select members.memberID, name, birth, regist_date, address, sex, phone, guardian, rank, plan_name, plan_value
from members, students
where members.memberID = students.memberID
order by members.name;

Table members:

drop table members cascade constraints;
create table members(
  memberID number(10) not null,
  name varchar2(30) not null,
  birth date not null,
  regist_date date not null,
  address varchar2(50) not null,
  sex char(1) not null CHECK (sex IN ('F', 'M')),
  phone number(9),
  primary key(memberID)
);

Table Students:

drop table students cascade constraints;
create table students(
  memberID number(10) not null,
  guardian varchar2(30),
  rank varchar2(20) not null,
  plan_name varchar2 (30) not null,
  plan_value number(10) not null,
  primary key(memberID),
  foreign key(memberID) references members(memberID)
);

EDIT FIX: We had a trigger in conflict with the insertion of the memberID.

Please post the table structure for the members and students tables along with all the constraints. Also, can you try inserting a row in the v_allstudents from SQLPLUS or SQL Developer. Doing this will help you understand if the problem is with Oracle Apex or not. — phonetic_man

phonetic_man phonetic_man · Accepted Answer · 2016-05-23T05:55:06

I could successfully reproduce the issue from SQLPLUS.

The following statement works.

declare
  v_memberid number;
begin
  insert into 
  v_allstudents(memberID, name, birth, regist_date, address, sex, phone, guardian, rank, plan_name, plan_value)
  values
  (1,'Neo',to_date('13-Jan-2009','dd-mon-yyyy'),to_date('13-Jan-2009','dd-mon-yyyy'),'My address','M',1234,'Trinity',1,'My Plan',2525)
end;

The following statement does not work and throws ORA-22816.

declare
  v_memberid number;
begin
  insert into 
  v_allstudents(memberID, name, birth, regist_date, address, sex, phone, guardian, rank, plan_name, plan_value)
values
  (1,'Neo',to_date('13-Jan-2009','dd-mon-yyyy'),to_date('13-Jan-2009','dd-mon-yyyy'),'My address','M',1234,'Trinity',1,'My Plan',2525)
  returning memberID into v_memberid;
end;

Here is the description for the error from Oracle documentation.

RETURNING clause is currently not supported for object type columns, LONG columns, remote tables, INSERT with subquery, and INSTEAD OF Triggers.

In this case either Oracle Apex is generating an Insert statement with the returning clause or you have written bespoke code in Oracle Apex with a returning clause.

I would recommend doing away with instead of trigger and inserting values into both these tables using Oracle apex forms.

Error creating row on form

1 Answers