Oracle: getting references in PLSQL

Question

I need to get the reference of :new.OBJECT_VALUE but I don't now how.

I tried doing this.

Declaration of types:

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;
/

Declatation 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;
/

Declaration of trigger:

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 next error:

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

Can anyone help me?

Answer 1

Try MAKE_REF:

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