Oracle World 2003 Experience the Oracle 9 i

Oracle. World 2003 Experience the Oracle 9 i PL/SQL New Features Joe Trezzo The Ultimate Software Consultants (TUSC) A TUSC Presentation Session ID 36954

The Essence of a New Day This is the beginning of a new day. You have been given this day to do as you will. You can waste it or use it for good. What you do today is important because you are exchanging a day of your life for it. When tomorrow comes, this day will be gone forever; in its place is something that you have left behind…let it be something good. A TUSC Presentation 2

Audience Knowledge • • • Oracle Experience Developer Experience DBA Experience PL/SQL Knowledge Oracle 8 i Experience Oracle 9 i Experience A TUSC Presentation 3

Presentation Goals/Non-Goals • Goals – Highlight New Oracle 9 i PL/SQL Features – Highlight Oracle 9 i Features – Add to your Arsenal of PL/SQL Knowledge • Non-Goals – Detail Every New PL/SQL Feature A TUSC Presentation 4

Presentation Outline · · · · Oracle PL/SQL Version History Oracle 9 i PL/SQL Compiler Limits & Internal Improvements New Data Types New Built-In Functions New SQL Commands New Native PL/SQL Compilation Expanded Oracle Supplied Packages Additional Oracle 9 i Features A TUSC Presentation 5

Oracle PL/SQL Version History A TUSC Presentation 6

Oracle PL/SQL Version History • Oracle and PL/SQL History Oracle Database Version PL/SQL Version 6. x 7. 0 7. 1 7. 2 7. 3 8. 0 8. 1. 5 (Oracle 8 i) 8. 1. 6 (Oracle 8 i R 2) 8. 1. 7 (Oracle 8 i R 3) 9. 0 (Oracle 9 i) 9. 2 (Oracle 9 i R 2) 1. 0 2. 1 2. 2 2. 3 8. 0 8. 1. 5 8. 1. 6 8. 1. 7 9. 0 9. 2 A TUSC Presentation 7

Oracle PL/SQL Version History SELECT banner FROM v$version; • Oracle 8 i (Release 3) BANNER -------------------------------Oracle 8 i Enterprise Edition Release 8. 1. 7. 0. 0 - Production PL/SQL Release 8. 1. 7. 0. 0 - Production • Oracle 9 i R 2 BANNER -------------------------------Oracle 9 i Enterprise Edition Release 9. 2. 0. 1. 0 - Production PL/SQL Release 9. 2. 0. 1. 0 – Production A TUSC Presentation 8

Oracle 9 i PL/SQL Compiler Limits & Internal Improvements A TUSC Presentation 9

Oracle PL/SQL Compiler Limits Item bind variables passed to a program unit exception handlers in a program unit fields in a record levels of block nesting levels of record nesting levels of subquery nesting levels of label nesting magnitude of a BINARY_INTEGER value magnitude of a PLS_INTEGER value objects referenced by a program unit parameters passed to an explicit cursor parameters passed to a function or procedure precision of a FLOAT value (binary digits) precision of a NUMBER value (decimal digits) A TUSC Presentation Limit 32 K 64 K 255 32 254 98 2 G 2 G 64 K 64 K 126 38 10

Oracle PL/SQL Compiler Limits Item precision of a REAL value (binary digits) size of an identifier (characters) size of a string literal (bytes) size of a CHAR value (bytes) size of a LONG RAW value (bytes) size of a VARCHAR 2 value (bytes) size of an NCHAR value (bytes) size of an NVARCHAR 2 value (bytes) size of a BIFLE value (bytes) size of a BLOB value (bytes) size of a CLOB value (bytes) size of an NCLOB value (bytes) A TUSC Presentation Limit 63 30 32 K 32 K-7 32 K 32 K 4 G 4 G 11

Oracle System Options • Oracle 9 i R 2 Options Detail on Your System SELECT * FROM v$option; PARAMETER ------------Partitioning Objects Real Application Clusters Advanced replication Bit-mapped indexes Connection pooling Oracle Label Security VALUE -------TRUE FALSE TRUE FALSE – 48 Options Listed and Only 2 Set to False By Default A TUSC Presentation 12

Internal PL/SQL Enhancements • Integration of SQL and PL/SQL Parsers – Prior to Oracle 9 i • Separate SQL Parser for SQL Engine and PL/SQL Engine – Oracle 9 i • SQL Parser the Same for SQL Engine and PL/SQL Engine • Internal PL/SQL Performance Improvements – SQL and PL/SQL Runtime Engines Integration – Reduction of Calling PL/SQL from SQL (60%+) A TUSC Presentation – Cross Package References Handled More Efficiently 13

New Data Types A TUSC Presentation 14

New Data Types • New Date/Time Data Types – TIMESTAMP (fractional_seconds_precision) • Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. – TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE • All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. – TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE • All values of TIMESTAMP WITH TIME ZONE, with the following exceptions: · Data is normalized to the database time zone when it is stored in the database. · When the data is retrieved, users see the data in the session time zone. 15 A TUSC Presentation

New Data Types • New Date/Time Data Types – INTERVAL YEAR (year_precision) TO MONTH • Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. – INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision) • Stores a period of time in days, hours, minutes, and seconds, where · day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2. · fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6. – Tables for Reference • Oracle 9 i SQL Reference – fractional_seconds_precision depends on your database server platform • Oracle Enterprise Edition Release 9. 2. 0. 1. 0 on a Compaq Tru 64 UNIX the number of digits was 6 maximum • Oracle Enterprise Edition Release 9. 2. 0. 1. 0 on Windows 2000 Professional the number of digits was 3 maximum A TUSC Presentation 16

New Data Types • TIMESTAMP Data Type Example – Create transaction table TIMESTAMP data type CREATE TABLE transactions (trans_id VARCHAR 2(5), trans_date TIMESTAMP(9)); – Two records inserted with SYSDATE value INSERT INTO transactions VALUES ('1', SYSDATE); INSERT INTO transactions VALUES ('2', SYSDATE); – Two records inserted with SYSTIMESTAMP value INSERT INTO transactions VALUES ('3', SYSTIMESTAMP); INSERT INTO transactions VALUES ('4', SYSTIMESTAMP); A TUSC Presentation 17

New Data Types • TIMESTAMP Data Type Example – Four Records selected from transaction Table SELECT * FROM transactions; TRANS_ID -------1 2 3 TRANS_DATE -----------------------11 -OCT-02 08. 05. 09. 00000 AM 11 -OCT-02 08. 05. 12. 00000 AM 11 -OCT-02 08. 05. 26. 534392000 AM 4 11 -OCT-02 08. 05. 29. 921128000 AM – Use SYSTIMESTAMP versus SYSDATE to set timestamp datatypes A TUSC Presentation 18

New Data Types • TIMESTAMP Data Type Example – Attempting to change the precision to a lower value is only allowed if the column is empty as shown below ALTER TABLE transactions MODIFY trans_date TIMESTAMP(6); ERROR at line 1: ORA-30082: datetime/interval column to be modified must be empty to decrease fractional second or leading field precision A TUSC Presentation 19

New Built-in Functions A TUSC Presentation 20

New Built-In Functions • Several New Built-In Functions • Covered in SQL Reference Manual • List of New Functions asciistr current_date existsnode from_tz localtimestamp rawtonhex sys_dburigen systimestamp to_nchar (character) to_timestamp tz_offset bin_to_num current_timestamp extract (datetime) group_id nullif rowidtonchar sys_extract_utc to_char (character) to_nchar (datetime) to_timestamp_tzto unistr coalesce dbtimezone extract (xml) grouping_id percentile_cont sessiontimezone sys_xmlagg to_clob to_nchar (number) to_yminterval width_bucket A TUSC Presentation compose decompose first last percentile_disc sys_connect_by_path sys_xmlgen to_dsinterval to_nclob treat 21

New Built-In Functions • New NULL Type Functions – NULLIF • Returns NULL if values match, otherwise returns first value – COALESCE • Returns first non-NULL value in list – SQL and PL/SQL Functions A TUSC Presentation 22

New Built-In Functions • New NULL Type Functions SELECT NULLIF('XXX', 'XXX') col 1, NULLIF('XXX', 'AAA') col 2, COALESCE(NULL, 123, 456) col 3, COALESCE(NULL, 789) col 4 FROM DUAL; COL 1 COL 2 COL 3 COL 4 ----- -----XXX 123 789 A TUSC Presentation 23

New Built-In Functions • New NULL Type Functions SET SERVEROUTPUT ON SIZE 1000000 DECLARE x 1 PLS_INTEGER : = 1234; x 2 PLS_INTEGER : = 1234; x 3 PLS_INTEGER : = 5678; x 4 PLS_INTEGER; x 5 PLS_INTEGER; x 6 PLS_INTEGER; x 7 PLS_INTEGER; x 8 PLS_INTEGER; BEGIN x 5 : = NULLIF(x 1, x 2); X 6 : = NULLIF(x 4, x 1); X 7 : = COALESCE(x 3, x 4, 4321); X 8 : = COALESCE(x 4, x 5, x 1); DBMS_OUTPUT. PUT_LINE('x 5: ' || x 5); DBMS_OUTPUT. PUT_LINE('x 6: ' || x 6); DBMS_OUTPUT. PUT_LINE('x 7: ' || x 7); DBMS_OUTPUT. PUT_LINE('x 8: ' || x 8); END; A TUSC Presentation / 24

New Built-In Functions • New NULL Type Functions x 5: x 6: x 7: 5678 x 8: 1234 PL/SQL procedure successfully completed. A TUSC Presentation 25

New SQL Commands A TUSC Presentation 26

New SQL Commands • 5 New SQL Commands • Covered in SQL Reference Manual • List of New Commands – CREATE PFILE – CREATE SPFILE – CASE Statement – MERGE – Multi-Table Inserts A TUSC Presentation 27

New SQL Commands • New CASE Expression – Similar to IF Statement – Multiple Methods of Writing Statements • Selector Option • Search Option – Boolean Evaluation: If TRUE, CASE Terminated – Recommendation: Order CASE Conditions Based on Most Highly Evaluated to TRUE First – Sets a Variable: Treated as Function – If No Match, Returns a NULL A TUSC Presentation 28

New SQL Commands • New CASE Expression – Selector Option DECLARE customer_rating VARCHAR 2(10); customer_credit VARCHAR 2(1); BEGIN customer_credit : = 'B'; customer_rating : = CASE customer_credit WHEN 'A' THEN 'Rank 1' WHEN 'B' THEN 'Rank 2' WHEN 'C' THEN 'Rank 3' ELSE 'Rank 5' END; DBMS_OUTPUT. PUT_LINE('Customer Ranking: ' || customer_rating); END; / Customer Ranking: Rank 2 PL/SQL procedure successfully completed. A TUSC Presentation 29

New SQL Commands • New CASE Expression – Search Option DECLARE customer_rating VARCHAR 2(10); customer_credit VARCHAR 2(1); BEGIN customer_credit : = 'B'; customer_rating : = CASE WHEN customer_credit = 'A' THEN 'Rank 1' WHEN customer_credit = 'B' THEN 'Rank 2' WHEN customer_credit = 'C' THEN 'Rank 3' ELSE 'Rank 5' END; DBMS_OUTPUT. PUT_LINE('Customer Ranking: ' || customer_rating); END; / Customer Ranking: Rank 2 PL/SQL procedure successfully completed. A TUSC Presentation 30

New SQL Commands • New CASE Statement – Similar to CASE Expression, but Each Condition Contains a PL/SQL Command – Not Treated as a Function – If No Match, Raises a CASE_NOT_FOUND Exception A TUSC Presentation 31

New SQL Commands • New CASE Statement – Selector Option DECLARE customer_rating VARCHAR 2(10); customer_credit VARCHAR 2(1); BEGIN customer_credit : = 'B'; CASE customer_credit WHEN 'A' THEN customer_rating : = 'Rank 1'; WHEN 'B' THEN customer_rating : = 'Rank 2'; WHEN 'C' THEN customer_rating : = 'Rank 3'; ELSE customer_rating : = 'Rank 5'; END CASE; DBMS_OUTPUT. PUT_LINE('Customer Ranking: ' || customer_rating); END; / Customer Ranking: Rank 2 PL/SQL procedure successfully completed. A TUSC Presentation 32

New SQL Commands • New CASE Statement – Search Option DECLARE customer_rating VARCHAR 2(10); customer_credit VARCHAR 2(1); BEGIN customer_credit : = 'B'; CASE WHEN customer_credit='A' THEN customer_rating: ='Rank 1'; WHEN customer_credit='B' THEN customer_rating: ='Rank 2'; WHEN customer_credit='C' THEN customer_rating: ='Rank 3'; ELSE customer_rating: ='Rank 5'; END CASE; DBMS_OUTPUT. PUT_LINE('Customer Ranking: ' || customer_rating); END; / Customer Ranking: Rank 2 PL/SQL procedure successfully completed. A TUSC Presentation 33

New SQL Commands • Merge Statement – Insert or Update Operation in One Statement • If record exists, update the record • If record does not exist, insert the record – Example Scenario • System 1: – Internal ADP Payroll Processing – ADP is the Single Point of New/Updates of Employees – Cannot Update Database Structures • System 2: – Time and Expense (T&E) Entry System – Replicate the Master Employee Table A TUSC Presentation 34

New SQL Commands • Merge Statement – Example Scenario • Prior to Oracle 9 i – Execute a Scheduled Routine to Update System 2 Master Emps – ADP Employee Information Updates T&E Employee Information – Select Each ADP Employee – If Exists in T&E, then Update – If Not Exists in T&E, then Insert • Oracle 9 i – Superceded with the Merge Statement A TUSC Presentation 35

New SQL Commands • Example Scenario SELECT employee_id, title, salary FROM adp_employees; EMPLOYEE_ID -----1 2 3 4 5 TITLE SALARY -------------PRESIDENT and COO 5000 VP, OPERATIONS 1450 VP, SALES 1400 VP, FINANCE 1450 VP, ADMINISTRATION 1550 SELECT employee_id, title, salary FROM employees; EMPLOYEE_ID -----1 2 3 TITLE SALARY -------------PRESIDENT 2500 VP, OPERATIONS 1450 A TUSC Presentation VP, SALES 1400 36

New SQL Commands • Prior to Oracle 9 i SET SERVEROUTPUT 1000000 DECLARE lv_adp_employees. employee_id%TYPE; CURSOR cur_adp_employee IS SELECT employee_id, title, salary FROM adp_employees; CURSOR cur_employee IS SELECT employee_id, title, salary FROM employees where employee_id = lv_adp_employee; lv_cur_employee%ROWTYPE; lv_emp_insert PLS_INTEGER : = 0; lv_emp_update PLS_INTEGER : = 0; BEGIN FOR lv_cur_adp_employee_rec IN cur_adp_employee LOOP lv_adp_employee : = lv_cur_adp_employee_rec. employee_id; OPEN cur_employee; FETCH cur_employee INTO lv_cur_employee; A TUSC Presentation 37

New SQL Commands • Prior to Oracle 9 i IF cur_employee%FOUND THEN UPDATE employees SET title = lv_cur_adp_employee_rec. title, salary = lv_cur_adp_employee_rec. salary WHERE employee_id = lv_cur_adp_employee_rec. employee_id; lv_emp_update : = lv_emp_update + 1; ELSE INSERT INTO employees (employee_id, title, salary) VALUES (lv_cur_adp_employee_rec. employee_id, lv_cur_adp_employee_rec. title, lv_cur_adp_employee_rec. salary); lv_emp_insert : = lv_emp_insert + 1; END IF; CLOSE cur_employee; END LOOP; DBMS_OUTPUT. PUT_LINE('Records Inserted: ' || lv_emp_insert); DBMS_OUTPUT. PUT_LINE('Records Updated: ' || lv_emp_update); END; A TUSC Presentation 38 /

New SQL Commands • Prior to Oracle 9 i Records Inserted: 2 Records Updated: 3 PL/SQL procedure successfully completed. A TUSC Presentation 39

New SQL Commands • Merge Statement MERGE INTO employees dest USING (SELECT employee_id, title, salary FROM adp_employees) orig ON (dest. employee_id = orig. employee_id) WHEN MATCHED THEN UPDATE SET dest. title = orig. title, dest. salary = orig. salary WHEN NOT MATCHED THEN INSERT (dest. employee_id, dest. title, dest. salary) VALUES (orig. employee_id, orig. title, orig. salary); 5 rows merged. A TUSC Presentation 40

New SQL Commands • Merge Statement SELECT employee_id, title, salary FROM employees; EMPLOYEE_ID -----1 2 3 4 5 TITLE SALARY -------------PRESIDENT and COO 5000 VP, OPERATIONS 1450 VP, SALES 1400 VP, FINANCE 1450 VP, ADMINISTRATION 1550 A TUSC Presentation 41

New SQL Commands • Multi-Table Inserts – Ability to Insert into Multiple Tables in One Statement – Allows for a Single Pass Through Data With Flexibility Based on the Values to Insert into Multiple Tables – Prior to this Command: Multiple Passes Required or Procedural Logic to Perform Separate Inserts – Example Scenario 1 • Master Expense Table Exists (expense_detail) • Load Expense >= $300 into a Review Table • Load Expenses < $300 into Process Table without Review A TUSC Presentation 42

New SQL Commands • Multi-Table Inserts – Example Scenario 1 • Create 2 New Tables CREATE TABLE expense_detail_review AS SELECT * FROM expense_detail WHERE 1=2; CREATE TABLE expense_detail_process AS SELECT * FROM expense_detail WHERE 1=2; SELECT count(*) FROM expense_detail; COUNT(*) -----158530 A TUSC Presentation 43

New SQL Commands • Multi-Table Inserts – Example Scenario 1 • Mutli-Table Insert Command Based on Criteria INSERT FIRST WHEN exdt_amt < 300 THEN INTO expense_detail_process ELSE INTO expense_detail_review SELECT * FROM expense_detail; 158530 rows created. A TUSC Presentation 44

New SQL Commands • Multi-Table Inserts – Example Scenario 1 • Table Counts of Expense Tables SELECT COUNT(*) FROM expense_detail; COUNT(*) -----158530 SELECT COUNT(*) FROM expense_detail_process; COUNT(*) -----156919 SELECT COUNT(*) FROM expense_detail_review; COUNT(*) -----1611 A TUSC Presentation 45

New SQL Commands • Multi-Table Inserts – Example Scenario 2 • Master Expense Table Exists (expense_detail) • Load Expense >= $300 into a Review Table • Load All Expenses into Process Table INSERT FIRST WHEN exdt_amt < 300 THEN INTO expense_detail_process ELSE INTO expense_detail_process INTO expense_detail_review SELECT * FROM expense_detail; 160141 rows created. A TUSC Presentation 46

New SQL Commands • Multi-Table Inserts – Example Scenario 2 • Table Counts of Expense Tables SELECT COUNT(*) FROM expense_detail; COUNT(*) -----158530 SELECT COUNT(*) FROM expense_detail_process; COUNT(*) -----158530 SELECT COUNT(*) FROM expense_detail_review; COUNT(*) -----1611 A TUSC Presentation 47

New Native PL/SQL Compilation A TUSC Presentation 48

New PL/SQL Compilation • Native Compilation of PL/SQL Code – Prior to Oracle 9 i • PL/SQL Code Stored in the Database • Upon Creation, Compiled into Byte Code (p-code) • Upon Execution, Loaded into Memory and Interpreted – Oracle 9 i Option • Convert PL/SQL Code into C Code • Becomes a Shared Library and Linked into Executable • Advantage: For Compute Intensive PL/SQL with Limited SQL Code, Faster Execution A TUSC Presentation 49

New PL/SQL Compilation • Native Compilation of PL/SQL Code – Oracle 9 i Option • Steps to Use: – Update $ORACLE_HOME/plsql/spnc_makefile. mk – Set the Following Init. ora Values: • PLSQL_NATIVE_LIBRARY_DIR • PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT • PLSQL_NATIVE_MAKE_UTILITY • PLSQL_NATIVE_MAKE_FILE_NAME – Set the PLSQL_COMPILER_FLAGS Init. ora Value • INTERPRETED (default) • NATIVE (C compilation) • Can Modify with an ALTER SESSION A TUSC Presentation 50

New PL/SQL Compilation • Native Compilation of PL/SQL Code DESCRIBE user_stored_settings Name Null? Type -----------------OBJECT_NAME NOT NULL VARCHAR 2(30) OBJECT_ID NOT NULL NUMBER OBJECT_TYPE VARCHAR 2(12) PARAM_NAME NOT NULL VARCHAR 2(30) PARAM_VALUE VARCHAR 2(4000) SELECT * FROM user_stored_settings; no rows selected A TUSC Presentation 51

New PL/SQL Compilation • Native Compilation of PL/SQL Code CREATE PROCEDURE xyz AS BEGIN DBMS_OUTPUT. PUT_LINE('Hello'); END; / Procedure created. SET SERVEROUTPUT ON EXECUTE xyz Hello PL/SQL procedure successfully completed. A TUSC Presentation 52

New PL/SQL Compilation • Native Compilation of PL/SQL Code SELECT * FROM user_stored_settings; OBJECT OBJ_ID OBJ_TYPE PARAM_NAME PARAM_VALUE ---------------XYZ 32506 PROCEDURE plsql_compiler_flags INTERPRETED, NON_DEBUG XYZ 32506 PROCEDURE nls_length_semantics BYTE ALTER SESSION SET plsql_compiler_flags = native; Session altered. CREATE PROCEDURE abc AS BEGIN DBMS_OUTPUT. PUT_LINE('Good Bye'); END; / A TUSC Presentation 53

New PL/SQL Compilation • Native Compilation of PL/SQL Code SET SERVEROUTPUT ON EXECUTE abc Good Bye PL/SQL procedure successfully completed. SELECT * FROM user_stored_settings WHERE object_name = 'ABC'; OBJECT OBJ_ID OBJ_TYPE PARAM_NAME PARAM_VALUE ---------------ABC 32508 PROCEDURE plsql_compiler_flags NATIVE, NON_DE BUG ABC 32508 PROCEDURE nls_length_semantics BYTE A TUSC Presentation 54

Expanded Oracle Supplied Packages A TUSC Presentation 55

Expanded Oracle Supplied Packages • Identify Packages Installed By Default (catproc. sql) – Groups Packages Installed – Provides Short Description of Grouping • New Reference Manual in Oracle 8. 1 (Oracle 8 i Supplied Package Reference) – Covers 70 Packages – Includes Many of the Packages Prior to Oracle 8 i • Oracle 9 i Supplied PL/SQL Packages and Type Reference A TUSC Presentation – Covers over 100 Packages 56

Expanded Oracle Supplied Packages • List of New Oracle 9 i Oracle Supplied Packages DBMS_AQELM DBMS_FGA DBMS_LDAP DBMS_LOGMNR_CDC_PUBLISH DBMS_METADATA DBMS_OUTLN_EDIT DBMS_TRANSFORM DBMS_XMLGEN DMBS_XMLSAVE UTL_URL DBMS_ENCODE DBMS_FLASHBACK DBMS_LIBCACHE DBMS_LOGMNR_CDC_SUBSCRIBE DBMS_ODCI DBMS_REDEFINITION DBMS_WM DBMS_XMLQUERY UTL_ENCODE A TUSC Presentation 57

Expanded Oracle Supplied Packages • List of New Oracle 9 i R 2 Oracle Supplied Packages DBMS_APPLY_ADM DBMS_LOGSTDBY DBMS_MGWMSG DBMS_RULE DBMS_STORAGE_MAP DBMS_STREAMS_ADM DBMS_XDBT DBMS_XMLDOM DBMS_CAPTURE_ADM DBMS_MGWADM DBMS_PROPAGATION_ADM DBMS_RULE_ADM DBMS_STREAMS DBMS_XDB_VERSION DBMS_XMLPARSER DBMS_XPLAN DBMS_XSLPROCESSOR A TUSC Presentation 58

Expanded Oracle Supplied Packages • View List of Supplied Packages on Your System SELECT object_name FROM all_objects WHERE owner = 'SYS' AND object_type = 'PACKAGE' ORDER BY object_name; OBJECT_NAME ---------------DBMS_APPLICATION_INFO DBMS_OUTPUT DBMS_PIPE DBMS_SESSION DBMS_SHARED_POOL A TUSC Presentation 59

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package – Perform Redefinition Online While Table Being Accessed – Change Table Structure, Storage, etc. – Creation Script: dbmshord. sql • Called By catproc. sql script • Located in $ORACLE_HOME/rdbms/admin Directory – Grant Privilege on Package to Schema • EXECUTE Privilege or EXECUTE_CATALOG_ROLE A TUSC Presentation 60

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package(DESCRIBE) PROCEDURE CAN_REDEF_TABLE Argument Name -------------UNAME TNAME PROCEDURE START_REDEF_TABLE Argument Name -------------UNAME ORIG_TABLE INT_TABLE COL_MAPPING PROCEDURE FINISH_REDEF_TABLE Argument Name -------------UNAME ORIG_TABLE INT_TABLE Type -----------VARCHAR 2 In/Out Default? -------IN IN Type -----------VARCHAR 2 In/Out Default? -------IN IN DEFAULT Type -----------VARCHAR 2 In/Out Default? -------IN IN IN A TUSC Presentation 61

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package(DESCRIBE) PROCEDURE SYNC_INTERIM_TABLE Argument Name -------------UNAME ORIG_TABLE INT_TABLE PROCEDURE ABORT_REDEF_TABLE Argument Name -------------UNAME ORIG_TABLE INT_TABLE Type ----------------------VARCHAR 2 VARCHAR 2 In/Out Default? -------IN IN IN A TUSC Presentation 62

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – Create Employee Table CREATE TABLE s_employee (employee_id NUMBER(7) employee_last_name VARCHAR 2(25) CONSTRAINT s_employee_id_nn NOT NULL, CONSTRAINT s_employee_last_name_nn NOT NULL, employee_first_name VARCHAR 2(25), userid VARCHAR 2(8), start_date DATE, comments VARCHAR 2(255), manager_id NUMBER(7), title VARCHAR 2(25), department_id NUMBER(7), salary NUMBER(11, 2), commission_pct NUMBER(4, 2), CONSTRAINT s_employee_id_pk PRIMARY KEY (employee_id), CONSTRAINT s_employee_userid_uk UNIQUE (userid), CONSTRAINT s_employee_commission_pct_ck CHECK (commission_pct IN (10, 12. 5, 17. 5, 20))); A TUSC Presentation 63

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – 25 Records Inserted into Employee Table – Assume the Following Desired: • • Remove COMMENTS Column Add FULL_NAME Column Change DEPARTMENT_ID Column Name to DEPT_ID Increase Salary of All Employees by 50% – Step 1: Ensure Table Can be Redefined • Execute CAN_REDEF_TABLE Procedure • If Execution Succeeds without Error, Then can Redefine A TUSC Presentation 64

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – Step 1: Ensure Table Can be Redefined EXECUTE dbms_redefinition. can_redef_table('plsql_user', 's_employee') PL/SQL procedure successfully completed. – Success: s_employee Table Can be Redefined – Illustration of a Table that Cannot Be Redefined • Create TEMP Table • Execute CAN_REDEF_TABLE • Error Due to No Primary Key in Table A TUSC Presentation 65

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – Illustration of a Table that Cannot Be Redefined CREATE TABLE temp (temp VARCHAR 2(10)); EXECUTE dbms_redefinition. can_redef_table('plsql_user', 'temp') begin dbms_redefinition. can_redef_table('plsql_user', 'temp'); end; * ERROR at line 1: ORA-12089: cannot online redefine table "PLSQL_USER". "TEMP" with no primary key ORA-06512: at "SYS. DBMS_REDEFINITION", line 8 ORA-06512: at "SYS. DBMS_REDEFINITION", line 236 ORA-06512: at line 1 – Set of Criteria Checked to Ensure Redefinition Allowed A TUSC Presentation 66

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – Criteria Detailed: • Oracle 9 i Database Administration Guide (Chp. 15) • Oracle 9 i Supplied PL/SQL Packages Reference (Chp. 45) – Step 2: Create Temporary Table with New Structure CREATE TABLE temp_s_employee (employee_id NUMBER(7) CONSTRAINT s_employee_id_nn 2 NOT NULL, employee_last_name VARCHAR 2(25) CONSTRAINT s_employee_last_name_nn 2 NOT NULL, employee_first_name VARCHAR 2(25), employee_full_name VARCHAR 2(51), userid VARCHAR 2(8), start_date DATE, manager_id NUMBER(7), title VARCHAR 2(25), dept_id NUMBER(7), salary NUMBER(11, 2), commission_pct NUMBER(4, 2), CONSTRAINT s_employee_id_pk 2 PRIMARY KEY (employee_id), CONSTRAINT s_employee_userid_uk 2 UNIQUE (userid), CONSTRAINT s_employee_commission_pct_ck 2 CHECK (commission_pct IN (10, 12. 5, 17. 5, 20))); A TUSC Presentation 67

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – Step 3: Start the Redefinition Process • Execute START_REDEF_TABLE Procedure • Map Existing Columns to Temporary Table Columns BEGIN dbms_redefinition. start_redef_table('plsql_user', 's_employee', 'temp_s_employee', 'employee_id, employee_last_name, employee_first_name, employee_first_name||'' ''||employee_last_name employee_full_name, userid, start_date, manager_id, title, department_id dept_id, salary * 1. 5 salary, commission_pct'); END; / A TUSC Presentation 68

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – Step 3: Start the Redefinition Process • If Singe Quote Desired, Use 2 Single Quotes (Line 7) • Standard SELECT Capabilities Valid in Column Mapping • Once Step 3 Complete, 25 Records Inserted into Temporary Table – Step 4: Complete the Redefinition Process • Execute FINISH_REDEF_TABLE Procedure • Synchronizes the Redefinition and Applies the Temporary Architecture and Contents to the Existing Table • Prior to Executing the FINISH_REDEF_TABLE Procedure, Option to Execute SYNC_INTERIM_TABLE Procedure to Synchronize Table Contents and Reduce the Completion Process Time A TUSC Presentation 69

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – Step 4: Complete the Redefinition Process BEGIN dbms_redefinition. sync_interim_table('plsql_user', 's_employee', 'temp_s_employee'); END; / BEGIN dbms_redefinition. finish_redef_table('plsql_user', 's_employee', 'temp_s_employee'); END; / A TUSC Presentation 70

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – Step 4: Complete the Redefinition Process DESCRIBE s_employee Name ---------------EMPLOYEE_ID EMPLOYEE_LAST_NAME EMPLOYEE_FIRST_NAME EMPLOYEE_FULL_NAME USERID START_DATE MANAGER_ID TITLE DEPT_ID SALARY COMMISSION_PCT Null? -------NOT NULL A TUSC Presentation Type ---NUMBER(7) VARCHAR 2(25) VARCHAR 2(51) VARCHAR 2(8) DATE NUMBER(7) VARCHAR 2(25) NUMBER(7) NUMBER(11, 2) NUMBER(4, 2) 71

Expanded Oracle Supplied Packages • DBMS_REDEFINITION Package Example – Step 4: Complete the Redefinition Process SELECT * FROM s_employee WHERE employee_id = 1; EMP_ID EMP_L_NAME EMP_FULL_NAME USERID ----------- -------1 VELASQUEZ CARMEN VELASQUEZ cvelasqu START_DATE MANAGER_ID TITLE DEPT_ID SALARY COMMISSION_PCT ---------- -------03 -MAR-90 PRESIDENT 50 3750 – Step 5: Drop the Temporary Table A TUSC Presentation 72

Expanded Oracle Supplied Packages • DBMS_METADATA Package – Provides an API to the Object Creation Layer – 19 Total Procedures and Functions – Concentration on GET_DDL Function FUNCTION GET_DDL RETURNS CLOB Argument Name Type -------------OBJECT_TYPE VARCHAR 2 NAME VARCHAR 2 SCHEMA VARCHAR 2 VERSION VARCHAR 2 MODEL VARCHAR 2 TRANSFORM VARCHAR 2 A TUSC Presentation In/Out -----IN IN IN Default? -------DEFAULT 73

Expanded Oracle Supplied Packages • DBMS_METADATA Package – Table Example (Create and GET_DDL) CREATE TABLE temp (temp VARCHAR 2(10) NOT NULL); SET LONG 350 SELECT dbms_metadata. get_ddl('TABLE', table_name) FROM user_tables WHERE table_name = 'TEMP'; DBMS_METADATA. GET_DDL('TABLE', TABLE_NAME) -------------------------------------CREATE TABLE "PLSQL_USER". "TEMP" ( "TEMP" VARCHAR 2(10) NOT NULL ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" A TUSC Presentation 74

Expanded Oracle Supplied Packages • DBMS_METADATA Package – Procedure Example (Create and GET_DDL) CREATE OR REPLACE PROCEDURE abc AS BEGIN NULL; END abc; SET LONG 350 SELECT dbms_metadata. get_ddl('PROCEDURE', object_name) FROM user_objects WHERE object_name = 'ABC'; DBMS_METADATA. GET_DDL('PROCEDURE', OBJECT_NAME) -------------------------------CREATE OR REPLACE PROCEDURE "PLSQL_USER". "ABC" AS BEGIN NULL; A TUSC Presentation 75 END abc;

Expanded Oracle Supplied Packages • UTL_FILE Package Enhancements in R 2 – Around Many Versions for Reading and Writing to Operating System Files – Several New Procedures to Provide More Control and Flexibility with Operating System Files – Directory Additions for Easier Maintenance CREATE OR REPLACE DIRECTORY 'TEMP_DIR' AS '/usr/users/oracle'; GRANT READ, WRITE ON DIRECTORY TEMP_DIR TO plsql_user; A TUSC Presentation 76

Expanded Oracle Supplied Packages • UTL_FILE Package Enhancements in R 2 – 4 New Procedures Added • Delete an Operating System File PROCEDURE FREMOVE Argument Name -------------LOCATION FILENAME Type -----------VARCHAR 2 In/Out Default? -------IN IN • Rename/Move an Operating System File PROCEDURE FRENAME Argument Name -------------SRC_LOCATION SRC_FILENAME DEST_LOCATION DEST_FILENAME OVERWRITE Type -----------VARCHAR 2 BOOLEAN A TUSC Presentation In/Out Default? -------IN IN IN DEFAULT 77

Expanded Oracle Supplied Packages • UTL_FILE Package Enhancements in R 2 – 4 New Procedures Added • Copy a Complete or Partial Operating System File PROCEDURE FCOPY Argument Name -------------SRC_LOCATION SRC_FILENAME DEST_LOCATION DEST_FILENAME START_LINE END_LINE Type -----------VARCHAR 2 BINARY_INTEGER In/Out -----IN IN IN Default? ---- DEFAULT • Retrieve Attribute Information About an Operating System PROCEDURE File FGETATTR Argument Name -------------LOCATION FILENAME FEXISTS FILE_LENGTH BLOCK_SIZE Type -----------VARCHAR 2 BOOLEAN NUMBER A TUSC Presentation BINARY_INTEGER In/Out Default? -------IN IN OUT 78 OUT

Additional Oracle 9 i New Features A TUSC Presentation 79

Additional Oracle 9 i Features · Resumable Space Allocation (suspend long running processes) · Segment Space Management (auto versus manual) · Multiple Block Sizes (tablespace dependent and separate cache for each) · Dynamic SGA Sizing (ability to modify the SGA with init. ora parameters) · Managing UNDO Space (rollback segment management by Oracle) A TUSC Presentation 80

Additional Oracle 9 i Features · Drop the OS Data Files Automatically (when dropping a tablespace) · EXTERNAL TABLES (reference flat file data within Oracle) · SERVER PARAMETER FILES (modify system parameters via ALTER SYSTEM and kept on shutdown/startup) · MONITORING INDEX USAGE (determine index usage) · i. SQL*Plus (browser based SQL*Plus interface) · New Hints 81 A TUSC Presentation

Additional Oracle 9 i Features · Java Enhancements · Oracle Enterprise Manager (OEM) Enhancements · Plan Stability · Automate Statistic Gathering (DBMS_STATS. GATHER*) · Automate SQL Execution Memory Management · Standby Databases Improvement · Version 1. 2 of IFS with Oracle 9 i · V$SQL_PLAN Enhanced (actual plan versus 82 theoretical plan executed) A TUSC Presentation

Additional Oracle 9 i Features · New Join Syntax · True Real Application Clusters (RACs - replaces Oracle Parallel Server for real scalability by adding nodes and making it transparent to the environment (add nodes and disk when desired to improve performance/failover)) · Security Improvements · Log. Miner Improvements (graphical user interface and more automated) · Skip Scan Index (ability to skip the first portion of an index if not referenced) A TUSC Presentation 83

Additional Oracle 9 i Features · Flashback Queries · Better Integration and More Flexibility on LOBs · Increased Support for XML · Enhanced Bulk Operation Support · List Partitioning · Modifications in Storing Execution Plans · cursor_sharing Enhancements (similar) A TUSC Presentation 84

Presentation Coverage · · · · Oracle PL/SQL Version History Oracle 9 i PL/SQL Compiler Limits & Internal Improvements New Data Types New Built-In Functions New SQL Commands New Native PL/SQL Compilation Expanded Oracle Supplied Packages Additional Oracle 9 i Features A TUSC Presentation 85

For More Information • The V$ Views for Oracle 9 i Poster This poster displays the V$ views of Oracle 9 i categorized by major function. The V$ (fixed) views are a supplement of views to the data dictionary and contain valuable real-time information that is stored in the SGA. • Oracle PL/SQL Tips & Techniques Expand your PL/SQL knowledge to the next level by using hundreds of PL/SQL tips and techniques provided in this one-of-a-kind reference, designed for every Oracle developer and database administrator. This book highlights many solutions that are either hidden, undocumented, or can only be discovered through many years of experience or through laborious trial and error. (Released: September, 1999; 942 pages) A TUSC Presentation 86

The Power of Attitude Our lives are not determined by what happens to us, but by how we respond to what happens; not by what life brings us, but by attitude we bring to life. A positive attitude causes a chain reaction of positive thoughts, events, and outcomes. It is a catalyst…a spark that creates extraordinary results. A TUSC Presentation 87

Summary • PL/SQL Has Grown Tremendously with Oracle 9 i and Oracle 9 i R 2 • This Growth Includes a Wide Range of Both DBA and Developer Enhancements • Learn These New Features and Make Sure You Thoroughly Understand the New Features Prior to Deploying in Your Environment • Expand Your Arsenal and Utilize These New Features that Oracle Has Provided A TUSC Presentation 88

Thank You for Coming Joe Trezzo Contact Information Phone: 630 -960 -2909 Email: trezzoj@tusc. com This presentation will be available on the TUSC Web Site www. tusc. com A TUSC Presentation

References • • • • • Oracle PL/SQL Tips & Techniques (Oracle Press), Joseph C. Trezzo Oracle 9 I Instant PL/SQL Scripts (Oracle Press), Kevin Loney Oracle 9 I DBA Handbook (Oracle Press), Kevin Loney Oracle 9 i The Complete Reference (Oracle Press), Kevin Loney Oracle 9 i New Features (Oracle Press), Robert Freeman PL/SQL User's Guide and Reference(Release 9. 0. 1 & 9. 2. 0), Oracle Corporation Supplied PL/SQL Packages and Types Reference (Release 9. 0. 1 & 9. 2. 0), Oracle Corp. Application Developer’s Guide - Fundamentals (Release 9. 0. 1 & 9. 2. 0), Oracle Corp. Oracle 9 i Database New Features (Release 9. 0. 1 & 9. 2. 0), Oracle Corporation Oracle 9 i Database Administrators Guide (Release 9. 0. 1 & 9. 2. 0), Oracle Corporation Oracle 9 I SQL Reference (Release 9. 0. 1 & 9. 2. 0), Oracle Corporation Concepts (Release 9. 0. 1 & 9. 2. 0), Oracle Corporation $ORACLE_HOME/rdbms/doc/README_rdbms. htm www. tusc. com All companies and product names are trademarks or registered trademarks of the respective owners. Please report errors in this article to TUSC. Neither TUSC nor the author warrant that this document is error-free. Please provide comments/questions to trezzoj@tusc. com. TUSC copyright 2003. This document cannot be reproduced without expressed written consent from an officer of TUSC A TUSC Presentation 90