SAGE Computing Services

Customised Oracle Training Workshops and Consulting

11g New Features

… of the SQL & PL/SQL Variety

Scott Wesley

Systems Consultant

Documentation

Passwords

Sequences

Triggers

SQL

PL/SQL

Recursion

Read only tables

Virtual columns

11g

Documentation is your friend

Readme’s are still around

  • Features Not Available or Restricted in This Release
    • Edition-based redefinition is not available in Oracle Database 11g Release 1 (11.1). You cannot create an edition, an editioning view, or a crossedition trigger; nor can you use the ENABLE EDITIONS clause in the CREATE USER and ALTER USER commands. As a consequence, other related functionality (for example, the ALTER SESSION SET EDITION statement or the new overload of DBMS_Sql.Parse() that lets you specify an edition or a crossedition trigger) becomes uninteresting and attempting to use it will cause a semantic error.

Can you log in?

SAGE@sw11g> conn scott/tiger

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SAGE@sw11g> conn scott/Tiger

Connected.

SYS@sw11g> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = false;

System altered.

SCOTT@sw11g> conn scott/tiger

Connected.

SYS@sw11g> select username, password_versions

from dba_users where username = 'SCOTT';

USERNAME PASSWORD

--------------- --------

SCOTT 10G 11G

1 row selected.

Password Complexity

SYS@sw11g> @$ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql

SYS@sw11g> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11g;

Profile altered.

SCOTT@sw11g> password

Changing password for SCOTT

Old password:

New password:

Retype new password: sagesage

ERROR:

ORA-28003: password verification for the specified password failed

ORA-20006: Password too simple

Password unchanged

SYS@sw11g> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.

Quick win: SQL*Plus supports BLOBs

SQL*Plus

Free

Available at every site

Supported by Oracle

Thin

Fast

Can do pie charts

Let’s get our hands dirty...

Allow Sequence in PL/SQL expressions

SCOTT@sw11g> create table T ( id number, value number );

Table created.

SCOTT@sw11g> create sequence id_seq;

Sequence created.

SCOTT@sw11g> create or replace

2 trigger populate_id

3 before insert on T

4 for each row

5 begin

6 -- dbms_db_version.ver_le_10

7 -- select id_seq.nextval into from dual;

8

9 -- dbms_db_version.ver_le_11

10 :new.id := id_seq.nextval;

11 end;

12 /

Trigger created.

This feature brings improved usability for the PL/SQL programmer and improved runtime performance and scalability.

SCOTT@sw11g> declare

2 n pls_integer;

3 begin

4 for i in 1 .. 50000 loop

5 select id_seq.nextval into n from dual;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.18

SCOTT@sw11g> declare

2 n pls_integer;

3 begin

4 for i in 1 .. 50000 loop

5 n := id_seq.nextval;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.68

alter session set sql_trace=true;

variable n number

begin

for i in 1 .. 100 loop

:n := scott.id_seq.nextval;

end loop;

end;

/

alter session set sql_trace=false;

Select ID_SEQ.NEXTVAL

from

dual

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 100 0.01 0.03 0 0 0 0

Fetch 100 0.00 0.05 0 0 5 100

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 201 0.01 0.09 0 0 5 100

Same Same. But Different

SCOTT@sw11g> insert into T test2

2 select rownum, rownum from dual connect by level < 50000;

49999 rows created.

Elapsed: 00:00:04.01

SCOTT@sw11g> drop trigger populate_id;

Trigger dropped.

SCOTT@sw11g> insert into T test2

2 select id_seq.nextval, rownum

from dual connect by level < 50000;

49999 rows created.

Elapsed: 00:00:00.71

Triggers are still an overhead (in this case)

Seth Godin -->

As Connor McDonald likes to say:

What we really need is...

create table typical_table

( id_col number default id_seq.nextval,

...

But we do have trigger improvements

create or replace

trigger populate_id

before insert on T

for each row

disable

begin

:new.id := id_seq.nextval;

end;

/

Compound triggers

CREATE OR REPLACE TRIGGER compound_trigger

FOR UPDATE OF salary ON employees

COMPOUND TRIGGER

-- Declarative part (optional)

-- Variables declared here have firing-statement duration.

threshold CONSTANT SIMPLE_INTEGER := 200;

BEFORE STATEMENT IS

BEGIN

NULL;

END BEFORE STATEMENT;

BEFORE EACH ROW IS

BEGIN

NULL;

END BEFORE EACH ROW;

AFTER EACH ROW IS

BEGIN

NULL;

END AFTER EACH ROW;

AFTER STATEMENT IS

BEGIN

NULL;

END AFTER STATEMENT;

END compound_trigger;

/

Trigger created.

To avoid the mutating-table error

eg: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department.

To accumulate rows destined for a second table so that you can periodically bulk-insert them

create table audit_emp

(employee_id number(20)

,old_salary number(10)

,new_salary number(10)

,ts timestamp);

create or replace trigger old_way

after update of salary

on emp_large

for each row

begin

insert into audit_emp

values (:new.employee_id

,:old.salary

,:new.salary

,systimestamp);

end old_way;

/

SAGE@sw11g> update emp_large set salary = salary -1;

107892 rows updated.

Elapsed: 00:00:08.75

SAGE@sw11g> select count(*) from audit_emp;

COUNT(*)

----------

107892

1 row selected.

alter trigger old_way disable;

create or replace trigger new_way

for update of salary on emp_large

compound trigger

threshhold constant simple_integer := 100;

type audit_t is table of

audit_emp%rowtype index by simple_integer;

t_audit audit_t;

ln_index simple_integer := 0;

after each row is

begin

ln_index := ln_index + 1;

t_audit(ln_index).employee_id := :new.employee_id;

t_audit(ln_index).old_salary := :old.salary;

t_audit(ln_index).new_salary := :new.salary;

t_audit(ln_index).ts := systimestamp;

if ln_index >= threshhold then -- index >= 100

flush_array;

end if;

end after each row;

procedure flush_array is

n constant SIMPLE_INTEGER := t_audit.count();

begin

forall j in 1..n

insert into audit_emp values t_audit(j);

t_audit.delete();

ln_index := 0;

end flush_array;

procedure flush_array is

n constant SIMPLE_INTEGER := t_audit.count();

begin

forall j in 1..n

insert into audit_emp values t_audit(j);

t_audit.delete();

ln_index := 0;

end flush_array;

after statement is

begin

flush_array;

end after statement;

SAGE@sw11g> update emp_large set salary = salary -1;

107892 rows updated.

Elapsed: 00:00:04.01

SAGE@sw11g> select count(*) from audit_emp;

COUNT(*)

----------

107892

1 row selected.

Triggers are still ok (in this case)

create or replace trigger package_trigger

after update of salary

on employees

for each row

begin

dbms_output.put_line('package_trigger');

end old_way;

/

create or replace trigger custom_stuff

after update of salary

on employees

for each row

follows package_trigger

begin

dbms_output.put_line('custom_stuff');

end old_way;

/

HR@sw11g>

update employees set salary=1

where employee_id = 99;

package_trigger

custom_stuff

1 row updated.

What about “PRECEDES”?

Named Parameters in SQL

create or replace function

f(p1 in integer

,p2 in integer := 2

,p3 in integer := null) return number is

begin

return nvl(p1,0)

+nvl(p2,0)

+nvl(p3,0);

end;

/

SAGE@sw11g> select f(1,2,3) from dual;

F(1,2,3)

----------

6

1 row selected.

SAGE@sw11g> select f from dual;

select f from dual

*

ERROR at line 1:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'F'

SAGE@sw11g> select f(1,null) from dual;

F(1,NULL)

----------

1

1 row selected.

SAGE@sw11g> select f(1,p3=>3) from dual;

F(1,P3=>3)

----------

6

1 row selected.

CONTINUE-WHEN

declare

x number := 0;

begin

<< my_loop >>

loop -- after continue statement, control resumes here

dbms_output.put_line ('Inside loop: x = ' || to_char(x));

x := x + 1;

continue my_loop when x < 3;

dbms_output.put_line ('Inside loop, after CONTINUE: x = '

|| to_char(x));

exit when x = 5;

end loop my_loop;

dbms_output.put_line ('After loop: x = ' || to_char(x));

end;

/

Inside loop: x = 0

Inside loop: x = 1

Inside loop: x = 2

Inside loop, after CONTINUE: x = 3

Inside loop: x = 3

Inside loop, after CONTINUE: x = 4

Inside loop: x = 4

Inside loop, after CONTINUE: x = 5

After loop: x = 5

PL/SQL procedure successfully completed.

Native PL/SQL Compilation

create or replace

function factorial_interpreted(p_n number)

return number is

begin

if (p_n = 1)

then

return 1;

else

return factorial_interpreted(p_n-1)*p_n;

end if;

end;

/

create or replace

function factorial_native(p_n number)

return number is

begin

if (p_n = 1)

then

return 1;

else

return factorial_native(p_n-1)*p_n;

end if;

end;

/

ALTER PROCEDURE factorial_native

COMPILE PLSQL_CODE_TYPE=NATIVE

REUSE SETTINGS;

declare

l_n number;

begin

for i in 1..500000

loop

l_n := factorial_interpreted(50);

end loop;

end;

/

Elapsed: 00:00:14.85

declare

l_n number;

begin

for i in 1..500000

loop

l_n := factorial_native(50);

end loop;

end;

/

Elapsed: 00:00:10.26

Read only tables

alter table logons read only;

HR@sw11g> update logons set user_id = upper(user_id);

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "HR"."LOGONS"

conn sage/sage

SAGE@sw11g> select privilege

from user_tab_privs

where table_name = 'LOGONS';

PRIVILEGE

--------------------

UPDATE

1 row selected.

SAGE@sw11g> update hr.logons set user_id = upper(user_id)

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "HR"."LOGONS"

Very

Versatile

Virtual

Verticals

Virtual Columns

  • Formula/computed columns – on the database
  • Further constraints – on the database
  • New category for partitioning – on the database
  • Creative referential integrity – on the database

Without

  • Triggers - expensive
  • Views – sometimes forgotten
  • Re-design – too much hard work!

And there’s more!

Query result cache

PL/SQL Result Cache

Pivot / Unpivot

Invisible indexes

PL/SQL Inlining Optimisation

SQL Plan Management

SQL Performance Analyser

DBA Stuff

SAGE Computing Services

Customised Oracle Training Workshops and Consulting

Questions and Answers?

Presentations are available from our website:

http://www.sagecomputing.com.au

enquiries@sagecomputing.com.au

scott.wesley@sagecomputing.com.au

11g New Features - Google Slides