Oracle 10g New features

 

1.        COLLECT  function

            SELECT   deptno, COLLECT(ename) AS emps
                FROM   emp
              GROUP BY deptno;

2.        Regular Expressions

         ·        REGEXP_LIKE

         ·        REGEXP_INSTR

         ·        REGEXP_SUBSTR

         ·        REGEXP_REPLACE

3.    Aggregating data with RETURNING clause 

     DELETE FROM emp      WHERE deptno =(SELECT deptno                                      FROM dept WHERE dname ='RESEARCH'                     RETURNINGCOUNT(sal),SUM(sal)       INTO r_savings;

4. DBMS_SCHEDULER : place and extend the functionality provided by the DBMS_JOBpackage. 

           Provide privileges and roles specifically for the scheduler to increase control over the scheduling of job

 

5. Enhancements in  DBMS_OUTPUT package
   

Line length : In versions prior to 10.2, the PUT and PUT_LINE procedures have an imposed limit of 255 bytes. This means that to display anything longer than this requires a "string chopping" utility (almost every Oracle developer must have written or borrowed one of these at some time). This is less than ideal, especially when using this package for screen-based debugging. In 10.2, this restriction has been relaxed to the more sensible 32,767 bytes (the same upper line-length as the equivalently-named UTL_FILE procedures).

Eg : BEGIN  
    DBMS_OUTPUT.put_line ( RPAD('x',32767,'x'));
    END;

Buffer Limits:  SET SERVEROUTPUT ON SIZE UNLIMITED

            SQL> SHOW SERVEROUTPUT

            SERVEROUTPUT ON SIZE UNLIMITED FORMAT WORD_WRAPPED

6. Flashback Restore Points

         This technology provides a means of restoring data as it existed at a specific point in time (defined by either a timestamp or System Change Number)

SQL> CREATE RESTORE POINT before_we_do_anything;

Restore point created.

SQL> DELETE FROM t WHERE ROWNUM <= 2;

2 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> FLASHBACK TABLE t TO RESTORE POINT before_we_do_anything;

Flashback complete.

 

7.  MERGE Enhancements

·         MERGE was introduced in Oracle 9i. Since then it has gone through a revamp to accommodate new features, most importantly:MERGE in 10G supports insert-only and update-only operations. Its 9i predecessor required both WHEN MATCHED and WHEN NOT MATCHED to be present in the statement, this is no longer necessary in 10G.

o   You may ask why one would use MERGE for only a single operation – when there is no all-in-one "upsert" happening, what is the point? What benefit does it give us over good old INSERT or UPDATE?

o   Well, the big advantage is of ease of coding and understanding. MERGE is a lot more intuitive to read – and so a lot less error-prone – than the equivalent UPDATE with correlated subqueries.

o   MERGE might also have a performance advantage over INSERT or UPDATE – don’t assume that though, do benchmark and test.

·         MERGE in 10G supports the delete operation as part of the update clause.

·         Merge in 10G has improved exception reporting.
MERGE INTO student_n a
   USING (SELECT SID, sname, tot_m

            FROM student) b

   ON (a.SID = b.SID)

   WHEN MATCHED THEN

      UPDATE

         SET a.sname = b.sname

      DELETE

         WHERE a.SID > 7

   WHEN NOT MATCHED THEN

      INSERT (a.SID, a.sname, a.tot_m, a.nn)
      VALUES (b.SID, b.sname, b.tot_m, 'Good');