Exforsys

H I D E

Home arrow Technical Training arrow Oracle 11g Tutorial

Table of Contents

 Oracle 11g Collections
 Oracle 11g Collections - Nested Tables
 Oracle 11g Collections - VARRAY
 Oracle 11g Collections - Collection Methods

Oracle 11g Collections - Collection Methods

Page 4 of 4
Author: Saurabh Gupta     Published on: 2nd Jun 2011

Oracle 11g Collections

Collection methods

Oracle provides set of inbuilt methods, which can be used with a non null collection (except EXISTS), to yield some information and perform basic operations on the collection subscript and element. The list below shows the collections available and their applicability with the different collection types. If the collection is NULL, system raises COLLECTION_IS_NULL exception.

The collection information includes their null behavior of the collection, and element count. The basic utilities provided by Oracle are collection extension, deletion, and trimming.

All collection methods are compiled along with their behavior, in the below table.

Syntax [4]

Sample Code
  1. collection_name.method_name[(parameters)]
Copyright exforsys.com


These collection methods can be used as PL/SQL construct. Oracle does not support the collection methods in SQL.

Now, we shall study the usage of collection methods.

EXISTS

The method checks a collection for the NULL property. It returns FALSE for null collections while TRUE for initialized ones. It is the only collection method which is capable to work with NULL collections.

Note that if the input index value is out of range of the collection, system raises SUBSCRIPT_OUTSIDE_LIMIT exception.

Check the below illustration to demonstrate its use.

Example [7a]

Sample Code
  1. IF EX_NEST_TAB.EXISTS (5) THEN
  2. ...
  3. ELSE
  4. …
  5. END IF;
Copyright exforsys.com


COUNT

The method counts the number of elements in a collection. Check the below illustration.

Check the below illustration to demonstrate the use of COUNT method.

Example [7b]

Sample Code
  1. DECLARE
  2.   TYPE T IS TABLE OF NUMBER;
  3.   L T := T();
  4. BEGIN
  5.   FOR I IN 1..100
  6.   LOOP
  7.     L.EXTEND;
  8.     L(L.LAST):=NULL;
  9.   END LOOP;
  10.   DBMS_OUTPUT.PUT_LINE('The Collection has '||to_char(L.COUNT)||' elements');
  11. END;
  12. /
  13.  
  14. The Collection has 100 elements
  15. PL/SQL block successfully completed.
Copyright exforsys.com


LIMIT

The method returns the maximum count of elements in a collection array. It can only be used with VARRAYs.

Note the difference between COUNT and LIMIT methods in the below example.

Example [7c]

Sample Code
  1. DECLARE
  2.   TYPE v IS VARRAY(10) OF NUMBER;
  3.   M V := V();
  4. BEGIN
  5.   M := V(1,3,2,3,2,3);
  6.   DBMS_OUTPUT.PUT_LINE('The Collection has '||to_char(M.COUNT)||' elements');
  7.   DBMS_OUTPUT.PUT_LINE('The Collection can accommodate '||to_char(M.LIMIT)||' elements');
  8. END;
  9. /
  10.  
  11. The Collection has 6 elements
  12. The Collection can accommodate 10 elements
  13.  
  14. PL/SQL procedure successfully completed.
Copyright exforsys.com


FIRST and LAST

The methods return the first and last subscript of a collection. For an uninitialized or empty collection, the method values are NULL. Check the below illustration to demonstrate its use of the methods.

Example [7d]

Sample Code
  1. DECLARE
  2.   TYPE T IS TABLE OF NUMBER;
  3.   M T := T();
  4. BEGIN
  5.    DBMS_OUTPUT.PUT_LINE('FIRST and LAST subscripts for Empty collection are '||M.FIRST||' and '||M.LAST);
  6.    M := T(272,4720,482,183,481,372,482,127,5943);
  7.    DBMS_OUTPUT.PUT_LINE('FIRST and LAST subscripts for populated collection are '||M.FIRST||' and '||M.LAST);  
  8. END;
  9. /
  10.  
  11. FIRST AND LAST subscripts FOR Empty collection are  AND
  12. FIRST AND LAST subscripts FOR populated collection are 1 AND 9
  13.  
  14. PL/SQL procedure successfully completed.
Copyright exforsys.com


PRIOR and NEXT

The method give the subscript which is before and after the current subscript. These methods are used as to avoid NO_DATA_FOUND exception in sparse collections.

Example [7e]

Sample Code
  1. DECLARE
  2. TYPE T IS TABLE OF NUMBER;
  3. M T := T(12,15,73,1,87,22,89);
  4.    BEGIN
  5. DBMS_OUTPUT.PUT_LINE('Subscript before the 5th element is:'||TO_CHAR(M.PRIOR(5)));
  6. DBMS_OUTPUT.PUT_LINE('Subscript after the 5th element is:'||TO_CHAR(M.NEXT(5)));
  7.    END;
  8.    /
  9.  
  10.    Subscript before the 5th element IS:4
  11.    Subscript after the 5th element IS:6
  12.  
  13.    PL/SQL procedure successfully completed.
Copyright exforsys.com


EXTEND

The method allows addition of new elements to a persistent collection. By default, it appends one element to a collection. Upon specification, it can add N elements to a collection. EXTEND(m,n) make ‘m’ copies of nth element to a collection.

Note that is cannot be used for Associative arrays.

Example [7f]

Sample Code
  1. DECLARE
  2.   TYPE T IS TABLE OF NUMBER;
  3.   M T := T(12,15,73,1,87,22,89);
  4. BEGIN
  5.  DBMS_OUTPUT.PUT_LINE('No of elements in Collection:'|| TO_CHAR(M.COUNT));
  6.  M.EXTEND;
  7.  DBMS_OUTPUT.PUT_LINE('No of elements in Collection after first extension:'|| TO_CHAR(M.COUNT));
  8.  M.EXTEND(5);
  9.  DBMS_OUTPUT.PUT_LINE('No of elements in Collection after second extension:'|| TO_CHAR(M.COUNT));
  10.  M.EXTEND(2,3);
  11.  DBMS_OUTPUT.PUT_LINE('No of elements in Collection after third extension:'|| TO_CHAR(M.COUNT));
  12. END;
  13. /
  14.  
  15. No of elements IN Collection:7
  16. No of elements IN Collection after first extension:8
  17. No of elements IN Collection after second extension:13
  18. No of elements IN Collection after third extension:15
  19.  
  20. PL/SQL procedure successfully completed.
Copyright exforsys.com


TRIM

As the name suggests, the method trims a persistent collection. By default, it trims single element in a collection. It can also trim N elements, if the count is specified with the method/<.p>

Note that is cannot be used for Associative arrays.

Example [7g]

Sample Code
  1. DECLARE
  2.   TYPE T IS TABLE OF NUMBER;
  3.   M T := T(12,15,73,1,87,22,89);
  4. BEGIN
  5.  DBMS_OUTPUT.PUT_LINE('Elements in Collection:'|| TO_CHAR(M.COUNT));
  6.  M.TRIM;
  7.  DBMS_OUTPUT.PUT_LINE('Elements in Collection after first trim:'|| TO_CHAR(M.COUNT));
  8.  M.TRIM(5);
  9.  DBMS_OUTPUT.PUT_LINE('Elements in Collection after second trim:'|| TO_CHAR(M.COUNT));
  10. END;
  11. /
  12.  
  13. Elements IN Collection:7
  14. Elements IN Collection after first trim:6
  15. Elements IN Collection after second trim:1
  16.  
  17. PL/SQL procedure successfully completed.
Copyright exforsys.com


Ads

DELETE

The method drops a cell from a collection. It removes a single element by default, but can remove N elements and even by range, if specified with the method.

Example [7h]

Sample Code
  1. DECLARE
  2.   TYPE T IS TABLE OF NUMBER;
  3.   M T := T(12,15,73,1,87,22,89);
  4. BEGIN
  5.    DBMS_OUTPUT.PUT_LINE ('Count of elements before deletion:'||TO_CHAR(M.COUNT));
  6.    DBMS_OUTPUT.PUT_LINE ('Last index of elements before deletion:'||TO_CHAR(M.LAST));
  7.    M.DELETE(2);
  8.    DBMS_OUTPUT.PUT_LINE ('Count of elements after deletion:'||TO_CHAR(M.COUNT));
  9.    DBMS_OUTPUT.PUT_LINE ('Last subscript of elements after deletion:'||TO_CHAR(M.LAST));
  10. END;
  11. /
  12.  
  13. Count of elements before deletion:7
  14. Last INDEX of elements before deletion:7
  15. Count of elements after deletion:6
  16. Last INDEX of elements after deletion:7
  17.  
  18. PL/SQL procedure successfully completed.
Copyright exforsys.com


Read Next: Oracle Functions and Procedures


 
This tutorial is part of a Oracle 11g Tutorial tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Tutorial

  1. Oracle 11g Result Cache
  2. Compound Triggers in Oracle 11g
  3. Oracle 11g Invisible Index
  4. Oracle 11g Virtual Columns
  5. Oracle 11g Collections
  6. Oracle Functions and Procedures
  7. Oracle 11g Exception Handling
  8. Oracle 11g Read Only Tables
  9. Oracle 11g Subprogram Overloading
  10. Conditional compilation in Oracle PL/SQL
  11. Oracle Pragma
  12. UTL_FILE
  13. Manage Oracle dependencies
  14. Oracle 11g SecureFiles
  15. Oracle VPD implementation
  16. PL/Scope and PL/SQL Hierarchical Profiler
  17. Oracle Partitioning Overview
  18. Oracle Analytic Enhancements
  19. Oracle XML Storage
 

Comments

 

Ads

Exforsys Connect

Ads

Exforsys e-Newsletter

ebook