Oracle: getting links in PLSQL

Question

I need to get a link to: new.OBJECT_VALUE, but I don't know how to do this.

I tried to do this.

Type declaration:

CREATE TYPE Virus_ObjType AS OBJECT ( name VARCHAR2(50), description VARCHAR2(700) ); / CREATE TYPE Paper_ObjType AS OBJECT ( id NUMBER, title VARCHAR(100), magazine VARCHAR2(100), vol NUMBER, num NUMBER, year NUMBER(4) ); / CREATE TYPE Virus_NestTabType AS TABLE OF REF Virus_ObjType; / CREATE TYPE Paper_NestTabType AS TABLE OF REF Paper_ObjType; / ALTER TYPE Virus_ObjType ADD ATTRIBUTE papers Paper_NestTabType CASCADE; / ALTER TYPE Paper_ObjType ADD ATTRIBUTE virus Virus_NestTabType CASCADE; / 

Declaration of tables:

 CREATE TABLE Virus_ObjTable OF Virus_ObjType ( PRIMARY KEY(name)) NESTED TABLE papers STORE AS papersVirus_NestTable; CREATE TABLE Paper_ObjTable OF Paper_ObjType ( PRIMARY KEY (id)) NESTED TABLE virus STORE AS virus_NestTable; CREATE GLOBAL TEMPORARY TABLE virus_aux ( type varchar2(1), virus REF Virus_ObjType, paper REF Paper_ObjType) ON COMMIT DELETE ROWS; / 

Trigger declaration:

 create or replace trigger Virus_Trigger_Before BEFORE INSERT ON Virus_ObjTable FOR EACH ROW DECLARE paper REF Paper_ObjType; virus REF Virus_ObjType; BEGIN IF inserting THEN IF :new.papers IS NOT NULL THEN FOR i IN 1..:new.papers.COUNT LOOP -- getting the pointer of the papers SELECT refe INTO paper FROM (SELECT REF(a) as refe FROM Paper_ObjTable a) WHERE refe=:new.papers(i); SELECT REF(:NEW.OBJECT_ID) INTO virus FROM dual; INSERT INTO virus_aux VALUES ('i', virus, paper); END LOOP; END IF; END IF; END; 

If someone wants to try with data:

 INSERT INTO paper_objtable (id, title, magazine, vol, num, year) VALUES (1, 'Que mala es la Gripe', 'Fuzzy Sets', 1, 1, 1993); INSERT INTO paper_objtable (id, title, magazine, vol, num, year) VALUES (3, 'La Gripe ataca de nuevo', 'Information Sciences', 1, 1, 1996); INSERT INTO virus_objtable (SELECT 'Gripe', 'Virus de la Gripe', CAST(COLLECT(REF(a)) AS Paper_NestTabType) FROM paper_objtable a); 

but I get the following error:

 ORA-01747: invalid user.table.column, table.column, or columns specification 

Can anybody help me?

Answer 1

Try MAKE_REF :

 SELECT MAKE_REF(virus_objtable, :new.object_id) INTO virus FROM dual; 

Answer 2

The parameter to solve this Oracle error:

OPTION number 1

Try overriding the table so that none of your column names are reserved words.

OPTION number 2

Try to include the reserved word in double quotes.

For example, if you have a vendor table with a column named number, and you tried to update this field as follows:

 UPDATE suppliers SET number = 10000;