Oracle:在PLSQL中获取引用

Question

我需要获取:new.OBJECT_VALUE的引用，但我现在不知道如何获取。

我试过这么做。

类型声明：

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

表的声明：

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

触发器的声明：

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;

如果有人想尝试使用数据：

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

但是我得到了下一个错误：

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

有谁可以帮我？

Answer 1

试试MAKE_REF

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

Answer 2

解决此Oracle错误的选项包括：

选项#1

尝试重新定义您的表，使您的列名都不是保留字。

选项#2

尝试用双引号将保留字引起来。

例如，如果您有一个包含名为number的列的供应商表，并且您尝试按如下方式更新此字段：

UPDATE suppliers SET number = 10000;