Have you ever tried to create an associative array with
index by other than
binary_integer?
In Oracle 8i, if i remember well, binary_integer was the only option available.
Starting from 9i, you could define a varchar2 indexed array, making oracle arrays very similar to the associative arrays found in other programming languages.
However there are still some limitations, for instance you cannot create a collection based on a date index and a declaration like the following would return a compilation error:
DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF VARCHAR INDEX BY DATE;
current_year_tab bank_holiday_tab_type;
BEGIN
...
END;
PLS-00315 Implementation restriction:
unsupported table index type.
But not all is lost.
An
index-by-date array could be easily simulated by converting the date value into a proper varchar2 type value using function TO_CHAR with the desired date format mask.
The drawback when using such associative arrays, is in that you cannot use BULK COLLECT or FORALL constructs, because they work only with numerical integer indices.
In case you attempt to use one of such statements, you'll get an exception at compile-time like the following:
PLS-00657: Implementation restriction:
bulk SQL with associative arrays with
VARCHAR2 key is not supported.
The reason is quite simple, it's impossible for Oracle to predict what the next VARCHAR2 subscript should be, whereas in the case of a binary integer subscript it is just automatically filling up the array with consecutive numbers, starting from 1.
In other words a normal array is something like:
A(1), A(2),... A(n)
Where the value of each element can be any of any type you like, including a user defined object type.
On the contrary an associative array is something like:
A(string
1), A(string
2),... A(string
n)
If you look at the syntax of BULK COLLECT, you can see that there is no way to specify a mapping between the value being returned from the table and its corresponding index.
For instance, let's take my original array definition and transform it into a varchar2(8) indexed array.
Suppose we need to fill up the aforementioned array of dates, where each value is a string indicating the bank holiday name.
There is no way of doing this with BULK COLLECT.
CREATE TABLE bank_holidays(
bank_holiday DATE,
bank_holiday_name VARCHAR2(50));
DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF VARCHAR2(50)
INDEX BY VARCHAR2(8);
current_year_tab bank_holiday_tab_type;
BEGIN
SELECT TO_CHAR(bank_holiday,'YYYYMMDD'),
bank_holiday_name
BULK COLLECT INTO current_year_tab
FROM dates_of_year;
...
END;
As you see we would need to stuff a pair of table columns (the subscript and its value) as a single element of the array, but BULK COLLECT is not designed to do that.
For the sake of discussion, let's suppose it makes some sense to get rid of the column bank_holiday_name from the last sql statement above, just to see what happens when we try to compile this program in 10g.
Well, all we get is:
PLS-00657: Implementation restriction:
bulk SQL with associative arrays with
VARCHAR2 key is not supported
So, while we wait until oracle comes up with a statement syntax extension like:
SELECT BULK_PAIR(TO_CHAR(bank_holiday,'YYYYMMDD'), bank_holiday_name)
BULK COLLECT INTO current_year_tab
FROM bank_holidays;
we can stick to a classic and slower explicit cursor doing the job:
DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(8);
current_year_tab bank_holiday_tab_type;
BEGIN
FOR each_record IN (
SELECT TO_CHAR(bank_holiday,'YYYYMMDD') bank_holiday$,
bank_holiday_name
FROM bank_holidays)
LOOP
current_year_tab(each_record.bank_holiday$)
:= each_record.bank_holiday_name;
END LOOP;
END;
or perhaps define a custom type like this:
CREATE TYPE bank_holiday_type AS
OBJECT (bank_holiday DATE, bank_holiday_name VARCHAR2(50));
and then use BULK COLLECT on an array of bank_holiday_type, as follows:
DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF bank_holiday_type INDEX BY BINARY_INTEGER;
current_year_tab bank_holiday_tab_type;
BEGIN
SELECT
bank_holiday_type(
TRUNC(bank_holiday),
bank_holiday_name)
BULK COLLECT INTO current_year_tab
FROM bank_holidays;
END;
Happy collecting.
