Choosing COALESCE over NVL

The NVL and the COALESCE functions are basically the same. They return the first NOT NULL argument and if there are no NOT NULL arguments, they return null. The main difference is that the NVL function accepts only two arguments, but the COALESCE function accepts a minimum of 2 and a maximum of 65535 arguments.

Another important difference between the NVL and COALESCE functions is that the NVL function evaluates both the arguments, but the COALESCE function stops its evaluation at the first occurrence of the not null argument. Because of this, there is a performance loss in the NVL function if the first argument is not null and the second argument consumes more time in its evaluation. This scenario is handled perfectly by the COALESCE function with two arguments.

In the below example, both the NVL and the COALESCE functions are passed with the literal “1” as their first argument and a function returning the literal “2” after a 5 second time gap as their second argument. The NVL function returns the first argument (literal “1”) as it is the first NOT NULL argument but takes 5 seconds to finish the task as it also evaluates the second argument. The COALESCE function, on the other hand, returns the first argument (literal “1”) as it is the first NOT NULL argument in no time as it doesn't have to evaluate the second argument.

DECLARE
  l_n_start_time NUMBER;
  l_n_end_time   NUMBER;
  l_n_var1       NUMBER;
  FUNCTION func_sleep
    RETURN NUMBER
  IS
  BEGIN
    dbms_lock.sleep(5);
    RETURN 2;
  END;
BEGIN
  l_n_start_time:=dbms_utility.get_time;
  l_n_var1      :=COALESCE(1,func_sleep);
  l_n_end_time  :=dbms_utility.get_time;
  dbms_output.put_line('Run time taken by the COALESCE function is '||(l_n_end_time-l_n_start_time)/100||' Seconds');
  l_n_start_time:=dbms_utility.get_time;
  l_n_var1      :=NVL(1,func_sleep);
  l_n_end_time  :=dbms_utility.get_time;
  dbms_output.put_line('Run time taken by the NVL function is '||(l_n_end_time-l_n_start_time)/100||' Seconds');
END;
/

Result:

Run time taken by the COALESCE function is 0 Seconds
Run time taken by the NVL function is 5 Seconds


Thank you,
Boobal Ganesan

Evolution of Oracle

This blog explains the evolution of Oracle from its inception as a relational database and then out numbering its competitors for the past 3 decades as the world’s most robust and flexible relational database.

Oracle version 2 (1979) was the very first commercial release and they named it as version 2 as they believed that any potential customer will be more hesitant to buy the very first version of any software. This version had the complete implementation of SQL but lacked reliability.

Oracle version 3 (1983) was completely rewritten in C language to support portability beyond the range of operating systems. This version of Oracle solved the reliability issue with the earlier release. Any SQL statement executed here would either be committed or rolled back.

Oracle version 4 (1984) improved the stability and read consistency. For example, the transaction performed between the accounts is not mis-computed when a query is being executed.

Oracle version 5.1 (1985) and 5.2 (1986) worked as a distributed database by introducing the first every service/ client relational database so that a client running on a machine in one location could access a server over a network. This version also supported distributed querying as one query could access data stored from more than one location.

Oracle version 6 (1988) introduced a new architecture on the availability of the database during a transaction. This version primarily changed the locking method from table level to row level, resulting in better system throughput when many users are accessing the database. This version also allowed hot backups, the process of taking a backup of the database while it is still in use.

Oracle version 7 (1992) solved the procedural limitation of the SQL by allowing the database users to create Programmable Logical Structured Query Language in short PL/SQL codes and thus making it an efficient programming language of all the fourth generation languages. This version allowed us to create Procedures, Functions and Triggers along with distributed transactions and security methods allowing programmers to create complex business rules.

Oracle version 8 (1997) was designed to work with Oracle’s Network Computer and this version supported OLTP system, HTML and Java interface. Oracle version 8.1 (1998) supported parallel processing with Linux, which eliminated the highly costing downtime.

Oracle version 9i (2001) introduced the concept of Real Application Clustering and provided high availability in the Oracle database. This version allowed the database to integrate relational and multidimensional processing and introduced the concept of table compression, which reduced the disk size from 3 to 10 times and increased the performance when accessing the data.

Oracle version 10g (2003) introduced us to the grid technology for sharing the hardware resources against the data centers, thus lowering the infrastructure cost. By introducing Oracle 10g Express Edition, Oracle gave the entry level business corporations to make the most of the Oracle technology with no cost at all.

Oracle version 11g (2007) introduced some salient features like flashback data archive, virtual column, parallel back up of the same files, case sensitive passwords and wide range of performance and optimization enhancements making it a more stable release of all the time.

Oracle version 12c (2013) is out of Oracle’s major innovation which supported multi-tenancy – an option which allows us to share common infrastructure like memory and background processes between multiple databases. These are called as “pluggable” databases which are plugged into a “container” or “master” database. The main advantage of this technology is to possibly allow n- number of databases to run on a single hardware with complete security and isolation between them.


Thank you,
Boobal Ganesan

PL/SQL Multiset operator tips

The multiset operators are a bunch in number, which combines two or more collection types of the same type and returns a collection result of the same type that is appropriate to the operator used. These perform a similar operation to that of the SQL set operators, where the former works only on the collection types and the latter on the SQL SELECT statements.

Multiset operators with their SQL equivalents

Collection SET Operator	Equivalent SQL SET Operator
Multiset Union	Union all
Multiset Union all	Union all
Multiset Union Distinct	Union
Multiset Intersect	NA
Multiset Intersect Distinct	Intersect
Multiset Except	NA
Multiset Except Distinct	Minus

The below nested table type is created as a persistent object for explaining the different multiset operators.

CREATE OR REPLACE type type_ntt

IS

  TABLE OF VARCHAR2(1);

/

Multiset Union [All]

The multiset union operator evaluates two collection instances and returns a single instance, which contains all the elements from the input two instances. If there are any duplicate elements found, they are returned as such. This operator is identical to the multiset union alloperator. This operator is equivalent to the union all operator in SQL.

In the below example, two instances of a single nested type are created. The first instance is assigned to A, B, C and D characters and the second instance is assigned to A and B characters. When they are operated, they just append the elements of the second nested table type instance of the first one.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('A', 'B', 'C', 'D');

 l_ntt_var2 type_ntt :=type_ntt('A', 'B');

BEGIN

  RETURN l_ntt_var1 multiset UNION l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

A

B

C

D

A

B

Multiset Union Distinct

This operator works similar to the multiset union operator along with the duplication elements removal. This operator is equivalent to the union operator in SQL. The distinct keyword used in this operator cannot be replaced with its synonymous keyword unique.

% Note: The result returned by this operator is notsorted, unlike the SQL union operator.

In the below example, the functional with clause has two instances with a list of values assigned to them. The TABLE function in the select statement returns the total list of elements from the two instances removing the duplication without sorting them.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('E', 'B', 'C', 'D');

 l_ntt_var2 type_ntt :=type_ntt('A', 'B');

BEGIN

  RETURN l_ntt_var1 multiset UNION Distinct l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

E

B

C

D

A

Multiset Intersect

The multiset intersect operator evaluates two collection instances of the same type and returns the common elements in them without any duplication removal. This operator does not have an equivalent SQL set operator.

In the below listing, the two instances of the nested table type are created and assigned to a list of values to them. The TABLE function returns the common duplicated unsorted elements from two instances.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('A', 'A', 'C', 'D');

 l_ntt_var2 type_ntt :=type_ntt('A', 'A', 'B');

BEGIN

  RETURN l_ntt_var1 multiset intersect l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

A

A

Multiset Intersect Distinct

This operator works similar to the multiset intersect operator explained in the above section with additional duplicate element removal. In the below example, the operator works on the two instances loaded with a list of values with each other and return common unique unsorted element values using the TABLE function. Usually, the equivalent SQL set operator sorts their results, unlike this collection operator. The distinct keyword used in this operator cannot be replaced with its synonymous keyword unique.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('B', 'B', 'A', 'A');

 l_ntt_var2 type_ntt :=type_ntt('A', 'A', 'C', 'C', 'B', 'B');

BEGIN

  RETURN l_ntt_var1 multiset intersect distinct l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

B

A

Multiset Except

The multiset except operator evaluates two similar collection instances and returns the remaining elements from the first instance after removing any matching elements from the second instance as its resultant. This operator does not have an SQL equivalent.

In the below example, the two instances with a list of elements are operated on the multiset except operator and returns the uncommon elements from the first instance without performing any duplication removal or sorting.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('C', 'C', 'B', 'A', 'D');

 l_ntt_var2 type_ntt :=type_ntt('D', 'A');

BEGIN

  RETURN l_ntt_var1 multiset except l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

C

C

B

Multiset Except Distinct

This operator works similar to the multiset except operator but performs an additional duplication removal operation to the result. This operator still does not sort its resultant, unlike its SQL equivalent. The distinct keyword used in this operator cannot be replaced with its synonymous keyword unique.

In the below example, the two instances with a list of elements are operated on this operator to return the uncommon elements list from the first instance with duplication check and no sorting.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('C','C', 'B', 'A', 'D');

 l_ntt_var2 type_ntt :=type_ntt('D', 'A');

BEGIN

  RETURN l_ntt_var1 multiset except distinct l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

C

B

PL/SQL collection types tips

The collections are homogeneous powerful single dimensional structures which are capable of managing large sets of ordered values belonging to the same type, where each element can be identified individually by their index value. The data processed by the collection are session specific and are stored in the user global area (UGA), which is stored in the SGA when using a shared server connection and in PGA when using a dedicated connection. There are three different collection types as Nested table, VARRAY and Associative array, which can be categorized based on multiple terminologies as described below.

Persistent and Transient

The collections are segregated into two types, Persistent, and Transient, based on their lifetime in the database. The persistent collection types can store their instances permanently in the database. Nested tables and VARRAYs fall under this category as these are capable of acting as the data types of columns in a database table. The non-persistent or transient collection types are not stored in the database permanently and have their lifetime limited to the duration of the PL/SQL unit. Associative arrays, Nested tables, and VARRAYs fall under this category when they are declared dynamically in the declaration section of a PL/SQL unit, subprogram or a package.

Bounded and Unbounded

The collections are categorized into two types, Bounded, and Unbounded, based on the limit of the number of elements they can accommodate. The bounded collection types have a predefined limit on the number of elements to be accommodated. VARRAY is a bounded type, where its upper bound value has to be determined during their declaration. Nested table and Associative array fall under the unbounded category, where they do not have a limit on the number of elements they can accommodate them.

Numeric Index and String Index

The collection types are divided into two types, Numeric index type and String index type, based on the data type of the index value they use for their element storage. The numeric index based collection types allow us to define only integers as the index values of the elements. Nested table and VARRAY types follow numeric indexing, where they prohibit us in using any other type of index value for their list. The index value starts from 1 by default for these collection types. The string index type based collection types allow us to define characters as their element’s index value. An associative array is a string index based collection type and it provides us with a flexibility of having either integer or string as its index type. This type also allows us to use negative integers for subscripts.

Sparse and Dense

The collections are split into two types, Sparse, and Dense, based on the element availability. The spare collections allow undefined or sequentialless elements, i.e., there can be gaps between the first and the last element in the collection. Nested table and Associative array are examples of the sparse collection, where they do not mind their elements getting deleted or removed from the sequence unless they are defined with an NOT NULL constraint. The dense collections must define all their elements with a value, at least Null unless it is defined with an NOT NULL constraint. They do not allow any gaps in their list and raise an error when we try to delete an element from their list. VARRAY is a dense collection type, and they do not allow us to make a gap in their element list.

The PL/SQL collection types do not support multidimensional structure with SQL data types. We can, however, build multidimensional collection structures using records, objects or other collection types as their element’s data type.

Choosing a Collection Type

Choosing the right collection type for our application might be challenging as there could be multiple acceptable choices in a situation. The below section guarantees in providing a high insight of the advantages of one collection type over the other, which will help us in making up our mind to some extent.

When to choose an Associative array?

1.       When we want to cache a small key-pair valued table in the memory for temporary lookup purpose.

2.       When we need flexibility in our subscripts. This type allows string, negative integers, and non-sequential integers to be used as subscripts for the collection elements.

3.       When we want to pass the collection to and from the database server using Oracle Call Interface (OCI) or the Oracle precompiler.

When to choose a Nested table?

1.       When we want to store a large amount of persistent data in a table’s column.

2.       When we must not worry about the subscript going out of sequence due to updates and deletes performed over the collection elements.

3.       When we do not know the maximum number of elements to be stored in a collection during its declaration.

When to choose a VARRAY?

1.       When we want to store a small amount of persistent data in a table’s column.

2.       When we are more concerned about the number of elements stored in the collection.

3.       When we want to preserve the order in which the elements are inserted into the collection.

PL/SQL Collection Exceptions Tips

The bulk binding feature was introduced in Oracle version 8i as the FORALL statement, which is used for the batch processing of large DML statements without the need of switching between the SQL and the PL/SQL engine for every single row. Even though the performance was increased using this feature, there is also a downside to it. When a single row from the DML operation fails, the FORALL statement fails entirely. To address this issue, Oracle has introduced the SAVE EXCEPTIONS clause. This clause instructs the system to skip the error prone rows and to process the rest perfectly fine rows rather failing the entire statement.

When the SAVE EXCEPTIONS clause is added to the FORALL statement, all the errors spotted during the batch processing are saved in the cursor SQL%BULK_EXCEPTIONS, which stores a collection of records with two attributes as shown below.

·         ERROR_INDEX: The index value of the collection element which failed during the DML operation is stored in this attribute.

·         ERROR_CODE: The appropriate error code for the exception raised corresponding to the error index is stored in this attribute.

The exceptions raised during a FORALL DML operation can be captured by using the Oracle error code ORA-24381, when the SAVE EXCEPTIONS clause is used. During this time, the rest of the program code will not be continued and the control directly gets into the exception section once after the FORALL clause completes its operation successfully.

To portray the exceptions in bulk binding, an object type TYPE_OBJ is created with 6 attributes pertaining to an employee’s information is created as shown below,

CREATE OR REPLACE type type_obj

IS

  object

  (

    emp_id         VARCHAR2(50),

    emp_first_name VARCHAR2(50),

    emp_last_name  VARCHAR2(50),

    emp_email      VARCHAR2(50),

    emp_hire_date  VARCHAR2(50),

    emp_job_id     VARCHAR2(50));

  /

The below code depicts the complete functionality of the SAVE EXCEPTIONS clause in a FORALL statement. The code begins by assigning a name to the error code ORA-24381, which is the Oracle assigned error code for the bulk binding exceptions. The nested table typeTYPE_NTT is created over the object type to store more than one employee information in a collective way. An instance of the nested table type is then created and assigned with 7 employees information. Here, the employee details for the index value [3] has a Null job ID, [5] has an invalid hire date and [6] has a Null last name, which all results into exceptions when they are inserted into the EMPLOYEES table having these validations.

When the employee information stored in the nested table is bulk inserted into the EMPLOYEES table with the SAVE EXCEPTIONS clause, the DML operation successfully completes where the error-prone data are formally collected into the system defined collection type BULK_EXCEPTIONS as shown in the below example.

The failed records can then be collected for analysis from the exception block by iterating the error prone records using the BULK_EXCEPTIONS.COUNT attribute. The index value and the error code of the failed records can be retrieved through the BULK_EXCEPTIONS.ERROR_INDEX and BULK_EXCEPTIONS.ERROR_CODE attributes as mentioned earlier. The corresponding error message for the error code can be retrieved by negating the error code and passing it to the SQLERRM function as its only formal parameter.

DECLARE

  bulk_error EXCEPTION;

  pragma exception_init(bulk_error, -24381);

type type_ntt

IS

  TABLE OF type_obj;

  l_ntt_var1 type_ntt:=type_ntt();

BEGIN

  l_ntt_var1.extend(7);

  l_ntt_var1(1):= type_obj('207', 'Marie', 'John', 'MJOHN1', '10-April-2005', 'AD_PRES');

  l_ntt_var1(2):= type_obj('208', 'Abdul', 'Kalam', 'AKALAM2',

  '05-September-1995', 'IT_PROG');

  l_ntt_var1(3):= type_obj('209A', 'Rahul', 'Dravid', 'RDRAVID6',

  '02-August-2001', '');

  l_ntt_var1(4):= type_obj('210', 'Mike', 'Thompson', 'MTHOMS3',

  '29-December-1999', 'AD_VP');

  l_ntt_var1(5):= type_obj('211', 'Paul', 'Livingston', 'PLVNGS8',

  '22B-June-2008', 'FI_MGR');

  l_ntt_var1(6):= type_obj('212', 'King', '', 'KRAM8', '10-October-2005',

  'PU_CLERK');

  l_ntt_var1(7):= type_obj('213', 'Daniel', 'Morrison', 'DMORR4',

  '07-February-2011', 'SA_REP');

  FORALL loop_ntt IN indices OF l_ntt_var1 SAVE exceptions

  INSERT

  INTO

    employees

    (

      employee_id,

      first_name,

      last_name,

      email,

      hire_date,

      job_id

    )

    VALUES

    (

      l_ntt_var1(loop_ntt).emp_id,

      l_ntt_var1(loop_ntt).emp_first_name,

      l_ntt_var1(loop_ntt).emp_last_name,

      l_ntt_var1(loop_ntt).emp_email,

      l_ntt_var1(loop_ntt).emp_hire_date,

      l_ntt_var1(loop_ntt).emp_job_id

    );

  dbms_output.put_line('This message is printed only if there is no exception raised in the FORALL statement!');

EXCEPTION

WHEN bulk_error THEN

  dbms_output.put_line('Total number of inserted rows: '||sql%rowcount);

  dbms_output.put_line('Total number of rejected rows: '||

  sql%bulk_exceptions.count);

  FOR loop_error IN 1..sql%bulk_exceptions.count

  LOOP

    dbms_output.put_line

    ('Error Index: '||sql%bulk_exceptions(loop_error).error_index||

     '. Error Code: '||sql%bulk_exceptions(loop_error).error_code||

     '. Error Message: '||sqlerrm(-sql%bulk_exceptions(loop_error).error_code));

  END LOOP loop_error;

END;

/

Result:

Total number of inserted rows: 4

Total number of rejected rows: 3

Error Index: 3. Error Code: 1722. Error Message: ORA-01722: invalid number

Error Index: 5. Error Code: 1861. Error Message: ORA-01861: literal does not match format string

Error Index: 6. Error Code: 1400. Error Message: ORA-01400: cannot insert NULL into ()

The error report shows us the number of successfully inserted records, the number of failed records and then lists all the failed records with supporting reasons for their failure.

Thank you,

Boobal Ganesan