CURSOR_SPACE_FOR_TIME to be deprecated in future releases

According to a recently published metalink note (565424.1) the parameter "CURSOR_SPACE_FOR_TIME" is going to be deprecated starting release 11.1.0.7 onwards. In fact Oracle's next patch set release for 10g - 10.2.0.5 will also de-support this parameter. We will have to remove this parameter whenever these 2 patch sets - 10.2.0.5 for 10g and 11.1.0.7 for 11g are released and we decided to migrate. CURSOR_SPACE_FOR_TIME was introduced to reduce latch contention; This parameter may no longer be required with introduction of cursor mutexes hence Oracle has decided to deprecate this in future releases.

Oracle 11g New Features – PL/SQL enhancements Part-II

This is the concluding part of the 2 part series on PL/SQL enhancements in 11g. Here are few more useful enhancements in PL/SQL :-

1. Usage of Named and mixed notation with PL/SQL subprograms in a SQL statement

Prior to 11g, to use a sub-program (e.g. function) in a SELECT statement you have to provide the real parameters in positional notation. Let’s look at an example using both 10g and 11g to understand it better.

Here is a small piece of code which calculates the years of employment of an employee :-

CREATE OR REPLACE FUNCTION cal_employment_duration (
empid IN NUMBER,
as_on IN DATE
)
RETURN NUMBER
IS
vduration NUMBER;
BEGIN
SELECT MONTHS_BETWEEN (as_on, hiredate) / 12
INTO vduration
FROM emp
WHERE empno = empid;

RETURN ROUND (vduration);
END;
/

Now I call this function using a SELECT statement in Oracle 10g. Observe the difference when I use named and mixed notation :-

SIPRAS@ORA10G> SELECT empno, hiredate,
2 cal_employment_duration (empno, SYSDATE) || ' Years' "Employee Tenure"
3 FROM emp
4 WHERE empno = 7788
5 /

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

SIPRAS@ORA10G> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 as_on => SYSDATE
4 )
5 || ' Years' "Employee Tenure"
6 FROM emp
7 WHERE empno = 7788;
cal_employment_duration (empid => empno,
*
ERROR at line 2:
ORA-00907: missing right parenthesis

Now lets use the same example in Oracle 11g :-

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empno, SYSDATE) || ' Years' "Employee Tenure"
3 FROM emp
4 WHERE empno = 7788;

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

Now lets use named notations and see if it works or not:-

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 as_on => SYSDATE
4 )
5 || ' Years' "Employee Tenure"
6 FROM emp
7 WHERE empno = 7788;

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empno, as_on => SYSDATE)
3 || ' Years' "Employee Tenure"
4 FROM emp
5 WHERE empno = 7788;

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

Great! it works, except in the following case; Note the error message :-

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 SYSDATE)
4 || ' Years' "Employee Tenure"
5 FROM emp
6 WHERE empno = 7788;
cal_employment_duration (empid => empno,
*
ERROR at line 2:
ORA-06553: PLS-312: a positional parameter association may not follow a named
association

Not sure why this was left out of the enhancements.

2. Trigger Enhancements: A couple of enhancements have been done to triggers. I have captured all of them with examples below.

i) You can now ENABLE or DISABLE triggers at creation time e.g

CREATE TRIGGER test_trig
BEFORE INSERT ON EMP
DISABLED/ENABLED

ii) Compound trigger: A new trigger type called “compound” has been introduced. It basically implements actions for all of the DML timing points in a single trigger. Action for each timing point “BEFORE STATEMENT”, “AFTER STATEMENT”, “BEFORE EACH ROW” and “AFTER EACH ROW” can now be written in a single trigger. Here is an example of :-

This trigger tracks updates to the “sal” column; It (a) assigns an update_id, notes start and end time for each update statement (b) keeps old and new salary for every row updated. All of this done with a single trigger.

CREATE OR REPLACE TRIGGER track_sal_upd
FOR UPDATE OF sal ON emp
COMPOUND TRIGGER
max_sal_upd_id number(4);
BEFORE STATEMENT IS
BEGIN
select nvl(max(sal_upd_id),0) into max_sal_upd_id from sal_update_log;
max_sal_upd_id := max_sal_upd_id + 1;
insert into sal_update_log values(max_sal_upd_id,null,null,null,systimestamp,'salary update process '||max_sal_upd_id||' started');
END BEFORE STATEMENT;

AFTER EACH ROW IS
BEGIN
insert into sal_update_log values(max_sal_upd_id,:old.empno,:old.sal,:old.sal,systimestamp,'updated by process '||max_sal_upd_id);
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
insert into sal_update_log values(max_sal_upd_id,null,null,null,systimestamp,'salary update process '||max_sal_upd_id||' finished');
END AFTER STATEMENT;
END track_sal_upd;
/

iii) Ordering of triggers: You can now control the order in which the triggers on a table would get fired. Oracle 11g has introduced a new clauses “FOLLOWS” to implement this feature. It will allow you to control the order in which the triggers fire when you have multiple triggers of same type on the same table. Oracle randomly picks up the triggers (if multiple triggers of same type on same table exist) if FOLLOWS clause is not used, which was the case prior to 11g.

Here an example which uses FOLLOWS clause. In this example, we have 2 triggers – first one “check_sal” is only for updates to SAL column whereas second one “check_update_job” will fire on updates to JOB & SAL column. Mark the FOLLOWS clause on “check_sal” trigger. It states that check_sal trigger should fire only after “check_update_job” has fired..

CREATE OR REPLACE TRIGGER check_update_job
BEFORE UPDATE OF job,sal
ON emp
FOR EACH ROW
DECLARE
v_start_range NUMBER (7, 2);
v_end_range NUMBER (7, 2);
BEGIN
IF :OLD.job IN ('CLERK','SALESMAN') THEN
IF :NEW.JOB != 'MANAGER' THEN
raise_application_error (num => -20001,
msg => 'Cannot change job to ' || :NEW.job
);
END IF;
END IF;
END;
/

CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
FOLLOWS check_update_job
DECLARE
v_start_range NUMBER (7, 2);
v_end_range NUMBER (7, 2);
BEGIN
SELECT start_range, end_range
INTO v_start_range, v_end_range
FROM sal_range
WHERE job = :NEW.job;
IF :NEW.sal NOT BETWEEN v_start_range AND v_end_range
THEN
raise_application_error (num => -20000,
msg => 'Salary is not in the prescribed range'
);
END IF;
END;
/

SIPRAS@11glab> select empno,job,sal from emp where empno=7369;

EMPNO JOB SAL
---------- --------- ----------
7369 CLERK 800

SIPRAS@11glab> update emp set sal=500, job='PRESIDENT' where empno=7369;
update emp set sal=500, job='PRESIDENT' where empno=7369
*
ERROR at line 1:
ORA-20001: Cannot change job to PRESIDENT
ORA-06512: at "SIPRAS.CHECK_UPDATE_JOB", line 7
ORA-04088: error during execution of trigger 'SIPRAS.CHECK_UPDATE_JOB'

See the above error, it fired “check_update_job” first. In releases prior to Oracle 11g, you would not be able to control this.

3. Enhancements to PL/SQL Native compilation

Prior to Oracle 11g, initialization parameters were used to setup native compilation of PL/SQL code. However starting 11g, only one parameter “PLSQL_CODE_TYPE” needs to be set. It can be set to “INTERPRETED” (default value) or “NATIVE”. This parameter can be set at session level (ALTER SESSION), system level (ALTER SYSTEM) and for specific subprograms too using ALTER PROCEDURE. We all know, native compilation help improve the speed of PL/SQL programs as it compiles them to native code. It is of great help when your code consists of lot of loops, calculations, branches etc.

The trigger enhancements are really cool! I particularly liked the compound trigger feature the most. Share your thoughts on what you like…This was the concluding part of the PL/SQL enhancement series. Here is the link to first post in this series.

Technorati Tags: Oracle 11g,PL/SQL New Features,11g New Features,Trigger Enhancements

Oracle Critical Patch Update July 2008

Oracle has release the 3rd critical patch update for 2008 (CPU July 2008). Critical patch updates mostly contain fixes to security vulnerabilities plus it would have non-security fixes too. This critical patch update contains 45 new security fixes across all products which includes14 for Oracle database. Please review the following URL to see if the product you are using requires this patch or not.

http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpujul2008.html

Also refer to Metalink note id 579278.1 for Oracle database and Fusion middleware products.

The next critical patch update (CPU) would be coming in October 2008.

Happy patching!

Technorati Tags: Oracle,CPU 2008,CPU,Patch Update