jQuery triggering custom event (Observer pattern)

Introduction

We all know what basic events in JavaScript and jQuery are. They represent handlers that are triggered at specify event (click, timer, mouse move, etc.). But how do we create custom events that are triggered on our command, here comes jQuery to the rescue! Also doing this (calling custom events), we can simulate classic Go4 observer pattern (publish/subscribe).

Using jQuery is beneficiary, because, we are not concerned about various DOM levels, browser specific stuff (like: (event.target) ? event.target : event.srcElement;...), does IE 6,7,8 support event capture phase, and stuff like that. We basically create another abstraction level over various JavaScript implementations.

So, let the fun begin!

Live event handling

In our example we will use jQuery ability to manage event handler on the fly (as we manipulate the DOM by adding or removing elements). This means that we will proactively establish event handlers for elements that don't exist yet. jQuery provide this functionality with the live() method. Syntax for this method is similar like bind() or for example click() method.

Triggering events

In out example we will also need some method that will automatically invoke (trigger) event handlers on our behalf under script control. For this we will use the trigger method. This method does its best to simulate the event to be triggered. One curiosity is that it even populate instance of jQuery event (event object in jQuery style :) ), but because there is no real event, properties that report event-specific values such as the location of mouse x and y cordinates, have no value. Ok, let's move on.

Example

First jQuery part:

$(function() { //Wait until DOM is fully loaded .
    $('#addBox').click(function() {
        $('td.box:first-child').clone().appendTo('tr.boxLine');                
    });

    $('#paintBlue').click(function() {                
        $('td.box').trigger('paintThem');
    });

    $('td.box').live('paintThem', function() {
        $(this).css('background', 'blue');                
    });            
});

2-5: First we create simple click handler that add new box like element on the DOM. We create boxes using simple table and just adding new table data elements by coping them. Please see html part, and first picture where we add couple of boxes.

6-8: Here we trigger custom event named "paintThem". This is simple click event triggered by button. We need to trigger custom event on elements (in this case 'td.box') that posses custom event (but, also we can trigger standard events like: click, hover...).

10-12: The "real meat" of this whole story. Here we see a use of the live() method to proactively establish event handlers. The 'td.box' elements will be added dynamically to the DOM and live method will automatically establish handlers as necessary.
This way, we sat it up _once_ and jQuery will handle the details whenever an item that matches that selector (td.box) is create (or destroyed). Great stuff!

So as you can see, this example is not so fancy, it just paint red boxes to blue. But the way it does it is interesting. I can also walk trough all elements and paint them to certain color, but this approach is much cleaner and in some situations better (asynchronous refresh (AJAX) for example).

Then, html part. My apologies for this fuzzy looking html code (it's blogger fault! :) ).

Custom event is a very useful concept. Using it, we can attach code to an element as a handler for a custom event, and cause it to execute by triggering the event. The beauty of this approach, as opposed to directly calling code, is that we can register the custom handlers in advance, and by simply triggering the event, cause any registered handlers to be executed, without having to know they've been established.

Custom event concept in jQuery can be seen as limited version of Observer pattern. In this pattern (also know as publish/subscribe pattern) we subscribe an element to a particular event by establishing a handler for that element, and then when the event is published (triggered), any elements that are subscribe to that event will automatically invoke their handlers.

In this way we are creating loose coupling (and that is always a _good_ idea) in our JavaScript code. This make our code cleaner and meaner!

Custom trigger in action:

Adding boxes.

Triggering event to paint boxes in blue.

Dynamic typing in PL/SQL

Introduction

PL/SQL is a statically typed language. This means that datatypes must be declared and checked at compile time. There are also occasions when you really need the capabilities of dynamic typing and for those occasions, the Any types were introduced in PL/SQL (back then in 9i). These dynamic datatypes enable you to write programs that manipulate data when you don't know the type of that data until runtime. You determine the type of the value at runtime through introspection (using gettype function, as you will see in example).

You cannot manipulate the internal structure of Any types, you must use procedures and functions for that.

We will use following family members of Any:
AnyData (can hold a single value of any type, whatever it's built-in scalar datatype or user-defined object type).
AnyType (can hold a description of a type -- you will see).

In following example I create three user-defined types that are representing some kind of transport mean. The subsequent PL/SQL code then uses SYS.AnyType to define a heterogeneous array of transports.

CREATE OR REPLACE TYPE airplane_o AS OBJECT(
        engine_type VARCHAR2(35),
        lift NUMBER                                       
)
/
CREATE OR REPLACE TYPE car_o AS OBJECT(
        engine_power NUMBER,                             
        color VARCHAR2(35)       
)  
/
CREATE OR REPLACE TYPE train_o AS OBJECT (
       engine_type VARCHAR2(35),
       speed NUMBER                
)
/

SET SERVEROUTPUT ON;

DECLARE    
    TYPE transports_t IS VARRAY(6) OF SYS.AnyData;
    transports transports_t;
    airplane airplane_o;
    car car_o;
    train train_o;    
    ret_val NUMBER;
BEGIN
    
    transports := transports_t(
                    AnyData.ConvertObject(
                        airplane_o('turboprop', 2300)), 
                    AnyData.ConvertObject(
                        airplane_o('jet', 3500)),                              
                    AnyData.ConvertObject(
                        car_o(55, 'red')),
                    AnyData.ConvertObject(
                        train_o('electric', 80)),
                    AnyData.ConvertObject(
                        train_o('steam', 45)),
                    AnyData.ConvertObject(
                        airplane_o('ramjet', 9000))                                    
                              );
                
    
    FOR i IN 1..transports.COUNT LOOP        
        IF transports(i).GetTypeName = 'HR.AIRPLANE_O' THEN            
            ret_val := transports(i).GetObject(airplane);
            --ret_val can be success or no_data...I did not check this.
            DBMS_OUTPUT.put_line('Airplane: ' || 'engine type: ' || 
                        airplane.engine_type || ', lift: ' || airplane.lift || 'lbs');
        ELSIF transports(i).GetTypeName = 'HR.CAR_O' THEN
            ret_val := transports(i).GetObject(car);
            DBMS_OUTPUT.put_line('Car: ' || 'engine power: ' || 
                        car.engine_power || 'KW, color: ' || car.color);
        ELSIF transports(i).GetTypeName = 'HR.TRAIN_O' THEN
            ret_val := transports(i).GetObject(train);    
            DBMS_OUTPUT.put_line('Train: ' || 'engine type: ' || 
                        train.engine_type || ', speed: ' || train.speed || 'KMh');
        END IF;
        
    END LOOP;    
    
END;

Execution of this program in Toad generates following output:

Now I will comment important points in this code.
28 - 41: Here are heterogeneous transports stored in a VARRAY. airplane_o, car_o, train_o are constructors of an object, and AnyData.ConvertObject cast this objects into instance of AnyData.
45, 50, 54: Here we introspect current object (in the loop) and get its type.
46, 51, 55: Retrieve the specific object. We are ignoring return code.

48, 52, 56: Once I had the object in a variable, I can write its properties in DBMS_OUTPUT.

Oracle PL/SQL - cut down round-trips to database with collections

Introduction

Using PL/SQL collections you could combine the master and detail tables together in a single SELECT that convert the detail records into a collection type. This feature has enormous significance for data intensive programs (almost all enterprise applications) because the number of round-trips can be cut down. This is happening because we do not incurring the overhead of duplicating the master record with each and every detail record.

So imagine that I use following SELECT in my version of popular HR application:

SELECT 
        d.department_id,
        d.department_name,
        e.employee_id,
        e.first_name,
        e.last_name
 FROM departments d 
    INNER JOIN employees e
        ON  d.department_id = e.department_id

Ok, here the departments table act as master table of employee table. So for each department we have zero or more employees.

Now, consider following example:

SET SERVEROUTPUT ON

CREATE OR REPLACE TYPE employees_ids_t AS TABLE OF NUMBER(6); 
/
DECLARE
    CURSOR dep_emp_cur IS
        SELECT 
                d.department_id,
                d.department_name,
          CAST(MULTISET(SELECT e.employee_id
                        FROM employees e
                        WHERE d.department_id = e.department_id)
            AS employees_ids_t) AS emp_collection
         FROM departments d;
    dep_emp_row dep_emp_cur%ROWTYPE;
    
    row_index PLS_INTEGER;    
    
        
BEGIN
    OPEN dep_emp_cur;
    
    LOOP
        
        FETCH dep_emp_cur INTO dep_emp_row;

        EXIT WHEN dep_emp_cur%NOTFOUND;
        
        DBMS_OUTPUT.put(dep_emp_row.department_id || ', ' 
                || dep_emp_row.department_name || ': employees: ');
        
        row_index := dep_emp_row.emp_collection.FIRST;
        LOOP            
            EXIT WHEN row_index IS NULL; 
            
            DBMS_OUTPUT.put(dep_emp_row.emp_collection(row_index) || ', ');
            
            row_index := dep_emp_row.emp_collection.NEXT(row_index);
            
        END LOOP;
        
        DBMS_OUTPUT.put_line('');
            
    END LOOP;
    
    CLOSE dep_emp_cur;    
    
END;

What the hell is happening here?

Ok look at this pictures from Toad:
When I execute first simple JOIN I get something like this:



You can see how master table is repeated for each detail table.

But if I run second code, you will get something different:



Here you have "blurred" master data with detail data in _single_ row and no repeat of master whatsoever. This is powerful stuff!

JavaScript concepts part 4 - closures

Introduction

JavaScript is a language that's widely used across the web, but it's often not deeply used by many of the page authors writing it. So in this "JavaScript concepts series" I try to introduce some of the deeper concepts of the JavaScript language. This is last article in this short tutorial.

Closures

To people programming in traditional OO or procedural languages, closures are often an add concept to grasp. While to those programmers with functional programming background, they're really natural and cozy concept.

Stated as easy as possible, a closure is a Function instance coupled with the local variables that are not declared in function inner scope, but in outer scope (an outer scope of the function if you know what I mean). So they are just Functions + local variables, no more and no less. Put this all in dynamic environment and you get some powerful concept for handling asynchronous callbacks.

When function is declared, it has the ability to reference any variables that are in its scope (in scope that function has access to) at the point of the declaration. This is off course all familiar, but catch is that this "outer" variables are carried along with the function even after the point of declaration has gone out of scope, closing the declaration.
This ability is essential tool for writing effective JavaScript code. Consider following example:

var local = 1;
window.setInterval(function() {
                    alert(local);
                    local++;
                   },
                   3000);

In example we declare local variable (named "local") and assign number to it. We then use timer function ("setInterval") to establish a timer that will fire every 3 seconds. As the callback for timer, we specify an inline function that references local "local" variable and shot its value. Also we increment that variable in body of the function.

So what happen here. We might assume that because the callback will fire off three seconds after the page loads, the value of the local "local" variable will be undefined. After all, the block in which "local" is declared goes out of scope when page finished loading, because the script in header (or else on the page) finished executing? But it works!

Although it is true that the block in which "local" is declared goes out of scope when page finished loading, the closure created by the declaration of the function + variable "local" stays in scope for lifetime of the function. So, woala!

This is the end of this series, I hope you learn something useful and interesting.

JavaScript concepts part 3 - callbacks and context

Introduction

JavaScript is a language that's widely used across the web, but it's often not deeply used by many of the page authors writing it. So in this "JavaScript concepts series" I try to introduce some of the deeper concepts of the JavaScript language. This is third article in series that bring you closer to understand how functions works.

Function callbacks

The nature of the code in a web pages is asynchronous, so it is nature of functions in JavaScript. And one of the most interesting concept in asynchronous programming are callback functions.

Consider example:

var myarray = [22, 21, 3, 5, 1, 105];

function sortAscending(a, b) {
    return a - b;
}

myarray.sort(sortAscending);

alert(myarray);

The result of this function is alert with elements 22, 21, 3, 5, 1, 105 sorted ascending (1, 3, 5, 21, 22, 105).
So we passed function as reference inside of another function (sort). Passing a function as a parameter is no different in JavaScript than passing any other value. So, because sort function call back to function in our own code, this type of function constructions are termed as call back functions.

Usually are callback functions defined as anonymous functions because it look more elegant (sort of). As in the following example:

var myarray = [22, 21, 3, 5, 1, 105];

function sortAscending(a, b) {
    return a - b;
}

myarray.sort(function (a, b) {
                return a - b;
            });

alert(myarray);

I personally like declared functions because you never really know if you are going to need it in some other place, and it also look less "hack-like" (I hate code that hides stuff from me!).
Functions context

OO languages provide a means (usually by using this) to reference the current instance of the object that we working on. JavaScript also posses this reference, but JavaScript implementation of this differs from its OO counterparts in subtle but significant ways.
In JavaScript, the context (this) of function is the object which contains some reference to invoke the function. This sound pretty confusing, but consider following example:

var desc = function() {
    alert (this.address + "," + this.yearBuild);
}

var house = {
  noOfdoors : 5,
  address : "Main road 51",
  yearBuild : new Date(2001, 2, 11),
  description : desc
};

So in this example we have function that reference current context and show alert box. If we call this function like in following code:

house.description();

It will work fine, but if we call in on top-level context (window):

var desc = function() { //Top-level function declaration.
    alert (this.address + "," + this.yearBuild);
}
desc(); //or window.description();

It will not work, to make it work we need to put variables on top-level context like so:

var desc = function() {
    alert (this.address + "," + this.yearBuild);
}
address = 'Some road from Window';
yearBuild = new Date(2002, 3, 12);
desc();
description(); 

It will work because this is pointing to current execution context that is in this case top-level context.
So to wrap it up: In JavaScript the object referenced by this is determined not by how the function is declared but by how it's invoke (on which context is invoked to be precise). This means that the same function can have different context depending on how it's called.
As this is not enough, JavaScript gives us the means to explicitly control what's used as the function context. We can set the function context to whatever we want by invoking a function via the Funcion method call() or applay(). The call() method invokes the function specifying, as its first parameter the context that will be using, and the remainder of the parameters becomes the parameters of the called function. Second method (applay()) only use array of parameter and it will not be considered in following example because it behave exactly like call() function.
Consider the following code:

function showMe() {
    return this.me;
}

var m1 = {
    me : 'meFirst'
}
var m2 = {
    me : 'meSecond'
}

window.me = 'meWindow'; //or me= 'meWindow'

m1.whoAmI = showMe;

alert(showMe()); //meWindow
alert(m1.whoAmI()); //meFirst, we change context to m1.
alert(showMe.call(m2)); //meSecond, explicitly changing context to m2.

I hope that this clear thing a little bit. In first alert we call function on top-context, simply by call it in alert function. In second alert we call it through referenced property on m1 object (so we call it on m1 object context). In third and last alert we explicitly change context to m2 and call it on m2 object context.

This is the end on part 3, please continue to part 4 (closures) if you like.

PL/SQL Collections as columns in oracle database

Intruduction

This time I will try to show, how you can put collection in database columns in relational table. This can be useful when you want to persist collection data, or you don't want to create additional detail table in you schema. Whatever is your reason, this can be quite useful.

Note that putting collection in table columns is nothing new for object databases (for instance for Google App Engine Bigtable).

Simple example

So in this example I will demonstrate use of collection (VARRAY in this case, but you can also use nested table - see this article about PL/SQL collections) as columns in a relational table.

First, I declare schema-level collection TYPE named varchar_collection_t that can contain up to four VARCHAR2 elements. Then I create a relational table, which will contain collection TYPE as columns (characters column). Finally I populate characters collection and INSERT that collection into the anime table.

CREATE TYPE varchar_collection_t IS VARRAY (4) OF VARCHAR2(100); 
/
CREATE TABLE animes (
    name VARCHAR2(255),
    characters varchar_collection_t
);

DECLARE
    anime_characters varchar_collection_t := varchar_collection_t();
BEGIN
    anime_characters.EXTEND(4);
    
    anime_characters(1) := 'Satsuki';
    anime_characters(2) := 'Mei';
    anime_characters(3) := 'Totoro';
    anime_characters(4) := 'Kanta';
    
    INSERT INTO animes 
            (name, characters)          
    VALUES  ('My Neighbor Totoro', anime_characters);    
            
END;

As you can see I use simple INSERT syntax for inserting collection in table. Simple as that!

Using VARRAY as columns datatype actually store collection data "inline" with the rest of the table's data (something like comma separated values), but using nested table it create behind the scene real database table. So Oracle says that VARRAY columns are intended for "small" arrays, and that nested tables for "large" data arrays.

One important note when storing collection in database is that you cannon directly manipulate with data stored in collection. You need to use CAST and TABLE syntax for this.
This in out of scope of this tutorial, I will just show how can you filter using data from "table" collection in plain SQL.

SELECT * FROM animes a
WHERE 'Mei' IN
       (SELECT * FROM TABLE(a.characters));

Please, note the use of TABLE syntax. Also I have only one row in table, but I think that is not relevant to understand the example.

JavaScript concepts part 2 - functions

Introduction

JavaScript is a language that's widely used across the web, but it's often not deeply used by many of the page authors writing it. So in this "JavaScript concepts series" I try to introduce some of the deeper concepts of the JavaScript language. This is second article in series that gives you quick overview about JavaScript functions.

Functions: what it is all about?

In many traditional OO languages (Java, C++, OBERON, C#,...) objects can contain data and they can possess methods. In this languages, the data and methods are distinct concepts. We use this methods to alter state of the objects by altering values of object properties (fields).
JavaScript walks a different path.

Functions in JavaScript are considered object. JavaScript will make no distinction between object type like String, Numbers, windows object, custom object and functions. Like other objects, functions are defined by a constructor named "Function". Using this constructor is similar like using constructor in OO language.
Function can have parameters and value of the function (because it is an object) can be assigned to variable, or to an property of the object, returned as function return value, or it can be passed as parameter to another function. All this is possible because function in JavaScript are treated in the same way as other objects. Because of that we say that functions are first-class object in JavaScript.

Example of functions:

function one() {
    alert('doStuff');
}
function two(name, value) {
    alert('calculate stuff');
}
function three() {
    return 'stuff';
}

Functions names

Consider following example:

function findMatix() {
  alert('Hello Neo, what is Matrix?');
}

Does this create function named "findMatix"? No, it doesn't! Although that notation may seem familiar, it's in essence the same syntactic sugar (for which is JavaScript popular) used by var to create window properties (which is described in previous article about JavaScript objects). So that mean that this function create a function instance and assign it to the window property using the function name, as in the following:

  findMatix = function() {
    alert('Hello Neo, what is Matrix?');
  }

When we declare a top-level named function, a Function instance is created ans assigned to a property (with name of the function name) of window object.

Although this may seem like syntactic juggling, it's important to understand that Functions instances are values that can be assigned to variables, properties, or parameters just like instances of other object type. And it is important to note that disembodies instances are not of any use unless they're assigned to a variable, property, or parameter.

JavaScript concepts part 1 - objects

Introduction

JavaScript is a language that's widely used across the web, but it's often not deeply used by many of the page authors writing it. So in this "JavaScript concepts series" I try to introduce some of the deeper concepts of the JavaScript language. This is first article in series.

Why objects?

The most important concept in JavaScript it that functions are first-class object in JavaScript, which is a result of the way JavaScript defines and deals with functions. In order to understand what it means for a function to be an object, we must first make sure that we understand what JavaScript object is all about.

In JavaScript when object is created it holds no data and expose little in the way of semantics. JavaScript object don't support "classic" object-oriented. At least not in obvious and familiar (to people used to object programming) way.

We could create object by using the "new" operator (there are also some other ways to create objects, as we will see shortly).

Object can contain properties and possess "methods" (sort of). Unlike those in classic object-oriented statically typed languages (like Java) properties and "methods" aren't predeclared for and object; we create them dynamically as needed. But keep in mind that this flexibility always comes wit a price!

In next example we create new Object instance and assign it a variable named
house;

var house = new Object();
house.noOfdoors = 5;
house.address = "Main road 51";
house.yearBuild = new Date(2001, 2, 11);

Properties are not limited to primitive types. An object property can be another Object instance.

So let's add a new property to our house instance. We will call this new property owner.

var owner = new Object();
owner.name = "Kimi Raikkonen";
owner.occupation = "Rally driver";
owner.previousOccupation = "F1 driver";
//Add to house without creating property on house instance.
house.owner = owner;

If we want to access nested property we write this:

var homeOwnerName = house.owner.name;

JSON

We can also use a more compact notation for creating the object. This notation has come to be termed JSON (JavaScript Object Notation) is much preferred by most page authors. See http://www.json.org for more informations.

var house = {
  noOfdoors : 5,
  address : "Main road 51",
  yearBuild : new Date(2001, 2, 11),
  owner : {
    name: 'Kimi Raikkonen',
    name: 'Kimi Raikkonen',
    occupation: 'Rally driver'
  }
};
alert('House owner: '+house.owner.name); //Works fine.

Window object

When you use var keyword for declaring variable at the top level scope (global or window scope) you are creating top-level properties of window object. Also when you don't use var keyword inside function you are also creating top-level property on window object.

var name = 'Simon'; //Global variable,property of window object.
alert(window.name) //Works fine.
function calculatePi() {
  //something...
  result = 3.14; //Without var keyword you declare this as window property.
}
calculatePi();
alert(window.result); //Works fine.

This is the end of the story about object, please read ahead about JavaScript functions if you are interested.

Caching data with PL/SQL Collections

Introduction

In many applications, there is always situation when you will need same data from database over and over again in your PL/SQL program. In same cases, the data that you need is static. This data can be some kind of codes and descriptions that rarely (if ever) change. Well, if the data isn't changing - especially during a user session - then why would you want to keep querying the same data from database?

Idea is that you create collection that will be stored in session's PGA. You will query only once your static data and put them into collection. Essentially, you use the collection's index as intelligent key.

Consider following code:

CREATE OR REPLACE PACKAGE onlyonce AS 
    
    TYPE names_t IS 
    TABLE OF employees.first_name%TYPE
            INDEX BY PLS_INTEGER;
            
    names names_t;

    FUNCTION get_name(employee_id_in IN employees.employee_id%TYPE)
        RETURN employees.first_name%TYPE;
        
END onlyonce; 
/
CREATE OR REPLACE PACKAGE BODY onlyonce AS
    
    FUNCTION name_from_database(employee_id_in IN employees.employee_id%TYPE)
            RETURN employees.first_name%TYPE
        IS
            local_names employees.first_name%TYPE;
     BEGIN
        SELECT first_name
            INTO local_names
            FROM employees
           WHERE employee_id = employee_id_in;
        RETURN local_names;
     END;

    FUNCTION get_name(employee_id_in IN employees.employee_id%TYPE) 
        RETURN employees.first_name%TYPE
    IS                
        return_value employees.first_name%TYPE;        
    BEGIN
        RETURN names(employee_id_in);
    -- I admit, that this slip of code that follows is little bit of hacking...
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            names(employee_id_in) := name_from_database(employee_id_in);
            RETURN names(employee_id_in);
    END;

END onlyonce;

SET SERVEROUTPUT ON

BEGIN    
    FOR j IN 1..10 LOOP
        FOR i IN 100..150 LOOP
            DBMS_OUTPUT.put_line(onlyonce.get_name(i));
        END LOOP;
    END LOOP;
END; 

Ok, here we have much stuff going on. So I will explain one step at the time. Off course (as in all almost oracle examples), this example use HR table.

3-7
Declare a collection type and collection to hold cached data.

16-26
Function for retrieve data from database (one by one).

28
Declaration of our main retrieval function. This function return value from database or from collection. This depend if there is already value with that key in collection or not. Only parameter for this function is id of employee which name we want to retentive.

32-40
This is _the_ meat of the caching. It is little bit of hacking, but what can you do. :) If it does not find element with that id in collection then it will pull it out from db.

46-52
We loop trough table. In first iteration in outer loop it will pull data from database, in all sequential iteration it will retrieve data from collection.

Sh how much of difference does this caching make? For example to execute 10.000 queries against some table it took about 2 second, while pulling same amount of data from collection it took 0.1 sec. That is really and oder of magnitude of improvement. And also caching static data will improve code quality, because of implicit documentation of static structures in your program.

Oracle PL/SQL Collections

It is strange that relatively few database programmers really know or use collections in PL/SQL. They tend to program more in SQL-like way and not thinking much about performance or readability of their programs.

With collections you can improve performances by cache data that are queried repeatedly in a single program or you can process data more quickly not using relational tables or global temporary tables.

PL/SQL collections are cumbersome and at least confusing (in compare to collections in other languages like Java for instance).

There are three different types of collections. I will show all types with examples.

Associative arrays

These are single-dimensional, unbounded, sparse (do not need to be filled up sequentially) collections of homogeneous elements. In the next example I declare such array, populate it with some data and iterate through the collection.

SET SERVEROUTPUT ON

DECLARE
    TYPE names_list_t IS TABLE OF VARCHAR2(255)
        INDEX BY PLS_INTEGER;
    people names_list_t;

    l_row PLS_INTEGER; -- Same type as index.

BEGIN

    people(1)         := 'Bob';
    people(33)        := 'Bruce';
    people(43)        := 'Rocky';
    people(-12)        := 'Grozni';
    people(1555555)   := 'Ivan'; 
  
    l_row := people.FIRST;

    WHILE (l_row IS NOT NULL)
    LOOP
        DBMS_OUTPUT.put_line(people(l_row));
        l_row := people.NEXT(l_row);
    END LOOP;
END;

This type of collection is the most efficient, but if you want to store your collection within a database table, you cannot use an associative array (you need to use one of other two types). If you need sparse collections, you only practical option is an associative array. This is also true if your PL/SQL application requires negative index subscript (like -12 in example).

Nested table

These are also single-dimensional, unbounded (unrestricted) collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables are multisets, which means that there is no inherent order to the elements in a nested table. This can be problem if we need to preserve order of elements. We can use keys and indexes, but there is another type of collection VARRAY that will preserver order.

In following example we will first declare a nested table type as a schema-level type. Then we will declare couple of nested tables based on that type, create their union and display result of union.

CREATE OR REPLACE TYPE car_names_list_t IS TABLE OF VARCHAR2(100);

DECLARE
    great_cars          car_names_list_t := car_names_list_t();
    not_so_great_cars   car_names_list_t := car_names_list_t();
    
    all_this_cars       car_names_list_t := car_names_list_t();    
    
BEGIN
    great_cars.EXTEND(3);    
    great_cars(1) := 'Golf';
    great_cars(2) := 'Impreza';
    great_cars(3) := 'Focus';
    
    not_so_great_cars.EXTEND(2);
    not_so_great_cars(1) := 'Zastava';
    not_so_great_cars(2) := 'Dacia';
    
    all_this_cars := great_cars MULTISET UNION not_so_great_cars;
    
    FOR l_row IN all_this_cars.FIRST .. all_this_cars.LAST
    LOOP
        DBMS_OUTPUT.put_line(all_this_cars(l_row));
    END LOOP;
    
END; 

EXTENDS method is used for "making more room" in nested tables. So, when using nested tables we need explicitly resize our collection. MULTISET UNION (there are others like MULTISET EXCEPT) is used for high-level set operations. In this case (using some kind of operation on sets), we don't need to use EXTENDS (obliviously).

Nested tables are useful if you need to perform high-level set operations on your collections, but this is only true if you use older Oracle database (<=10g). Also this kind of collections are only choice if you intent to store large amounts of persistent data in column collection (this is because database will behind scene create real table to hold data).

VARRAY

Like the other two collection type, VARRAYs (variable-sized arrays) are also single-dimensional collections of homogeneous elements. However, the are always limited (bounded) and never sparse. When you declare a type of VARRAY, you must also specify the maximum number of elements it can contain. Important difference to nested tables is that when you store VARRAY as database column, its elements preserve the order.

In following example we will see demonstration simple usage of VARRAYs.

SET SERVEROUTPUT ON

DECLARE
    TYPE anime_movies_t IS VARRAY (3) OF VARCHAR2(100);
        
    anime_movies anime_movies_t := anime_movies_t();

BEGIN
    --extendet only to first
    anime_movies.EXTEND(1);
    anime_movies(1) := 'Akira';
    --here we extend to full length
    anime_movies.EXTEND(2);
    anime_movies(2) := 'Castle in the sky';
    anime_movies(3) := 'My neighbour totorro';
    
    --loop, or do something
END;

Ok, now. This example speak for it self, just note that you also need to use EXTEND to make room for elements.
You will probably use this type of collection when you need to preserve the order of the elements stored in the database collection column, and also when you have relatively small amount of data in collection. Also with VARRAY you also do not need to worry about deletion occurring in the middle of the data set; your data has intrinsic upper bound; or you need to retrieve entire collection simultaneously.

My tip for the end is that, if you are using collections in PL/SQL, you probably should create your set of procedures and functions (as package) that will encapsulate
managing collections, and maybe will hide also what kind of collection you are using...but this is probably not good idea for most of the situations (but collection package _is_ a good idea).