Referencing array values

Question

I have been faced with the following issue.

create type some_ty as object(
s_id number
);

create table some_tbl of some_ty;

insert into some_tbl values(1);
insert into some_tbl values(2);
insert into some_tbl values(3);

create type some_arr as varray(5) of number;

create type test_ty as object(
t_id number,
array some_arr
) ;

create table test_tbl of test_ty;

insert into test_tbl values(10, some_arr(1,2,3,4));

My question is if there is an sql way to check if the values of some_arr are exist in some_tbl? Because now it inserts "4" as well which is not a record of some_tbl.

I cannot use ref on varray.

I have managed to do this using pl/sql loop. I just want to know if there is an easy way for that.

Answer 1

If you are using an Object-Relational database and want to to have a reference to another object then use a REF. You can use this in a VARRAY:

create type some_ty as object( s_id number );
/

create table some_tbl of some_ty;

insert into some_tbl values(1);
insert into some_tbl values(2);
insert into some_tbl values(3);

create or replace type some_arr as varray(5) of REF some_ty;
/

create type test_ty as object(
  t_id  number,
  t_list some_arr
);
/

create table test_tbl of test_ty(
  t_list CONSTRAINT test_tbl__t_list__nn NOT NULL
);

-- Collection to allow a variable length list to be passed to the insert.
CREATE TYPE INTLIST AS TABLE OF INTEGER;
/

insert into test_tbl values(
  10,
  (
    SELECT CAST( COLLECT( REF(t) ORDER BY s_id ) AS some_arr )
    FROM   some_tbl t
    WHERE  s_id MEMBER OF INTLIST( 1,2,3,4 )
    HAVING COUNT(*) = CARDINALITY( INTLIST( 1,2,3,4 ) ) -- Ensure all items are matched
  )
);

Would throw an error for violating the NOT NULL constraint but:

insert into test_tbl values(
  10,
  (
    SELECT CAST( COLLECT( REF(t) ORDER BY s_id ) AS some_arr )
    FROM   some_tbl t
    WHERE  s_id MEMBER OF INTLIST( 1,2,3 )
    HAVING COUNT(*) = CARDINALITY( INTLIST( 1,2,3 ) ) -- Ensure all items are matched
  )
);

Would work and you could then do:

SELECT t.t_id, DEREF( l.COLUMN_VALUE ).s_id
FROM   test_tbl t
       LEFT OUTER JOIN TABLE( t.t_list ) l
       ON ( 1 = 1 );

Outputs:

      T_ID              DEREF(L.COLUMN_VALUE).S_ID
---------- ---------------------------------------
        10                                       1
        10                                       2
        10                                       3