Comparing Nested Table Collections

Oracle 11g

Oracle lets you compare PL/SQL nested table collections, but there are several rules to keep in mind. These rules are evaluated in the order given.

Create these types and this helper procedure to test these rules.

create type type1_nt is table of varchar2(100)
/
CREATE PROCEDURE p_comp_coll(p_col1 IN type1_nt,
                             p_col2 IN type1_nt) IS
BEGIN
  IF p_col1 = p_col2 THEN
    dbms_output.put_line('EQUAL');
  ELSIF p_col1 <> p_col2 THEN
    dbms_output.put_line('NOT EQUAL');
  ELSE
    dbms_output.put_line('NULL');
  END IF;
END;
/

Rule 1. Nested table collections of objects or records cannot be compared.

SQL> DECLARE
  2    TYPE varrec_t IS RECORD(
  3      val1 VARCHAR2(100));
  4    TYPE mycol_t IS TABLE OF varrec_t;
  5    l_coll1 mycol_t;
  6    l_coll2 mycol_t;
  7  BEGIN
  8    IF l_coll1 = l_coll2 THEN -- will not compile
  9      NULL;
 10    END IF;
 11  END;
 12  /
 
DECLARE
  TYPE varrec_t IS RECORD(
    val1 VARCHAR2(100));
  TYPE mycol_t IS TABLE OF varrec_t;
  l_coll1 mycol_t;
  l_coll2 mycol_t;
BEGIN
  IF l_coll1 = l_coll2 THEN -- will not compile
    NULL;
  END IF;
END;
 
ORA-06550: line 9, column 14:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

Rule 2: Collections must be of EXACTLY the same type. Being based on the same element type is not sufficient.

This will not compile:

SQL> create type type2_nt is table of varchar2(100) -- same as type1_nt
  2  /
  
 
Type created

SQL> declare
  2  l_col1 type1_nt := type1_nt('data');
  3  l_col2 type2_nt := type2_nt('data');
  4  begin
  5  if l_col1 = l_col2 then -- not allowed
  6  null;
  7  end if;
  8  end;
  9  /
 
declare
l_col1 type1_nt := type1_nt('data');
l_col2 type2_nt := type2_nt('data');
begin
if l_col1 = l_col2 then
null;
end if;
end;
 
ORA-06550: line 6, column 11:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored

Rule 3: If either of the nested tables is unitialized, the result is NULL.

SQL> DECLARE
  2    l_col1 type1_nt := type1_nt('data');
  3    l_col2 type1_nt;
  4  BEGIN
  5    p_comp_coll(l_col1, l_col2);
  6  END;
  7  /
 
NULL
 
PL/SQL procedure successfully completed

SQL> DECLARE
  2    l_col1 type1_nt;
  3    l_col2 type1_nt;
  4  BEGIN
  5    p_comp_coll(l_col1, l_col2);
  6  END;
  7  /
 
NULL
 
PL/SQL procedure successfully completed

Rule 4: If the collections have different numbers of elements, they are not equal.

SQL> DECLARE
  2    l_col1 type1_nt := type1_nt('data', NULL);
  3    l_col2 type1_nt := type1_nt('data');
  4  BEGIN
  5    p_comp_coll(l_col1, l_col2);
  6  END;
  7  /
 
NOT EQUAL
 
PL/SQL procedure successfully completed

SQL> DECLARE
  2    l_col1 type1_nt := type1_nt('data');
  3    l_col2 type1_nt := type1_nt('data');
  4  BEGIN
  5    l_col2.extend();
  6    p_comp_coll(l_col1, l_col2);
  7  END;
  8  /
 
NOT EQUAL
 
PL/SQL procedure successfully completed

Rule 5: If the collections have the same number of elements but any of those elements are NULL, the collections are not equal. Note that '' is NOT treated as NULL when comparing collections!

SQL> DECLARE
  2    l_col1 type1_nt := type1_nt('data',
  3                                NULL);
  4    l_col2 type1_nt := type1_nt('data',
  5                                NULL);
  6  BEGIN
  7    p_comp_coll(l_col1,
  8                l_col2);
  9  END;
 10  /
 
NULL
 
PL/SQL procedure successfully completed

SQL> DECLARE
  2    l_col1 type1_nt := type1_nt('data1',
  3                                'data2');
  4    l_col2 type1_nt := type1_nt('',
  5                                'data1');
  6  BEGIN
  7    p_comp_coll(l_col1,
  8                l_col2);
  9  
 10    dbms_output.put_line('Coll1 size:' || l_col1.count);
 11    dbms_output.put_line('Coll2 size:' || l_col2.count);
 12    IF '' IS NULL THEN
 13      dbms_output.put_line('yes, '''' is generally considered to be null!');
 14    END IF;
 15  END;
 16  /
 
NOT EQUAL
Coll1 size:2
Coll2 size:2
yes, '' is generally considered to be null!
 
PL/SQL procedure successfully completed

Rule 6: If the collections have the same number of elements, none of the elements are NULL, and all of the values are the same regardless of order, the collections are equal.

SQL> DECLARE
  2    l_col1 type1_nt := type1_nt('data1',
  3                                'data2');
  4    l_col2 type1_nt := type1_nt('data1',
  5                                'data2');
  6  BEGIN
  7    p_comp_coll(l_col1,
  8                l_col2);
  9  END;
 10  /
 
EQUAL
 
PL/SQL procedure successfully completed

SQL> DECLARE
  2    l_col1 type1_nt := type1_nt('data1',
  3                                'data2');
  4    l_col2 type1_nt := type1_nt('data2',
  5                                'data1');
  6  BEGIN
  7    p_comp_coll(l_col1,
  8                l_col2);
  9  END;
 10  /
 
EQUAL
 
PL/SQL procedure successfully completed

Writing a Procedure for PL/SQL, SQL, and Java

Oracle 11g

Problem

• You need to write a PL/SQL function that returns a collection of records
• The function will be used by
  • other PL/SQL programs
  • SQL queries
  • a Java service

This can be a tough set of requirements to meet, especially when the records that you are returning don't map to a row from an existing table or view.

Solution

Write two flavors of the function: one that returns a nested table object and a second that calls the first function and returns a SYS_REFCURSOR.

Step 1: Create an object type that represents the record you will return.

CREATE OR REPLACE TYPE my_obj IS OBJECT
(
  obj_id           INTEGER,
  status           VARCHAR2(30),
  active           VARCHAR2(1)
)
/

Step 2: Create a nested table type based on your new object.

CREATE OR REPLACE TYPE my_obj_tab IS TABLE OF my_obj
/

Step 3: Create the package that contains the two flavors of the function.

CREATE OR REPLACE PACKAGE mypack IS

  FUNCTION f_get_objects RETURN my_obj_tab; -- use this for PL/SQL and SQL

  FUNCTION f_get_objects_cur RETURN SYS_REFCURSOR; -- use this one for Java

END mypack;
/
CREATE OR REPLACE PACKAGE BODY mypack IS

  FUNCTION f_get_objects RETURN my_obj_tab IS
  BEGIN
    RETURN my_obj_tab(my_obj(obj_id => 1,
                             status => 'whatever',
                             active => 'Y'),
                      my_obj(obj_id => 2,
                             status => 'whatever again',
                             active => 'Y'));
  END f_get_objects;


  FUNCTION f_get_objects_cur RETURN SYS_REFCURSOR IS
    l_cursor SYS_REFCURSOR;
  BEGIN
    OPEN l_cursor FOR
      SELECT *
      FROM   TABLE(f_get_objects);
    RETURN l_cursor;
  END f_get_objects_cur;

END mypack;
/

Using the function in PL/SQL

DECLARE
  l_objects my_obj_tab;
BEGIN
  l_objects := mypack.f_get_objects;
  dbms_output.put_line(l_objects(1).status);
END
/

whatever
 
PL/SQL procedure successfully completed

Using the function in SQL

SELECT *
FROM   TABLE(mypack.f_get_objects)
ORDER  BY status;

Using the function in JAVA

...
OracleCallableStatement cs = (OracleCallableStatement)
  conn.prepareCall("{? = call mypack.f_get_objects_cur() }"); //no params
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
OracleResultSet rs = (OracleResultSet) cs.getCursor(1);
while (rs.next()){
 System.out.println("ID:" + rs.getInt("obj_id") + " STATUS:"
   + rs.getString("status"));
}
...

FORALL iteration variable ... not allowed in this context

Oracle 11g

CREATE TABLE items (
 item_id   INTEGER, 
 item_name VARCHAR2(100)
);

Let's say we're constructing a collection of items in memory that we then want to write to the item table. It seems sensible to use an associative array and index our collection by the item ID. But we run into issues when it comes to writing that collection to our table:

DECLARE
  TYPE items_aat IS TABLE OF items.item_name%TYPE INDEX BY PLS_INTEGER;  
  l_items items_aat;
  
  -- for using FORALL with a sparse collection
  TYPE indices_aat IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
  l_item_indices indices_aat;
  
BEGIN
  l_items(100) := 'item1';
  l_item_indices(100) := TRUE; 
  
  l_items(200) := 'item2';  
  l_item_indices(200) := TRUE;
  
    
  FORALL itr IN INDICES OF l_item_indices
    INSERT INTO items
      (item_id, item_name)
    VALUES
      (itr, l_items(itr)); -- error!
END;
/

ORA-06550: line 22, column 8:
PLS-00430: FORALL iteration variable ITR is not allowed in this context

It's complaining about my usage of itr as the item_id value. Why can't I use the value of the iterator like that? I have no idea.

The same error is generated if you have a dense collection and the more typical FORALL usage:

DECLARE
  TYPE items_aat IS TABLE OF items.item_name%TYPE INDEX BY PLS_INTEGER;
  l_items items_aat;

BEGIN
  l_items(1) := 'item1';
  l_items(2) := 'item2';

  FORALL itr IN l_items.first .. l_items.last
    INSERT INTO items
      (item_id, item_name)
    VALUES
      (itr, l_items(itr)); -- error!
END;
/

ORA-06550: line 14, column 8:
PLS-00430: FORALL iteration variable ITR is not allowed in this context

The only way that I know to get around this error-- so long as I'm using a sparsely defined associative array with FORALL-- is to simply use item ID both as the value and index for the indices collection. I don't like this at all.

DECLARE
  TYPE items_aat IS TABLE OF items.item_name%TYPE INDEX BY PLS_INTEGER;  
  l_items items_aat;
  
  -- for using FORALL with a sparse collection
  TYPE indices_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  l_item_indices indices_aat;
  
BEGIN
  l_items(100) := 'item1';
  l_item_indices(100) := 100; 
  
  l_items(200) := 'item2';  
  l_item_indices(200) := 200;
  
    
  FORALL itr IN INDICES OF l_item_indices
    INSERT INTO items
      (item_id, item_name)
    VALUES
      (l_item_indices(itr), l_items(itr));
END;
/

PL/SQL procedure successfully completed

Managing User-Defined Exceptions in PL/SQL

Database version: Oracle 11g

There are two basic ways to raise a user-defined exception in PL/SQL.

Here's one way.

DECLARE
  my_exception EXCEPTION;
BEGIN
  RAISE my_exception;
END;
/

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 5

This method is simple, but it doesn't provide you with any information when the error is thrown. Here is a better method.

BEGIN
  raise_application_error(-20100,
                          'describe your exception here');
END;
/

ORA-20100: describe your exception here
ORA-06512: at line 3

Oracle gives us the somewhat stingy range of -20000 to -20999 (inclusive) for error codes that can be passed to raise_application_error. (Incidentally, you can also pass in decmial numbers such as -20001.142435.) But assuming I need to raise the same application error in multiple places, what's the best way for me to consistently use the same error number and the same error message?

The following is a simple framework that can help manage error codes and error messages. It has two components: a package that defines the exceptions and links them to an error number, and a table that associates error numbers with error messages.

Here's the table.

-- create table to relate error codes to error definitions
CREATE TABLE error_def (
       error_id NUMBER, 
       message VARCHAR2(1000), 
       comments VARCHAR2(1000), 
       CONSTRAINT error_def_pk PRIMARY KEY(error_id)
);

Something to consider: with some additional work you could tie a particular range of error numbers to a particular application. You would do this by creating a second table that links applications to error ranges (min/max). Then add a foreign key to the error_def table to tie the error to a particular application, and a before insert trigger to ensure that the error_id is within the valid range for its application.

Here's the package.

-- create a package to tie exceptions to numbers and messages
CREATE OR REPLACE PACKAGE errlib IS

  SUBTYPE errmsg IS error_def.message%TYPE;
  SUBTYPE errnum IS error_def.error_id%TYPE;

  FUNCTION f_errmsg(p_errnum_in IN errnum) RETURN errmsg;

  /* Write these three statements for each of your errors */
  invalid_status_errnum CONSTANT errnum := -20105;
  invalid_status EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_status,
                        -20105);

END errlib;
/

CREATE OR REPLACE PACKAGE BODY errlib IS

  FUNCTION f_errmsg(p_errnum_in IN errnum) RETURN errmsg IS
    l_errmsg errmsg;
  BEGIN
    SELECT error_def.message
    INTO   l_errmsg
    FROM   error_def
    WHERE  error_def.error_id = p_errnum_in;
    RETURN l_errmsg;
  EXCEPTION
    WHEN no_data_found THEN
      RETURN NULL;
  END f_errmsg;

END errlib;
/

Each of your exceptions will need to have three entries in the errlib spec. One downside of this approach is that it may invalidate other packages that use the errlib package, though you may be OK (depending on your version of Oracle) if add your new entries at the bottom of the spec.

Finally, here's how you set the error message for the exception.

-- add an application error message to the table
INSERT INTO error_def
  (error_id, message, comments)
VALUES
  (-20105, 'Something bad happened!', 'If you see this error, call Bill.');
COMMIT;

Test it!

-- test!
BEGIN
  raise_application_error(errlib.invalid_status_errnum,
                          errlib.f_errmsg(errlib.invalid_status_errnum));
END;
/

ORA-20105: Something bad happened!
ORA-06512: at line 3