Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

EnterpriseDB: Collection Attributes Oracle Tips by Burleson

When dealing with collections (INDEX BY TABLES), it is necessary to retrieve meta information about those collections.  Oracle's PL/SQL supports 7 collections attribute methods (on INDEX BY TABLES):

* COUNT – Number of rows in the collection

* EXISTS – Does a specific row exist

* FIRST – Returns the first row index value

* LAST – Returns the last row index value

* PRIOR – Returns the row before the indicated index value

* NEXT – Returns the row after the indicated index value

* DELETE – Delete a single row or all rows

Here is an Oracle example showing the various uses of these attributes:

   1  DECLARE
  2    TYPE v_tab IS TABLE OF VARCHAR2(30)
  3    INDEX BY BINARY_INTEGER;
  4    vv_tab v_tab;
  5  BEGIN
  6    vv_tab(1) := 'Lewis';
  7    vv_tab(2) := 'Howdy';
  8    dbms_output.put_line( 'Count: ' || vv_tab.count );
  9    IF vv_tab.exists(2)
 10    THEN
 11      dbms_output.put_line( 'Row 2 Exists' );
 12    ELSE
 13      dbms_output.put_line( 'Row 2 does not Exist' );
 14    END IF;
 15    dbms_output.put_line( 'First: ' || vv_tab.first );
 16    dbms_output.put_line( 'Last: ' || vv_tab.last );
 17    dbms_output.put_line( 'Next 0: ' || vv_tab.next(0) );
 18    dbms_output.put_line( 'Next 1: ' || vv_tab.next(1) );
 19    dbms_output.put_line( 'Next 2: ' || vv_tab.next(2) );
 20    dbms_output.put_line( 'Next 3: ' || vv_tab.next(3) );
 21    dbms_output.put_line( 'Prior 0: ' || vv_tab.prior(0) );
 22    dbms_output.put_line( 'Prior 1: ' || vv_tab.prior(1) );
 23    dbms_output.put_line( 'Prior 2: ' || vv_tab.prior(2) );
 24    dbms_output.put_line( 'Prior 3: ' || vv_tab.prior(3) );
 25    vv_tab.delete(1);
 26    dbms_output.put_line( 'Delete: ' || vv_tab.count );
 27    vv_tab.delete;
 28    dbms_output.put_line( 'Delete: ' || vv_tab.count );
 29* END;
SQL> /
Count: 2
Row 2 Exists
First: 1
Last: 2
Next 0: 1
Next 1: 2
Next 2:
Next 3:
Prior 0:
Prior 1:
Prior 2: 1
Prior 3: 2
Delete: 1
Delete: 0

PL/SQL procedure successfully completed.

EnterpriseDB does not support any of the above collection attributes except count.  This can be very problematical for those applications that use collections heavily.

Exception Handling

As I have mentioned several times, exception handling in both SPL and PL/SQL is very robust.  Very elaborate exception handling processes can be constructed using the native exception handling in these languages.

EnterpriseDB does support the raise_application_error procedure for generating user defined errors.

BEGIN
  DBMS_OUTPUT.PUT_LINE( 'Hello' );
  RAISE no_data_found;
  DBMS_OUTPUT.PUT_LINE( 'Goodbye' );
EXCEPTION
  WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE( 'Got a no_data_found: ' || sqlcode);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE( 'Got an error: ' || sqlcode  );
END;

In Oracle, when I run this block, I get:

Hello
Got a no_data_found: 100

PL/SQL procedure successfully completed.

In EnterpriseDB, when I run this I get:

INFO:  Hello
INFO:  Got an error: P0001

EDB-SPL Procedure successfully complete 

The raised exception is always P0001 which means that I have no way of raising a pre-defined exception, or even knowing which exception I meant to raise.  If you use this ability in your Oracle applications, you will need to make modifications to run them against EnterpriseDB.

There is also no facility for named, user defined exceptions.  In Oracle, you may use the PRAGMA exception_init to associate a name with an exception.  EnterpriseDB does not support that functionality.

On the other hand, EnterpriseDB does recognize certain codes when the database itself raises them.

For example, if I select from an empty table I will get a NO_DATA_FOUND exception and the ORA-01403 code:

CREATE TABLE one_col (a_col VARCHAR2(10) );

DECLARE
  v_col one_col.a_col%TYPE;
BEGIN
  SELECT a_col
  INTO v_col
  FROM one_col;
EXCEPTION
  WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE( 'Got a no_data_found: ' || sqlcode);
END;    

The output of this is:

INFO:  Got a no_data_found: -01403

EDB-SPL Procedure successfully complete

That output is not exactly the same as Oracle but if you are accessing by name, your code will continue to work.

The Oracle error codes supported by EnterpriseDB are:

Table 8.1: EnterpriseDB Supported Oracle Exceptions

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