Skip to Main Content
  • Questions

  • Unique value in Oracle Collection variable

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Samby.

Asked: July 19, 2016 - 2:28 pm UTC

Last updated: July 22, 2016 - 7:37 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi team,

I have one doubt on oracle collection while implementing on Scenario in my assignment.

We need to have collection variable column (vARRAYS) inside a Table ,
Sample table structure looks like below

id name list
1 ABC varray_type(1,2,3,4) Ok
2 DEF varray_type(1,2,3,4) ok
3 GHI varray_type(1,2,3,4,4) Not ok.

Here the column LIST is of varray type, each id will hold a list.
We need to have a constraint such that list should not have a duplicate value. (Example id =3 ).

Could you please suggest on this.

and Chris said...

There is an "is a set" operator. This returns false if there are duplicates. But this only applies to nested tables. Not varrays. And you can't use it in a check constraint:

SQL> create or replace type nt is table of int;
  2  /

Type created.

SQL> create table t (
  2    id int,
  3    n  nt
  4  ) nested table n store as n_tab;

Table created.

SQL>
SQL> alter table t add constraint n_c check (n is a set);
alter table t add constraint n_c check (n is a set)
                                          *
ERROR at line 1:
ORA-22958: This operation is not allowed in check constraints or triggers


So that's not much good!

The multiset operators only work on nested tables. So you can't do something similar for your varray.

You could create a trigger on the table. This validates that there are no duplicates in the array.

One way to do this is to:

- Loop through the varray
- For each element, assign it to an associative array. The index of this is the current value of the varray.
- Check whether the count of the varray equals the count of the associate array. If they're different, then you've mapped one or more values from the varray to the same index. So you have a duplicate!

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create or replace type varr is varray(10) of int;
  2  /

Type created.

SQL>
SQL> create table t (
  2    id int,
  3    v  varr
  4  );

Table created.

SQL>
SQL> create or replace trigger trg
  2  before insert or update on t
  3  for each row
  4  declare
  5    type tp is table of int index by pls_integer;
  6    arr tp;
  7  begin
  8    for i in 1 .. :new.v.count loop
  9       arr(:new.v(i)) := i;
 10    end loop;
 11
 12    if arr.count <> :new.v.count then
 13      raise_application_error (-20000, 'duplicate elements!');
 14    end if;
 15  end;
 16  /

Trigger created.

SQL>
SQL> insert into t values (1, varr(1, 2, 3));

1 row created.

SQL> insert into t values (1, varr(1, 2, 3, 3));
insert into t values (1, varr(1, 2, 3, 3))
            *
ERROR at line 1:
ORA-20000: duplicate elements!
ORA-06512: at "CHRIS.TRG", line 10
ORA-04088: error during execution of trigger 'CHRIS.TRG'


This is icky though. I wouldn't recommend this approach in the real world.

Instead, I'd have the varray a normal relational table. This would have a FK pointing to the original table. You can then create unique constraints on it to prevent duplicates!

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Samby, July 21, 2016 - 4:41 pm UTC

Hi Chris,

Thanks for your time for answering my question. But i am sorry i couldn't get the below line. It would be great if you give some example on this.

"Instead, I'd have the varray a normal relational table. This would have a FK pointing to the original table. You can then create unique constraints on it to prevent duplicates! "

Thanks in Advance.

Chris Saxon
July 22, 2016 - 7:37 am UTC

Instead of a varray, have something like:

create table t (
  x number primary key
);

create table t_chd (
  x number, 
  y number,
  primary key (x, y),
  foreign key (x) references t (x)
);

The values that were in your varray go in t_chd.y instead. The primary key ensures you can't have any duplicates for a given value of X.

To Samby

Rajeshwaran, Jeyabal, July 22, 2016 - 10:41 am UTC 

rajesh@ORA12C> create table t2 as
  2  select b1.owner,b1.table_name,b1.column_name
  3  from dba_tab_columns b1,
  4      dba_tables b2
  5  where b1.owner = b2.owner and
  6  b1.table_name = b2.table_name;

Table created.

rajesh@ORA12C>
rajesh@ORA12C> insert /*+ append */ into t2 select * from t2;

30075 rows created.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C> insert /*+ append */ into t2 select * from t2;

60150 rows created.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C> insert /*+ append */ into t2 select * from t2;

120300 rows created.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C>
rajesh@ORA12C> create table t1 as
  2  select *
  3  from dba_tables;

Table created.

rajesh@ORA12C> alter table t1 add constraint t1_pk
  2  primary key(owner,table_name);

Table altered.

rajesh@ORA12C>
rajesh@ORA12C> alter table t2 add constraint t2_fk
  2  foreign key(owner,table_name)
  3  references t1;

Table altered.

rajesh@ORA12C>
rajesh@ORA12C> create index t2_idx on t2(owner,table_name);

Index created.

rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T2');

PL/SQL procedure successfully completed.

rajesh@ORA12C>
rajesh@ORA12C> create or replace type myobj is object(
  2     owner varchar2(30),
  3     table_name varchar2(30),
  4     column_name varchar2(30));
  5  /

Type created.

rajesh@ORA12C> create or replace type mytab is table of myobj;
  2  /

Type created.

rajesh@ORA12C> create table t3
  2  nested table t2_list store as ntt_list
  3  as
  4  select t1.* ,
  5     cast(multiset(select myobj(t2.owner,t2.table_name,t2.column_name)
  6     from t2
  7     where t2.owner = t1.owner and
  8             t2.table_name = t1.table_name) as mytab) t2_list
  9  from t1 ;

Table created.

rajesh@ORA12C>
rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T3');

PL/SQL procedure successfully completed.

rajesh@ORA12C>
rajesh@ORA12C>


Tkprof shows this.

select *
from t1, t2
where t1.owner = t2.owner and
t1.table_name = t2.table_name

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1605      0.62       0.91        231       3157          0      240600
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1607      0.62       0.92        231       3157          0      240600

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    240600     240600     240600  HASH JOIN  (cr=3157 pr=231 pw=0 time=427116 us cost=607 size=72180000 card=240600)
      2427       2427       2427   NESTED LOOPS  (cr=96 pr=88 pw=0 time=19265 us cost=607 size=72180000 card=240600)
      2427       2427       2427    NESTED LOOPS  (cr=96 pr=88 pw=0 time=18624 us)
      2427       2427       2427     STATISTICS COLLECTOR  (cr=96 pr=88 pw=0 time=18240 us)
      2427       2427       2427      TABLE ACCESS FULL T1 (cr=96 pr=88 pw=0 time=13873 us cost=37 size=633447 card=2427)
         0          0          0     INDEX RANGE SCAN T2_IDX (cr=0 pr=0 pw=0 time=0 us)(object id 112224)
         0          0          0    TABLE ACCESS BY INDEX ROWID T2 (cr=0 pr=0 pw=0 time=0 us cost=520 size=3861 card=99)
    240600     240600     240600   TABLE ACCESS FULL T2 (cr=3061 pr=143 pw=0 time=49868 us cost=520 size=9383400 card=240600)

select
t3.owner                      ,
t3.table_name                 ,
t3.tablespace_name            ,
t3.cluster_name               ,
t3.iot_name                   ,
t3.status                     ,
t3.pct_free                   ,
t3.pct_used                   ,
t3.ini_trans                  ,
t3.max_trans                  ,
t3.initial_extent             ,
t3.next_extent                ,
t3.min_extents                ,
t3.max_extents                ,
t3.pct_increase               ,
t3.freelists                  ,
t3.freelist_groups            ,
t3.logging                    ,
t3.backed_up                  ,
t3.num_rows                   ,
t3.blocks                     ,
t3.empty_blocks               ,
t3.avg_space                  ,
t3.chain_cnt                  ,
t3.avg_row_len                ,
t3.avg_space_freelist_blocks  ,
t3.num_freelist_blocks        ,
t3.degree                     ,
t3.instances                  ,
t3.cache                      ,
t3.table_lock                 ,
t3.sample_size                ,
t3.last_analyzed              ,
t3.partitioned                ,
t3.iot_type                   ,
t3.temporary                  ,
t3.secondary                  ,
t3.nested                     ,
t3.buffer_pool                ,
t3.flash_cache                ,
t3.cell_flash_cache           ,
t3.row_movement               ,
t3.global_stats               ,
t3.user_stats                 ,
t3.duration                   ,
t3.skip_corrupt               ,
t3.monitoring                 ,
t3.cluster_owner              ,
t3.dependencies               ,
t3.compression                ,
t3.compress_for               ,
t3.dropped                    ,
t3.read_only                  ,
t3.segment_created            ,
t3.result_cache               ,
t3.clustering                 ,
t3.activity_tracking          ,
t3.dml_timestamp              ,
t3.has_identity               ,
t3.container_data             ,
t3.inmemory                   ,
t3.inmemory_priority          ,
t3.inmemory_distribute        ,
t3.inmemory_compression       ,
t3.inmemory_duplicate         ,
t4.*
from t3, table(t3.t2_list) t4

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0         15          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1605      0.76       7.43       2566      17931          0      240600
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1607      0.76       7.44       2566      17946          0      240600

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    240600     240600     240600  NESTED LOOPS  (cr=17931 pr=2566 pw=0 time=34590784 us cost=41 size=86937567 card=256453)
    240600     240600     240600   NESTED LOOPS  (cr=11624 pr=827 pw=0 time=11357980 us cost=41 size=86937567 card=4645278)
      2427       2427       2427    TABLE ACCESS FULL T3 (cr=1238 pr=0 pw=0 time=10905 us cost=40 size=674706 card=2427)
    240600     240600     240600    INDEX RANGE SCAN SYS_FK0000112225N00066$ (cr=10386 pr=827 pw=0 time=1823054 us cost=0 size=0 card=1914)(object id 112227)
    240600     240600     240600   TABLE ACCESS BY INDEX ROWID NTT_LIST (cr=6307 pr=1739 pw=0 time=4278679 us cost=0 size=6466 card=106)


Nested Table and Varray rock in PL/SQL, but not as storage mechanism.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library