Oracle APEX master detail page creation: ORA-06531 error

I am creating a flock management application with APEX with the following PL/SQL scheme:

-- SHEEP table
create table sheep (
    sheep_id number(*,0) generated by default on null as identity,
    eid varchar2(6) not null,
    sex varchar2(6) not null,
    name varchar2(45),
    breed varchar2(255) not null,
    birth date,
    sheep_state varchar2(45) not null,
    lambs number(*,0) not null,
    lambs_sets number(*,0) not null,
    markings varchar2(4000),
    note varchar2(4000),
    constraint sheep_pk primary key (sheep_id)
);

-- EVENT table
create table event (
    event_id number(*,0) generated by default on null as identity,
    sheep_id varchar2(6) not null,
    name varchar2(255) not null,
    event_date date not null,
    description varchar2(4000) not null,
    location varchar2(45) not null,
    constraint event_pk primary key (event_id)
);

-- SALE table
create table sale (
    sale_id number(*,0) generated by default on null as identity,
    sheep_id varchar2(6) not null,
    price number(*,2) not null,
    sale_date date not null,
    location varchar2(45) not null,
    note varchar2(4000),
    constraint sale_pk primary key (sale_id)
);

In this scheme, the SALE and EVENT tables reference the EID field of the SHEEP table (unique identifier of 5 numbers and one letter – representing the yellow tag they have in their ears). I am trying to create a Master Detail page that would link a sheep to specific events (what happened to the sheep) and a sales history (if it has been sold, when, where, for how much).

I tried to use both the Create Application Wizard and the Create Page Wizard but each time I get the same error:

Ajax call returned server error ORA-06531: Reference to uninitialized collection for Execute PL/SQL Code.

It still lets me continue in the wizard but when I want to select the details tables (SALE & EVENT), they don’t even show up in selecting list (cf here).

Does someone have an idea? I am quite new to APEX and I might have missed something. Also, if you have any recommendations considering my relational scheme (change to make, improvements for efficiency), don’t hesitate to tell me as well.

Thanks in advance!

Answer

There is no relationship between event and sheep or between sale and sheep. You state the SALE and EVENT tables reference the EID field of the SHEEP table but the database doesn’t know about that, because that relationship has not been defined by way of a foreign key. A foreign key in a table references the primary key of the other table, you can’t reference another column (like the EID), even if that is unique.

In your script, make sheep_id NUMBER in all tables and create a foreign key in SALE and EVENT. Like this:

-- SHEEP table
create table sheep (
    sheep_id number(*,0) generated by default on null as identity,
    eid varchar2(6) not null,
    sex varchar2(6) not null,
    name varchar2(45),
    breed varchar2(255) not null,
    birth date,
    sheep_state varchar2(45) not null,
    lambs number(*,0) not null,
    lambs_sets number(*,0) not null,
    markings varchar2(4000),
    note varchar2(4000),
    constraint sheep_pk primary key (sheep_id)
);

-- EVENT table
create table event (
    event_id number(*,0) generated by default on null as identity,
    sheep_id number not null,
    name varchar2(255) not null,
    event_date date not null,
    description varchar2(4000) not null,
    location varchar2(45) not null,
    constraint event_pk primary key (event_id),
    constraint fk_event_sheep
      foreign key (sheep_id)
      references sheep(sheep_id)
);

-- SALE table
create table sale (
    sale_id number(*,0) generated by default on null as identity,
    sheep_id number not null,
    price number(*,2) not null,
    sale_date date not null,
    location varchar2(45) not null,
    note varchar2(4000),
    constraint sale_pk primary key (sale_id),
    constraint fk_sale_sheep
      foreign key (sheep_id)
      references sheep(sheep_id)    
);

This is the way to create a master detail relationship in relational database system and it should allow you to create a Master-Detail form in APEX as well.