Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

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: ' || sqlcod
e);
  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:

EXCEPTION

SQL CODE

ZERO_DIVIDE

ORA-01476

DUP_VAL_ON_INDEX

ORA-00001

DUPLICATE_CURSOR

ORA-06511

INVALID_CURSOR

ORA-01001

NO_DATA_FOUND

ORA-01403    

TOO_MANY_ROWS

ORA-01422    

CASE_NOT_FOUND

ORA-06592    

PACKAGE_BODY_INEXECUTABLE

ORA-04067    

DUPLICATE_PACKAGE

ORA-00955    

PACKAGE_COMPILE_ERROR

PLS-00304    

INVALID_OBJECT

PLS-00202

INVALID_CONTAINER

PLS-00201

INSUFFICIENT_ELEMENTS

PLS-00323

Table 8.1: EnterpriseDB Supported Oracle Exceptions




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

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter