Transcript Best Practices for PL/SQL

All non-technical views expressed are those of Steven Feuerstein and do
not necessarily (and not likely!) reflect those of Quest Software.
The Brave New World
of Oracle PL/SQL
"Gotta Know, Gotta Use" New Features
of Oracle8i and Oracle9i PL/SQL
Steven Feuerstein
[email protected]
Copyright 2000-2002 Steven Feuerstein - Page 1
The New Topography of a
Powerful Language
 New/enhanced data structures and data
types
– Collections, object types, TIMESTAMP, INTERVAL,
XMLType
 Enhancements to the SQL-PL/SQL
interface
– Native Dynamic SQL, bulk processing, record-based DML
 Miscellaneous - but not leftovers
– Java from PL/SQL, UTL_FILE enhancements, native
compilation, CASE
Copyright 2000-2002 Steven Feuerstein - Page 2
Software Used in Training
 Download training materials and demonstration
scripts from the Quest Pipelines:
– www.quest-pipelines.com
– Click on "Quest Experts Page" and then click on the presentation
of interest.
– Or visit http://www.stevenfeuerstein.com, then "PL/SQL", then
"Downloads"
 A PL/SQL Integrated Development Environment
– You no longer have to use SQL*Plus and a crude editor!
plsql_ides.txt
Copyright 2000-2002 Steven Feuerstein - Page 3
Oh, Those Data Structures!
 Collections grow ever more powerful
 Object types finally start to remotely
resemble classes
 More intelligent date-time-interval
handling
 Native XML datatype and operations
Copyright 2000-2002 Steven Feuerstein - Page 4
Working with Collections
 Collections are single-dimensioned lists of
information.
 Three types of collections:
– Associative arrays (previously known as "index-by tables" and
"PL/SQL tables") - available in PL/SQL only
– Nested tables - can be defined in PL/SQL and SQL
– Variable arrays - can be defined in PL/SQL and SQL
 They are an invaluable data structure; all PL/SQL
developers should be familiar with them.
– This seminar touches on highlights and focuses on new features.
Copyright 2000-2002 Steven Feuerstein - Page 5
Why Use Collections?
 Maintain any kind of list of related information for use
in your programs.
 Emulate bi-directional cursors, which are not yet
supported in PL/SQL
 Cache data in program memory for faster access.
 Build hash tables (custom indexing structures).
 Improve query performance by avoiding joins.
 Avoid mutating table errors in database triggers.
Copyright 2000-2002 Steven Feuerstein - Page 6
Overview of Collections




Defining collection types and collections
Navigating collection contents
Manipulating collections inside SQL
New features in Oracle9i
– Multi-level collections
– Indexing by strings
Copyright 2000-2002 Steven Feuerstein - Page 7
Defining Collections
 First, you define the TYPE of the collection.
– For associative arrays, this can only occur in a PL/SQL declaration
section. Best option: package specification.
– For nested tables and VARRAYs, you can define the TYPE in the
database with a CREATE statement, or in a PL/SQL declaration section.
 Then you declare one or more instances, actual
collections, from the TYPE..
CREATE OR REPLACE PACKAGE coll_types
IS
TYPE integer_aat IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
TYPE integer_nt IS TABLE OF INTEGER;
TYPE integer_vat IS VARRAY(10) OF INTEGER;
...
END coll_types;
Copyright 2000-2002 Steven Feuerstein - Page 8
Initializing Collections
 Before you can use a collection, it must be initialized.
– Nested tables and VARRAYs are atomically null. You must initialize them
explicitly with a constructor. Associative arrays are initialized automatically.
DECLARE
TYPE numbers_t IS VARRAY (10) OF NUMBER;
salaries numbers_t := numbers_t (100, 200, 300);
BEGIN
TYPE defined in
PL/SQL
CREATE TYPE numbers_t IS VARRAY (10) OF NUMBER;
/
DECLARE -- Initialize the collection.
salaries numbers_t := numbers_t (100, 200, 300);
BEGIN
TYPE defined in
the database
CREATE TABLE employee_denorm (
employee_id INTEGER,
salary_history numbers_t);
Collection used
in a table
Copyright 2000-2002 Steven Feuerstein - Page 9
Collection Methods
 Obtain information about the collection
–
–
–
–
COUNT returns number of rows currently defined in the table.
EXISTS returns TRUE if the specified row is defined.
FIRST/LAST return lowest/highest numbers of defined rows.
NEXT/PRIOR return the closest defined row after/before the specified
row.
– LIMIT tells you the max. number of elements allowed in a VARRAY.
 Modify the contents of the collection
– DELETE deletes one or more rows from the index-by table.
– EXTEND adds rows to a nested table or VARRAY.
– TRIM removes rows from a VARRAY.
Copyright 2000-2002 Steven Feuerstein - Page 10
The DELETE Method
 You can delete one or more rows from a
collection using DELETE:
BEGIN
-- Delete all rows
myCollection.DELETE;
-- Delete one (the last) row
myCollection.DELETE (myCollection.LAST);
-- Delete a range of rows
myCollection.DELETE (1400, 17255);
END;
DELETE releases memory, but you may also want to call
DBMS_SESSION.FREE_UNUSED_USER_MEMORY.
Copyright 2000-2002 Steven Feuerstein - Page 11
Navigating Through Collections
 Use FIRST and NEXT to move from beginning to end.
 Use LAST and PRIOR to move from end to beginning.
rowind PLS_INTEGER := birthdays.FIRST;
-- REVERSE: birthdays.LAST
BEGIN
LOOP
EXIT WHEN rowind IS NULL;
DBMS_OUTPUT.PUT_LINE (birthdays(rowind).best_present);
rowind := birthdays.NEXT (rowind);
-- REVERSE: birthdays.PRIOR
END LOOP;
END;
plsqlloops.sp
Copyright 2000-2002 Steven Feuerstein - Page 12
Which Collection Type
Should I Use?
 Associative Arrays
– Easiest to work with, lowest overhead. Can be used from
Oracle7 onwards, can be sparse, meaning that the row
number can be used as an "intelligent keys".
 Nested tables
emplu.*
– Store large amounts of persistent data in the column of a
table
 Varying Arrays
– Store small amounts of persistent data in the column of a
table, preserving the order in which elements are stored.
Copyright 2000-2002 Steven Feuerstein - Page 13
Oracle9i
Multi-level Collections
 Oracle9i allows you to create collections
of collections, or collections of records
that contain collections, or...
 Applies to all three types of collections.
 Two scenarios to be aware of:
– Named collection columns
– Anonymous collection columns
Copyright 2000-2002 Steven Feuerstein - Page 14
Collections with Named,
Multi-level Collections
 When a collection is based on a record or
object that in turn contains a collection, that
collection has a name.
CREATE TYPE vet_visit_t IS OBJECT (
visit_date DATE,
reason
VARCHAR2 (100));
/
CREATE TYPE vet_visits_t IS TABLE OF vet_visit_t
/
CREATE TYPE pet_t IS OBJECT (
Collection nested inside
tag_no INTEGER,
object type
NAME VARCHAR2 (60),
petcare vet_visits_t,
MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
RETURN pet_t);
/
Copyright 2000-2002 Steven Feuerstein - Page 15
multilevel_collections.sql
Continued...
Collections with Named, Multi-level
Collections, continued
DECLARE
TYPE bunch_of_pets_t IS TABLE
my_pets
bunch_of_pets_t;
BEGIN
my_pets (1) :=
pet_t (
100, 'Mercury',
vet_visits_t (
vet_visit_t (
'01-Jan-2001',
vet_visit_t (
'01-Apr-2002',
)
);
DBMS_OUTPUT.put_line (my_pets
END;
Copyright 2000-2002 Steven Feuerstein - Page 16
OF pet_t INDEX BY BINARY_INTEGER;
Outer collection
Inner collection
'Clip wings'),
'Check cholesterol')
(1).petcare (2).reason);
Anonymous Collection Columns
CREATE OR REPLACE PROCEDURE set_steven_nicknames
IS
steven_nicknames
nicknames.nickname_set_t;
universal_nicknames
nicknames.multiple_sets_t;
BEGIN
-- Without use of named constant:
steven_nicknames (99) (1000) := 'Steve';
steven_nicknames (99) (2000) := 'Troublemaker';
universal_nicknames (nicknames.french) :=
nicknames.to_french (steven_nicknames);
-- Triple-nested reference to display "Provocateur"
DBMS_OUTPUT.PUT_LINE (
universal_nicknames(1005)(111)(2000));
multilevel_collections2.sql
END;
multdim.*
gen_multcoll.sp
/
 If a collection's column has no name, you simply
string
together
index
subscripts.
Copyright 2000-2002 Steven Feuerstein - Page 17
Oracle9i Release 2
New Indexing Capabilities
 You can now define the index on your
associative array to be:
– Any sub-type derived from BINARY_INTEGER
– VARCHAR2(n), where n is between 1 and 32767
– %TYPE against a database column that is consistent with
the above rules
 This means that you can now index on string
values!
Copyright 2000-2002 Steven Feuerstein - Page 18
Examples of New
TYPE Variants
 All of the following are now valid TYPE declarations
in Oracle9i Release 2
– You cannot use %TYPE against an INTEGER column, because
INTEGER is not a subtype of BINARY_INTEGER.
DECLARE
TYPE
TYPE
TYPE
TYPE
TYPE
TYPE
TYPE
array_t1
array_t2
array_t3
array_t4
array_t1
array_t3
array_t4
IS
IS
IS
IS
IS
IS
IS
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
Copyright 2000-2002 Steven Feuerstein - Page 19
OF
OF
OF
OF
OF
OF
OF
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
INDEX BY BINARY_INTEGER;
INDEX BY PLS_INTEGER;
INDEX BY POSITIVE;
INDEX BY NATURAL;
INDEX BY VARCHAR2(64);
INDEX BY VARCHAR2(32767);
INDEX BY
employee.last_name%TYPE;
Working with VARCHAR2Indexed Collections
DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;
BEGIN
country_population('Greenland') := 100000;
country_population('Iceland') := 750000;
howmany := country_population('Greenland');
assoc_array*.sql
continent_population('Australia') := 30000000;
END;
 Specifying a row via a string takes some getting
used to, but if offers some very powerful advantages.
Copyright 2000-2002 Steven Feuerstein - Page 20
Rapid Access to Data
Via String Keys
 One of the most powerful applications of this
features is to construct very fast pathways to
static data from within PL/SQL programs.
– If you are repeatedly querying the same data from the
database, why not cache it in your PGA inside collections?
– And use the various indexing mechanisms to provide
different ways to get at that data!
vocab*.*
assoc_array5.sql
genaa.sql
Copyright 2000-2002 Steven Feuerstein - Page 21
The power of multi-level
and string-based indexes
 Careful application of this functionality can result in
simpler code that reflects the business model very
closely.
FOR rec IN (SELECT * FROM all_arguments)
LOOP
l_arguments (NVL (l_arguments.LAST, 0) + 1)
:= rec;
l_programs
(rec.object_name)
(NVL (rec.overload, 0))
(rec.position)
(rec.data_level) := rec;
END LOOP;
multilevel.sql
Copyright 2000-2002 Steven Feuerstein - Page 22
Typical sequential
load into collection
of records
Multi-level load in
single assignment
The Wacky and Wonderful
World of Oracle Object Types
A New Frontier:
Object Types and
Object-oriented Development
in Pl/SQL
Copyright 2000-2002 Steven Feuerstein - Page 23
Which best describes your relationship with
Object Types?
 I love 'em and use 'em all the time.
 They scare me. I'll stick with good, oldfashioned relational tables.
 I am comfortable with defining and using
object types, but not in production.
 We use object types and have
incorporated them into our production
applications.
Copyright 2000-2002 Steven Feuerstein - Page 24
Object Types in Oracle
 Object types were first introduced into the
Oracle8 RDBMS (the "object-relational" model).
– Oracle uses object types in many of its new features (e.g.,
Oracle AQ, the XML datatype).
– Few development shops work with object types.
 The implementation is weak.
– Not really object oriented.
– Advantages are not persuasive to developers and DBAs with
relational and procedural backgrounds.
– Oracle9i support for inheritance may well change this situation..
Copyright 2000-2002 Steven Feuerstein - Page 25
An Object Type Example
 The timer
object
calculates
elapsed time.
 It consists of
four attributes
and five
methods.
CREATE TYPE tmr_t AS OBJECT (
startTime INTEGER,
endTime INTEGER,
repetitions INTEGER,
name VARCHAR2(2000),
MEMBER PROCEDURE go ,
MEMBER PROCEDURE stop (
show_timing IN BOOLEAN := TRUE),
MEMBER FUNCTION timing RETURN INTEGER,
MEMBER PROCEDURE reset (
name IN VARCHAR2 := NULL),
Methods
STATIC FUNCTION make (
name IN VARCHAR2,
repetitions IN INTEGER := 1)
RETURN tmr_t
);
tmr.ot
Copyright 2000-2002 Steven Feuerstein - Page 26
Attributes
Using the Timer Object
Declare multiple object
types instances
Use default and pseudoconstructors functions
to initialize the
instances.
Invoke object type
methods using dot
notation.
thisuser.tst
Copyright 2000-2002 Steven Feuerstein - Page 27
DECLARE
v VARCHAR2(30);
func_tmr tmr_t :=
tmr_t (NULL, NULL, 'Function', 1000);
const_tmr tmr_t :=
tmr_t.make ('Constant', 1000);
BEGIN
func_tmr.go();
FOR indx IN 1 .. &&1
LOOP
v := thisuser.name;
END LOOP;
func_tmr.stop();
Oracle9i
Support for inheritance in
object types
 You can now define a hierarchy of subtypes of
object types.
 A subtype contains all the attributes and methods
of the parent type (or supertype).
 The subtypes can also contain additional attributes
and additional methods, and can override methods
from the supertype.
 You decide if an object type is INSTANTIABLE or is
FINAL (cannot be extended to a subtype).
Copyright 2000-2002 Steven Feuerstein - Page 28
Oracle9i
Type Hierarchy Example
supertype/"wider"
subtype/"narrower"
Citizen
Person
Employee
Hourly Worker
Corporation
Salaried
Worker
"root" type
person.ot
Management
NonManagement
An employee is one sort of person. An hourly worker is one
sort of employee. An employee is always a person, but a
person may not be an employee.
Copyright 2000-2002 Steven Feuerstein - Page 29
Why Bother with Hierarchy?
 Define shared functionality once at the
"widest" level and it is automatically available
at all narrower points in the hierarchy.
– A very powerful approach to code reuse.
 Easily customize or override functionality for
specific subtypes.
– You get the best of both worlds: rely on the supertypestandard, and selectively over-ride that functionality as
needed.
Copyright 2000-2002 Steven Feuerstein - Page 30
Let's Build a Type Hierarchy
Oracle9i
"root",
supertype
of dessert
food
subtype of
food,
supertype
of cake
dessert
subtype
of dessert
cake
 We have a three level
hierarchy:
– food is the root type.
– desserts are a type of food
– cakes are a type of dessert.
 We will make cake the most
specialized type of food
allowed in the hierarchy.
food.ot
Copyright 2000-2002 Steven Feuerstein - Page 31
Creating a Simple
Object Type Hierarchy
CREATE TYPE food_t AS OBJECT (
name VARCHAR2(100),
food_group VARCHAR2 (100),
grown_in
VARCHAR2 (100))
NOT FINAL;
CREATE TYPE dessert_t UNDER food_t (
contains_chocolate
CHAR(1),
year_created
NUMBER(4))
NOT FINAL;
CREATE TYPE cake_t UNDER dessert_t (
diameter
NUMBER,
inscription
VARCHAR2(200));
 NOT FINAL
indicates that
this type can be
a supertype.
 UNDER
denotes that
this type is a
subtype.
food.ot
An object instantiated from food_t has three attributes. A dessert
object has five attributes. A cake has seven.
Copyright 2000-2002 Steven Feuerstein - Page 32
Substitutability of
Object Types
"Any object of type cake is also a dessert,
is also a food."
 A supertype is substitutable if one of its
subtypes can substitute or stand in for it in a
slot (a variable, column, etc.) whose
declared type is the supertype.
 Oracle supports object type substitution in
columns of relational tables, attributes of
object types and elements in collections.
Copyright 2000-2002 Steven Feuerstein - Page 33
Populate an Object Table
 Create a table of objects of type food (root type).
CREATE TABLE sustenance OF food_t;
 Populate it with objects at different levels in hierarchy.
Use of constructor to
DECLARE
initialize a variable
my_favorite_vegetables food_t :=
food_t ('Brussel Sprouts', 'VEGETABLE', 'farm' );
BEGIN
INSERT INTO sustenance VALUES (my_favorite_vegetables);
INSERT INTO sustenance
VALUES (dessert_t ('Jello', 'PROTEIN', 'bowl', 'N', 1887 ) );
INSERT INTO sustenance
Substitution of subtypes
VALUES (cake_t (
'Marzepan Delight', 'CARBOHYDRATE', 'bakery',
'N', 1634, 8, 'Happy Birthday!' ) );
food.ot
END;
Copyright 2000-2002 Steven Feuerstein - Page 34
Objects in a Collection
 Create a table of objects of type food (root type).
DECLARE
TYPE foodstuffs_nt IS TABLE OF food_t;
Declare a nested
table
fridge_contents
foodstuffs_nt
:= foodstuffs_nt (
food_t ('Eggs benedict', 'PROTEIN', 'Farm'),
dessert_t ('Strawberries and cream', 'FRUIT',
'Backyard', 'N', 2001),
cake_t (
'Chocolate Supreme', 'CARBOHYDATE', 'Kitchen',
'Y', 2001, 8, 'Happy Birthday, Veva'
)
Insert three different
);
objects in the collection,
BEGIN
each of a different type.
...
Copyright 2000-2002 Steven Feuerstein - Page 35
Accessing Attributes in
Substituted Types
 You can substitute a subtype in a supertype column
or attribute, but subtype-specific attributes and
methods are by default not visible.
SQL> DECLARE
4
mmm_good food_t :=
5
dessert_t ('Super Brownie', 'CARBOHYDRATE',
6
'my oven', 'Y', 1994);
7 BEGIN
8
DBMS_OUTPUT.PUT_LINE (mmm_good.contains_chocolate);
9 END;
10 /
DBMS_OUTPUT.PUT_LINE (mmm_good.contains_chocolate);
*
ERROR at line 8:
PLS-00302: component 'CONTAINS_CHOCOLATE' must be declared
Copyright 2000-2002 Steven Feuerstein - Page 36
Use TREAT to Identify
Constrained Types
/* Show all the meals in which a main course is a dessert */
SELECT *
FROM meal
WHERE TREAT (main_course AS dessert_t) IS NOT NULL;
/* Will fail, since main_course is of food_t type */
SELECT main_course.contains_chocolate
FROM meal
WHERE TREAT (main_course AS dessert_t) IS NOT NULL;
/* Now works, since I am treating main_course as a dessert */
SELECT TREAT (main_course AS dessert_t).contains_chocolate
FROM meal
WHERE TREAT (main_course AS dessert_t) IS NOT NULL;
/* Set to NULL any desserts that are not cakes... */
UPDATE meal
SET dessert = TREAT (dessert AS cake_t);
Copyright 2000-2002 Steven Feuerstein - Page 37
treat.sql
Creating and Overriding Methods
 Most real-world object types will have both
attributes and methods, programs that
perform operations on attributes.
 With inheritance, you can:
– inherit supertype methods
– override or replace supertype methods with subtype
implementations
– add completely new methods
Copyright 2000-2002 Steven Feuerstein - Page 38
Overriding to Provide
Specificity for Subtypes
 Two different
calculations for
desserts and
cakes.
CREATE OR REPLACE TYPE BODY dessert_t
IS
OVERRIDING MEMBER
FUNCTION price RETURN NUMBER IS
mult NUMBER := 1;
BEGIN
IF SELF.contains_chocolate = 'Y'
THEN mult := 2; END IF;
IF SELF.year_created < 1900
THEN mult := mult + 0.5; END IF;
RETURN (10.00 * mult );
END;
END;
CREATE OR REPLACE TYPE BODY cake_t
IS
OVERRIDING MEMBER FUNCTION price RETURN NUMBER
IS
BEGIN
RETURN (
5.00
+ 0.25 * (LENGTH (SELF.inscription))
+ 0.50 * diameter);
END;
END;
Copyright 2000-2002 Steven Feuerstein - Page 39
food2.ot
Generic dessert prices are
determined by chocolate
content and age. Cake prices
are driven by inscription
length and size..
Quiz: What is "Dynamic Polymorphism"?
 A new kind of diet regimen.
 A form of sex therapy practiced in
Luxembourg.
 A computer language's ability to anticipate
the requirements of a system and generate
matching code.
 A computer language's ability to choose at
run-time among different forms of the same
program.
Copyright 2000-2002 Steven Feuerstein - Page 40
About Polymorphism
 The ability to choose from multiple methods of the
same name and execute the appropriate method.
– Static polymorphism: the decision about which method to execute is
made at the time the code is compiled. Static polymorphism is also known
as overloading, and is supported in declaration sections of PL/SQL
blocks.
– Dynamic polymorphism: the decision about which method to execute is
made at the time the code is executed, at run-time. This is also known as
"dynamic method dispatch", and is available for the first time in PL/SQL
with support for object type inheritance.
Copyright 2000-2002 Steven Feuerstein - Page 41
Exploring Dynamic Polymorphism
 The food and
dessert types
each have a
price method,
but cake does
not. It simply
inherits the
dessert
method.
Copyright 2000-2002 Steven Feuerstein - Page 42
CREATE TYPE food_t AS OBJECT (
...attributes...
MEMBER FUNCTION price RETURN NUMBER
) NOT FINAL;
CREATE TYPE dessert_t UNDER food_t (
...attributes...
OVERRIDING MEMBER FUNCTION price
RETURN NUMBER
) NOT FINAL)
;
CREATE TYPE cake_t UNDER dessert_t (
...attributes...
-- No price method of its own.
);
A Visual Representation
food
Price
the "original"
dessert
Price
An override
cake
Inherited
calculation
Copyright 2000-2002 Steven Feuerstein - Page 43
 The root price
function is overridden in the
dessert subtype.
 The cake
subtype now
simply inherits its
price calculation
from its dessert
supertype.
Dynamically Choosing
the Right Method
DECLARE
TYPE foodstuffs_nt IS TABLE OF food_t;
A collection of foods is
populated with three
different object types.
fridge foodstuffs_nt
:= foodstuffs_nt (
food_t ('Eggs benedict', ...),
dessert_t ('Strawberries and cream', ...),
cake_t ('Chocolate Supreme', ...));
BEGIN
food3.ot
FOR indx IN
fridge.FIRST ..
fridge.LAST
LOOP
DBMS_OUTPUT.put_line (
'Price of ' || fridge (indx).NAME ||
' = ' ||
The price invocation is
fridge (indx).price);
resolved at run-time, and
END LOOP;
not necessarily as the
END;
food_t.price method.
Copyright 2000-2002 Steven Feuerstein - Page 44
Object Types Summary
 They are finally becoming robust enough to
be useful
 Object types are being used extensively by
Oracle itself.
– This fact makes more confident of the future, performance
and capabilities of object types.
 Get familiar with the syntax so that you can
work with object types with confidence.
Copyright 2000-2002 Steven Feuerstein - Page 45
New and Enhanced
Datatypes in Oracle9i
 TIMESTAMP and INTERVAL
 XMLType
 The ANY* "generic" types
Copyright 2000-2002 Steven Feuerstein - Page 46
Timestamps & Intervals
 TIMESTAMP
– Extends the DATE datatype, offering a much higher (and
variable) precision of seconds.
 INTERVAL
– Store and manipulate intervals of years and months.
– DAY TO SECOND: represent the precise difference
between two datetime values.
– YEAR TO MONTH: calculate the difference between two
datetime values, where the only significant portions are the
year and month.
Copyright 2000-2002 Steven Feuerstein - Page 47
Timestamp Precision
DECLARE
checkout TIMESTAMP(3);
BEGIN
checkout := '1999-06-22 07:48:53.275';
...
END;
 When you declare a TIMESTAMP, you
provide a precision (from 0 to 9) for the
seconds component of the value.
 Use TIMESTAMP WITH TIME ZONE to
handle time zone displacement.
Copyright 2000-2002 Steven Feuerstein - Page 48
Working with Time Zones
DECLARE
logoff TIMESTAMP(3) WITH TIME ZONE;
logon TIMESTAMP(3) WITH LOCAL TIME ZONE;
BEGIN
logoff := '1999-10-31 09:42:37.114 +02:00';
...
END;
 Automatically work with the local time zone or specify
a specific displacement.
– Useful when gathering data that crosses time zones.
 The TIMEZONE_REGION and TIMEZONE_ABBR
columns of the V$TIMEZONE_NAMES data
dictionary view provide the names of available time
zones.
tzset.sql
Copyright 2000-2002 Steven Feuerstein - Page 49
tzset_show.sql
tzmisc.sql
tzglobal_events_local.sql
Interval Computations
 In the example below, declare a variable of type
INTERVAL YEAR TO MONTH, then assign a value
of 101 years and 3 months to it in three different
ways.
– These are not points in time, but amounts of elapsed time.
DECLARE
lifetime
BEGIN
lifetime
lifetime
lifetime
lifetime
...
END;
INTERVAL YEAR(3) TO MONTH;
:=
:=
:=
:=
INTERVAL
'101-3';
INTERVAL
INTERVAL
'101-3' YEAR TO MONTH; -- interval literal
-- implicit conversion from character type
'101' YEAR; -- Can specify just the years
'3' MONTH; -- Can specify just the months
Copyright 2000-2002 Steven Feuerstein - Page 50
Function with Interval
MEMBER FUNCTION age RETURN INTERVAL YEAR TO MONTH
IS
retval INTERVAL YEAR TO MONTH;
BEGIN
retval := (SYSDATE - SELF.dob) YEAR TO MONTH;
RETURN retval;
END;
 Notice the explicit conversion of the age
calculation formula to an INTERVAL.
person.ot
Copyright 2000-2002 Steven Feuerstein - Page 51
Lots of New Functions
 New conversion and "right now" capabilities:
EXTRACT
NUMTODSINTERVAL
NUMTOYMINTERVAL
TO_DSINTERVAL
TO_YMINTERVAL
TO_TIMESTAMP
TO_TIMESTAMP_TZ
FROM_TZ
SESSIONTIMEZONE
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
LOCALTIMESTAMP
SYSTIMESTAMP
TZ_OFFSET
extract.sql
Copyright 2000-2002 Steven Feuerstein - Page 52
Deploying XML in PL/SQL
 Introduction to XML
 Coding in Oracle8i
 New Oracle9i capabilities
Copyright 2000-2002 Steven Feuerstein - Page 53
What is XML?
 Stands for Extensible Markup Language and
defines a universal standard for electronic
data exchange...."EDI for the 21st century"
 Offers a rigorous set of rules to give structure
to data and make it easy to send and receive
information.
 Nothing but "text with tags"... a vendorneutral, platform-neutral, standards-based
information pathway.
Copyright 2000-2002 Steven Feuerstein - Page 54
Simple XML Example
<envReport>
<reportID>70689</reportID>
<source>ThickPlating, Inc.</source>
<Violations>
<Incident ReportedOn="01/15/2001"
ReportedBy="STEVEN.HARRISON">
Waste dumped in river</Incident>
<Incident ReportedOn="02/07/2001"
ReportedBy="SANDRA.HAJIJ">
Chimney filters disabled</Incident>
</Violations>
<NextSteps>
<Media>
Press conference to call for fines and penalties.
</Media>
<Legal>File suit in state and federal counts.</Legal>
</NextSteps>
</envReport>
Copyright 2000-2002 Steven Feuerstein - Page 55
Why Use XML?
 XML can serve as application integration "glue".
– XML over the HTTP protocol may provide the technology to relatively
seamlessly and inexpensively connect heterogeneous databases,
applications and networks.
 XML makes it easier to publish and reuse
information.
– Complete separation of data from presentation layer.
– Standard utilities to transform data to specific output styles.
 XML is extensible.
– "Roll your own" markup language.
Copyright 2000-2002 Steven Feuerstein - Page 56
Extensions to XML
 XML is tabula rosa.
– You get to define the tags (you can think of HTML as a
subset of XML with pre-defined formatting tags).
 Different industries are now defining
standardized markup languages that know
about those industries' requirements. Some
examples:
– ebXML electronic business infrastucture
– WML wireless markup language
– docBook documentation standards
Copyright 2000-2002 Steven Feuerstein - Page 57
www.xml.org
Send/Receive XML over
Internet
 You can easily send XML documents over the
Web using:
– FTP - File Transfer Protocol (transfer files)
– SMTP - Simple Mail Transfer Protocol (transfer email)
– HTTP - HyperText Transfer Protocol (transfer documents)
 XML will likely figure prominently in the
explosion of wireless Internet devices.
– "Small footprint" of information-rich data.
Copyright 2000-2002 Steven Feuerstein - Page 58
Web
Oracle 8i
XML
XML
Oracle
Apps
SAP
Apps
XSLT
Stylesheet
XML
SQL
Server
XML
XML and HTTP can connect heterogeneous applications
Copyright 2000-2002 Steven Feuerstein - Page 59
Web
Oracle Support for XML
 Oracle has moved very aggressively to support XML
from both Java and PL/SQL.
– Started in Oracle8i and accelerated tremendously in Oracle9i: the XDB
 JDeveloper allows you create, syntax-check and
debug XML, XSLT and XSQL.
 interMedia lets you index and search an XML
document.
 Many utilities available through PL/SQL (next page).
Copyright 2000-2002 Steven Feuerstein - Page 60
Some Oracle XML Components
Download the Oracle XDK from the Oracle Technology
Network; the 9i XDK works for 8i as well.
XML Parser
Use it to parse, construct and validate XML
documents.
XPath Engine
A utility that searches in-memory XML documents
using the declarative syntax of XPath, another
element of the XML standard
XSLT
Processor
Supports XSLT in Oracle, allowing you to
transform XML documents into different formats
XML SQL
Utility
Utility to facilitate the production of XML
documents from SQL and to easily insert XMLbased data into Oracle tables.
XSQL Pages
Technology allowing you to assemble XML data
declaratively and then publish that data with XSLT.
Copyright 2000-2002 Steven Feuerstein - Page 61
Overview of key Oracle8i technologies for XML
Save entire document or
fragments
in indexed CLOB columns
XML Document
Retrieve based on searching
XML document structure
Serialize
Parse
Automatically store XML
in a table, view, object view
Manipulate
DOM
XML Infoset
Oracle 8i
Automatically produce XML from
results of any SQL query
Select/query
XPath
Copyright 2000-2002 Steven Feuerstein - Page 62
Transform
XSLT
Some XML Concepts and Capabilities
 The XML Document
 Document Type Definitions and
Schemas
– Similar to DDL for tables, DTDs and schemas define
valid syntax for an XML document
 The XML Infoset
– Tree representation of XML document
 XPath
– Search contents of XML documents
Copyright 2000-2002 Steven Feuerstein - Page 63
From Document to Infoset
Text Document
<?xml version = “1.0”?>
<transaction><account>89-344</ac
count><buy shares = “100”><ticker>
WEBM</ticker></buy><sell shares=
“30”><ticker>INTC</ticker></sell
></transaction>
/
“Information Set”
<transaction>
<account>
89-344
<buy>
Shares = “100”
<ticker>
WEBM
<sell>
Shares = “30”
<ticker>
INTC
Copyright 2000-2002 Steven Feuerstein - Page 64
Using the
Document Object Model
 The DOM API (the
xmldom package in
Sequence of characters
encoding tree structured data
PL/SQL)
offers
a
following rules specified by the
standard set of
XML standard
programs to manipulate
an infoset.
can be parsed
can be serialized
XML Document
to produce
to produce
XML Infoset
W3C standard data model for
the tree-structured information
items in the XML document
Copyright 2000-2002 Steven Feuerstein - Page 65
can be manipulated
using
DOM
Document Object Model
W3C standard API for
constructing and manipulating
an XML information set
XPath: The XML Search Syntax
 W3C offers a declarative language called
XPath to query the contents of an XML
document.
– Operators on an information set
– Leverages our familiarity with the hierarchical structure and
path notation of directories and URLs.
 The Oracle xslProcessor package
implements the XPath functionality.
Copyright 2000-2002 Steven Feuerstein - Page 66
Some XPath Examples
 What are the names of the newspaper in the
document?
/Newspaper/Name
 Does this company's CEO have any bonus payments
over $100,000 while hiring of new employees was
frozen?
//CEO/Bonus[. > 100000 and @HiringFreeze="ON"]
 When did Sheri S. Tepper publish "Grass" ?
/Publication/Novel[Title="Grass"]/@PublicationDate
Copyright 2000-2002 Steven Feuerstein - Page 67
Oracle9i
New Oracle9i XML-Related
Features
 Significant steps forward in complete integration of
XML and SQL in the Oracle database: the XDB.
 The DBMS_XMLGEN package
– Takes in any arbitrary SQL query and converts them into the XML
format and returns the result as a CLOB
 The SYS.XMLType (SYS. not needed in Release 2)
– A system-defined object type that has predefined member functions
available to extract XML nodes and fragments
Copyright 2000-2002 Steven Feuerstein - Page 68
About DBMS_XMLGEN
 Takes in any arbitrary SQL query and
converts them into the XML format and
returns the result as a CLOB.
 Concepts and steps:
– Create and manage "contexts" for a specific SQL query.
– Set names for tags, set maximum number of rows queried
and define rows to be skipped.
– Fetch data as XML.
– Close and release context information.
Copyright 2000-2002 Steven Feuerstein - Page 69
DBMS_XMLGEN Example
 High-level programs do most of the work for you.
DECLARE
ctx
DBMS_XMLGEN.ctxhandle;
result
CLOB;
BEGIN
-- create a new context with the SQL query
ctx := DBMS_XMLGEN.newcontext ('select * from employee');
-- generate the CLOB as a result.
result := DBMS_XMLGEN.getxml (ctx);
-- print out the result of the CLOB
printclobout (result);
-- close the context
DBMS_XMLGEN.closecontext (ctx);
END;
Copyright 2000-2002 Steven Feuerstein - Page 70
xmlgen.tst
Oracle9i
The New XML Datatype
 A system-defined object type that has
predefined member functions available to
extract XML nodes and fragments.
 Brings the XML and SQL worlds together
– SQL operations on XML content
– XML operations on SQL content
– Apply standard XML functionality, such as XPath, directly
against data without need to convert.
Copyright 2000-2002 Steven Feuerstein - Page 71
Oracle9i
Set of XMLtype Methods
 createXML: creates an XMLtype instance from a string
or CLOB.
 existsNode: returns 1 if the given XPath expression
returns any result nodes.
 extract: applies an XPath expression over the XML data
to return a XMLType instance containing the resultant
fragment.
 isFragment: returns 1 if the XMLtype contains a
fragment.
 getCLOBval, getStringval, getNumberval: returns an
XML document or fragment as CLOB, string or number.
Copyright 2000-2002 Steven Feuerstein - Page 72
Oracle9i
Deposit XML Docs to Tables
 Create a table with an XMLtype column and
insert values with the CreateXML procedure.
CREATE TABLE xml_tab (xmlval SYS.XMLTYPE);
INSERT INTO xml_tab VALUES (
SYS.XMLTYPE.CREATEXML('<?xml version="1.0"?>
<EMP>
<EMPNO>221</EMPNO>
<ENAME>John</ENAME>
</EMP>'));
INSERT INTO xml_tab VALUES (
SYS.XMLTYPE.CREATEXML('<?xml version="1.0"?>
<PO>
<PONO>331</PONO>
<PONAME>PO_1</PONAME>
</PO>'));
Copyright 2000-2002 Steven Feuerstein - Page 73
xmltype.sql
Oracle9i
Retrieve XML Data
SQL> select x.xmlval.getstringval() from xml_tab x;
X.XMLVAL.GETSTRINGVAL()
--------------------------------------------------------<?xml version="1.0"?>
<EMP>
<EMPNO>221</EMPNO>
<ENAME>John</ENAME>
</EMP>
<?xml version="1.0"?>
<PO>
<PONO>331</PONO>
<PONAME>PO_1</PONAME>
</PO>
 Note: you must use an alias on the table name.
Copyright 2000-2002 Steven Feuerstein - Page 74
Oracle9i
Other Examples of Integration
SELECT p.podocument.getclobval () "Document"
FROM purchaseorder p
WHERE SYS.XMLTYPE.EXTRACT (
p.podocument,
'/PurchaseOrder/User/text()').getstringval()
= 'SMITH';
 And function-based indexes based on XPath!
CREATE UNIQUE INDEX i_purchase_order_reference
ON purchaseorder p
(
SUBSTR(SYS.XMLTYPE.GETSTRINGVAL(
SYS.XMLTYPE.EXTRACT(
p.PODOCUMENT,
'/PurchaseOrder/Reference/text()')),1,26)
)
Copyright 2000-2002 Steven Feuerstein - Page 75
Oracle9i
View Based on XML Data
CREATE OR REPLACE VIEW purchaseorderview
AS
SELECT SUBSTR (
sys.xmltype.getstringval (
sys.xmltype.EXTRACT (
p.podocument,
'/PurchaseOrder/Reference/text()'
)),1,26) "REFERENCE",
SUBSTR (
sys.xmltype.getstringval (
sys.xmltype.EXTRACT (
p.podocument,
'/PurchaseOrder/User/text()'
)),1,10) "USERID",
SUBSTR (
sys.xmltype.getstringval (
sys.xmltype.EXTRACT (
p.podocument,
'/PurchaseOrder/ShippingInstructions/name/text()'
)),1,20) "SHIPTO",
FROM purchaseorder p;
Copyright 2000-2002 Steven Feuerstein - Page 76
Oracle's Just Getting Started
 Oracle is now referring to its XML-aware
product as XDB: the XML DataBase.
 Oracle9i Release 2 will offer repository
features to manage XML data and documents,
including:
– Access control lists for security
– Foldering, allowing for the creation of hierarchies of directories
and utilities to search and manage them.
– WebDAV** and FTP access
**Web-based Distributed Authoring and Versioning", HTTP extensions for collaborative
editing and management of files on remote web servers.
Copyright 2000-2002 Steven Feuerstein - Page 77
Data Types - Summary
 A broader, deeper choice for data types and
data structures offers great potential for
improving the quality and reducing the
quantity of your code base.
 Potentially dramatic impact on the quality of
your code.
 Get comfortable with the structures and put
them to work for you.
Copyright 2000-2002 Steven Feuerstein - Page 78
SQL-Related Enhancements






Native Dynamic SQL
Bulk processing of DML and queries
Record-based DML
Autonomous transactions
Table functions
The MERGE statement
Copyright 2000-2002 Steven Feuerstein - Page 79
The Beauty and Elegance
of
Native Dynamic SQL
Copyright 2000-2002 Steven Feuerstein - Page 80
What is Dynamic SQL?
 Dynamic SQL actually refers, in the world of
PL/SQL, to two things:
– SQL statements, such as a DELETE or CREATE TABLE,
that are constructed and executed at run-time.
– Anonymous PL/SQL blocks that are constructed, compiled
and executed at run-time.
Copyright 2000-2002 Steven Feuerstein - Page 81
The Possibilities of Dynamic SQL
 Build ad-hoc query and update applications.
– When the user gets to decide what to do and see...a common
requirements for Internet apps.
 Execute DDL statements from within PL/SQL.
– They are not otherwise available in a PL/SQL block.
 Construct very generic and highly useful utilities
that work on "any" table or data structure.
 Optimize at run-time through soft-coding of hints.
 Write one set of code to work across and with
multiple schemas.
Copyright 2000-2002 Steven Feuerstein - Page 82
Two Methods Available
 DBMS_SQL
– A large and complex built-in package that made dynamic
SQL possible in Oracle7 and Oracle8.
 Native Dynamic SQL
– A new (with Oracle8i), native implementation of dynamic
SQL that does almost all of what DBMS_SQL can do, and
much more easily and quickly.
Let's focus on Native Dynamic SQL or NDS.
Copyright 2000-2002 Steven Feuerstein - Page 83
Native Dynamic SQL
 Prior to Oracle8i, you would use the DBMS_SQL builtin package to execute dynamic SQL.
– But this package is very complex, difficult to use, and relatively slow
(performance did improve significantly as of Oracle8).
 The new "native dynamic SQL" or NDS of Oracle8i
offers two native statements in the PL/SQL language
to implement most of your dynamic SQL requirements:
– EXECUTE IMMEDIATE <sql string>, used for DDL, DML and single row
fetches.
– OPEN FOR <sql string>, used for multi-row queries.
Copyright 2000-2002 Steven Feuerstein - Page 84
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE sql-string
[INTO {define_variable[, define_variables]... | record }]
[USING {IN | OUT | IN OUT] bind argument
[, {IN | OUT | IN OUT] bind argument]...];
 Use this statement to execute any dynamic SQL
statement (including a PL/SQL block) except for multi-row
queries.
 The INTO clause allows you to pass values from the
select list of a single row query into local variables,
including objects, collections and records.
 The USING clause allows you to specify bind arguments
or variables to be passed into the SQL string before
execution.
Copyright 2000-2002 Steven Feuerstein - Page 85
DDL within PL/SQL
 Very easy, very dangerous with NDS.
– Here's a procedure that "drops whatever".
CREATE OR REPLACE PROCEDURE drop_whatever (nm IN VARCHAR2)
AUTHID CURRENT_USER
IS
CURSOR type_cur IS
SELECT object_type FROM USER_OBJECTS
WHERE object_name LIKE UPPER (nm);
type_rec type_cur%ROWTYPE;
dropwhatever.sp
creind81.sp
BEGIN
health$.pkg
OPEN type_cur; FETCH type_cur INTO type_rec;
settrig.sp
IF type_cur%FOUND THEN
EXECUTE IMMEDIATE
'DROP ' || type_rec.object_type || ' ' || nm;
END IF;
END;
Copyright 2000-2002 Steven Feuerstein - Page 86
COUNT(*) For Any Table
 Here's a handy and simple utility based on NDS:
CREATE OR REPLACE FUNCTION tabCount (
tab IN VARCHAR2, whr IN VARCHAR2 := NULL, sch IN VARCHAR2 := NULL)
RETURN INTEGER
IS
Specify schema, table and
retval INTEGER;
WHERE clause...
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || NVL (sch, USER) ||
'.' || tab || ' WHERE ' || NVL (whr, '1=1') INTO retval;
RETURN retval;
END;
IF tabCount ('citizens', 'insured = ''NO''') > 40,000,000
THEN
DBMS_OUTPUT.PUT_LINE (
'Not the best health care system in the world....');
END IF;
Copyright 2000-2002 Steven Feuerstein - Page 87
tabcount81.sf
compare with:
tabcount.sf
DML with NDS
CREATE OR REPLACE PROCEDURE salary_raise (
raise_percent NUMBER, job VARCHAR2)
IS
TYPE loc_array_type IS TABLE OF VARCHAR2 (40)
INDEX BY BINARY_INTEGER;
dml_str
VARCHAR2 (200);
loc_array
loc_array_type;
BEGIN
SELECT location BULK COLLECT INTO loc_array
FROM offices;
Different
table for
each location
FOR i IN loc_array.FIRST .. loc_array.LAST LOOP
dml_str :=
'UPDATE emp_' || loc_array (i)
||
' SET sal = sal * (1+(:raise_percent/100))'
|| ' WHERE job = :job_title';
EXECUTE IMMEDIATE dml_str USING raise_percent, job;
END LOOP;
END;
Copyright 2000-2002 Steven Feuerstein - Page 88
Works w/User-defined types
 In the following example, the USING clause allows
me to pass an object and nested table to an
INSERT statement with a variable table name.
– Completely transparent support.
PROCEDURE add_profit_source (
hosp_name IN VARCHAR2,
pers IN Person,
cond IN preexisting_conditions)
IS
health$.pkg
BEGIN
EXECUTE IMMEDIATE
'INSERT INTO ' || tabname (hosp_name) ||
' VALUES (:revenue_generator, :revenue_inhibitors)'
USING pers, cond;
END;
Copyright 2000-2002 Steven Feuerstein - Page 89
Multiple Row Queries and NDS
 Familiar syntax, tiny curve: OPEN FOR
– Here is a simple utility that displays the values of any date, number or
string column in any table.
CREATE OR REPLACE PROCEDURE showcol (
tab IN VARCHAR2, col IN VARCHAR2, whr IN VARCHAR2 := NULL)
IS
TYPE cv_type IS REF CURSOR;
cv cv_type;
val VARCHAR2(32767);
BEGIN
OPEN cv FOR 'SELECT ' || col || ' FROM ' || tab ||
' WHERE ' || NVL (whr, '1 = 1');
LOOP
FETCH cv INTO val;
EXIT WHEN cv%NOTFOUND;
showcol81.sp
DBMS_OUTPUT.PUT_LINE (val);
ndsutil.pkg
END LOOP;
CLOSE cv;
Copyright 2000-2002
END; Steven Feuerstein - Page 90
Fetch Into Records!
 In DBMS_SQL, you had to write many tedious
lines of code to fetch into individual variables.
CREATE OR REPLACE PROCEDURE showemps (where_in IN VARCHAR2 := NULL)
IS
cv SYS_REFCURSOR;
rec employee%ROWTYPE;
New (Oracle9i) preBEGIN
defined weak REF
OPEN cv FOR
CURSOR type
'SELECT * FROM employee
WHERE ' || NVL (where_in, '1=1');
LOOP
FETCH cv INTO rec;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
TO_CHAR (rec.employee_id) || '=' || rec.last_name);
END LOOP;
CLOSE cv;
END;
Copyright 2000-2002 Steven Feuerstein - Page 91
Dynamic PL/SQL with NDS
 So many possibilities. Some things I have
done:
– Dramatically reduce code volume, improve performance.
– Generic string parsing engine: parse any string into your own
collection.
– Generic calculator engine.
– Implement support for "indirect referencing": read and change
values of variables whose names are only determined at runtime.
dynplsql.sql
str2list.pkg
dynvar81.pkg
dyncalc.pkg
utPLSQL
Copyright 2000-2002 Steven Feuerstein - Page 92
Some Fine Print for NDS
 You cannot pass schema elements (table names,
column names, etc.) through the USING clause.
 You cannot pass the NULL literal directly in the USING
clause. Instead, pass a variable with a NULL value.
 The USING clause for a query can only have IN bind
arguments.
 You can have duplicate placeholders (for bind
arguments).
– If dynamic SQL, provide value for each placeholder (by position).
– If dynamic PL/SQL, provide value for each distinct placeholder (by name).
dupbind.sql
Copyright 2000-2002 Steven Feuerstein - Page 93
Dangers of Dynamic SQL
 Common tradeoff: flexibility vs. performance
– Loss of dependency information in the data dictionary
 Runtime compilation is always slower than precompilation
– Extra parsing/optimization (CPU) and memory allocation
 May only be problematic when volume is large
– So don’t be dogmatic about it!
 Dynamic DDL can really be a killer
– Library cache object invalidation/recompilation
Copyright 2000-2002 Steven Feuerstein - Page 94
Recommendations for NDS
 Bind (vs. concatenate) whenever possible.
– Increased chance of reusing parsed SQL, and easier code to write.
 Handle errors gracefully and comprehensively.
– It can very difficult to figure out what went wrong, so trap exceptions and
display the problematic SQL to make it easier to track down and resolve
the problem.
 Use AUTHID CURRENT_USER (Oracle8i
invoker rights model) for all stored programs
that contain dynamic SQL.
updnval_nds1.sp
– Otherwise that SQL will be executed under the authority
of the owner of the code, not the invoker of the code.
Copyright 2000-2002 Steven Feuerstein - Page 95
updnval_nds2.sp
updnval_nds3.sp
whichsch81.sql
Choose Binding over Concatenation
 Simpler code to build and maintain
– Concatenation results in much more complicated and error-prone code
unless you are doing a very simple operation
 Improved application performance
– Concatenates increases likelihood that the statement will be physically
different, requiring re-parsing and additional SGA utilization
 You can only bind variable values.
usebinding.sp
– Schema elements, like table names, or portions of the SQL statement,
useconcat.sp
toomuchbind.sp
must be concatenated.
 A caveat: use of bind variables can reduce the
effectiveness of the cost-based optimizer
Copyright 2000-2002 Steven Feuerstein - Page 96
Handle Dynamic SQL Errors
 Handle errors gracefully and comprehensively.
– Do not assume that the SQL statement will be constructed
properly.
 Most important during parse phase with
DBMS_SQL.
– It can very difficult to figure out what went wrong, so trap
exceptions and display the problematic SQL to make it easier
to track down and resolve the problem.
dynerrhdlg.sp
openprse.pkg
Copyright 2000-2002 Steven Feuerstein - Page 97
NDS or DBMS_SQL: Which is best?
 Major Advantages of
NDS:
– Ease of use
– Performance
– Works with all SQL datatypes
(including user-defined
object and collection types)
– Fetch into records
 When You'd Use
DBMS_SQL:
–
–
–
–
–
Method 4 Dynamic SQL
DESCRIBE columns of cursor
SQL statements larger than 32K
RETURNING into an array
Better reuse of parsed SQL
statements
– Available from client-side PL/SQL
Bottom line: NDS should be your first choice.
Copyright 2000-2002 Steven Feuerstein - Page 98
SQL on Steroids: Bulk Processing
 Oracle8i and Oracle9i offer groundbreaking new
syntax to improve the performance of both DML and
queries.
 In Oracle8, updating from a collection (or, in
general, performing multi-row DML) meant writing
code like this:
CREATE TYPE dlist_t AS TABLE OF INTEGER;
/
PROCEDURE whack_emps_by_dept (deptlist dlist_t)
IS
BEGIN
FOR aDept IN deptlist.FIRST..deptlist.LAST
LOOP
DELETE emp WHERE deptno = deptlist(aDept);
END LOOP;
END; Steven Feuerstein - Page 99
Copyright 2000-2002
“Conventional
binds” (and lots
of them!)
Conventional Bind
Oracle server
PL/SQL Runtime Engine
PL/SQL block
FOR aDept IN deptlist.FIRST..
deptlist.LAST
LOOP
DELETE emp
WHERE deptno = deptlist(aDept);
END LOOP;
SQL Engine
Procedural
statement
executor
Performance penalty
for many “context
switches”
Copyright 2000-2002 Steven Feuerstein - Page 100
SQL
statement
executor
Enter the “Bulk Bind”
Oracle server
PL/SQL Runtime Engine
PL/SQL block
FORALL aDept IN deptlist.FIRST..
deptlist.LAST
DELETE emp
WHERE deptno = deptlist(aDept);
Procedural
statement
executor
SQL Engine
SQL
statement
executor
Much less overhead for
context switching
Copyright 2000-2002 Steven Feuerstein - Page 101
Use the FORALL Bulk Bind
Statement
 Instead of the individual DML operations, you can
do this:
PROCEDURE whack_emps_by_dept (deptlist dlist_t)
IS
BEGIN
FORALL aDept IN deptlist.FIRST..deptlist.LAST
DELETE FROM emp WHERE deptno = deptlist(aDept);
END;
 Some restrictions:
– Only the single DML statement is allowed. If you want to INSERT
and then UPDATE, two different FORALL statements
– Cannot put an exception handler on the DML statement -- until
Oracle9i Release 2.
Copyright 2000-2002 Steven Feuerstein - Page 102
Use BULK COLLECT for Queries
 BULK COLLECT
performs bulk
bind of results
from SQL select
statement
– Returns each
selected expression
in a table of scalars
Copyright 2000-2002 Steven Feuerstein - Page 103
CREATE OR REPLACE FUNCTION get_a_mess_o_emps
(deptno_in IN dept.depno%TYPE)
RETURN emplist_t
IS
emplist emplist_t := emplist_t();
TYPE numTab IS TABLE OF NUMBER;
TYPE charTab IS TABLE OF VARCHAR2(12);
TYPE dateTab IS TABLE OF DATE;
enos numTab;
names charTab;
hdates dateTab;
BEGIN
SELECT empno, ename, hiredate
BULK COLLECT INTO enos, names, hdates
FROM emp
WHERE deptno = deptno_in;
emplist.EXTEND(enos.COUNT);
FOR i IN enos.FIRST..enos.LAST
LOOP
emplist(i) := emp_t(enos(i),
names(i), hiredates(i));
END LOOP;
RETURN emplist;
END;
Combining FORALL & BULK COLLECT
 Use the RETURNING clause to obtain information
about each of the DML statements executed in the
FORALL
– Since you are executing multiple DML statements, you need to BULK
COLLECT the RETURNING results into one or more collections
FUNCTION whack_emps_by_dept (deptlist dlist_t)
RETURN enolist_t
IS
enolist enolist_t;
BEGIN
FORALL aDept IN deptlist.FIRST..deptlist.LAST
DELETE FROM emp WHERE deptno IN deptlist(aDept)
RETURNING empno BULK COLLECT INTO enolist;
RETURN enolist;
END;
Copyright 2000-2002 Steven Feuerstein - Page 104
bulkcoll.sql
bulktiming.sql
Oracle9i
Oracle9i Enhancements
 You can now use dynamic SQL strings in
the bulk bind and collect statements.
– FORALL for bulk DML
– BULK COLLECT for bulk queries.
 This gives you virtually unlimited flexibility
without a tradeoff in performance.
Copyright 2000-2002 Steven Feuerstein - Page 105
Oracle9i
Dynamic FORALL Example
 This example shows the use of bulk binding and
collecting, plus application of the RETURNING clause.
CREATE TYPE NumList IS TABLE OF NUMBER;
CREATE TYPE NameList IS TABLE OF VARCHAR2(15);
PROCEDURE update_emps (
col_in IN VARCHAR2, empnos_in IN numList) IS
enames NameList;
BEGIN
FORALL indx IN 1..5
EXECUTE IMMEDIATE
'UPDATE emp SET ' || col_in || ' = ' || col_in
|| ' * 1.1 WHERE empno = :1
RETURNING ename INTO :2'
USING empnos_in(i indx )
Notice that empnos_in
RETURNING BULK COLLECT INTO enames;
is indexed, but enames
...
is not.
END;
Copyright 2000-2002 Steven Feuerstein - Page 106
Oracle9i
Dynamic BULK COLLECT
 Now you can even avoid the OPEN FOR and just
grab your rows in a single pass!
CREATE OR REPLACE PROCEDURE fetch_by_loc (loc_in IN VARCHAR2)
IS
TYPE numlist_t IS TABLE OF NUMBER;
TYPE namelist_t IS TABLE OF VARCHAR2 (15);
Both
emp_cv
sys_refcursor;
empnos
numlist_t;
approaches
enames
namelist_t;
work
sals
numlist_t;
BEGIN
OPEN emp_cv FOR 'SELECT empno, ename FROM emp_' || loc_in;
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;
EXECUTE IMMEDIATE 'SELECT sal FROM emp_' || loc_in
BULK COLLECT INTO sals;
END;
Copyright 2000-2002 Steven Feuerstein - Page 107
Still cannot
fetch into
collections
of records.
Oracle9i
Better Exception Handling
for Bulk Operations
 Allows you to continue past errors and obtain error
information for each individual operation (for
dynamic and static SQL).
CREATE OR REPLACE PROCEDURE load_books (books_in IN book_obj_list_t)
IS
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
BEGIN
FORALL indx IN books_in.FIRST..books_in.LAST
Allows processing of all
SAVE EXCEPTIONS
rows, even after an
INSERT INTO book values (books_in(indx));
error occurs.
EXCEPTION
WHEN BULK_ERRORS THEN
New cursor
FOR indx in 1..SQL%BULK_EXCEPTIONS.COUNT
attribute, a pseudoLOOP
collection
log_error (SQL%BULK_EXCEPTIONS(indx));
END LOOP;
END;
Copyright 2000-2002 Steven Feuerstein - Page 108
bulkexc.sql
Tips and Fine Points
 Use bulk binds if you write code with these
characteristics:
– Recurring SQL statement in PL/SQL loop
– Use of a collection as the bind variable, or code that could be
transformed to use a collection containing the bind variable information
 Bulk bind rules:
– Can be used with any kind of collection; Collection subscripts cannot be
expressions; The collections must be densely filled; If error occurs, prior
successful DML statements are NOT ROLLED BACK
 Bulk collects:
– Can be used with implicit and explicit cursors
Collection is filled starting at row 1
Copyright 2000-2002 Steven Feuerstein - Page 109
Cursor FOR Loop ... or BULK COLLECT?
 Why would you ever use a cursor FOR loop
now that you can perform a BULK
COLLECT?
– If you want to do complex processing as each row is
queried.
– If you are still running Oracle8i, which means you cannot
SAVE EXCEPTIONS.
 Otherwise, moving to BULK COLLECT is a
smart move!
cfl_vs_bulkcollect.sql
Copyright 2000-2002 Steven Feuerstein - Page 110
Oracle9i
RECORD-BASED DML
 PL/SQL records (similar in structure to a row
in a table) offer powerful ways to manipulate
data
– Prior to Oracle9i R2, however, records could not be used in
DML statements
 That restriction has now been lifted
– You can INSERT specifying a record rather than individual
fields of the record
– You can UPDATE an entire row with a record
Copyright 2000-2002 Steven Feuerstein - Page 111
RECORD-BASED INSERTS
DECLARE
TYPE book_list_t IS TABLE OF books%ROWTYPE;
my_books book_list_t := book_list_t();
BEGIN
my_books.EXTEND (2);
my_books(1).isbn := '1-56592-335-9';
my_books(1).title := 'ORACLE PL/SQL PROGRAMMING';
my_books(2).isbn := '0-596-00121-5';
my_books(2).title := 'ORACLE PL/SQL BEST PRACTICES';
FORALL indx IN my_books.FIRST .. my_books.LAST
INSERT INTO books VALUES my_books(indx);
END;
 This example shows a record-based insert
inside the high-speed FORALL statement
Copyright 2000-2002 Steven Feuerstein - Page 112
RECORD-BASED UPDATES
DECLARE
my_book books%ROWTYPE;
BEGIN
my_book.isbn := '1-56592-335-9';
my_book.title := 'ORACLE PL/SQL PROGRAMMING';
my_book.summary := 'General user guide and reference';
my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';
my_book.page_count := 950; -- new page count for 3rd edition
UPDATE books
SET ROW = my_book
WHERE isbn = my_book.isbn;
END;
 You can only update the entire ROW, and not a
subset via, say, a programmer-defined record type
Copyright 2000-2002 Steven Feuerstein - Page 113
Autonomous Transactions
 Prior to Oracle8i, a COMMIT or ROLLBACK in any
program in your session committed or rolled back
all changes in your session.
– There was only one transaction allowed per connection.
 With Oracle8i, you can now define a PL/SQL block
to execute as an "autonomous transaction".
– Any changes made within that block can be saved or reversed
without affecting the outer or main transaction.
CREATE OR REPLACE PROCEDURE loginfo (
code IN PLS_INTEGER,
msg IN VARCHAR2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
Copyright 2000-2002 Steven Feuerstein - Page 114
When to Use Autonomous Transactions
 Reusable Application Components
– ATs are more or less required in the new distributed
application architecture of the Internet.
 Logging Mechanism
– Solves problems of error logs in database tables, with log
entries a part of your transaction.
 Call functions within SQL that change the
database.
 Issue commits and rollbacks inside DB
triggers.
Copyright 2000-2002 Steven Feuerstein - Page 115
Logging with ATs
CREATE OR REPLACE PACKAGE BODY log
IS
PROCEDURE putline (
code_in IN INTEGER, text_in IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO logtab
VALUES (code_in, text_in,
SYSDATE, USER, SYSDATE, USER,
rec.machine, rec.program
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN ROLLBACK;
END;
END;
logger.sp
log81.pkg
log81*.tst
Copyright 2000-2002 Steven Feuerstein - Page
116
Avoid interdependencies with
the main
transaction.
Save on
successful exit
Don't forget to
rollback on error!
retry.pkg
retry.tst
Tips and Gotchas
 The Oracle initialization parameter
TRANSACTIONS specifies the maximum number
of concurrent transactions.
– Which might be exceeded if autonomous transactions (running
concurrently with main transaction) are not taken into account.
Any changes committed in an AT are visible in the
outer transaction.
– You can use the SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE to indicate that you do not want the changes visible
until the outer transaction commits.
– Place the SET TRANSACTION statement in the outer transaction.
Copyright 2000-2002 Steven Feuerstein - Page 117
autonserial.sql
auton_in_sql.sql
autontrigger*.sql
Making it Easy
Use editor templates and libraries to help developers
use autonomous transactions properly...
Copyright 2000-2002 Steven Feuerstein - Page 118
Serialization Example
DECLARE
num INTEGER;
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) INTO num FROM emp2;
DBMS_OUTPUT.PUT_LINE (
'Before isolated AT delete ' || num);
empty_the_table;
 Must come
before any
DML
statements
in your
transaction.
SELECT COUNT(*) INTO num FROM emp2;
DBMS_OUTPUT.PUT_LINE (
'After isolated AT delete ' || num);
COMMIT;
SELECT COUNT(*) INTO num FROM emp2;
DBMS_OUTPUT.PUT_LINE (
'After MT commit ' || num);
END;
Copyright 2000-2002 Steven Feuerstein - Page 119
The output:
Before isolated AT delete 14
After isolated AT delete 14
After MT commit 0
Enabling DML in SQL-Run
Functions
CREATE OR REPLACE FUNCTION nothing
RETURN INTEGER IS
BEGIN
DELETE FROM emp;
COMMIT;
RETURN 1;
END;
/
SELECT DISTINCT (nothing) FROM EMP;
CREATE OR REPLACE FUNCTION nothing
RETURN INTEGER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
... same as before...
END;
/
SELECT DISTINCT (nothing) FROM EMP;
Copyright
2000-2002
Steven Feuerstein
- Page 120
SELECT
COUNT(*)
FROM emp;
The output:
Function created.
SELECT DISTINCT (nothing)
*
ERROR at line 1:
ORA-14551: cannot perform a
DML operation inside a query
Function created.
NOTHING
---------1
COUNT(*)
---------0
Autonomous Transactions
in Triggers
 Very helpful when needing to perform audits
that track "before" and "after" activities on
individual row operations.
– If you need to roll back the transaction due to a failure in,
say, an INSERT, you still want to record the fact that an
attempt to perform the insert occurred, possibly push that
information off into some kind of audit history table.
Copyright 2000-2002 Steven Feuerstein - Page 121
Trigger Example
CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
BEFORE INSERT ON ceo_compensation FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ceo_comp_history VALUES (
:new.name, 'BEFORE INSERT', SYSDATE);
COMMIT;
END;
/
CREATE OR REPLACE TRIGGER aft_ins_ceo_comp
AFTER INSERT ON ceo_compensation FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ceo_comp_history VALUES (
:new.name, 'AFTER INSERT', SYSDATE);
COMMIT;
END;
/ 2000-2002 Steven Feuerstein - Page 122
Copyright
Audit before operation
Audit after operation
Autonomous Transactions




Easy to define
Lots of immediate applications
Minimal learning curve
Low implementation risks
 You should immediately explore opportunities
to utilize this feature.
Copyright 2000-2002 Steven Feuerstein - Page 123
Exploring Table Functions
SELECT c.name, Book.name, Book.author, Book.abstract
FROM Catalogs c,
TABLE (te_Book.result_set (c.cat)) Book;
 Table functions return a collection type instance
and can be queried like a table by calling the
function in the FROM clause of a query.
 If the function accepts as its IN argument a REF
CURSOR (new to Oracle9i), then it can also serve
as a "transformative" function.
– Pass results sets from one function to another without the need for
intermediate data structures.
Copyright 2000-2002 Steven Feuerstein - Page 124
Benefits of Table Functions
 Improved performance, particularly for data
warehouse applications.
– Full support for parallel processing.
 Increased language flexibility.
– Better encapsulation of complex logic.
– You can, in effect, create parameterized views.
– Allow emulation of nested tables as relational data.
Copyright 2000-2002 Steven Feuerstein - Page 125
Performance Possibilities
Pipelining and parallel execution using table functions
• Enables multi-threaded, concurrent execution
of table functions.
• Eliminates intermediate staging between
processes.
• Allows iterative return of result set; rows can be
returned as they are identified, before the
function execution ends.
Copyright 2000-2002 Steven Feuerstein - Page 126
Oracle8i Table Function Example
 Create an object TYPE and a nested table TYPE of
those objects.
 Then define a function that returns a nested table of
this type.
CREATE TYPE pet_t IS OBJECT (
NAME
VARCHAR2 (60),
breed
VARCHAR2 (100),
dob
DATE);
/
CREATE TYPE pet_nt IS TABLE OF pet_t;
/
CREATE OR REPLACE FUNCTION pet_family (
dad_in IN pet_t, mom_in IN pet_t)
RETURN pet_nt ...
Copyright 2000-2002 Steven Feuerstein - Page 127
Continued...
Table Function Example, cont.
 Populate the collection, and then use it in a query.
CREATE OR REPLACE FUNCTION pet_family (
dad_in IN pet_t, mom_in IN pet_t) RETURN pet_nt
IS
l_count PLS_INTEGER;
retval
pet_nt := pet_nt ();
BEGIN
retval.EXTEND; retval (retval.LAST) := dad_in;
retval.EXTEND; retval (retval.LAST) := mom_in;
IF mom_in.breed = 'RABBIT' THEN l_count := 12;
ELSIF mom_in.breed = 'DOG' THEN l_count := 4;
ELSIF mom_in.breed = 'KANGAROO' THEN l_count := 1;
END IF;
FOR indx IN 1 .. l_count LOOP
retval.EXTEND;
retval (retval.LAST) :=
pet_t (
'BABY' || indx,
mom_in.breed, SYSDATE);
END LOOP;
RETURN retval;
Copyright
2000-2002 Steven Feuerstein - Page 128
END;
SELECT * FROM TABLE (CAST (
pet_family (
pet_t ('Hoppy', 'RABBIT', SYSDATE),
pet_t ('Hippy', 'RABBIT', SYSDATE)
) AS pet_nt));
tabfunc3.sql
Oracle9i Table Function
Enhancements
 You can now use the CURSOR expression
syntax to pass a result set as an argument
to a table function.
– This function can then be called in top-level queries.
 Table functions can now be "pipelined",
allowing data to be returned iteratively.
– Such a function can be executed in parallel, offering
significant performance improvements in data
warehousing applications.
Copyright 2000-2002 Steven Feuerstein - Page 129
Passing Cursors as Arguments
CREATE OR REPLACE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR
RETURN StockTable%ROWTYPE;
END refcur_pkg;
CREATE OR REPLACE FUNCTION StockPivot (
cur_in refcur_pkg.refcur_t)
RETURN TickerTypeSet...
Define a REF
CURSOR type
Create a function that
accepts a cursor of
that type.
INSERT INTO tickertable
SELECT *
FROM TABLE (StockPivot (
CURSOR (SELECT * FROM StockTable)));
tabfunc.sql
Call the function from within SQL,
passing to it another query.
Copyright 2000-2002 Steven Feuerstein - Page 130
Working w/Pipelined Functions
CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t)
RETURN TickerTypeSet PIPELINED
 Pipelined functions allow you to return data
iteratively.
– As data is produced within the function, it is passed back to the
calling process/query.
 Pipelined functions can be defined to support
parallel execution.
– Iterative data processing allows multiple processes to work on
that data simultaneously.
Copyright 2000-2002 Steven Feuerstein - Page 131
Outputting Rows Iteratively
CREATE FUNCTION stockpivot (p refcur_pkg.refcur_t)
RETURN tickertypeset
PIPELINED
Define as
IS
out_rec
tickertype := tickertype (NULL, NULL, NULL); PIPELINED
in_rec
p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
out_rec.ticker := in_rec.ticker;
out_rec.pricetype := 'O';
out_rec.price := in_rec.openprice;
PIPE ROW sends
PIPE ROW (out_rec);
the data out.
END LOOP;
CLOSE p;
RETURN;
No RETURN of
END;
any actual data.
tabfunc.sql
/
Copyright 2000-2002 Steven Feuerstein - Page 132
Parallel Execution and
Table Functions
 Prior to Oracle9i, calling a function inside a
SQL statement caused serialization.
– The parallel query mechanism could not be used.
 Now you can enable parallel execution of a
table function.
– This greatly increases the usability of PL/SQL-enriched SQL
in data warehouse applications.
{[ORDER | CLUSTER] BY column_list}
PARALLEL_ENABLE ({PARTITION p BY
[ANY | (HASH | RANGE) column_list]} )
Copyright 2000-2002 Steven Feuerstein - Page 133
Enabling Parallel Execution
 The table function's parameter list must consist
only of a single strongly-typed REF CURSOR.
 Include the PARALLEL_ENABLE hint in the
program header.
– Choose a partition option that specifies how the function's execution
should be partitioned.
– "ANY" means that the results are independent of the order in which
the function receives the input rows (through the REF CURSOR).
{[ORDER | CLUSTER] BY column_list}
PARALLEL_ENABLE ({PARTITION p BY
[ANY | (HASH | RANGE) column_list]} )
Copyright 2000-2002 Steven Feuerstein - Page 134
Examples of Parallelized Functions
CREATE OR REPLACE FUNCTION Aggregate_Xform (
p_input_rows in My_Types.cur_t) RETURN My_Types.dept_sals_tab
PIPELINED
with
with
with
PARALLEL_ENABLE (
Partition p_input_rows BY ANY )
Simplest form, results don't vary
from order in which function gets
input rows.
CLUSTER P_INPUT_ROWS BY (dept)
PARALLEL_ENABLE
( PARTITION p_input_rows
BY HASH (dept) )
All rows for a given department
must go to the same slave, and
rows are delivered consecutively.
ORDER p_input_rows BY (c1, c2)
PARALLEL_ENABLE
( PARTITION p_input_rows
BY RANGE (c1) )
Rows are delivered to a particular
slave as directed by partition...
and will be locally sorted by that
slave.
Copyright 2000-2002 Steven Feuerstein - Page 135
Miscellaneous, But
Really Good Stuff
 Leveraging Java from within PL/SQL
 Execution model options: definer and invoker
rights
 CASE statement and expression
 Native compilation of PL/SQL code
 Improvements to UTL_FILE
Copyright 2000-2002 Steven Feuerstein - Page 136
Leveraging Java
from within
PL/SQL Programs
Copyright 2000-2002 Steven Feuerstein - Page 137
Overview of Java
Interoperability
 Java inside or outside 8i server can call PL/SQL
– Standard JDBC and SQLJ calls with Oracle extensions
– Same Java on client, mid-tier, or server
 PL/SQL can call Java inside 8i server
–
–
–
–
Command-line tools load Java classes
DDL extensions publish Java classes
Writing stored procedures, functions, triggers in Java
Distinct Java & PL/SQL namespaces
 But first...a BRIEF introduction to Java...
Copyright 2000-2002 Steven Feuerstein - Page 138
Question 1:
What is Java?
 Could it be...
– The end of programming history as we know it?
– The easiest, fastest, slickest piece of software ever designed
by human beings?
– Just the latest in a series of "silver bullets" promoted by
software vendors in order to prop up quarterly sales?
– The first and only successful O-O language?
– None of the above?
 We don't really need to take a vote.
– We just need to keep a firm grip on common sense and stay
focused on delivering solutions.
Copyright 2000-2002 Steven Feuerstein - Page 139
Question 2:
Will Java Replace PL/SQL?
 While that scenario is certainly possible, it is
very unlikely and totally unthinkable for years to
come.
 PL/SQL will still be:
– Faster and more productive than Java for database operations.
– A language in which hundreds of thousands of developers are
trained.
– Ubiquitous in thousands of production applications and millions
of lines of code.
– Supported and improved by Oracle -- and very aggressively, to
boot.
Copyright 2000-2002 Steven Feuerstein - Page 140
Some Important Things to
Remember
 Java is a case sensitive language...
– string is definitely not the same as String.
 Everything is a class (or an object instantiated
from a class)...
– Before you can call a (non-static) class method, you have to
instantiate an object from that class.
– Well, everything except the primitive datatypes.
 You don't have to know how to do everything
with Java to get lots of value out of it...
– Don't get overwhelmed by all the classes and all the strange quirks.
Copyright 2000-2002 Steven Feuerstein - Page 141
Java's Not So Tough!
 You can learn enough Java in less than a week
to:
– Build simple classes
– Leverage Java inside PL/SQL
 Moving to the next level of expertise will be more
of a challenge.
– Object oriented development (Java) is very different from procedural
coding (PL/SQL).
 Now let's explore how you can put Java to work
for you inside PL/SQL programs.
Copyright 2000-2002 Steven Feuerstein - Page 142
Java Stored Procedures
(JSPs?!)
Java applet or
app. using
JDBC or
SQLJ
Oracle
Developer
client
(PL/SQL)
Oracle 8i server
Net8
OCI or
Pro*C
client
VB or C++
via OO4O or
ODBC
Copyright 2000-2002 Steven Feuerstein - Page 143
PL/SQL cover for Java
method
Java virtual machine running
Java method
JSPs: Some sample uses
 PL/SQL extender
– For example, better file I/O
– Clean access to operating system functionality
 PL/SQL replacement
– More standard language
– Good performer for numeric processing tasks
– Beware database I/O & string manipulation performance
Copyright 2000-2002 Steven Feuerstein - Page 144
Creating JSP to call from PL/SQL
 1.Create Java classes in your favorite IDE
 2.Load into server using “loadjava”
command-line tool
 3.Publish PL/SQL cover using AS
LANGUAGE JAVA... rather than
BEGIN...END
 4.Grant privileges as desired
 5.Call from PL/SQL (or SQL) as if calling
PL/SQL
Copyright 2000-2002 Steven Feuerstein - Page 145
Create Java class(es)
class Corporation extends Person {
long layoffs;
long CEOCompensation;
public Corporation (
String Pname, long Playoffs, long PceoComp) {
name = Pname;
layoffs = Playoffs;
CEOCompensation = PceoComp;
}
public static void main (String[] args) {
// A very scary company
Corporation TheGlobalMonster =
new Corporation (
"Northrup-Ford-Mattel-Yahoo-ATT",
5000000, 50000000);
System.out.println (TheGlobalMonster);
}}
person.java
Copyright 2000-2002 Steven Feuerstein - Page 146
Notes on Java
classes
 toString method
automatically used
by
System.out.println
 main method is used
to test the class.
 Classes may call
other classes
 Avoid GUI calls
Upload using “loadjava” utility
.class file
.java file
Java
resource
file
.jar file
Oracle 8i server
loadjava
Java
class
Java
source
Example:
loadjava -user scott/tiger -oci8
-resolve datacraft/bill/Hello.class
loadjava options (abbreviated)
-oci8
loadjava will connect using OCI driver
-resolve Resolves external class references at
compile time
-resolver (shown later) Search path like CLASSPATH
Copyright 2000-2002 Steven Feuerstein - Page 147
Java
resource
Publish
 Example (top-level call spec)
 Syntax (simplified)
CREATE OR REPLACE FUNCTION hello_emp
(empno_in IN NUMBER)
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'datacraft.bill.Hello.Emp(int)
return java.lang.String';
/
CREATE [ OR REPLACE ] { PROCEDURE | FUNCTION } <name>
[ RETURN <sqltype> ]
[ ( <args> ) ]
[ AUTHID { DEFINER | CURRENT_USER } ]
AS LANGUAGE JAVA
NAME '<method fullname> (<Java type fullname>, ...)
[ return <Java type fullname> ]';
Copyright 2000-2002 Steven Feuerstein - Page 148
Call the wrapped method
 Method 1: Call as if PL/SQL module
BEGIN
DBMS_OUTPUT.PUT_LINE(hello_emp(7499));
END;
 Method 2: Use 8i SQL CALL statement; for
example, from SQL*Plus:
VARIABLE thename VARCHAR2(12)
CALL hello_emp(7499) INTO :thename;
PRINT :thename
jsp.sql
Copyright 2000-2002 Steven Feuerstein - Page 149
Publishing -- more concepts
Shape mapping
– Java methods declared “void” become PL/SQL procedures
– Signature mismatches detected only at runtime
Type mapping (typical)
java.lang.String
java.sql.Timestamp
java.math.BigDecimal
oracle.sql.STRUCT
<named type>
oracle.sql.REF
oracle.sql.ARRAY
Copyright 2000-2002 Steven Feuerstein - Page 150
VARCHAR2
DATE
NUMBER
user-defined object type
user-defined object type
object REFerence
user-defined collection type
New DDL Statements and Roles
 CREATE JAVA
– Alternative to “loadjava” utility, Creates or replaces an Oracle “library
unit” from Java source, class, or resource
– Can read file designated with BFILE() function
 ALTER JAVA
utlzip.sql
– Compiles Java source, resolves Java class references.
 DROP JAVA
– Drops a named Java library unit
 Several roles available for Java operations:
– JAVAUSERPRIV (read I/O operations) and JAVASYSPRIV (write IO
operations), JAVA_ADMIN, JAVAIDPRIV, JAVADEBUGPRIV
– You can also grant specific privileges.
Copyright 2000-2002 Steven Feuerstein - Page 151
Example: Improving File I/O
 You can read/write files in PL/SQL with
UTL_FILE, but that package is very
limited.
 Java offers many file-related classes
with much greater capabilities.
 Let's see how we can make that great
Java stuff available from within
PL/SQL.
Copyright 2000-2002 Steven Feuerstein - Page 152
Encapsulate Java Classes
 You won't generally access native Java
methods in your PL/SQL wrapper.
– Instead build a static method that instantiates a Java object
from the class and then invokes the relevant method
against that object.
 Let's start with something simple...
– The File class offers a length method that returns the
number of bytes in a file.
– This is not available through UTL_FILE (though you can get
it through DBMS_LOB).
Copyright 2000-2002 Steven Feuerstein - Page 153
A Java Class for File Manipulation
import java.io.File;
public class JFile2 {
public static long length (String fileName) {
File myFile = new File (fileName);
return myFile.length(); }
}
Accept the
name of a file
and return the
length.
 Take each of these steps:
– Import the File class to resolve reference.
– Instantiate a File object for the specified name.
– Call the method of choice against that object and
return the value.
Copyright 2000-2002 Steven Feuerstein - Page 154
JFile2.java
Build Package over Java Method
 Let's put it in a package; we will certainly want
to add more functionality over time.
– I translate the Java long to a PL/SQL NUMBER.
CREATE OR REPLACE PACKAGE xfile
IS
FUNCTION length (file IN VARCHAR2) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY xfile
IS
FUNCTION length (file IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'JFile.length (java.lang.String) return long';
END;
xfile2.pkg
/
Copyright 2000-2002 Steven Feuerstein - Page 155
Mapping the Boolean Datatype
 Both Java and PL/SQL support a native Boolean
datatype, so you'd expect smooth sailing. Not so!
 To pass a Boolean back from Java to PL/SQL, you
will need to take these steps:
– 1. Convert the Java boolean to a String or number and return that value.
– 2. Write a "hidden" PL/SQL wrapper function that returns the string or
number.
– 3. Write a "public" PL/SQL wrapper function to convert that number to a
true PL/SQL Boolean.
Copyright 2000-2002 Steven Feuerstein - Page 156
Translate Boolean to Number
 Am I allowed to read this file? For real?
import java.io.File;
public class JFile3 {
public static int canRead (String fileName) {
File myFile = new File (fileName);
boolean retval = myFile.canRead();
if (retval) return 1; else return 0; }
}
 Translate TRUE to 1 and FALSE to 0.
– And don't forget: this is a boolean primitive, not a Boolean
class.
JFile3.java
Copyright 2000-2002 Steven Feuerstein - Page 157
Wrap Pseudo-Boolean Function
 Simple translation back to PL/SQL Boolean.
– Avoid the hard-codings with named constants...
CREATE OR REPLACE PACKAGE xfile IS
FUNCTION canRead (file IN VARCHAR2) RETURN BOOLEAN;
END;
/
CREATE OR REPLACE PACKAGE BODY xfile
IS
FUNCTION IcanRead (file IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'JFile3.canRead (java.lang.String) return int';
FUNCTION canRead (file IN VARCHAR2) RETURN BOOLEAN AS
BEGIN
RETURN IcanRead (file) = 1;
END;
END;
Copyright 2000-2002 Steven Feuerstein - Page 158
xfile3.pkg
JFile4.java
xfile4.pkg
JFile.java
xfile.pkg
Passing Collections to Java
 Let's take a look at what is needed to move a
PL/SQL collection to a Java array.
– A utility that deletes all the files found in the specified directories (one
per row in the collection) that have not been modified since the
specified date.
CREATE or REPLACE JAVA SOURCE NAMED "DeleteFile" AS
import
import
import
import
import
import
import
java.io.*;
java.sql.*;
oracle.jdbc.driver.*;
oracle.sql.*;
java.util.Date;
java.text.*;
java.text.DateFormat.*;
Copyright 2000-2002 Steven Feuerstein - Page 159
DeleteFile.java
I need the SQL and date-related
packages, so I specify them in
my import list.
Passing Collections to Java, continued.
 Use oracle.sql.ARRAY to pass in the collection. Cast to
STRUCT to extract the individual attribute values (name of
file and timestamp).
public class DeleteFile {
public static int delete (oracle.sql.ARRAY tbl)
throws SQLException {
try {
ResultSet rs = tbl.getResultSet();
for (int ndx = 0; ndx < tbl.length(); ndx++) {
rs.next();
int aryndx = (int)rs.getInt(1);
STRUCT obj = (STRUCT)rs.getObject(2);
Object[] attrs = obj.getAttributes();
String
fileDir = (String)attrs[0];
Timestamp saveDate = (java.sql.Timestamp)attrs[1];
...
}
Copyright 2000-2002 Steven Feuerstein - Page 160
Some Other Cool Extensions
Courtesy of Vadim Loevski, Quest Software
 Zip files from within PL/SQL.
FileOutputStream fout = new FileOutputStream(outfilename);
ZipOutputStream zout = new ZipOutputStream(fout);
ZipEntry ze = new ZipEntry((new File(infilename)).getName());
 Execute any operating system command
Runtime rt = java.lang.Runtime.getRuntime();
Copyright 2000-2002 Steven Feuerstein - Page 161
utlzip.sql
utlcmd.sql
Viewing Java Output
 Java provides a "print line" method:
System.out.println
– Call it within methods and output will display in your Java
environment...but what if you are running this code from within
a PL/SQL wrapper?
System.out.println (my_object)
 Redirect the output to the DBMS_OUTPUT
buffer.
SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT (1000000);
Copyright 2000-2002 Steven Feuerstein - Page 162
Execution Model Options
Execution Model Options
for Oracle8i PL/SQL:
Invoker and Definer Rights
Copyright 2000-2002 Steven Feuerstein - Page 163
Some background...
 Prior to Oracle8i, whenever you executed a stored
program, it ran under the privileges of the account
in which the program was defined.
– This is called the …
Definer Rights Model
 With Oracle8i, you can now decide at compilation
time whether your program or package will execute
in the definer's schema (the default) or the schema
of the invoker of the code.
– This is called the …
Invoker Rights Model
Copyright 2000-2002 Steven Feuerstein - Page 164
About Definer Rights
 Allows you to
centralize access to
and control of
underlying data
structures.
 Ignores roles and
relies on directlygranted privileges.
 But it can be a source
of confusion and
architectural
problems.
Copyright 2000-2002 Steven Feuerstein - Page 165
OE Code
Sam_Sales
Order_Mgt
Place
Close Old
Orders
Cancel
OE Data
Orders
X
Cannot alter
table directly.
Note: Oracle built-in packages have
long had the capability of running
under the invoker's authority.
Problems with Definer Rights
 Deployment & maintenance
– Must install module in all remote databases where needed
– In some databases, each user has own copy of table(s), requiring
copy of stored module
 Security
– No declarative way to restrict privileges on certain modules in a
package -- it's all or nothing, unless you write code in the package to
essentially recreate roles programmatically.
– Difficult to audit privileges
 Sure would be nice to have a choice...and now you
do!
Copyright 2000-2002 Steven Feuerstein - Page 166
Oracle8i Invoker Rights
 For top level modules:
CREATE [ OR REPLACE ] <module type>
[ AUTHID { DEFINER | CURRENT_USER } ]
AS ...
 For modules with separate spec and body,
AUTHID goes only in spec, and must be at
the package level.
– Holds true for packages and object types.
Copyright 2000-2002 Steven Feuerstein - Page 167
"Reflection" Capability of
Invoker Rights
 With invoker rights, you can execute code owned
by another schema, yet have all references to data
structures "reflect back" into your own schema.
Central Code schema
PACKAGE acct_mgr
make
AUTHID
CURRENT_USER
modify
destroy
...FROM accounts
WHERE...
Copyright 2000-2002 Steven Feuerstein - Page 168
User/Data schema
PROCEDURE mng_account IS
BEGIN
...
code.acct_mgr.destroy(...);
END;
accounts table
When Invoker Rights Applies
 Resolution against invoker's privileges is made
for these statements:
– SELECT, INSERT, UPDATE, and DELETE data manipulation
statements
– The LOCK TABLE transaction control statement
– OPEN and OPEN-FOR cursor control statements
– EXECUTE IMMEDIATE and OPEN-FOR-USING dynamic
SQL statements
– SQL statements parsed using DBMS_SQL.PARSE()
 For all other statements, resolution is by the
owner's privileges.
– This includes ALL code references.
Copyright 2000-2002 Steven Feuerstein - Page 169
Roles and Privileges
 With definer rights, roles are disabled and
ignored.
– All references are resolved against directly granted privileges.
 With invoker rights, roles are enabled and
used for privilege checking.
– You can even use dynamic SQL to set roles for the session,
altering how the reference is resolved at run-time.
– Exception: if the CURRENT_USER programs was called
directly or indirectly by a definer-rights subprogram.
invrole.sql
Copyright 2000-2002 Steven Feuerstein - Page 170
PL/SQL Now Uses Roles!
Change the
current role
and view
tables.
DECLARE
PROCEDURE setrole (role_in IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.put_line (
'Set role to '
|| role_in);
SYS.DBMS_SESSION.set_role (role_in);
scott.showcount; scott.showcount (TRUE);
END;
BEGIN
setrole ('invoker_system_emp');
setrole ('invoker_scott_emp');
END;
The output...
Behavior
changes with
the role
Set role to invoker_system_emp
count of emp_thru_role = 1
Error counting emp_thru_role =
ORA-00942: table or view does not exist
Set role to invoker_scott_emp
Error counting emp_thru_role =
Copyright 2000-2002 Steven Feuerstein - Page 171 ORA-00942: table or view does not exist
count of scott.emp_thru_role = 14
Compiling with "Template" Objects
 If you are writing code with the intention of
relying on invoker rights, the data object
referenced may not be present in the code's
schema.
– You need some kind of "template" against which to
successfully compile the code.
 Two options:
– Create a synonym to any of the possible resolved objects.
– Create a local, "dummy" object to allow the code to compile,
knowing that it will never be used at run-time.
Copyright 2000-2002 Steven Feuerstein - Page 172
Invoker-Definer Precedence
 If the first program in the execution stack is
defined with invoker rights, then it executes
under the session user's authority.
 When and if a definer rights program is called
in the stack, all subsequent calls in the stack
are resolved according to the privileges of that
program.
– AUTHID CURRENT_USER is ignored until the definer rights
program terminates and passes control back to the invoker
rights program.
invdefinv.sql
invdefinv.tst
irdynsql.sql
Copyright 2000-2002 Steven Feuerstein - Page 173
Oracle9i Release 2
New and Improved UTL_FILE
 With UTL_FILE, you can now:
–
–
–
–
UTL_FILE.FREMOVE
UTL_FILE.FRENAME
UTL_FILE.FCOPY
UTL_FILE.FGETATTR
length
Remove a file
Rename a file, and also in effect move files
Copy all or part of one file to another
Retrieves attributes of the file, such as its
 You can also use a database DIRECTORY to
specify the location of the file; UTL_FILE_DIR will
be ignored!!!!
Copyright 2000-2002 Steven Feuerstein - Page 174
COPY A FILE
DECLARE
file_suffix
VARCHAR2 (100)
:= TO_CHAR (SYSDATE, 'YYYYMMDDHHMISS');
BEGIN
-- Copy the entire file...
UTL_FILE.fcopy (
src_location
=> 'DEVELOPMENT_DIR',
src_filename
=> 'archive.zip',
dest_location
=> 'ARCHIVE_DIR',
dest_filename
=>
'archive'
|| file_suffix
|| '.zip'
);
END;
fcopy.sql
fileIO92.pkg
 You can specify an operating system directory or a
database object of type DIRECTORY (as shown
above)
Copyright 2000-2002 Steven Feuerstein - Page 175
REMOVE A FILE
BEGIN
UTL_FILE.fremove (
src_location
=> 'DEVELOPMENT_DIR',
src_filename
=> 'archive.zip'
);
EXCEPTION
-- If you call FREMOVE, you should check explicitly
-- for deletion failures.
WHEN UTL_FILE.delete_failed
THEN
... Deal with failure to remove
END;
 If no error is raised, then you deleted successfully
fremove.sql
fileIO92.pkg
Copyright 2000-2002 Steven Feuerstein - Page 176
RENAME/MOVE A FILE
DECLARE
file_suffix VARCHAR2 (100) := TO_CHAR (SYSDATE, 'YYYYMMDD');
BEGIN
-- Rename/move the entire file in a single step.
UTL_FILE.frename (
src_location
=> 'DEVELOPMENT_DIR',
src_filename
=> 'archive.zip',
dest_location
=> 'ARCHIVE_DIR',
dest_filename
=> 'archive' || file_suffix || '.zip',
overwrite
=> FALSE
);
EXCEPTION
WHEN UTL_FILE.rename_failed
frename.sql
THEN
fileIO92.pkg
... Deal with failure to rename
END;
 You specify target location and file name
Copyright 2000-2002 Steven Feuerstein - Page 177
Obtaining attributes
of a file
CREATE OR REPLACE FUNCTION flength (
location_in
IN
VARCHAR2,
file_in
IN
VARCHAR2
)
RETURN PLS_INTEGER
IS
TYPE fgetattr_t IS RECORD (
fexists
BOOLEAN,
file_length
PLS_INTEGER,
block_size
PLS_INTEGER
);
 How big is a file? What is
its block size? Does the file
exist?
 All valuable questions.
 All answered with a call to
UTL_FILE.FGETATTR.
fgetattr_rec
fgetattr_t;
BEGIN
UTL_FILE.fgetattr (
location
=> location_in,
filename
=> file_in,
fexists
=> fgetattr_rec.fexists,
file_length
=> fgetattr_rec.file_length,
block_size
=> fgetattr_rec.block_size
);
RETURN fgetattr_rec.file_length;
END flength;
Copyright 2000-2002 Steven Feuerstein - Page 178
flength.sql
fileIO92.pkg
Encapsulate and Improve
 The best way to take advantage of the new
UTL_FILE features is to encapsulate or wrap
them inside a layer of enhancing code.
– Provide easy backups of files.
– Higher level programs like "change the extension of my file"
– Improved error handling
fileIO92.pkg
Copyright 2000-2002 Steven Feuerstein - Page 179
Other Handy Oracle9i Features
 CASE statement and expression
 Native compilation
Copyright 2000-2002 Steven Feuerstein - Page 180
CASE Statements and Expressions
 Yes! Finally! It is here: the CASE statement!
Plus a CASE expression!
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1]
CASE
END;
WHEN search_condition1 THEN result1
WHEN search_condition2 THEN result2
...
WHEN search_conditionN THEN resultN
[ELSE resultN+1]
END;
Copyright 2000-2002 Steven Feuerstein - Page 181
CASE Example
 Just another
step towards
writing cleaner,
easier to read
and maintain
code.
DECLARE
grade CHAR(1);
appraisal VARCHAR2(20);
BEGIN
...
appraisal :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
WHEN 'D' THEN 'Fair'
WHEN 'F' THEN 'Poor'
ELSE 'No such grade'
END;
...
END;
case1.sql
Copyright 2000-2002 Steven Feuerstein - Page 182
And NVL2, too!
SELECT last_name, salary,
NVL2 (
commission_pct,
/* expr1 */
salary
+ (salary * commission_pct), /* expr2 */
salary
/* expr3 */
) income
FROM employees
WHERE last_name LIKE 'B%';
l_income :=
NVL2 (
rec.commission_pct, /* expr1 */
rec. salary
+ (rec. salary * rec. commission_pct), /* expr2 */
salary;
 If expr1 is not null, NVL2 returns expr2.
 If expr1 is null, NVL2 returns expr3.
Copyright 2000-2002 Steven Feuerstein - Page 183
Native compilation of PL/SQL code
 For compute-intensive programs; does not
affect SQL performance.
 You must explicitly turn the feature on.
– Set PLSQL_COMPILER_FLAGS to NATIVE
– Update the makefile
$ORACLE_HOME/plsql/spnc_makefile.mk to utilize your C
compiler or change the plsql_native_c_compiler parameter.
Copyright 2000-2002 Steven Feuerstein - Page 184
Setting up Native Compilation
 Use ALTER SESSION statement to set relevant
parameters.
CONNECT scott/tiger;
SET serveroutput on;
ALTER SESSION SET plsql_native_library_dir='/home/orauser/lib';
ALTER SESSION SET plsql_native_make_utility='gmake';
ALTER SESSION SET plsql_native_make_file_name=
'/home/orauser/spnc_makefile.mk';
ALTER SESSION SET plsql_compiler_flags='NATIVE';
CREATE OR REPLACE PROCEDURE hello_native_compilation
AS
BEGIN
DBMS_OUTPUT.put_line ('Hello world');
SELECT SYSDATE
FROM DUAL;
END;
Copyright 2000-2002 Steven Feuerstein - Page 185
Native compilation miscellany
SELECT
FROM
WHERE
AND
param_value
user_stored_settings
param_name = 'PLSQL_COMPILER_FLAGS'
object_name = 'MY_PROC';
 Check the compilation type of your program by
querying the USER_STORED_SETTINGS view.
 Cannot use debugger against natively compiled code
 Large numbers of NC programs can affect overall
system performance.
 Transparent integration of native and interpreted
compiled code.
Copyright 2000-2002 Steven Feuerstein - Page 186
So Much to Learn...
 Don't panic -- but don't stick your head in the sand,
either.
– You won't survive as an Oracle7 or Oracle8 developer!
 On the one hand, you simply have to be conversant
with more than just PL/SQL.
– Pick up the basics of Java and XML.
 On the other hand, you can do so much more from
within PL/SQL than you could ever do before!
Copyright 2000-2002 Steven Feuerstein - Page 187