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

Answer 2

One way to solve this is to encapsulate the lookup into a constructor function.

create or replace type test_ty as object(
  t_id number,
  array some_arr ,
  constructor function test_ty (self in out test_ty
             , t_id number
             , array some_arr)  
    return self as result        
) ;
/

create or replace type body test_ty as 

  constructor function test_ty (self in out test_ty
             , t_id number
             , array some_arr)  
    return self as result        
    is
        n number;
    begin
        for idx in array.first() .. array.last
        loop
            select s_id into n
            from some_tbl
            where s_id = array(idx);
        end loop;
        self.t_id := t_id;
        self.array := array;
        return;         
    exception 
        when no_data_found then
           raise_application_error(-20099, 'array contains invalid value');     
    end test_ty ;
end ;
/

Here is how to invoke it:

SQL> insert into test_tbl values(test_ty(9, some_arr(1,2,3)));

1 row created.

SQL> insert into test_tbl values(test_ty(10, some_arr(1,2,3,4)));
insert into test_tbl values(test_ty(10, some_arr(1,2,3,4)))
                            *
ERROR at line 1:
ORA-20099: array contains invalid value
ORA-06512: at "FOX.TEST_TY", line 21


SQL> 

Note that you need to use the type explicitly to invoke the constructor:

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

1 row created.

SQL> 

---

"Example student and class. one class can have maximum 10 students. and with many-many table i cannot do that."

Well, you could do it like this:

create table class_student (
    class_id number not null 
    , student_id number not null 
    , attendee_no number(2,0) not null 
    , constraint class_student_pk 
        primary key (class_id, student_id, attendee_no) 
    , constraint class_student_ck
        check (attendee_no between 1 and 10 ) 
    , constraint class_student_class_fk 
        foreign key (class_id) references class 
    , constraint class_student_student_fk 
        foreign key (student_id) references student
);