Version Enhancements: 9i to 10g

Here we discuss about the new features introduced in Oracle 10g, which do not exist in Oracle 9i.

1. Regular Expressions
2. Merge Statement Enhancements
3. Collect Function
4. DBMS_OUTPUT Size Limit
5. Quoting String Literals

1. Regular Expressions

A regular expression is a set of characters that specify a search pattern.
Oracle 10g provides 4 functions to implement regular expressions.

• REGEXP_LIKE - Returns true if the pattern is matched, otherwise false.

• REGEXP_INSTR - Returns the position of the start or end of the matching string. Returns zero if the pattern does not match.

• REGEXP_REPLACE - Returns a string where each matching string is replaced with the text specified.

• REGEXP_SUBSTR - Returns the matching string, or NULL if no match is found.

We will discuss more on Regular Expressions in coming posts...

2. Merge Statement Enhancements

Sample Merge Statement in 9i

MERGE INTO table_name tbl
USING (SELECT NULL FROM DUAL)
ON (tbl.column1 = l_value1 AND
  tbl.column2 = l_value2
 )
WHEN MATCHED THEN
 UPDATE SET column3 = l_value3
     ,column4 = l_value4
WHEN NOT MATCHED THEN
 INSERT ( column1
   ,column2
   ,column3
   ,column4
  )
 VALUES ( l_value1
   ,l_value2
   ,l_value3
   ,l_value4
  );

Enhancements in 10g:

• The MATCHED and NOT MATCHED clauses are now optional

--Merge statement with only MATCHED clause
MERGE INTO table_name tbl
USING (SELECT NULL FROM DUAL)
ON (tbl.column1 = l_value1 AND
  tbl.column2 = l_value2
 )
WHEN MATCHED THEN
 UPDATE SET column3 = l_value3
     ,column4 = l_value4;

--Merge statement with only NOT MATCHED clause
MERGE INTO table_name tbl
USING (SELECT NULL FROM DUAL)
ON (tbl.column1 = l_value1 AND
  tbl.column2 = l_value2
 )
WHEN NOT MATCHED THEN
 INSERT ( column1
   ,column2
   ,column3
   ,column4
  )
 VALUES ( l_value1
   ,l_value2
   ,l_value3
   ,l_value4
  );

• Conditional inserts and updates are now possible by using a WHERE clause

MERGE INTO table_name tbl
USING (SELECT NULL FROM DUAL)
ON (tbl.column1 = l_value1 AND
  tbl.column2 = l_value2
 )
WHEN MATCHED THEN
 UPDATE SET column3 = l_value3
     ,column4 = l_value4
  WHERE column5 = l_value5
WHEN NOT MATCHED THEN
 INSERT ( column1
   ,column2
   ,column3
   ,column4
  )
 VALUES ( l_value1
   ,l_value2
   ,l_value3
   ,l_value4
  )
  WHERE l_value5 <> 100;

• An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.

MERGE INTO table_name tbl
USING (SELECT NULL FROM DUAL)
ON (tbl.column1 = l_value1 AND
  tbl.column2 = l_value2
 )
WHEN MATCHED THEN
 UPDATE SET column3 = l_value3
     ,column4 = l_value4
  WHERE column5 = l_value5
WHEN NOT MATCHED THEN
 INSERT ( column1
   ,column2
   ,column3
   ,column4
  )
 VALUES ( l_value1
   ,l_value2
   ,l_value3
   ,l_value4
  )
  WHERE l_value5 <> 100
DELETE WHERE (column4 <> l_value4);

3. Collect Function

COLLECT function is used to aggregate data into a collection, retaining multiple records of data within a single row (like a nested table).

SQL> SELECT manager_name, COLLECT(emp_name) Emps
  2    FROM test_employee
  3   GROUP BY manager_name;

MANAGER_NAME EMPS
-----------------------------------------------------------------------
Reddy        SYSTPgYIGt3FzukLgQGRALxhZSQ==('Gagan', 'Surya')
Vijay        SYSTPgYIGt3FzukLgQGRALxhZSQ==('Akash', 'Ganga', 'Prithvi')

Here SYSTPgYIGt3FzukLgQGRALxhZSQ== is a supporting type (Collection) created by Oracle.

4. DBMS_OUTPUT Size Limit

Prior to Oracle 10gR2, put and put_line procedures of dbms_output package have an imposed limit of 255 bytes. In 10.2, this restriction has been relaxed to 32,767 bytes.

5. Quoting String Literals

Oracle 10g introduces new quoting mechanism in PL/SQL which enables us to embed single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. It is very useful in writing dynamic queries.

The quoting mechanism is invoked with a simple "q" in PL/SQL. The syntax is q'[...]', where the "[" and "]" characters can be any of the following.

! !
[ ]
{ }
( )
< >

DECLARE
  l_sql VARCHAR2(1024);
  l_cnt PLS_INTEGER;
BEGIN
  l_sql := q'{SELECT COUNT(emp_name)
                FROM test_employee
               WHERE manager_name = 'Vijay'
             }';

  EXECUTE IMMEDIATE l_sql INTO l_cnt;


  DBMS_OUTPUT.PUT_LINE(l_cnt || ' employees reporting to Vijay.');
END;

Output:
3 employees reporting to Vijay.

More to follow...

Table Mutating Trigger

When a Trigger is created on a table and the trigger code is performing the read operation on the same table, then the mutating happens.

CREATE TRIGGER sample_trigger
 AFTER INSERT
    ON sample_table
   FOR EACH ROW
local_variable NUMBER;
BEGIN
  IF INSERTING
  THEN
    SELECT sample_column
      INTO local_variable
      FROM sample_table;
  .
  .
  .

  END IF;
END sample_trigger;

To resolve this, we need to store the required values in global variables when row level trigger is fired and create a statement level trigger which uses this global variable.

CREATE PACKAGE sample_package AS
  global_variable NUMBER;
END sample_package;

--row level trigger

CREATE TRIGGER sample_trigger_1
 AFTER INSERT
    ON sample_table
   FOR EACH ROW
BEGIN
  IF INSERTING
  THEN
    global_variable := :NEW.sample_column;
  END IF;
END sample_trigger_1;

--statement level trigger

CREATE TRIGGER sample_trigger_2
 AFTER INSERT
    ON sample_table
BEGIN
  IF INSERTING
  THEN
    local_variable := global_variable;
  .
  .
  .

  END IF;
END sample_trigger_2;

Disadvantage: It will be useful only when COMMIT happens for each and every row separately. It will not be useful when COMMIT happens after multiple updates. The second trigger fires only after COMMIT and the required operation happens only for the last row.

NUMBER Datatype - Scale greater than Precision

Number(p,s) is one of the numeric datatypes available in Oracle.

Where p is the precision and s is the scale.

For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal -> 12345.67

You can specify a scale that is greater than precision, although it is uncommon.

In this case,

1. Only digit before the decimal point is 0 (zero)
2. Scale s specifies the maximum number of digits to the right of the decimal point
3. Scale-Precision s-p specifies the minimum number of zeros present after the decimal point

For example, number(2,7) can store 0.0000012.

Maximum number of digits to the right of the decimal point is 7

Minimum number of zeros present after the decimal point is 7-2=5

Reset Oracle Sequence Value

Many of the Oracle developers are searching the internet for resetting the sequence value.

There is no such attribute to alter the sequence value directly in Oracle.

But a few simple steps will solve the problem.

Let me take an example and explain the steps.

I have a sequence, emp_id_seq, and its current value is 100.

I need to reset the sequence value to 150.

SELECT emp_id_seq.currval FROM DUAL;

O/P: 100

In Oracle, we can alter the sequence increment by value.

With the help of it, alter the increment value such that the next increment should reach your expected value. In our example, I am incrementing by 50 to reach 150 from 100.

ALTER SEQUENCE emp_id_seq INCREMENT BY 50;

Do select the sequence next value to reach the expected value.

SELECT emp_id_seq.nextval FROM DUAL;

O/P: 150

Set back your increment value. If your increment value before is 1, set it back to 1.

ALTER SEQUENCE emp_id_seq INCREMENT BY 1;

Thats it!!!

Its very simple.

From here, your sequence value increments by 1 as usual.

SELECT emp_id_seq.nextval FROM DUAL;

O/P: 151

Note: You can also give negative numbers in the Increment By option. For example, if you need to reset the sequence value from 150 to 100, then give -50 so that it will be decremented by 50.