How do u get the element previous to nth element of a PL/SQL table Type variable m?

How do u get the element previous to nth element of a PL/SQL table Type variable m?


Oracle PL/SQL actually has 2 table types - associative arrays (also known as index-by tables) and nested tables. Along with varrays (variable-length arrays) these are known as collection types. Fortunately Oracle is consistent with the features (methods) provided with these 3 structures so what works on one type will work on the others (with the exception of some methods that are specific to one type).

The methods Oracle provides for manipulating collections include
  • COUNT
  • PRIOR
  • NEXT
  • FIRST
  • and LAST
which make finding the element previous to the nth element actually very easy.

Let's look at an example with associative arrays.

DECLARE
 TYPE collection_of_numbers IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
 atomic_weights collection_of_numbers;
 atomic_no PLS_INTEGER;
BEGIN
 atomic_weights(1) := 1.008;
 atomic_weights(92) := 238.02891;
 atomic_weights(79) := 196.966569;
END;

We now have a (very small) collection of atomic weights so how do we find the nth element? Simply by specifying the index number. Therefore if we want Uranium we would access element 79 like so (assuming we have created the table as above):

DBMS_OUTPUT.PUT_LINE(
'atomic weight of gold is '||atomic_weights(79)
);


Now we need to know how to get the previous element. We could just access atomic_weights(78) like this:

DBMS_OUTPUT.PUT_LINE(
'atomic weight of platinum is '||atomic_weights(78)
);

but that cell hasn't been populated so if we did that Oracle would raise an "ORA-01403: no data found" error at run time, so we either need our PL/SQL procedure to remember which elements are populated or we can let the database do all the work and just use the PRIOR method.

Unfortunately the following won't work as PRIOR returns the index of the previous cell:

DBMS_OUTPUT.PUT_LINE(
'atomic weight of hydrogen is '||atomic_weights.PRIOR(79)
);


We need to find the cell number first:

DBMS_OUTPUT.PUT_LINE(
'atomic weight of hydrogen is '||atomic_weights(atomic_weights.PRIOR(79))
);


PRIOR does all the work for us and if we wanted to go the other way we would use NEXT. Both of these methods also work with PL/SQL NESTED TABLE types as well as with associative arrays using character strings for the index as shown in the following example:

DECLARE
 TYPE collection_of_numbers IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
 atomic_weights collection_of_numbers;
 atomic_no PLS_INTEGER;
BEGIN
 atomic_weights('hydrogen') := 1.008;
 atomic_weights('uranium') := 238.02891;
 atomic_weights('gold') := 196.966569;
DBMS_OUTPUT.PUT_LINE(
'atomic weight of hydrogen is '||atomic_weights(atomic_weights.PRIOR('gold'))
);
END;

However in this particular case there is no cell prior to the one called gold as alphabetically gold is the first element in our collection so we have to use NEXT instead as in the following example:


DECLARE
 TYPE collection_of_numbers IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
 atomic_weights collection_of_numbers;
 atomic_no PLS_INTEGER;
BEGIN
 atomic_weights('hydrogen') := 1.008;
 atomic_weights('uranium') := 238.02891;
 atomic_weights('gold') := 196.966569;
DBMS_OUTPUT.PUT_LINE(
'atomic weight of hydrogen is '||atomic_weights(atomic_weights.NEXT('gold'))
);
END;


Nested tables are slightly different because they are initially dense - cells are populated sequentially whereas index-by tables (associative arrays) are not (they are sparse) - and can be indexed only by positive integers.

DECLARE
TYPE colour_tab IS TABLE OF VARCHAR2(20);
colour colour_tab := colour_tab('green','purple');
BEGIN
DBMS_OUTPUT.PUT_LINE('colour before purple is '||colour.prior(2));
END;


In summary, therefore, the short answer to the question how do you get to the previous element is it depends on what is in your collection and the index type.

For more on Oracle PL/SQL collections see these tutorials:-
or start with this introduction to PL/SQL.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.