I have to set up a distributed object-relational system with replication between two users on the same database. That is, I've got a database with two users User1 and User2. Both of them use the same script to create the object types and the tables. Some of these tables need have the same content on each user, so I'm using triggers so whenever there's a change on one side, it gets replicated on the other side.
Suppose there's a UDT called DepartmentType and another one called AreaType. Each Area is related to one Department, and a Department may include many Areas. So, AreaType has a reference to a DepartmentType, and DepartmentType has a nested table of references to AreaTypes. This nested table has a type NT_AreasInDepartment (for example). Both types have a primary key called Name. This is the trigger for the insertions on the Department table:
CREATE OR REPLACE TRIGGER NewDept
AFTER INSERT
ON DeptObj
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO DIST_DeptObj VALUES (:NEW.Name, :NEW.Areas);
END NewDept;
/
It does not work, it says that User1.NT_AreasInDepartment was expected, but User2.NT_AreasInDepartment was found (error ORA-00932). If I use
INSERT INTO DIST_DeptObj VALUES (:NEW.Name, :NULL);
It works, but I'm not able to insert any record on the nested table afterwards. I've also tried using something like
INSERT INTO DIST_DeptObj VALUES (:NEW.Name, NT_AreasInDepartment());
But it doesn't work either. In the oracle official forum they told me I could use the same object identifier in to create the types, but given both users are in the same system, that is not possible.
Is there a way of having the same object types used by the two users?
Disclaimer: Probably this looks weird and there may be easier ways of doing this, but I'm afraid this is what I was requested. That is, yes, this is a college assignment. If it were up to me, I wouldn't even be using Oracle.
Yes it does look ugly. I'd firstly opt for a single UDT that is referenced by both user1 and user2.
Failing that, I'd want some explicit function that breaks the user1.udt down into its components (VARCHAR2, numbers, dates etc.) and uses them in a call to the user2.udt constructor. And vice versa.
An XML based extract/constructor is also worth considering (especially in a production environment if there's a chance that the UDT definitions would get out of step, which they almost certainly would in real life).