Understanding the Oracle APEX Master Detail Page Creation Process
===========================================================
In this article, we will explore the process of creating a master detail page in Oracle APEX. Specifically, we will address an ORA-06531 error that occurs when trying to select the details tables (SALE and EVENT) for a given sheep.
Background Information on Foreign Keys
Before diving into the solution, let’s take a look at how foreign keys work in relational database systems. A foreign key is a column in one table that references the primary key of another table. This creates a relationship between the two tables, allowing us to establish a connection between them.
In our example, we have three tables: SHEEP, EVENT, and SALE. The EVENT table has a foreign key (fk_event_sheep) that references the primary key (sheep_id) of the SHEEP table. Similarly, the SALE table has a foreign key (fk_sale_sheep) that also references the primary key (sheep_id) of the SHEEP table.
Defining Foreign Keys in APEX
To create a master detail page in Oracle APEX, we need to define foreign keys between the different tables. In our case, we need to add foreign keys to the EVENT and SALE tables that reference the primary key (sheep_id) of the SHEEP table.
Here is an example of how to define these foreign keys:
-- Create the SHEEP table with a primary key on sheep_id
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)
);
-- Create the EVENT table with a foreign key on sheep_id
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)
);
-- Create the SALE table with a foreign key on sheep_id
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)
);
Creating a Master Detail Page in APEX
Now that we have defined the foreign keys, we can create a master detail page in Oracle APEX.
To do this, we need to follow these steps:
- Create a new application in APEX and add a master view object (e.g., a table).
- Add a detail view object (e.g., another table) as the child of the master view.
- Establish a relationship between the two views using the foreign key constraint.
Here is an example of how to create a master detail page in APEX:
-- Create the master view object (e.g., a table)
create page master_page
begin
page_name := 'Master Page';
page_template_options := 'masterDetail';
declare
p_button_id PAGESheet2_BtnCreate;
p_grid_id PAGESheet2_Grid1;
begin
p_button_id := PAGESheet2_BtnCreate;
p_grid_id := PAGESheet2_Grid1;
-- Create the master view object (e.g., a table)
APEX_PAGE.BEGIN_CREATE_grid(p_grid_id);
APEX_PAGE.ADD_column_to_grid(
p_grid_id,
'column1',
NULL,
'column1',
'text'
);
APEX_PAGE.END_create_grid();
-- Create the detail view object (e.g., another table)
APEX_PAGE.BEGIN_CREATE_grid(p_grid_id);
APEX_PAGE.ADD_column_to_grid(
p_grid_id,
'column2',
NULL,
'column2',
'text'
);
APEX_PAGE.END_create_grid();
-- Establish a relationship between the two views using the foreign key constraint
APEX_PAGE.BEGIN_CREATE_grid(p_grid_id);
APEX_PAGE.ADD_column_to_grid(
p_grid_id,
'foreign_key',
NULL,
'fk_event_sheep',
'text'
);
APEX_PAGE.END_create_grid();
-- Set the detail view to be the child of the master view
APEX_PAGE.BEGIN_CREATE_grid(p_grid_id);
APEX_PAGE.ADD_column_to_grid(
p_grid_id,
'detail_view',
NULL,
'master_page.column1',
'text'
);
APEX_PAGE.END_create_grid();
-- Set the master view to be displayed on the page
APEX_PAGE.BEGIN_CREATE_grid(p_grid_id);
APEX_PAGE.ADD_column_to_grid(
p_grid_id,
'master_view',
NULL,
'master_page.column1',
'text'
);
APEX_PAGE.END_create_grid();
end;
end;
-- Page properties
begin
page_name := 'Master Detail Page';
page_template_options := 'masterDetail';
declare
p_button_id PAGESheet2_BtnCreate;
p_grid_id PAGESheet2_Grid1;
begin
p_button_id := PAGESheet2_BtnCreate;
p_grid_id := PAGESheet2_Grid1;
-- Set the page properties
APEX_PAGE.BEGIN_SET_properties(p_page_id);
APEX_PAGE.ADD_PROPERTY(p_page_id, 'master_view', p_grid_id);
APEX_PAGE.END_SET_properties();
end;
end;
-- Page events
begin
page_name := 'Master Detail Page';
declare
p_button_id PAGESheet2_BtnCreate;
p_grid_id PAGESheet2_Grid1;
begin
p_button_id := PAGESheet2_BtnCreate;
p_grid_id := PAGESheet2_Grid1;
-- Set the page events
APEX_PAGE.BEGIN_SET_events(p_page_id);
APEX_PAGE.ADD_EVENT(p_page_id, 'button_click', 'p_button_id.click');
APEX_PAGE.END_SET_events();
end;
end;
Conclusion
In this article, we explored the process of creating a master detail page in Oracle APEX. We discussed the importance of defining foreign keys between different tables to establish relationships between them.
We also provided an example of how to create a master detail page using APEX, including setting up the master view object, establishing a relationship between the two views, and displaying the detail view as the child of the master view.
By following these steps, you should be able to create a master detail page in Oracle APEX that meets your needs.
Last modified on 2023-11-22