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
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;
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 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
flush_array;
end if;
end after each row;
after statement is
begin
flush_array;
end after statement;
end new_way;
/
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;
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;
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 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
flush_array();
end if;
end after each row;
after statement is
begin
flush_array;
end after statement;
end new_way;
/
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;
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;
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 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
flush_array;
end if;
end after each row;
after statement is
begin
flush_array;
end after statement;
end new_way;
/
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
Without
Demo…
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
http://triangle-circle-square.blogspot.com