Search code examples
oracle-databaseplsqlref

How to use REF datatype in PL/SQL code or in SQL statements?

In my understanding REF is an datatype which can store reference to an Object.

I have created a type person.

CREATE TYPE person AS OBJECT(
    names   VARCHAR2(20),
    age     NUMBER(4, 2)
);

I created a table of it and inserted a row.

create table person_table of person;

insert into person_table values ('Sam', 24);

Can anyone please tell how I can use REF to store the reference of the inserted object in some other table or PL/SQL block or use REF in any SQL statement?

Solution

  • You need to simply use REF in table creation which will store reference of the PERSON type as follows:

    CREATE TABLE EMPLOYEE
(PERSON_DETAILS REF PERSON);

    and you can insert data into this table using REF keyword by pointing to the PERSON_TABLE which is itself table of PERSON type as follows:

    INSERT INTO EMPLOYEE
SELECT REF(P) FROM PERSON_TABLE P WHERE NAMES = 'Sam';