Customised Oracle Training Workshops: Exploring SQL & PL/SQL 11g New Features

1. SAGE Computing Services Customised Oracle Training Workshops and Consulting 11g New Features … of the SQL & PL/SQL Variety Scott Wesley Systems Consultant

2. Documentation Passwords Sequences Triggers SQL PL/SQL Recursion Read only tables Virtual columns 11g

3. Documentation is your friend

5. 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.

6. Can you log in?

7. SAGE@sw11g> conn scott/tiger ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.

8. SAGE@sw11g> conn scott/Tiger Connected.

9. SYS@sw11g> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = false; System altered.

10. SCOTT@sw11g> conn scott/tiger Connected.

11. SYS@sw11g> select username, password_versions from dba_users where username = 'SCOTT'; USERNAME PASSWORD --------------- -------- SCOTT 10G 11G 1 row selected.

12. Password Complexity

13. SYS@sw11g> @$ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql SYS@sw11g> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11g; Profile altered.

14. 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

15. SYS@sw11g> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL; Profile altered.

16. Quick win: SQL*Plus supports BLOBs

18. Available at every site Can do pie charts Free Supported by Oracle SQL*Plus Thin Fast

20. Let’s get our hands dirty...

21. Allow Sequence in PL/SQL expressions

22. 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.

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

24. 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

25. 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

26. 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;

27. 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

28. Same Same. But Different

29. SCOTT@sw11g> insert into T test2 2 select rownum, rownum from dual connect by level < 50000; 49999 rows created. Elapsed: 00:00:04.01

30. 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

31. Triggers are still an overhead (in this case) Seth Godin -->

32. As Connor McDonald likes to say: What we really need is...

33. create table typical_table ( id_col number default id_seq.nextval, ...

34. But we do have trigger improvements

35. create or replace trigger populate_id before insert on T for each row disable begin :new.id := id_seq.nextval; end; /

36. Compound triggers

37. 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.

38. 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.

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

40. create table audit_emp (employee_id number(20) ,old_salary number(10) ,new_salary number(10) ,ts timestamp);

41. 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; /

42. 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;

43. 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_auditaudit_t; ln_indexsimple_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;

44. 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_auditaudit_t; ln_indexsimple_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 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;

45. 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_auditaudit_t; ln_indexsimple_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;

46. 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.

47. Triggers are still ok (in this case)

48. 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; /

49. HR@sw11g> update employees set salary=1 where employee_id = 99; package_trigger custom_stuff 1 row updated.

50. What about “PRECEDES”?