Oracle Openworld Directory Index

New Oracle 9i & 8i Features Every i Developer Should Know

Agenda Topics
Internal PL/SQL Enhancements
Case Expression & Case Statement
New Internal Functions
New Data Types
Native Compilation of PL/SQL Code
Merge Statement
Autonomous Transactions
Triggers on System Events
Native Dynamic SQL
Global Temporary Tables
Bulk Binds with Collections
Internal PL/SQL Enhancements
Integration of SQL and PL/SQL Parsers
Prior to Oracle9i
Separate SQL Parser for SQL Engine and PL/SQL Engine
Oracle9i
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%+)
Cross Package References Handled More Efficiently
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
Case Expression
Selector Option
Case Expression
Search Option
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
Case Statement
Selector Option
Case Statement
Search Option
New Built-In Functions
New NULL Type Functions
NULLIF
If Values Match, Then the Result is NULL
COALESCE
If the List is NULL, Then the Result is the Last Value in the List
SQL and PL/SQL Functions
New Built-In Functions
New NULL Type Functions
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.
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
Oracle9i SQL Reference
New Data Types
New Date/Time Data Types
Native Compilation of PL/SQL Code
Prior to Oracle9i
PL/SQL Code Stored in the Database
Upon Creation, Compiled into Byte Code (p-code)
Upon Execution, Loaded into Memory and Interpreted
Oracle9i 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
Native Compilation of PL/SQL Code
Steps to Use the Oracle9i Option
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
Native Compilation of PL/SQL Code
Native Compilation of PL/SQL Code
Native Compilation of PL/SQL Code
Native Compilation of PL/SQL Code
Merge Statement
Insert or Update Operation in One Statement
If record exists, update the record
If record does not exist, insert the record
Example
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
Merge Statement
Merge Statement
Example
Prior to Oracle9i
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
Oracle9i
Superceded with the Merge Statement
Merge Statement
Merge Statement
Expanded Oracle Supplied Packages
DBMS_FLASHBACK
DBMS_LOGMNR_CDC_PUBLISH
DBMS_LOGMNR_CDC_SUBSCRIBE
DBMS_METADATA
DBMS_OUTLN_EDIT
DBMS_REDEFINITION
DBMS_TRANSFORM
Expanded Oracle Supplied Packages
DBMS_URL
DBMS_XMLGEN
DBMS_XMLQuery
DMBS_XMLSave
UTL_ENCODE
Additional Oracle9i Features
Better Integration and More Flexibility on LOBs
Increased Support for XML
Enhanced Bulk Operation Support
New Data Types
PL/SQL Object Inheritance
New Built-In SQL Functions
i SQL*Plus
Modifications in Storing Execution Plans
cursor_sharing Enhancements (similar)
Autonomous Transactions
An autonomous transaction is an independent transaction started by another transaction, the main transaction.
Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.
Advantages
Once started, an autonomous transaction is fully independent.
Autonomous transactions help you build modular, reusable software components.
Autonomous transactions have all the functionality of regular transactions.
Defining
To define autonomous transactions, you use the PRAGMA (compiler directive) AUTONOMOUS_TRANSACTION
The PRAGMA instructs the PL/SQL compiler to mark a routine as autonomous (independent).
Defining
You can code the PRAGMA anywhere in the declarative section of a routine (procedure, function, or trigger).
But, for readability, code the PRAGMA at the top of the section.
Syntax:
Basic Example
Restrictions
You cannot use the PRAGMA to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous.
You cannot mark a nested PL/SQL block as autonomous.
Procedure Example
Procedure Example (cont’d)
Trigger Example
Additional Information
Unlike regular triggers, autonomous triggers can commit and perform DDL
Autonomous functions never violate the RNDS or WNDS purity levels
If you attempt to leave an active autonomous transaction without committing or rolling back, Oracle raises an exception
Triggers on System Events
System events, like LOGON and SHUTDOWN, provide a mechanism for tracking system changes.
With Oracle, this tracking can be combined with database event notification.
Database event notification provides a simple and elegant method of delivering asynchronous messaging to an application.
Resource Manager Events
Client Events
Client Events (continued)
Example
Native Dynamic SQL
Within PL/SQL, you can execute any kind of SQL or PL/SQL statement (even data definition and data control statements) without resorting to cumbersome programmatic approaches.
Dynamic SQL blends seamlessly into your programs, making them more efficient, readable, and concise.
Similar to DBMS_SQL, but easier to use.
Why Use Dynamic SQL?
You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION).
Generally, in PL/SQL, such statements cannot be executed in-line, without previously using Oracle’s DBMS_SQL package.
Why Use Dynamic SQL?
Plain and simple…You want more flexibility!
For example, you might want to defer your choice of schema objects until run time. Or, you might want your program to build different search conditions for the WHERE clause of a SELECT statement. Or even possibly dynamically changing the ORDER BY clause.
Execute Immediate
The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block.
Examples
Processing Multi-Row Queries
Use a Cursor Variable (REF_CURSOR)
Implement a “weak” or “unconstrained” cursor variable
OPEN FOR statement accepts a character string for the query and can include a USING clause
As a rule of thumb, use a basic LOOP structure, with: EXIT WHEN cursor_variable_name%NOTFOUND
Multi-Row Queries Example
Using User’s Rights
Implement AUTHID at the Block Level
By default, PL/SQL is executed with definer’s rights
AUTHID CURRENT_USER can be specified in the block header to use the privileges of the invoker
Very important when using Native Dynamic SQL to perform DDL, such as creating or dropping a table
Syntax:
Advantages
Easy to use
Performance Improvements
Support for User-Defined Types
Support for Fetching into Records
Support for Client-Side Programs
Support for Bulk Dynamic SQL
Support for SQL statements larger than 32K
Temporary Tables
In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.
Creating Temporary Tables
The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific.
To utilize a GLOBAL TEMPORARY TABLE, you would need to be granted access to it
Data Lifespan
Transaction Specific Data
Exists for the duration of the transaction
Session Specific Data
Exists for the duration of the session
In general, data in a temporary table is always private to the session and each session can only see and modify its own data.
Different Syntax
To Delete Rows on Commit:

To Preserve Rows for Session:

Additional Information
DML statements on temporary tables do not generate redo logs for the data changes.
However, undo logs for the data and redo logs for the undo logs are generated.
Bulk Binds
New in Oracle8i, can significantly improve performance of “bulk” SQL statements
Used in conjunction with collections
Instead of “binding” each piece of a collection, an entire collection is passed back and forth to the SQL engine
Minimizes number of context switches between PL/SQL and SQL engines
FORALL Statement
Used to bulk bind input collections, in conjunction with DML statements
Iterates through collection, performing DML
Even though syntax is similar, it is not a FOR loop.
Syntax:
Basic Example
%BULK_ROWCOUNT(n)
Similar to scalar attributes of cursors (%ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT)
Refers to an Index-By table, storing the number of rows processed by the nth execution of the DML statement within the FORALL construct
Uses same subscripts as FORALL
Not maintained for Inserts
Example
BULK COLLECT Clause
Used to bulk bind output collections within SQL engine, before returning to PL/SQL engine
SQL engine initializes and extends collections automatically, starting with a subscript of 1, overwriting data if necessary
Syntax:
FORALL and BULK COLLECT
SQL engine incrementally bulk binds column values
With each iteration of the FORALL, column values returned by the BULK COLLECT are added to the output collection (current values in the collection are not overwritten)
Example
Summary
Internal PL/SQL Enhancements
Case Expressions
New Internal Functions
New Data Types
Native Compilation of PL/SQL Code
Merge Statement
Autonomous Transactions
Triggers on System Events
Native Dynamic SQL
Global Temporary Tables
Bulk Binds with Collections

Fill Out a Survey and Get a Chance to Win a Compaq iPAQ!
We want to know what you think! Fill out the survey that was handed out at the beginning of the session for a chance to win a Compaq iPAQ. Remember to include your name and email in the available section and we will enter your name into two daily drawings to win an iPAQ

Tony Catalano, Vice President
catalanoa@tusc.com
377 E. Butterfield Road
Suite 100
Lombard, IL 60148

630-960-2909
800-755-TUSC
www.tusc.com
Copyright Information
Neither TUSC or the author guarantee this document to be error-free. Please provide comments/questions to catalanoa@tusc.com.
TUSC © 2001. This document cannot be reproduced without expressed written consent from an officer of TUSC
For More Information
www.tusc.com
www.odtug.com
metalink.oracle.com
technet.oracle.com
Sources
Oracle9i Application Developer's Guide - Fundamentals, Oracle Corporation
Getting to Know Oracle9i, Oracle Corporation
Oracle9i Concepts, Oracle Corporation
Oracle9i SQL Reference, Oracle Corporation
Oracle9i Reference, Oracle Corporation

---------

examples

DECLARE
customer_rating VARCHAR2(10);
customer_credit VARCHAR2(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;
/

DECLARE
customer_rating VARCHAR2(10);
customer_credit VARCHAR2(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;
/

-------

DECLARE
x1 PLS_INTEGER := 1234;
x2 PLS_INTEGER := 1234;
x3 PLS_INTEGER;
x4 PLS_INTEGER;
x5 PLS_INTEGER;
x6 PLS_INTEGER;
BEGIN
x5 := NULLIF(x1, x2);
x6 := COALESCE(x3, x4, 4321);
DBMS_OUTPUT.PUT_LINE('x5: ' || x5);
DBMS_OUTPUT.PUT_LINE('x6: ' || x6);
END;
/

x5:
x6: 4321

PL/SQL procedure successfully completed.

------

DESCRIBE user_stored_settings
Name Null? Type
----------------------- -------- -----------------
OBJECT_NAME NOT NULL VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
OBJECT_TYPE VARCHAR2(12)
PARAM_NAME NOT NULL VARCHAR2(30)
PARAM_VALUE VARCHAR2(4000)

SELECT *
FROM user_stored_settings;

no rows selected

-------

CREATE PROCEDURE xyz AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello');
END;
/

Procedure created.

SET SERVEROUTPUT ON
EXECUTE xyz
Hello

PL/SQL procedure successfully completed.

--------

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

---------

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

---------

MERGE INTO employee_master dest
USING
(SELECT emp_id, start_date, salary, title
FROM adp_employee_master@adp) orig
ON (dest.emp_id = orig.emp_id)
WHEN MATCHED THEN UPDATE
SET dest.salary = orig.salary,
dest.title = orig.title
WHEN NOT MATCHED THEN
INSERT (dest.emp_id, dest.start_date, dest.salary, dest.title)
VALUES
(orig.emp_id, orig.start_date, orig.salary, orig.title);

--------

CREATE PACKAGE billing AS

FUNCTION generate_late_charges (client_id INTEGER)
RETURN REAL;

END billing;

CREATE PACKAGE BODY billing AS

FUNCTION generate_late_charges (client_id INTEGER)
RETURN REAL IS

PRAGMA AUTONOMOUS_TRANSACTION;

Client_charge REAL;
BEGIN
INSERT …
END;

END billing;

-----
PROCEDURE ilog_error_auto ( p_package_c IN varchar2 default 'UNKNOWN',
p_procedure_c IN varchar2 default 'UNKNOWN',
p_location_c IN varchar2 default 'UNKNOWN',
p_error_c IN varchar2 default 'UNKNOWN',
p_text_c IN varchar2 default 'NONE',
p_user_c IN varchar2 default NULL,
p_error_time IN date default NULL) is

-- Make the DML in this procedure autonomous to pending transactions

PRAGMA AUTONOMOUS_TRANSACTION;

v_error_stack_c varchar2(500);
v_global_vars_c varchar2(1000);
v_call_stack_c varchar2(500);

BEGIN
-- Capture the Call Stack.
v_call_stack_c := substr(dbms_utility.format_call_stack, 1, 500);
-- Capture the Error Stack.
v_error_stack_c := substr(dbms_utility.format_error_stack, 1, 500);

-- Capture Gloval Variables.
v_global_vars_c := substr(capture_globals, 1, 1000);

-- Insert system error
insert
into lss_system_errors (serror_id, package_name, procedure_name,
execution_location, oracle_error_text, additional_information,
global_variables, call_stack, error_stack,
insert_time,insert_user)
values (lss_serror_id_seq.nextval,
substr(p_package_c, 1, 50),
substr(p_procedure_c, 1, 50),
substr(p_location_c, 1, 20),
substr(p_error_c, 1, 200),
substr(p_text_c, 1, 4000),
substr(v_global_vars_c, 1, 4000),
substr(v_call_stack_c, 1, 2000),
substr(v_error_stack_c, 1, 2000),
nvl(p_error_time, sysdate),
substr(nvl(p_user_c, user), 1, 30));
commit;
END ilog_error_auto;

--------

CREATE OR REPLACE TRIGGER audit_inventory
AFTER UPDATE OR DELETE ON s_inventory
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO s_inventory_audit
( audited_user, audit_time, old_product_id, old_warehouse_id,
old_amount_in_stock, old_reorder_point, new_product_id,
new_warehouse_id, new_amount_in_stock, new_reorder_point)
VALUES ( USER, SYSDATE,
:OLD.product_id, :OLD.warehouse_id, :OLD.amount_in_stock,
:OLD.reorder_point, :NEW.product_id, :NEW.warehouse_id,
:NEW.amount_in_stock, :NEW.reorder_point );
COMMIT;
END;

------

create table source_history (change_dt date not null, owner varchar2(30) not null, name varchar2(30) not null, type varchar2(20), line number not null, text varchar2(4000));

create or replace trigger source_historyafter create on databasedeclare v_sysdate date := sysdate;begin insert into source_history (select v_sysdate, ds.owner, ds.name,
ds.type, ds.line, ds.text from dba_source ds where ora_dict_obj_owner = ds.owner and ora_dict_obj_name = ds.name and ora_dict_obj_type = ds.type);end source_history;