copyright 2000-2002 steven feuerstein - page 1 the brave new world of oracle pl/sql "gotta...
Post on 20-Dec-2015
228 views
TRANSCRIPT
Copyright 2000-2002 Steven Feuerstein - Page 1
The Brave New Worldof Oracle PL/SQL
"Gotta Know, Gotta Use" New Featuresof Oracle8i and Oracle9i PL/SQL
Steven [email protected]
All non-technical views expressed are those of Steven Feuerstein and do not necessarily (and not likely!) reflect those of Quest Software.
Copyright 2000-2002 Steven Feuerstein - Page 2
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 3
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 4
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 5
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 6
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 7
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 8
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_typesIS 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 9
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
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
TYPE defined in PL/SQL
CREATE TABLE employee_denorm ( employee_id INTEGER, salary_history numbers_t);
Collection used in a table
Copyright 2000-2002 Steven Feuerstein - Page 10
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 11
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 12
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.LASTBEGIN 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 13
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– 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.
emplu.*
Copyright 2000-2002 Steven Feuerstein - Page 14
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
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 15
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 ( tag_no INTEGER, NAME VARCHAR2 (60), petcare vet_visits_t, MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN pet_t);/
Continued...multilevel_collections.sql
Collection nested inside object type
Copyright 2000-2002 Steven Feuerstein - Page 16
Collections with Named, Multi-level Collections, continued
DECLARE TYPE bunch_of_pets_t IS TABLE OF pet_t INDEX BY BINARY_INTEGER; my_pets bunch_of_pets_t;BEGIN my_pets (1) := pet_t ( 100, 'Mercury', vet_visits_t ( vet_visit_t ( '01-Jan-2001', 'Clip wings'), vet_visit_t ( '01-Apr-2002', 'Check cholesterol') ) ); DBMS_OUTPUT.put_line (my_pets (1).petcare (2).reason);END;
Outer collection
Inner collection
Copyright 2000-2002 Steven Feuerstein - Page 17
Anonymous Collection Columns
CREATE OR REPLACE PROCEDURE set_steven_nicknamesIS 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)); END;/
If a collection's column has no name, you simply string together index subscripts.
multilevel_collections2.sqlmultdim.*
gen_multcoll.sp
Copyright 2000-2002 Steven Feuerstein - Page 18
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!
Oracle9i Release 2
Copyright 2000-2002 Steven Feuerstein - Page 19
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 array_t1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE array_t2 IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE array_t3 IS TABLE OF NUMBER INDEX BY POSITIVE; TYPE array_t4 IS TABLE OF NUMBER INDEX BY NATURAL; TYPE array_t1 IS TABLE OF NUMBER INDEX BY VARCHAR2(64); TYPE array_t3 IS TABLE OF NUMBER INDEX BY VARCHAR2(32767); TYPE array_t4 IS TABLE OF NUMBER INDEX BY employee.last_name%TYPE;
Copyright 2000-2002 Steven Feuerstein - Page 20
Working with VARCHAR2-Indexed Collections
Specifying a row via a string takes some getting used to, but if offers some very powerful advantages.
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');
continent_population('Australia') := 30000000;END;
assoc_array*.sql
Copyright 2000-2002 Steven Feuerstein - Page 21
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 22
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.
multilevel.sql
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;
Typical sequential load into collection
of records
Multi-level load in single assignment
Copyright 2000-2002 Steven Feuerstein - Page 23
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 24
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, old-
fashioned 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 25
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 26
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), STATIC FUNCTION make ( name IN VARCHAR2, repetitions IN INTEGER := 1) RETURN tmr_t );
Attributes
Methods
tmr.ot
Copyright 2000-2002 Steven Feuerstein - Page 27
Using the Timer Object
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();
Declare multiple object types instances
Invoke object type methods using dot
notation.
Use default and pseudo-constructors functions
to initialize the instances.
thisuser.tst
Copyright 2000-2002 Steven Feuerstein - Page 28
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).
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 29
Type Hierarchy Example
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.
Person
Citizen
Corporation
Employee
supertype/"wider" subtype/"narrower"
Hourly Worker
Salaried Worker
Management
Non-Management
"root" type
person.ot
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 30
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 supertype-
standard, and selectively over-ride that functionality as needed.
Copyright 2000-2002 Steven Feuerstein - Page 31
Let's Build a Type Hierarchy
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
dessert
cake
"root",supertypeof dessert
subtype offood,supertypeof cake
subtypeof dessert
food.ot
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 32
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.
An object instantiated from food_t has three attributes. A dessert object has five attributes. A cake has seven.
food.ot
Copyright 2000-2002 Steven Feuerstein - Page 33
Substitutability of Object Types
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.
"Any object of type cake is also a dessert, is also a food."
Copyright 2000-2002 Steven Feuerstein - Page 34
Populate an Object Table
Create a table of objects of type food (root type).
Populate it with objects at different levels in hierarchy.DECLARE 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 VALUES (cake_t ( 'Marzepan Delight', 'CARBOHYDRATE', 'bakery', 'N', 1634, 8, 'Happy Birthday!' ) );END;
CREATE TABLE sustenance OF food_t;
Substitution of subtypes
Use of constructor to initialize a variable
food.ot
Copyright 2000-2002 Steven Feuerstein - Page 35
Objects in a Collection
Create a table of objects of type food (root type).
DECLARE TYPE foodstuffs_nt IS TABLE OF food_t;
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' ) );BEGIN ...
Insert three different objects in the collection, each of a different type.
Declare a nested table
Copyright 2000-2002 Steven Feuerstein - Page 36
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 37
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);
treat.sql
Copyright 2000-2002 Steven Feuerstein - Page 38
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 39
Overriding to Provide Specificity for Subtypes
Two different calculations for desserts and cakes.
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;
Generic dessert prices are determined by chocolate
content and age. Cake prices are driven by inscription
length and size..
food2.ot
CREATE OR REPLACE TYPE BODY dessert_tIS 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;
Copyright 2000-2002 Steven Feuerstein - Page 40
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 41
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 42
Exploring Dynamic Polymorphism
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. );
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 43
A Visual Representation
The root price function is over-ridden in the dessert subtype.
The cake subtype now simply inherits its price calculation from its dessert supertype.
food
dessert
cake
Price
Price
the "original"
An override
Inheritedcalculation
Copyright 2000-2002 Steven Feuerstein - Page 44
Dynamically Choosing the Right Method
DECLARE TYPE foodstuffs_nt IS TABLE OF food_t;
fridge foodstuffs_nt := foodstuffs_nt ( food_t ('Eggs benedict', ...), dessert_t ('Strawberries and cream', ...), cake_t ('Chocolate Supreme', ...));BEGIN FOR indx IN fridge.FIRST .. fridge.LAST LOOP DBMS_OUTPUT.put_line ( 'Price of ' || fridge (indx).NAME || ' = ' || fridge (indx).price); END LOOP;END;
A collection of foods is populated with three
different object types.
The price invocation is resolved at run-time, and
not necessarily as the food_t.price method.
food3.ot
Copyright 2000-2002 Steven Feuerstein - Page 45
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 46
New and Enhanced Datatypes in Oracle9i
TIMESTAMP and INTERVAL XMLType The ANY* "generic" types
Copyright 2000-2002 Steven Feuerstein - Page 47
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 48
Timestamp Precision
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.
DECLARE checkout TIMESTAMP(3); BEGIN checkout := '1999-06-22 07:48:53.275'; ...END;
Copyright 2000-2002 Steven Feuerstein - Page 49
Working with Time Zones
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.
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;
tzset.sqltzset_show.sql
tzmisc.sqltzglobal_events_local.sql
Copyright 2000-2002 Steven Feuerstein - Page 50
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 INTERVAL YEAR(3) TO MONTH;BEGIN lifetime := INTERVAL '101-3' YEAR TO MONTH; -- interval literal lifetime := '101-3'; -- implicit conversion from character type lifetime := INTERVAL '101' YEAR; -- Can specify just the years lifetime := INTERVAL '3' MONTH; -- Can specify just the months ...END;
Copyright 2000-2002 Steven Feuerstein - Page 51
Function with Interval
Notice the explicit conversion of the age calculation formula to an INTERVAL.
MEMBER FUNCTION age RETURN INTERVAL YEAR TO MONTHIS retval INTERVAL YEAR TO MONTH;BEGIN retval := (SYSDATE - SELF.dob) YEAR TO MONTH; RETURN retval;END;
person.ot
Copyright 2000-2002 Steven Feuerstein - Page 52
Lots of New Functions
New conversion and "right now" capabilities:
EXTRACT NUMTODSINTERVAL NUMTOYMINTERVAL TO_DSINTERVAL TO_YMINTERVAL TO_TIMESTAMP TO_TIMESTAMP_TZ FROM_TZ
SESSIONTIMEZONECURRENT_DATECURRENT_TIMESTAMPDBTIMEZONELOCALTIMESTAMPSYSTIMESTAMPTZ_OFFSET
extract.sql
Copyright 2000-2002 Steven Feuerstein - Page 53
Deploying XML in PL/SQL
Introduction to XML Coding in Oracle8i New Oracle9i capabilities
Copyright 2000-2002 Steven Feuerstein - Page 54
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 vendor-neutral, platform-neutral, standards-based information pathway.
Copyright 2000-2002 Steven Feuerstein - Page 55
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 56
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 57
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 www.xml.org
Copyright 2000-2002 Steven Feuerstein - Page 58
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 59XML and HTTP can connect heterogeneous applications
Oracle 8i
SQL Server
XSLT Stylesheet
Oracle AppsSAP
Apps Message Hub
Web
XML
XML
XML
XML Web
Copyright 2000-2002 Steven Feuerstein - Page 60
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 61
Some Oracle XML Components
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 XML-based data into Oracle tables.
XSQL Pages Technology allowing you to assemble XML data declaratively and then publish that data with XSLT.
Download the Oracle XDK from the Oracle Technology Network; the 9i XDK works for 8i as well.
Copyright 2000-2002 Steven Feuerstein - Page 62
Overview of key Oracle8i technologies for XML
XML Document
Oracle XML
Parser
Oracle XPath
Engine
DOMDOM
XPathXPath
XML InfosetXML Infoset
Oracle XSLT Processor
XSLTXSLT
Oracle XML SQL UtilityOracle 8i
Manipulate
Select/query
ParseSerialize
Save entire document or fragments
in indexed CLOB columns
Transform
Retrieve based on searching XML document structure
Automatically produce XML from results of any SQL query
Oracle interMedia Text
Automatically store XML in a table, view, object view
Copyright 2000-2002 Steven Feuerstein - Page 63
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 64
From Document to Infoset
<?xml version = “1.0”?><transaction><account>89-344</account><buy shares = “100”><ticker>WEBM</ticker></buy><sell shares=“30”><ticker>INTC</ticker></sell></transaction>
Text Document
XML Parser
“Information Set”/<transaction>
<account>
89-344
<buy> Shares = “100”
<ticker>
WEBM
<sell> Shares = “30”
<ticker>
INTC
Copyright 2000-2002 Steven Feuerstein - Page 65
Using the Document Object Model
The DOM API (the xmldom package in PL/SQL) offers a standard set of programs to manipulate an infoset.
XML DocumentSequence of characters
encoding tree structured data following rules specified by the
XML standard
XML InfosetW3C standard data model for the tree-structured information
items in the XML document
DOMDocument Object Model
W3C standard API for constructing and manipulating
an XML information set
can be manipulated using
can be parsed to produce
can be serialized to produce
Copyright 2000-2002 Steven Feuerstein - Page 66
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 67
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 68
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
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 69
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 70
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;
xmlgen.tst
Copyright 2000-2002 Steven Feuerstein - Page 71
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.
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 72
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.
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 73
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>'));
xmltype.sql
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 74
Retrieve XML Data
Note: you must use an alias on the table name.
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>
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 75
Other Examples of Integration
SELECT p.podocument.getclobval () "Document" FROM purchaseorder p WHERE SYS.XMLTYPE.EXTRACT ( p.podocument, '/PurchaseOrder/User/text()').getstringval()
= 'SMITH';
CREATE UNIQUE INDEX i_purchase_order_referenceON purchaseorder p ( SUBSTR(SYS.XMLTYPE.GETSTRINGVAL( SYS.XMLTYPE.EXTRACT(
p.PODOCUMENT, '/PurchaseOrder/Reference/text()')),1,26))
And function-based indexes based on XPath!
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 76
View Based on XML Data
CREATE OR REPLACE VIEW purchaseorderviewAS 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;
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 77
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 78
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 79
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 80
The Beauty and Eleganceof
Native Dynamic SQL
Copyright 2000-2002 Steven Feuerstein - Page 81
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 82
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 83
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 84
Native Dynamic SQL
Prior to Oracle8i, you would use the DBMS_SQL built-in 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 85
EXECUTE IMMEDIATE
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.
EXECUTE IMMEDIATE sql-string
[INTO {define_variable[, define_variables]... | record }]
[USING {IN | OUT | IN OUT] bind argument [, {IN | OUT | IN OUT] bind argument]...];
Copyright 2000-2002 Steven Feuerstein - Page 86
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_USERIS CURSOR type_cur IS SELECT object_type FROM USER_OBJECTS WHERE object_name LIKE UPPER (nm); type_rec type_cur%ROWTYPE;BEGIN OPEN type_cur; FETCH type_cur INTO type_rec; IF type_cur%FOUND THEN EXECUTE IMMEDIATE 'DROP ' || type_rec.object_type || ' ' || nm; END IF;END;
dropwhatever.spcreind81.sphealth$.pkgsettrig.sp
Copyright 2000-2002 Steven Feuerstein - Page 87
COUNT(*) For Any Table
Here's a handy and simple utility based on NDS:
IF tabCount ('citizens', 'insured = ''NO''') > 40,000,000THEN DBMS_OUTPUT.PUT_LINE ( 'Not the best health care system in the world....');END IF;
tabcount81.sfcompare with:
tabcount.sf
CREATE OR REPLACE FUNCTION tabCount ( tab IN VARCHAR2, whr IN VARCHAR2 := NULL, sch IN VARCHAR2 := NULL) RETURN INTEGERIS retval INTEGER;BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || NVL (sch, USER) || '.' || tab || ' WHERE ' || NVL (whr, '1=1') INTO retval; RETURN retval;END;
Specify schema, table and WHERE clause...
Copyright 2000-2002 Steven Feuerstein - Page 88
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;
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;
Different table for
each location
Copyright 2000-2002 Steven Feuerstein - Page 89
PROCEDURE add_profit_source ( hosp_name IN VARCHAR2, pers IN Person, cond IN preexisting_conditions)ISBEGIN EXECUTE IMMEDIATE 'INSERT INTO ' || tabname (hosp_name) || ' VALUES (:revenue_generator, :revenue_inhibitors)' USING pers, cond;END;
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.
health$.pkg
Copyright 2000-2002 Steven Feuerstein - Page 90
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; DBMS_OUTPUT.PUT_LINE (val); END LOOP; CLOSE cv;END;
showcol81.spndsutil.pkg
Copyright 2000-2002 Steven Feuerstein - Page 91
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;BEGIN OPEN cv FOR '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;
New (Oracle9i) pre-defined weak REF
CURSOR type
Copyright 2000-2002 Steven Feuerstein - Page 92
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 run-time.
dynplsql.sqlstr2list.pkg
dynvar81.pkgdyncalc.pkg
utPLSQL
Copyright 2000-2002 Steven Feuerstein - Page 93
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 94
Dangers of Dynamic SQL
Common tradeoff: flexibility vs. performance– Loss of dependency information in the data dictionary
Runtime compilation is always slower than pre-compilation– 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 95
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.– Otherwise that SQL will be executed under the authority
of the owner of the code, not the invoker of the code.
updnval_nds1.spupdnval_nds2.spupdnval_nds3.spwhichsch81.sql
Copyright 2000-2002 Steven Feuerstein - Page 96
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.– Schema elements, like table names, or portions of the SQL statement,
must be concatenated. A caveat: use of bind variables can reduce the
effectiveness of the cost-based optimizer
usebinding.spuseconcat.sp
toomuchbind.sp
Copyright 2000-2002 Steven Feuerstein - Page 97
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.spopenprse.pkg
Copyright 2000-2002 Steven Feuerstein - Page 98
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 99
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)ISBEGIN FOR aDept IN deptlist.FIRST..deptlist.LAST LOOP DELETE emp WHERE deptno = deptlist(aDept); END LOOP;END;
“Conventional binds” (and lots of them!)
Copyright 2000-2002 Steven Feuerstein - Page 100
Oracle server
PL/SQL Runtime Engine SQL Engine
PL/SQL blockProcedural statement executor
SQL statement executor
FOR aDept IN deptlist.FIRST.. deptlist.LASTLOOP DELETE emp WHERE deptno = deptlist(aDept);END LOOP;
Performance penalty Performance penalty for many “context for many “context switches”switches”
Conventional Bind
Copyright 2000-2002 Steven Feuerstein - Page 101
Enter the “Bulk Bind”
Oracle server
PL/SQL Runtime Engine SQL Engine
PL/SQL blockProcedural statement executor
SQL statement executor
FORALL aDept IN deptlist.FIRST.. deptlist.LAST DELETE emp WHERE deptno = deptlist(aDept);
Much less overhead for Much less overhead for context switchingcontext switching
Copyright 2000-2002 Steven Feuerstein - Page 102
Use the FORALL Bulk Bind Statement
Instead of the individual DML operations, you can do this:
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.
PROCEDURE whack_emps_by_dept (deptlist dlist_t)ISBEGIN FORALL aDept IN deptlist.FIRST..deptlist.LAST DELETE FROM emp WHERE deptno = deptlist(aDept);END;
Copyright 2000-2002 Steven Feuerstein - Page 103
Use BULK COLLECT for Queries
CREATE OR REPLACE FUNCTION get_a_mess_o_emps (deptno_in IN dept.depno%TYPE)RETURN emplist_tIS 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;
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 104
Combining FORALL & BULK COLLECT
FUNCTION whack_emps_by_dept (deptlist dlist_t) RETURN enolist_tIS 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;
bulkcoll.sqlbulktiming.sql
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
Copyright 2000-2002 Steven Feuerstein - Page 105
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.
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 106
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 ) RETURNING BULK COLLECT INTO enames; ...END;
Notice that empnos_in is indexed, but enames
is not.
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 107
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); emp_cv sys_refcursor; empnos numlist_t; enames namelist_t; 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;
Both approaches
work
Still cannot fetch into
collections of records.
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 108
Better Exception Handlingfor 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 SAVE EXCEPTIONS INSERT INTO book values (books_in(indx));EXCEPTION WHEN BULK_ERRORS THEN FOR indx in 1..SQL%BULK_EXCEPTIONS.COUNT LOOP log_error (SQL%BULK_EXCEPTIONS(indx)); END LOOP;END;
Allows processing of all rows, even after an error
occurs.
New cursor attribute, a pseudo-
collection
bulkexc.sql
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 109
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 110
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 111
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
Oracle9i
Copyright 2000-2002 Steven Feuerstein - Page 112
This example shows a record-based insert inside the high-speed FORALL statement
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;
RECORD-BASED INSERTS
Copyright 2000-2002 Steven Feuerstein - Page 113
You can only update the entire ROW, and not a subset via, say, a programmer-defined record type
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;
RECORD-BASED UPDATES
Copyright 2000-2002 Steven Feuerstein - Page 114
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 115
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 116
Logging with ATs
logger.splog81.pkglog81*.tst
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;
retry.pkgretry.tst
Save on successful exit
Avoid inter-dependencies with
the main transaction.
Don't forget to rollback on error!
Copyright 2000-2002 Steven Feuerstein - Page 117
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.
autonserial.sqlauton_in_sql.sqlautontrigger*.sql
Copyright 2000-2002 Steven Feuerstein - Page 118
Making it Easy
Use editor templates and libraries to help developers use autonomous transactions properly...
Copyright 2000-2002 Steven Feuerstein - Page 119
Serialization Example
Must come before any DML statements in your transaction.
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; 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;
Before isolated AT delete 14After isolated AT delete 14After MT commit 0
The output:
Copyright 2000-2002 Steven Feuerstein - Page 120
Enabling DML in SQL-Run Functions
CREATE OR REPLACE FUNCTION nothing RETURN INTEGER ISBEGIN 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; SELECT COUNT(*) FROM emp;
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
The output:
Copyright 2000-2002 Steven Feuerstein - Page 121
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 122
Trigger Example
CREATE OR REPLACE TRIGGER bef_ins_ceo_compBEFORE INSERT ON ceo_compensation FOR EACH ROWDECLARE PRAGMA AUTONOMOUS_TRANSACTION;BEGIN INSERT INTO ceo_comp_history VALUES ( :new.name, 'BEFORE INSERT', SYSDATE); COMMIT;END;/
CREATE OR REPLACE TRIGGER aft_ins_ceo_compAFTER INSERT ON ceo_compensation FOR EACH ROWDECLARE PRAGMA AUTONOMOUS_TRANSACTION;BEGIN INSERT INTO ceo_comp_history VALUES ( :new.name, 'AFTER INSERT', SYSDATE); COMMIT;END;/
Audit before operation
Audit after operation
Copyright 2000-2002 Steven Feuerstein - Page 123
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 124
Exploring Table Functions
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.
SELECT c.name, Book.name, Book.author, Book.abstract FROM Catalogs c, TABLE (te_Book.result_set (c.cat)) Book;
Copyright 2000-2002 Steven Feuerstein - Page 125
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 126
Performance Possibilities
• 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.
Pipelining and parallel execution using table functions
Copyright 2000-2002 Steven Feuerstein - Page 127
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 ...
Continued...
Copyright 2000-2002 Steven Feuerstein - Page 128
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_ntIS 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;END;
SELECT * FROM TABLE (CAST ( pet_family ( pet_t ('Hoppy', 'RABBIT', SYSDATE), pet_t ('Hippy', 'RABBIT', SYSDATE) ) AS pet_nt));
tabfunc3.sql
Copyright 2000-2002 Steven Feuerstein - Page 129
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 130
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...
INSERT INTO tickertable SELECT * FROM TABLE (StockPivot ( CURSOR (SELECT * FROM StockTable)));
Define a REF CURSOR type
Create a function that accepts a cursor of
that type.
Call the function from within SQL, passing to it another query.
tabfunc.sql
Copyright 2000-2002 Steven Feuerstein - Page 131
Working w/Pipelined Functions
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.
CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet PIPELINED
Copyright 2000-2002 Steven Feuerstein - Page 132
Outputting Rows Iteratively
CREATE FUNCTION stockpivot (p refcur_pkg.refcur_t) RETURN tickertypesetPIPELINED IS out_rec tickertype := tickertype (NULL, NULL, NULL); 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 (out_rec); END LOOP; CLOSE p; RETURN;END;/
PIPE ROW sends the data out.
No RETURN of any actual data.
Define as PIPELINED
tabfunc.sql
Copyright 2000-2002 Steven Feuerstein - Page 133
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 134
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 135
Examples of Parallelized Functions
PARALLEL_ENABLE ( Partition p_input_rows BY ANY )
CREATE OR REPLACE FUNCTION Aggregate_Xform ( p_input_rows in My_Types.cur_t) RETURN My_Types.dept_sals_tab PIPELINED
CLUSTER P_INPUT_ROWS BY (dept) PARALLEL_ENABLE ( PARTITION p_input_rows BY HASH (dept) )
ORDER p_input_rows BY (c1, c2) PARALLEL_ENABLE ( PARTITION p_input_rows BY RANGE (c1) )
with
with
with
Simplest form, results don't vary from order in which function gets input rows.
All rows for a given department must go to the same slave, and rows are delivered consecutively.
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 136
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 137
Leveraging Javafrom within
PL/SQL Programs
Copyright 2000-2002 Steven Feuerstein - Page 138
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 139
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 140
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 141
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 142
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 143
Java Stored Procedures (JSPs?!)
Oracle 8i server
Java virtual machine running Java method
PL/SQL cover for Java method
Java applet or app. using JDBC or
SQLJ
Oracle Developer
client (PL/SQL)
OCI or Pro*C client
VB or C++ via OO4O or
ODBC
Net8
Copyright 2000-2002 Steven Feuerstein - Page 144
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 145
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 146
Create Java class(es)
toString method automatically used by System.out.println
main method is used to test the class.
Classes may call other classes
Avoid GUI calls
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); }}
Notes on Java classes
person.java
Copyright 2000-2002 Steven Feuerstein - Page 147
Upload using “loadjava” utility
Example:
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
loadjava -user scott/tiger -oci8 -resolve datacraft/bill/Hello.class
Oracle 8i server
loadjavaJava class
Java source
Java resource
.class fileJava
resource file
.jar file.java file
Copyright 2000-2002 Steven Feuerstein - Page 148
Publish
Example (top-level call spec)
Syntax (simplified)
CREATE OR REPLACE FUNCTION hello_emp (empno_in IN NUMBER)RETURN VARCHAR2AS 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 JAVANAME '<method fullname> (<Java type fullname>, ...)[ return <Java type fullname> ]';
Copyright 2000-2002 Steven Feuerstein - Page 149
Call the wrapped method
Method 1: Call as if PL/SQL module
Method 2: Use 8i SQL CALL statement; for example, from SQL*Plus:
BEGIN DBMS_OUTPUT.PUT_LINE(hello_emp(7499));END;
VARIABLE thename VARCHAR2(12)CALL hello_emp(7499) INTO :thename;PRINT :thename
jsp.sql
Copyright 2000-2002 Steven Feuerstein - Page 150
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 VARCHAR2java.sql.Timestamp DATEjava.math.BigDecimal NUMBERoracle.sql.STRUCT user-defined object type<named type> user-defined object typeoracle.sql.REF object REFerence oracle.sql.ARRAY user-defined collection type
Copyright 2000-2002 Steven Feuerstein - Page 151
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– 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.
utlzip.sql
Copyright 2000-2002 Steven Feuerstein - Page 152
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 153
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 154
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(); }}
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.JFile2.java
Accept the name of a file and return the
length.
Copyright 2000-2002 Steven Feuerstein - Page 155
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 xfileIS FUNCTION length (file IN VARCHAR2) RETURN NUMBER;END;/CREATE OR REPLACE PACKAGE BODY xfileIS 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 156
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 157
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 158
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 xfileIS 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;
xfile3.pkgJFile4.javaxfile4.pkgJFile.javaxfile.pkg
Copyright 2000-2002 Steven Feuerstein - Page 159
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 java.io.*;import java.sql.*;import oracle.jdbc.driver.*;import oracle.sql.*;import java.util.Date;import java.text.*;import java.text.DateFormat.*;
I need the SQL and date-related packages, so I specify them in
my import list.
DeleteFile.java
Copyright 2000-2002 Steven Feuerstein - Page 160
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 161
Some Other Cool Extensions
Zip files from within PL/SQL.
Execute any operating system command
Courtesy of Vadim Loevski, Quest Software
utlzip.sqlutlcmd.sql
Runtime rt = java.lang.Runtime.getRuntime();
FileOutputStream fout = new FileOutputStream(outfilename);ZipOutputStream zout = new ZipOutputStream(fout);ZipEntry ze = new ZipEntry((new File(infilename)).getName());
Copyright 2000-2002 Steven Feuerstein - Page 162
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?
Redirect the output to the DBMS_OUTPUT buffer.
System.out.println (my_object)
SET SERVEROUTPUT ON SIZE 1000000CALL DBMS_JAVA.SET_OUTPUT (1000000);
Copyright 2000-2002 Steven Feuerstein - Page 163
Execution Model Optionsfor Oracle8i PL/SQL:
Invoker and Definer Rights
Execution Model Options
Copyright 2000-2002 Steven Feuerstein - Page 164
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 …
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 …
Definer Rights Model
Invoker Rights Model
Copyright 2000-2002 Steven Feuerstein - Page 165
About Definer Rights
Allows you to centralize access to and control of underlying data structures.
Ignores roles and relies on directly-granted privileges.
But it can be a source of confusion and architectural problems.
Orders
OE Data
OE CodeOrder_Mgt
Cancel
Sam_Sales
PlaceClose Old
Orders
XCannot alter
table directly.
Note: Oracle built-in packages have long had the capability of running
under the invoker's authority.
Copyright 2000-2002 Steven Feuerstein - Page 166
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 167
For top level modules:
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.
Oracle8i Invoker Rights
CREATE [ OR REPLACE ] <module type> [ AUTHID { DEFINER | CURRENT_USER } ]AS ...
Copyright 2000-2002 Steven Feuerstein - Page 168
"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.
User/Data schemaUser/Data schema
accounts table
PROCEDURE mng_account ISBEGIN ... code.acct_mgr.destroy(...);END;
Central Code schemaCentral Code schema
PACKAGE acct_mgr
...FROM accounts WHERE...
destroy
modify
make
AUTHID
CURRENT_USER
Copyright 2000-2002 Steven Feuerstein - Page 169
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 170
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 171
PL/SQL Now Uses Roles!
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;
Change the current role
and view tables.
Behavior changes with
the role
Set role to invoker_system_empcount of emp_thru_role = 1Error counting emp_thru_role = ORA-00942: table or view does not exist
Set role to invoker_scott_empError counting emp_thru_role = ORA-00942: table or view does not existcount of scott.emp_thru_role = 14
The output...
Copyright 2000-2002 Steven Feuerstein - Page 172
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.
Compiling with "Template" Objects
Copyright 2000-2002 Steven Feuerstein - Page 173
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.sqlinvdefinv.tstirdynsql.sql
Copyright 2000-2002 Steven Feuerstein - Page 174
New and Improved UTL_FILE
With UTL_FILE, you can now:– UTL_FILE.FREMOVE Remove a file– UTL_FILE.FRENAME Rename a file, and also in effect move files– UTL_FILE.FCOPY Copy all or part of one file to another– UTL_FILE.FGETATTR Retrieves attributes of the file, such as its
length You can also use a database DIRECTORY to
specify the location of the file; UTL_FILE_DIR will be ignored!!!!
Oracle9i Release 2
Copyright 2000-2002 Steven Feuerstein - Page 175
COPY A FILE
You can specify an operating system directory or a database object of type DIRECTORY (as shown above)
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.sqlfileIO92.pkg
Copyright 2000-2002 Steven Feuerstein - Page 176
REMOVE A FILE
If no error is raised, then you deleted successfully
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 removeEND;
fremove.sqlfileIO92.pkg
Copyright 2000-2002 Steven Feuerstein - Page 177
RENAME/MOVE A FILE
You specify target location and file name
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 THEN ... Deal with failure to renameEND;
frename.sqlfileIO92.pkg
Copyright 2000-2002 Steven Feuerstein - Page 178
Obtaining attributes of a file
CREATE OR REPLACE FUNCTION flength ( location_in IN VARCHAR2, file_in IN VARCHAR2) RETURN PLS_INTEGERIS TYPE fgetattr_t IS RECORD ( fexists BOOLEAN, file_length PLS_INTEGER, block_size PLS_INTEGER ); 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;
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.
flength.sqlfileIO92.pkg
Copyright 2000-2002 Steven Feuerstein - Page 179
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 180
Other Handy Oracle9i Features
CASE statement and expression Native compilation
Copyright 2000-2002 Steven Feuerstein - Page 181
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] END; CASE
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 182
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 183
And NVL2, too!
If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3.
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;
Copyright 2000-2002 Steven Feuerstein - Page 184
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 185
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_compilationASBEGIN DBMS_OUTPUT.put_line ('Hello world'); SELECT SYSDATE FROM DUAL;END;
Copyright 2000-2002 Steven Feuerstein - Page 186
Native compilation miscellany
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.
SELECT param_value FROM user_stored_settings WHERE param_name = 'PLSQL_COMPILER_FLAGS' AND object_name = 'MY_PROC';
Copyright 2000-2002 Steven Feuerstein - Page 187
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!