PL/SQL – Collection Methods

Method Name & Purpose
EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
COUNT Returns the number of elements that a collection currently contains.
LIMIT Checks the maximum size of a collection.
FIRST Returns the first (smallest) index numbers in a collection that uses the integer subscripts.
LAST Returns the last (largest) index numbers in a collection that uses the integer subscripts.
PRIOR(n) Returns the index number that precedes index n in a collection.
NEXT(n) Returns the index number that succeeds index n.
EXTEND Appends one null element to a collection.
EXTEND(n) Appends n null elements to a collection.
EXTEND(n,i) Appends n copies of the i^th element to a collection.
TRIM Removes one element from the end of a collection.
TRIM(n) Removes n elements from the end of a collection.
DELETE Removes all elements from a collection, setting COUNT to 0.
DELETE(n) Removes the n^th element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
DELETE(m,n) Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Collection Methods Associative Array Or Index By Table

Example

DECLARE

  TYPE names_at IS TABLE OF NUMBER INDEX BY VARCHAR2(100); 
  
  v_names names_at; 
  v_name  VARCHAR2(100); 

BEGIN 

  -- Adding elements to the table 
  v_names('Scott') := 90; 
  v_names('Smith') := 92; 
  v_names('Jones') := 95;
  v_names('Arunn') := 93;
  v_names('AArun')  := 89;
  
  DBMS_OUTPUT.PUT_LINE('First Element: '|| v_names(v_names.FIRST));
  DBMS_OUTPUT.PUT_LINE('Last Element: '  || v_names(v_names.LAST));
  DBMS_OUTPUT.PUT_LINE('Count : '    || v_names.COUNT ||', '|| 'Always Return Number of Elements');
  DBMS_OUTPUT.PUT_LINE('First Index: '  || v_names.FIRST ||', '|| 'Always Return Smallest Index Value');
  DBMS_OUTPUT.PUT_LINE('Last Index : '  || v_names.LAST  ||', '|| 'Always Return Largest Index Value');
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Prior to First Element: '|| v_names.PRIOR('AArun'));
  DBMS_OUTPUT.PUT_LINE('Prior to Any Element(Except First): '|| v_names.PRIOR('Jones'));
  DBMS_OUTPUT.PUT_LINE('Next to Last Element: '|| v_names.NEXT('Smith'));
  DBMS_OUTPUT.PUT_LINE('Next to Any Element(Except Last): '|| v_names.NEXT('Jones'));
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Deleting Single Element: AArun, First Index');
  v_names.DELETE('AArun');
  IF v_names.EXISTS('AArun') THEN
	DBMS_OUTPUT.PUT_LINE(v_names('AArun'));
  ELSE
	DBMS_OUTPUT.PUT_LINE('Element Deleted');
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Refer Above Values of Count, First, Last And Compare It With Below....');
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_names.COUNT);
  DBMS_OUTPUT.PUT_LINE('First Index After Delete: '||v_names.FIRST);
  DBMS_OUTPUT.PUT_LINE('Last Index After Delete(Index 1 Deleted): '||v_names.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Deleting From Arunn to Scott');
  v_names.DELETE('Arunn', 'Scott');
  DBMS_OUTPUT.PUT_LINE('First Element After Delete: '||v_names.FIRST);
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_names.COUNT);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('You cannot use EXTEND, LIMIT and TRIM in Associative Array or Index By Table');
	
END;

Output

First Element: 89
Last Element: 92
Count : 5, Always Return Number of Elements
First Index: AArun, Always Return Smallest Index Value
Last Index : Smith, Always Return Largest Index Value
======================================
Prior to First Element: 
Prior to Any Element(Except First): Arunn
Next to Last Element: 
Next to Any Element(Except Last): Scott
======================================
Deleting Single Element: AArun, First Index
Element Deleted
======================================
Refer Above Values of Count, First, Last And Compare It With Below....
Count After Delete: 4
First Index After Delete: Arunn
Last Index After Delete(Index 1 Deleted): Smith
======================================
Deleting From Arunn to Scott
First Element After Delete: Smith
Count After Delete: 1
======================================
You cannot use EXTEND, LIMIT and TRIM in Associative Array or Index By Table

Collection Methods in Nested Table

Example

DECLARE 

  TYPE values_nt IS TABLE OF VARCHAR2(20);
  v_values values_nt := values_nt();

BEGIN 
  v_values := values_nt(108,97,98,99,100,101);
  
  DBMS_OUTPUT.PUT_LINE('First Element: '|| v_values(v_values.FIRST));
  DBMS_OUTPUT.PUT_LINE('Last Element: '  || v_values(v_values.LAST));
  DBMS_OUTPUT.PUT_LINE('Count : '    || v_values.COUNT ||', '|| 'Always Return Number of Elements');
  DBMS_OUTPUT.PUT_LINE('First Index: '  || v_values.FIRST ||', '|| 'Always Return Smallest Index Value');
  DBMS_OUTPUT.PUT_LINE('Last Index : '  || v_values.LAST  ||', '|| 'Always Return Largest Index Value');

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Prior to First Index: '|| v_values.PRIOR(1));
  DBMS_OUTPUT.PUT_LINE('Prior to Any Index(Except First): '|| v_values.PRIOR(5));
  DBMS_OUTPUT.PUT_LINE('Next to Last Index: '|| v_values.NEXT(6));
  DBMS_OUTPUT.PUT_LINE('Next to Any Index(Except Last): '|| v_values.NEXT(1));

  DBMS_OUTPUT.PUT_LINE('Deleting Single Element: 108, First Index');
  v_values.DELETE(1);
  --  DELETE and EXISTS method does not raises SUBSCRIPT_OUTSIDE_LIMIT error
  IF v_values.EXISTS(1) THEN
    DBMS_OUTPUT.PUT_LINE(v_values(1));
  ELSIF v_values.EXISTS(7) THEN
    DBMS_OUTPUT.PUT_LINE('7th Element Exist');
  ELSE
    DBMS_OUTPUT.PUT_LINE('1st Index Element Deleted, 7th Element Not Exist');
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Refer Above Values of Count, First, Last And Compare It With Below....');
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('First Index After Delete: '||v_values.FIRST);
  DBMS_OUTPUT.PUT_LINE('Last Index After Delete(Index 1 Deleted): '||v_values.LAST);
  DBMS_OUTPUT.PUT_LINE('Limit : '||v_values.LIMIT);

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Extending by 2 Elements Copying 6th Index Value to 7th and 8th');
  v_values.EXTEND(2,6);
  DBMS_OUTPUT.PUT_LINE('Printing 6th, 7th and 8th Index Values: '||v_values(6)||', '||v_values(7)||', '||v_values(8));
  DBMS_OUTPUT.PUT_LINE('Extending by 1 Elements With Null Value. And add 999 as 9th Element');
  v_values.EXTEND();
  v_values(9) := 999;
  DBMS_OUTPUT.PUT_LINE('Printing 9th Index Value: '||v_values(9));
  DBMS_OUTPUT.PUT_LINE('Count After Extend: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Extend: '||v_values.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Trim By Default Value, Which is last index');
  v_values.TRIM();
  DBMS_OUTPUT.PUT_LINE('Count After Trim by 1: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Trim by 1: '||v_values.LAST);
  DBMS_OUTPUT.PUT_LINE('Trim By 2 Index....');
  v_values.TRIM(2);
  DBMS_OUTPUT.PUT_LINE('Count After Trim by 2: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Trim by 2: '||v_values.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Deleting From Index 2 to 4');
  v_values.DELETE(2, 4);
  DBMS_OUTPUT.PUT_LINE('First Element After Delete: '||v_values.FIRST);
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Delete: '||v_values.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('You cannot use LIMIT in Nested Table');  
END;

Output

First Element: 108
Last Element: 101
Count : 6, Always Return Number of Elements
First Index: 1, Always Return Smallest Index Value
Last Index : 6, Always Return Largest Index Value
======================================
Prior to First Index: 
Prior to Any Index(Except First): 4
Next to Last Index: 
Next to Any Index(Except Last): 2
Deleting Single Element: 108, First Index
1st Index Element Deleted, 7th Element Not Exist
======================================
Refer Above Values of Count, First, Last And Compare It With Below....
Count After Delete: 5
First Index After Delete: 2
Last Index After Delete(Index 1 Deleted): 6
Limit : 
======================================
Extending by 2 Elements Copying 6th Index Value to 7th and 8th
Printing 6th, 7th and 8th Index Values: 101, 101, 101
Extending by 1 Elements With Null Value. And add 999 as 9th Element
Printing 9th Index Value: 999
Count After Extend: 8
Last Index After Extend: 9
======================================
Trim By Default Value, Which is last index
Count After Trim by 1: 7
Last Index After Trim by 1: 8
Trim By 2 Index....
Count After Trim by 2: 5
Last Index After Trim by 2: 6
======================================
Deleting From Index 2 to 4
First Element After Delete: 5
Count After Delete: 2
Last Index After Delete: 6
======================================
You cannot use LIMIT in Nested Table

Collection Methods in VARRAY

Example

DECLARE 

  TYPE list_vt IS VARRAY(26) OF VARCHAR2(20); 
  v_list list_vt := list_vt(); 

BEGIN 
  DBMS_OUTPUT.put_line('Varray Limit: '|| v_list.LIMIT);  
  v_list := list_vt(51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70);
  
  DBMS_OUTPUT.PUT_LINE('First Element: '|| v_list(v_list.FIRST));
  DBMS_OUTPUT.PUT_LINE('Last Element: '  || v_list(v_list.LAST));
  DBMS_OUTPUT.PUT_LINE('Count : '    || v_list.COUNT ||', '|| 'Always Return Number of Elements');
  DBMS_OUTPUT.PUT_LINE('First Index: '  || v_list.FIRST ||', '|| 'Always Return Smallest Index Value');
  DBMS_OUTPUT.PUT_LINE('Last Index : '  || v_list.LAST  ||', '|| 'Always Return Largest Index Value');

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Prior to First Index: '|| v_list.PRIOR(1));
  DBMS_OUTPUT.PUT_LINE('Prior to Any Index(Except First): '|| v_list.PRIOR(5));
  DBMS_OUTPUT.PUT_LINE('Next to Last Index: '|| v_list.NEXT(26));
  DBMS_OUTPUT.PUT_LINE('Next to Any Index(Except Last): '|| v_list.NEXT(13));

  DBMS_OUTPUT.PUT_LINE('Since Varrays Are Dense, Deleting Single Element Is Not Possible');
  -- v_list.DELETE(1); -- Not Possible in VARRAY
  -- EXISTS method does not raises SUBSCRIPT_OUTSIDE_LIMIT error
  IF v_list.EXISTS(21) THEN
    DBMS_OUTPUT.PUT_LINE(v_list(21));
  ELSE
    DBMS_OUTPUT.PUT_LINE('21st Element Not Exist');
  END IF;
 
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Extending by 2 Elements Copying 20th Index Value to 21st and 22nd');
  v_list.EXTEND(2,20);
  
  DBMS_OUTPUT.PUT_LINE('Printing 20th, 21st and 22nd Index Values: '||v_list(20)||', '||v_list(21)||', '||v_list(22));
  DBMS_OUTPUT.PUT_LINE('Extending by 1 Elements With Null Value. And add 71 as 23rd Element');
  v_list.EXTEND();
  v_list(23) := 71;
  DBMS_OUTPUT.PUT_LINE('Printing 23rd Index Value: '||v_list(23));
  DBMS_OUTPUT.PUT_LINE('Count After Extend: '||v_list.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Extend: '||v_list.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Trim By Default Value, Which is last index');
  v_list.TRIM();
  DBMS_OUTPUT.PUT_LINE('Count After Trim by 1: '||v_list.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Trim by 1: '||v_list.LAST);
  DBMS_OUTPUT.PUT_LINE('Trim By 2 Index....');
  v_list.TRIM(2);
  DBMS_OUTPUT.PUT_LINE('Count After Trim by 2: '||v_list.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Trim by 2: '||v_list.LAST);

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Deleting From Varray');
  v_list.DELETE();
  DBMS_OUTPUT.PUT_LINE('First Element After Delete: '||v_list.FIRST);
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_list.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Delete: '||v_list.LAST);
  DBMS_OUTPUT.PUT_LINE('Limit : '||v_list.LIMIT);

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Delete - All Or Nothing');
  DBMS_OUTPUT.PUT_LINE('You cannot Extend Varray Beyond Limit. Can''t Delete Selected Index');  
END;

Output

Varray Limit: 26
First Element: 51
Last Element: 70
Count : 20, Always Return Number of Elements
First Index: 1, Always Return Smallest Index Value
Last Index : 20, Always Return Largest Index Value
======================================
Prior to First Index: 
Prior to Any Index(Except First): 4
Next to Last Index: 
Next to Any Index(Except Last): 14
Since Varrays Are Dense, Deleting Single Element Is Not Possible
21st Element Not Exist
======================================
Extending by 2 Elements Copying 20th Index Value to 21st and 22nd
Printing 20th, 21st and 22nd Index Values: 70, 70, 70
Extending by 1 Elements With Null Value. And add 71 as 23rd Element
Printing 23rd Index Value: 71
Count After Extend: 23
Last Index After Extend: 23
======================================
Trim By Default Value, Which is last index
Count After Trim by 1: 22
Last Index After Trim by 1: 22
Trim By 2 Index....
Count After Trim by 2: 20
Last Index After Trim by 2: 20
======================================
Deleting From Varray
First Element After Delete: 
Count After Delete: 0
Last Index After Delete: 
Limit : 26
======================================
Delete - All Or Nothing
You cannot Extend Varray Beyond Limit. Can't Delete Selected Index.

Sathiyan

Oracle SQL, PL/SQL, Linux, Python, Odoo ERP

PL/SQL – Collection Methods

Collection Methods Associative Array Or Index By Table

Example

Output

Collection Methods in Nested Table

Example

Output

Collection Methods in VARRAY

Example

Output

Leave a comment Cancel reply

Collection Methods Associative Array Or Index By Table

Example

Output

Collection Methods in Nested Table

Example

Output

Collection Methods in VARRAY

Example

Output

Share this:

Leave a comment Cancel reply