Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




EnterpriseDB: SQL Collections
Oracle Tips by Burleson

I covered SQL collections in the last chapter in the section about Arrays.  Oracle PL/SQL provides three types of collections:  Associative Table (Indexed by Binary_Integer or Varchar2), Nested Table and Varray.  While SPL does not provide these three data types, it does provide an Index By Binary_Integer table. 

SPL also offers the Array[] type, i.e. Integer[] or text[], which can mimic both nested tables and Varrays.  The downside to using the native Array type is that you break compatibility with Oracle.  As EnterpriseDB works to make SPL increasingly compatible, the ability to use non-standard functionality may go away.  I will explain how to use the Oracle compatible methods as well as native and you can decide when and where they are appropriate.

The syntax for creating an SPL table is: 

TYPE <type name> IS TABLE OF <scalar data type OR
                             record type OR
                             %TYPE OR

I explained anchored variables and records above.  A scalar data type is a stand-alone type like Date, Varchar2, etc.  You cannot use a composite type in an index by table unless it follows the syntax for a RECORD as described below.


    INDEX BY BINARY_INTEGER;           -- Step 1 

  a_tab t_tab;                         -- Step 2 

  n_tab text[];                        -- Step 3 

  v_tab text[5];                       -- Step 4 


  a_tab(1) := 'Lewis';                 -- Step 5a
  a_tab(2) := 'Lewis';                 -- Step 5b

  n_tab := Array['Louis', 'Louis'];    -- Step 6

  v_tab := Array['Luis', 'Luis'];      -- Step 7

  DBMS_OUTPUT.PUT_LINE(                -- Step 8

   'A_TAB1 = ' || a_tab(1) ||
   ', A_TAB2 = ' || a_tab(2) ||
   ', N_TAB1 = ' || n_tab[1] ||
   ', N_TAB2 = ' || n_tab[2] ||
   ', V_TAB1 = ' || v_tab[1] ||
   ', V_TAB2 = ' || v_tab[2] );


From the point on, I will no longer show you the output of both the executable code and the output.  To save space, I will display the output only.  The above code produces:

INFO:  A_TAB1 = Lewis, A_TAB2 = Lewis, N_TAB1 = Louis, N_TAB2 = Louis, V_TAB1 = Luis, V_TAB2 = Luis

SPL Procedure successfully complete

Now I will walk through the code step by step.

In Step 1, I created the Oracle style, INDEX BY, or associative table.  This creates the TABLE data type, not the variable.

Step 2 is declaring a variable using the data type of the table I just created.  When using the TYPE keyword, I am actually creating a type, not a variable.  Step 2 creates the variables of the new type.

Step 3 is the declaration of an EnterpriseDB Advanced Server native array variable.  The syntax is exactly like that of the array types we covered in Chapter 2.  This type of array, without a limit on the size, is most like an Oracle Nested table.  Like a nested table, this type of array can be stored in the database.

Step 4 is also the declaration of a native array variable.  This one is more like an Oracle Varray.  This type of collection has a size limit, in this case 5.

In Steps 5a and 5b, I assign the value of "Lewis" to the first and second row of the a_tab array.  I refer to the value by use of a subscript.  The subscript is the position of the element in the array.  A subscript is like a row number in a database table.

In Step 6, I assign the value of "Louis" to the first and second row in the n_tab array.

In Step 7, I assign the value of "Luis" to the first and second row in the v_tab array.

Growing native arrays (n_tab and v_tab) is a bit more complicated than growing an index by table.  It is easy to size them on assignment (as I did above in steps 6 and 7).  I will show you how you can use the native arrays, and grow them dynamically, in the section on coding for compatibility.

In Step 8, I am using DBMS_OUTPUT to display the values of each table.  In this instance, I am displaying each of the array names followed by the data we just assigned to the arrays. 

If you are familiar with almost any other programming language, the table type is simply an array.  The case shown above is a single dimension array.

You can also create multi-dimensional arrays.  You can do that by using a RECORD type as the data type for your SPL table:


  TYPE r_user_record IS RECORD (
    emp_name emp.ename%TYPE,
    emp_dept emp.deptno%TYPE,
    emp_length_in_service INTERVAL );  -- Step 1 

  TYPE t_tab IS TABLE OF r_user_record
    INDEX BY BINARY_INTEGER;           -- Step 2 

  a_tab t_tab;                         -- Step 3 


  a_tab(1).emp_name := 'Lewis';        -- Step 4

  a_tab(2).emp_name := 'George';       -- Step 5 

  DBMS_OUTPUT.PUT_LINE(                -- Step 6

   'A_TAB1 = ' || a_tab(1).emp_name ||
   ', A_TAB2 = ' || a_tab(2).emp_name );

INFO:  A_TAB1 = Lewis, A_TAB2 = Lewis

When you use a table of records, you combine the syntax of tables and records.  You put your subscript for the table on your table variable, a_tab(1), and you append the record column you would like to access, a_tab(1).emp_name.  So the syntax to access an element in a table of records is:

<table name(<array row subscript>)>.<record column name>

In the example above, I declare the record type in step 1, declare the table type using that record type in step 2 and declare the variable I will use in my code in step 3.  In steps 4 and 5, I assign names to rows one and two (using subscripts) to my table(subscript).record_variable, in this case, emp_name.

In SPL, we can get sneaky and create an even more complex multi-dimensional array.  I want to point out that this syntax is not compatible with Oracle (although with minor changes you can make it work).

You can declare an array as part of your record type by anchoring to an array column in a table.  You would access the values of that array just as you would normally.


  TYPE r_user_record IS RECORD (
    emp_name emp.ename%TYPE,
    emp_dept emp.deptno%TYPE,
    emp_length_in_service INTERVAL,
    e_text array_tab.names%TYPE );     -- Step 1 

  TYPE t_tab IS TABLE OF r_user_record
    INDEX BY BINARY_INTEGER;           -- Step 2

a_tab t_tab;                         -- Step 3


  a_tab(1).emp_name := 'Lewis';        -- Step 4

  a_tab(2).emp_name := 'George';       -- Step 5

   a_tab(1).e_text := ARRAY['First Row', 'Second Row', 'Etc.']; -- Step 6

   DBMS_OUTPUT.PUT_LINE(                -- Step 7

   'A_TAB1 = ' || a_tab(1).emp_name ||
   ', A_TAB1.e_text[1]= ' || a_tab(1).e_text[1] );

INFO:  A_TAB1 = Lewis, A_TAB1.e_text[1]= First Row

That is a pretty neat side effect of combining SPL with native data types but remember that this syntax is not compatible with Oracle or PL/SQL.  If compatibility is important to you, you may want to be careful with this syntax.  In the chapter on maintaining compatibility between Oracle and EnterpriseDB Advanced Server, I cover compatibility issues in greater detail.


This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter