Collections in Oracle - Part 4

Collections continued .....


Working with Collections in SQL:

Oracle introduced the below pseudo-functions to manipulate collections that live in the database.

CAST

Maps a collection of one type to a collection of another type. This can encompass mapping a VARRAY to a nested table.

MULTISET

Maps a database table to a collection. With MULTISET and CAST, you can actually retrieve rows from a database table as a collection-typed column.

TABLE

Maps a collection to a database table. This is the inverse of MULTISET.

Note:
The collection pseudo-functions are not available in PL/SQL, only in SQL.
We can, however, employ these operators in SQL statements that appear in your PL/ SQL code.

CAST:

The CAST operator can be used in a SQL statement to convert from one built-in datatype to another built-in datatype.


CREATE OR REPLACE TYPE NAMES_NT is TABLE of VARCHAR2(200);
CREATE OR REPLACE TYPE NAMES_VA  IS VARRAY(10) OF VARCHAR2(200);

Declare
NAMES_LIST_NT NAMES_NT := NAMES_NT('KIRAN','VISHALI','KODAVATI','SENIVARAPU');
NAMES_LIST_VA NAMES_VA := NAMES_VA();
BEGIN
SELECT CAST(NAMES_LIST_NT AS NAMES_VA) INTO NAMES_LIST_VA FROM DUAL;
FOR I IN NAMES_LIST_VA.FIRST..NAMES_LIST_VA.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(NAMES_LIST_VA(I));
END LOOP;
END;
/

MULTISET:

The MULTISET function exists only for use within CAST.

MULTISET allows you to retrieve a set of data and convert it on the fly to a collection type.

SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)
       FROM DUAL;


EXAMPLE:

CREATE OR REPLACE TYPE NAMES_VA  IS VARRAY(10) OF VARCHAR2(200);


Declare

NAMES_LIST_VA NAMES_VA := NAMES_VA();
MY_LIST_LIMIT NUMBER;

BEGIN

MY_LIST_LIMIT:=NAMES_LIST_VA.LIMIT;

SELECT CAST(MULTISET(
                      SELECT ENAME FROM SCOTT.EMP
                      WHERE ROWNUM < MY_LIST_LIMIT+1
                    )
                       AS NAMES_VA
           ) INTO NAMES_LIST_VA
FROM DUAL;

FOR I IN NAMES_LIST_VA.FIRST..NAMES_LIST_VA.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(NAMES_LIST_VA(I));
END LOOP;

END;
/

NOTE: MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.


TABLE: ( key word : COLUMN_VALUE )

The TABLE operator converts a collection-valued column into something you can SELECT from.

Example:
CREATE OR REPLACE TYPE NAMES_T AS TABLE OF VARCHAR2 (100);

DECLARE
       MY_favorites   NAMES_T
          := NAMES_T ('KIRAN', 'KODAVATI');
    BEGIN
       FOR rec IN  (SELECT column_value favs
                      FROM TABLE (MY_favorites)
                    WHERE COLUMN_VALUE LIKE 'KO%'
                    )
       LOOP
          DBMS_OUTPUT.put_line (rec.favs);
       END LOOP;
    END;
/


DATA DICTIONARIES:

SELECT type_name FROM user_types WHERE typecode ='COLLECTION';

SELECT text FROM user_source WHERE type = 'TYPE';

SELECT table_name,column_name FROM user_tab_columns WHERE data_type = 'XXXX';

SELECT name, type FROM user_dependencies WHERE referenced_name='XXXX';


Privileges:


Only one Oracle privilege  EXECUTE applies to collection types.

Note: Even if a user has select privilege on a table and if the table has a column of collection type, the user can select the table only if he has execute privilege on the type of the collection column.