I'm very new to Oracle and struggling with REF's.
I have an employee type with the following structure:
CREATE TYPE person_type AS OBJECT(
address at_address_type,
name at_name_type,
phones at_nested_phone,
ni_num VARCHAR2(5)
)NOT FINAL;
/
CREATE TYPE employee_type UNDER at_person_type(
emp_id NUMBER,
supervisor_r REF at_employee_type, -- NOTE supervisor is a reference to the same type
job_r REF at_job_type,
branch_r REF at_branch_type,
join_date DATE
)FINAL;
/
Then I create a table from employee_type:
CREATE TABLE at_employee_table of at_employee_type(
emp_id PRIMARY KEY,
CONSTRAINT check_emp_address
CHECK (
address.street IS NOT NULL
AND
address.city IS NOT NULL
AND
address.postcode IS NOT NULL
),
CONSTRAINT check_emp_name
CHECK (
name.title IS NOT NULL
AND
name.firstname IS NOT NULL
AND
name.surname IS NOT NULL
),
CONSTRAINT check_emp_ni CHECK (ni_num IS NOT NULL),
CONSTRAINT unique_emp_ni UNIQUE (ni_num),
CONSTRAINT check_emp_job CHECK (job_r IS NOT NULL),
CONSTRAINT check_emp_branch CHECK (branch_r IS NOT NULL),
CONSTRAINT check_emp_join CHECK (join_date IS NOT NULL)
)NESTED TABLE phones STORE AS at_nested_emp_phones_storage;
/
What I want to be able to do is is check that the supervisor_r meets the following conditions:
supervisor_r.job_r.position IN ('head','manager','team leader')
AND
supervisor_r.branch_r.branch_code = :NEW.branch_r.branch_code
However, I can't seem to do this as a constraint as constraints don't allow DEREF, and I can't seem to do this with a trigger as triggers don't allow navigation through REF or to read or modify the table which is currently being modified. As I'm new to oracle, I feel there may be something I can do which I haven't thought of.
Note I require the supervisor to be stored as a REF (due to other reasons).
Does anyone have any solutions? I've been pulling my hair out over this one.
I have tried constraints, and I have tried triggers as explained in the details.
I have tried to rethink my design, but I require REF's to be used for supervisor_r, and struggling to think of another design where I can navigate through supervisors (and their supervisors), still using REF whilst also enforcing the conditions mentioned.
@Alex Hi Alex, yes it does compile, however when it is called and :new.supervisor_r is not null, it produces this error:
Error starting at line : 335 in command -
INSERT INTO at_employee_table
SELECT at_employee_type(
at_address_type(
'Adam', -- street
'Edinburgh', -- city
'EH1 6EA' -- postcode
),
at_name_type(
'Mr', -- title
'Jack', -- firstname
'Smith' -- surname
),
at_nested_phone(
at_phone_type(
'home', -- purpose
'01311112223' -- num
),
at_phone_type(
'mobile', -- purpose
'0781209890' -- num
)
),
'NI810', -- NI num
804, -- emp ID
REF(s), -- supervisor
REF(j), -- job
REF(b), -- branch
TO_DATE('05-Feb-18','DD-Mon-YY') -- join date
)
FROM at_job_table j, at_branch_table b, at_employee_table s
WHERE j.position = 'team leader'
AND b.branch_code = 908
AND s.emp_id = 101
Error at Command Line : 335 Column : 13
Error report -
SQL Error: ORA-04091: table SYSTEM.AT_EMPLOYEE_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.AT_EMPLOYEE_TRIG", line 6
ORA-04088: error during execution of trigger 'SYSTEM.AT_EMPLOYEE_TRIG'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
It seems that Oracle does not allow attributes of references to the same table that is being modified be read, and I'm not sure how to get around it and continue to use a REF to the same table for supervisor_r
Expanding on @Alex Poole's answer, the secondary problem you are having is using INSERT INTO ... SELECT and selecting from the table you are trying to insert into.
If you just want to insert a single row then use INSERT INTO ... VALUES ... and obtain the references using sub-queries (rather than joins).
Given the setup using @Alex Poole's trigger and adding the other necessary tables:
create type at_address_type as object(street varchar2(10), city varchar2(10), postcode varchar2(8))
/
create type at_name_type as object(title varchar2(5), firstname varchar2(10),surname varchar2(10))
/
create type at_phone_type as object (x varchar2(15))
/
create type at_nested_phone as table of at_phone_type
/
create type at_job_type as object (position varchar2(10))
/
create type at_branch_type as object (branch_code number)
/
CREATE TYPE at_person_type AS OBJECT(
address at_address_type,
name at_name_type,
phones at_nested_phone,
ni_num VARCHAR2(5)
)NOT FINAL;
/
CREATE TYPE at_employee_type UNDER at_person_type(
emp_id NUMBER,
supervisor_r REF at_employee_type, -- NOTE supervisor is a reference to the same type
job_r REF at_job_type,
branch_r REF at_branch_type,
join_date DATE
)FINAL;
/
CREATE TABLE at_employee_table of at_employee_type(
emp_id PRIMARY KEY,
CONSTRAINT check_emp_address
CHECK (
address.street IS NOT NULL
AND
address.city IS NOT NULL
AND
address.postcode IS NOT NULL
),
CONSTRAINT check_emp_name
CHECK (
name.title IS NOT NULL
AND
name.firstname IS NOT NULL
AND
name.surname IS NOT NULL
),
CONSTRAINT check_emp_ni CHECK (ni_num IS NOT NULL),
CONSTRAINT unique_emp_ni UNIQUE (ni_num),
CONSTRAINT check_emp_job CHECK (job_r IS NOT NULL),
CONSTRAINT check_emp_branch CHECK (branch_r IS NOT NULL),
CONSTRAINT check_emp_join CHECK (join_date IS NOT NULL)
)NESTED TABLE phones STORE AS at_nested_emp_phones_storage
/
CREATE TRIGGER at_employee_trig
BEFORE INSERT ON at_employee_table
FOR EACH ROW
DECLARE
supervisor_job at_job_type;
supervisor_branch at_branch_type;
branch at_branch_type;
BEGIN
SELECT DEREF(:new.branch_r),
DEREF(DEREF(:new.supervisor_r).job_r),
DEREF(DEREF(:new.supervisor_r).branch_r)
INTO branch,
supervisor_job,
supervisor_branch
FROM dual;
IF supervisor_job.position NOT IN ('head','manager','team leader')
OR supervisor_branch.branch_code != branch.branch_code
THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid supervisor');
END IF;
END;
/
CREATE TABLE branches OF at_branch_type;
INSERT INTO branches VALUES (at_branch_type(1));
CREATE TABLE jobs OF at_job_type;
INSERT INTO jobs VALUES (at_job_type('head'));
INSERT INTO jobs VALUES (at_job_type('X'));
Then you can insert a supervisor:
INSERT INTO at_employee_table (
emp_Id,
address,
name,
phones,
ni_num,
supervisor_r,
job_r,
branch_r,
join_date
) VALUES (
1,
at_address_type('street', 'city', 'postcode'),
at_name_type('t1', 'fname1', 'lname1'),
at_nested_phone(),
'00001',
NULL,
(SELECT REF(j) FROM jobs j WHERE position = 'head'),
(SELECT REF(b) FROM branches b WHERE branch_code = 1),
DATE '1970-01-01'
);
And then the employee managed by the supervisor:
INSERT INTO at_employee_table (
emp_Id,
address,
name,
phones,
ni_num,
supervisor_r,
job_r,
branch_r,
join_date
) VALUES (
2,
at_address_type('street', 'city', 'postcode'),
at_name_type('t2', 'fname2', 'lname2'),
at_nested_phone(),
'00002',
(SELECT REF(e) FROM at_employee_table e WHERE emp_id = 1),
(SELECT REF(j) FROM jobs j WHERE position = 'X'),
(SELECT REF(b) FROM branches b WHERE branch_code = 1),
DATE '1970-01-01'
);
But if you try to insert an employee with an invalid manager:
INSERT INTO at_employee_table (
emp_Id,
address,
name,
phones,
ni_num,
supervisor_r,
job_r,
branch_r,
join_date
) VALUES (
3,
at_address_type('street', 'city', 'postcode'),
at_name_type('t3', 'fname3', 'lname3'),
at_nested_phone(),
'00003',
(SELECT REF(e) FROM at_employee_table e WHERE emp_id = 2),
(SELECT REF(j) FROM jobs j WHERE position = 'X'),
(SELECT REF(b) FROM branches b WHERE branch_code = 1),
DATE '1970-01-01'
);
Then you get the exception:
ORA-20001: Invalid supervisor
ORA-06512: at "FIDDLE_HYWEWLWKRFTOFCGCZRUO.AT_EMPLOYEE_TRIG", line 17
ORA-04088: error during execution of trigger 'FIDDLE_HYWEWLWKRFTOFCGCZRUO.AT_EMPLOYEE_TRIG'