Using Refs With Objects Stored In Tables

Question

I'm trying to use refs to link objects between tables. Something like the following.

CREATE OR REPLACE TYPE TYP_REFT AS OBJECT 
(A NUMBER, B NUMBER);
/
CREATE TABLE REFTT
(
  ref_id number,
  my_reft typ_reft
)
/
CREATE TABLE REFTAB
(ID    REF    TYP_REFT);
/
INSERT INTO REFTT VALUES (typ_reft(1,2));
/
INSERT INTO REFTAB
SELECT REF(T.my_reft) FROM REFTT T
/
SELECT * FROM REFTAB;

This doesn't work though. Giving me the error

Error starting at line : 18 in command -
INSERT INTO REFTAB
SELECT REF(T.my_reft) FROM REFTT T
Error at Command Line : 19 Column : 12
Error report -
SQL Error: ORA-00904: "T"."MY_REFT": invalid identifier
00904. 00000 -  "%s: invalid identifier"

It works fine if the table REFTT is made by saying

CREATE TABLE REFTT OF TYP_REFT

But since I'd like to store other information in the table besides just the object, it's a little problematic.

score 1 · Accepted Answer · answered Sep 21 '15 at 02:43

REF reference

REF takes as its argument a table alias associated with a row of an object table.

CREATE TABLE REFTT OF TYP_REFT

If you create your REFTT table of TYPE_REFT, then your code would work, as you stated. You can store other information in REFTAB.

CREATE TABLE REFTAB
(ID    REF    TYP_REFT,
 MOREINFO VARCHAR2(20));


INSERT INTO REFTAB
  SELECT REF(T), 'BIGTIME'
    FROM REFTT T;

Using Refs With Objects Stored In Tables

1 Answers1