NINE NEW and NIFTY NUANCES OF
Download
1 / 64

Nine New and Nifty Nuances of Oracle 9i PLSQL - PowerPoint PPT Presentation


  • 450 Views
  • Uploaded on

NINE NEW and NIFTY NUANCES OF ORACLE 9 i PL/SQL by Steven Feuerstein [email protected] www.quest.com SOFTWARE USED IN TRAINING Presentations and demonstration scripts for this presentation can be found on the PL/SQL Pipeline: www.quest-pipelines.com

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Nine New and Nifty Nuances of Oracle 9i PLSQL' - niveditha


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Slide1 l.jpg

NINE NEW and NIFTY NUANCES OFORACLE 9i PL/SQL

by Steven Feuerstein

[email protected]

www.quest.com


Software used in training l.jpg
SOFTWARE USED IN TRAINING

  • Presentations and demonstration scripts for this presentation can be found on the PL/SQL Pipeline:

    • www.quest-pipelines.com

    • Quest Experts page for the presentations

    • Oracle PL/SQL archives, miscellaneous, PL/SQL seminar files, and download demo.zip and review filedesc.doc for a description of many of the files

filename.sql


Hey what about oracle8i l.jpg
Hey, what about Oracle8i?

  • You should all be familiar with at least the following key new features of Oracle8i...

  • Autonomous transactions

  • Invoker rights execution model

  • Native dynamic SQL

  • Row-level security


The nine niftiest nuances l.jpg
THE NINE NIFTIEST NUANCES

Nifty 1. Inheritance in object types

Nifty 2. Pipelined, parallel-executed table functions

Nifty 3. TIMESTAMP and INTERVAL

Nifty 4. New operators: CASE, NVL2, COALESCE

Nifty 5. Multi-level Collections

Nifty 6. Associative Tables (Oracle9i Release 2)

Nifty 7. Record-based DML (Oracle9i Release 2)

Nifty 8. The new and improved UTL_FILE (Oracle9i Release 2)

Nifty 9. The XMLType datatype


Integrated sql and pl sql parsers l.jpg
INTEGRATED SQL AND PL/SQL PARSERS

  • From now on, PL/SQL immediately supports all SQL syntax changes to embedded SQL

    • Anything you can do in SQL directly, you will be able to do in PL/SQL

  • The switch to a single parser (and more consistent error checking) means, however, that some of your previously "valid" code might not compile


Pl sql9i strictness can hurt l.jpg
PL/SQL9i STRICTNESS CAN HURT!

  • Pre-9i, PL/SQL permitted some abnormal behaviors that are no longer allowed by Oracle9i, including:

    • Make forward references to RECORD and TABLE types when declaring variables

    • Specify the name of a variable (not a datatype) in the RETURN clause of a function spec

    • Assign values to the elements of an index-by table IN parameter

    • Pass the fields of a record IN parameter to another subprogram as OUT parameters

    • Use the fields of a record OUT parameter on the right-hand side of an assignment statement

    • Use OUT parameters in the FROM list of a SELECT statement


How to ensure backward compatibility l.jpg
HOW TO ENSURE BACKWARD COMPATIBILITY

  • For backward compatibility, you might want to keep this particular Version 2 behavior

    • You can do that by setting the PLSQL_V2_COMPATIBILITY flag

    • On the server side, you can set the flag in two ways:

      • Add the following line to the Oracle initialization file:

      • PLSQL_V2_COMPATIBILITY=TRUE

  • Execute one of the following SQL statements:

    • ALTER SESSION SET PLSQL_V2_COMPATIBILITY = TRUE;

    • ALTER SYSTEM SET PLSQL_V2_COMPATIBILITY = TRUE;


Oracle object types l.jpg
ORACLE OBJECT TYPES

Nifty 1

  • Which best describes your relationship with Oracle's 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


Oracle object types9 l.jpg
ORACLE OBJECT TYPES

  • 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…


An object type example l.jpg
AN OBJECT TYPE EXAMPLE

CREATE TYPE food_t AS OBJECT (

name VARCHAR2(100),

food_group VARCHAR2(100),

grown_in VARCHAR2(100)

);

  • The food type contains three attributes and no methods or programs

  • It is very similar to a CREATE TABLE statement, but it does not create a "container" for data. Rather it is a "template" for data or instances

Attributes


Working with simple objects l.jpg
WORKING WITH SIMPLE OBJECTS

DECLARE

my_favorite_vegetable food_t

:= food_t ('Brussel Sprouts',

'VEGETABLE',

'Farm,Greenhouse,Backyard');

BEGIN

DBMS_OUTPUT.put_line (

my_favorite_vegetable.name);

my_favorite_vegetable.food_group :=

'SATISFACTION';

IF INSTR (

my_favorite_vegetable.grown_in,

'yard')>0

THEN

order_seeds (my_favorite_vegetable);

ENDIF;

END;

Create a new object with a constructor

Read an attribute value

Modify an attribute value

Pass an object as a parameter

objtype.sql


Another object type example l.jpg
ANOTHER OBJECT TYPE EXAMPLE

CREATETYPE tmr_t AS OBJECT (

startTime INTEGER,

endTime INTEGER,

repetitions INTEGER,

name VARCHAR2(2000),

MEMBER PROCEDUREgo,

MEMBER PROCEDUREstop(

show_timing INBOOLEAN:=TRUE),

MEMBER FUNCTION timing RETURNINTEGER,

MEMBER PROCEDURE reset (

name INVARCHAR2:=NULL),

STATIC FUNCTION make (

name INVARCHAR2,

repetitions ININTEGER:=1)

RETURN tmr_t

);

  • The timer object calculates elapsed time

  • It consists of four attributes and five methods

Attributes

Methods

tmr81.ot


Using the timer object l.jpg
USING THE TIMER OBJECT

Declare multiple object types instances

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();

Use default and pseudo-constructors functions to initialize the instances

Invoke object type methods using dot notation

thisuser.tst


New for oracle 9i support for inheritance l.jpg
NEW FOR ORACLE 9i: SUPPORT FOR INHERITANCE

  • 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


Now we can build a typle hierarchy l.jpg
NOW WE CAN BUILD A TYPLE HIERARCHY

  • In Oracle91, an object type can be a supertype, from which other object types are derived

  • Here is 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

"root",

supertype

of dessert

food

subtype of

food,

supertype

of cake

dessert

subtype

of dessert

cake

food.ot


Creating a simple object type hierarchy l.jpg
CREATING A SIMPLE OBJECT TYPE HIERARCHY

  • NOT FINAL indicates that this type can be a supertype

  • UNDER denotes that this type is a subtype

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

An object instantiated from food_t has three attributes. A dessert object has five attributes. A cake has seven.

food.ot


Populate an object table l.jpg
POPULATE AN OBJECT TABLE

CREATETABLEsustenanceOF food_t;

  • Create a table of objects of type food (root type)

  • Populate it with objects at different levels in the hierarchy

DECLARE

my_favorite_vegetables food_t :=

food_t ('Brussel Sprouts','VEGETABLE','farm');

BEGIN

INSERTINTOsustenanceVALUES(my_favorite_vegetables);

INSERTINTOsustenance

VALUES(dessert_t ('Jello','PROTEIN','bowl','N',1887));

INSERTINTOsustenance

VALUES(cake_t (

'Marzepan Delight','CARBOHYDRATE','bakery',

'N',1634,8,'Happy Birthday!'));

END;

Use of constructor to initialize a variable

Substitution of subtypes

food.ot


Accessing attributes in substituted types l.jpg
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


Use treat to identify contrained types l.jpg
USE TREAT TO IDENTIFY CONTRAINED TYPES

/* Show all the meals in which a main course is a dessert */

SELECT*

FROM meal

WHERE TREAT (main_course AS dessert_t)ISNOTNULL;

/* Will fail, since main_course is of food_t type */

SELECT main_course.contains_chocolate

FROM meal

WHERE TREAT (main_course AS dessert_t)ISNOTNULL;

/* 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)ISNOTNULL;

/* Set to NULL any desserts that are not cakes... */

UPDATE meal

SET dessert = TREAT (dessert AS cake_t);

treat.sql


Creating and overriding methods l.jpg
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


Overriding to provide specificity for subtypes l.jpg
OVERRIDING TO PROVIDE SPECIFICITY FOR SUBTYPES

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;

  • Two different calculations for desserts and cakes

food2.ot

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


About polymorphism l.jpg
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


Exploring dynamic polymorphism l.jpg
EXPLORING DYNAMIC POLYMORPHISM

  • The food and dessert types each have a price method, but cake does not. It simply inherits the dessert method

CREATETYPE food_t AS OBJECT (

...attributes...

MEMBER FUNCTION price RETURNNUMBER

)NOT FINAL;

CREATETYPE dessert_t UNDER food_t (

...attributes...

OVERRIDING MEMBER FUNCTION price

RETURNNUMBER

)NOT FINAL)

;

CREATETYPE cake_t UNDER dessert_t (

...attributes...

-- No price method of its own.

);


A visual representation l.jpg
A VISUAL REPRESENTATION

Food

  • The root price function is over-ridden in the dessert subtype

  • The cake subtype now simply inherits its price calculation from its dessert supertype

Price

the "original"

Dessert

Price

An override

Cake

Inheritedcalculation


Dynamically choosing the right method l.jpg
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.

food3.ot

The price invocation is resolved at run-time, and not necessarily as the food_t.price method.


Table functions l.jpg

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

TABLE FUNCTIONS

Nifty 2

INSERTINTOtickertable

SELECT*

FROMTABLE(StockPivot(

CURSOR (SELECT*FROMStockTable)));


Benefits of table functions l.jpg
BENEFITS OF TABLE FUNCTIONS queried like a table by calling the function in the

  • 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


Performance possibilities l.jpg
PERFORMANCE POSSIBILITIES queried like a table by calling the function in the

  • 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


Passing cursors as arguements l.jpg
PASSING CURSORS AS ARGUEMENTS queried like a table by calling the function in the

Define a REF CURSOR type

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

Create a function that accepts a cursor of that type

tabfunc.sql

Call the function from within SQL, passing to it another query


Working with pipelined functions l.jpg
WORKING WITH PIPELINED FUNCTIONS queried like a table by calling the function in the

  • 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


Outputting rows iteratively l.jpg
OUTPUTTING ROWS ITERATIVELY queried like a table by calling the function in the

CREATE FUNCTION stockpivot (p refcur_pkg.refcur_t)

RETURN tickertypeset PIPELINED

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;

/

Define as PIPELINED

PIPE ROW sends the data out

No RETURN of any actual data

tabfunc.sql


Parallel executions and table functions l.jpg
PARALLEL EXECUTIONS AND TABLE FUNCTIONS queried like a table by calling the function in the

  • 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]} )


Timestamps intervals l.jpg
TIMESTAMPS & INTERVALS queried like a table by calling the function in the

Nifty 3

  • 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


Timestamps provide precision and context l.jpg
TIMESTAMPS PROVIDE PRECISION AND CONTEXT queried like a table by calling the function in the

DECLARE

checkout TIMESTAMP(3);

departs_on TIMESTAMP(0) WITH TIME ZONE;

BEGIN

checkout := '1999-06-22 07:48:53.275';

departs_on := TO_TIMESTAMP_TZ (

'29-JAN-2002 12:00:00.0 US/Pacific PST',

'DD-MON-YYYY HH24:MI:SSXFF TZR TZD' );

END;

  • You can record time down to a nanosecond

    • When you declare a TIMESTAMP, you provide a precision (from 0 to 9) for the seconds component

  • You can handle time zone displacement with the TIMESTAMP WITH TIME ZONE datatype.

    • TIMESTAMPWITH LOCAL TIMEZONE automatically uses local time zone.


Interval computations l.jpg
INTERVAL COMPUTATIONS queried like a table by calling the function in the

  • In the example below, declare a variable of type INTERVALYEARTOMONTH, 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;


Lots of new functions l.jpg
LOTS OF NEW FUNCTIONS queried like a table by calling the function in the

  • New conversion and "right now" capabilities:

EXTRACT

NUMTODSINTERVAL

NUMTOYMINTERVAL

TO_DSINTERVAL

TO_YMINTERVAL

TO_TIMESTAMP

TO_TIMESTAMP_TZ

FROM_TZ

SESSIONTIMEZONE

CURRENT_DATECURRENT_TIMESTAMP

DBTIMEZONE

LOCALTIMESTAMP

SYSTIMESTAMP

TZ_OFFSET

extract.sql


Working with tzs and intervals l.jpg
WORKING WITH TZs AND INTERVALS queried like a table by calling the function in the

Figure out the amount of time I have to talk to my boss before I need to leave

DECLARE

boss_free TIMESTAMP(0)WITHTIME ZONE;

steven_leaves TIMESTAMP(0)WITHTIME ZONE;

window INTERVAL DAY(3)TO SECOND(3);

BEGIN

boss_free := TO_TIMESTAMP_TZ (

'29-JAN-2002 12:00:00.0 US/Pacific PST',

'DD-MON-YYYY HH24:MI:SSXFF TZR TZD');

steven_leaves := TO_TIMESTAMP_TZ (

'29-JAN-2002 16:45:00.0 US/Central CST',

'DD-MON-YYYY HH24:MI:SSXFF TZR TZD');

window := steven_leaves - boss_free;

DBMS_OUTPUT.PUT_LINE ( TO_CHAR ( window,'HH:MI:SSXFF'));

DBMS_OUTPUT.PUT_LINE ( ADD_MONTHS (boss_free,-5));

END;

Convert string to timezone

Assign value to the interval

Display converted values

tzset.sql

tzset_show.sql

tzmisc.sql

tzglobal_events_local.sql


Full support for case statement l.jpg
FULL SUPPORT FOR CASE STATEMENT queried like a table by calling the function in the

Nifty 4

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


Case example l.jpg
CASE EXAMPLE queried like a table by calling the function in the

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;

  • Just another step towards writing cleaner, easier to read and maintain code

case1.sql


And nvl2 l.jpg

If queried like a table by calling the function in the expr1 is not null, NVL2 returns expr2

If expr1is null, NVL2 returns expr3

AND NVL2

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;


And coalesce l.jpg

Returns the first non-NULL value in the list queried like a table by calling the function in the

AND COALESCE

l_income :=

COALESCE (

rec.commission_pct,

rec. salary

+ (rec. salary * rec. commission_pct),

salary,

);


Multi level collections l.jpg
MULTI-LEVEL COLLECTIONS queried like a table by calling the function in the

Nifty 5

  • You can now create collections of collections, or collections of records that contain collections, or...

  • Applies to all three types of collections

    • Index-by tables

    • Nested tables

    • Varrying arrays

  • Two scenarios to be aware of:

    • Named collection columns

    • Anonymous collection columns


Collections with named multi level collections l.jpg
COLLECTIONS WITH NAMED, queried like a table by calling the function in the 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);

/

multilevel_collections.sql

Continued...


Collections with named multi level collections44 l.jpg
COLLECTIONS WITH NAMED, queried like a table by calling the function in the MULTI-LEVEL COLLECTIONS

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


Anonymous collection columns l.jpg

If a collection's column has no name, you simply string together index subscripts

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

END;

/

multilevel_collections2.sql

multdim.*

gen_multcoll.sp


Associative tables oracle9i release 2 l.jpg

Nifty 6 together index subscripts

ASSOCIATIVE TABLES (ORACLE9i RELEASE 2)

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;

continent_population('Antarctica') :=

1000; -- Creates new entry

continent_population('Antarctica') := 1001;

-- Replaces previous value

END;

Now you can index by VARCHAR2 as well as INTEGER!

assoc_array*.sql


Record based dml l.jpg
RECORD-BASED DML together index subscripts

Nifty 7

  • 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


Record based inserts l.jpg
RECORD-BASED INSERTS together index subscripts

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


Record based updates l.jpg
RECORD-BASED UPDATES together index subscripts

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


New and improved utl file l.jpg

Nifty 8 together index subscripts

NEW AND IMPROVED UTL_FILE

  • UTL_FILE gets a long-needed upgrade and facelift in Oracle9i Release 2

  • 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


Copy a file l.jpg
COPY A FILE together index subscripts

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;

  • You can specify an operating system directory or a database object of type DIRECTORY (as shown above)


Remove a file l.jpg
REMOVE A FILE together index subscripts

  • 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 remove

END;


Rename move a file l.jpg
RENAME/MOVE A FILE together index subscripts

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 rename

END;

  • You specify target location and file name


Obtaining attributes of a file l.jpg
Obtaining attributes of a file together index subscripts

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

);

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.


Xmltype and the xdb l.jpg

Nifty 9 together index subscripts

XMLType and the XDB

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

  • Oracle offers a system-defined object type that has predefined member functions available to extract XML nodes and fragments.

Oracle9i Release 2: the "XML Database"


Set of xml type methods l.jpg
SET OF XML TYPE METHODS together index subscripts

  • 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


Deposit xml docs to table l.jpg

Create a table with an XMLtype column and insert values with the CreateXML procedure

DEPOSIT XML DOCS TO TABLE

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>'));

Note: in 9i Release 2, you no longer need the SYS. prefix

xmltype.sql


Retrieve xml data l.jpg
RETRIEVE XML DATA the CreateXML procedure

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, since XMLType is an object type.


Other examples of ntegration l.jpg
OTHER EXAMPLES OF NTEGRATION the CreateXML procedure

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)

)


View based on xml data l.jpg
VIEW BASED ON XML DATA the CreateXML procedure

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


Don t forget about generic datatypes l.jpg
DON’T FORGET ABOUT: GENERIC DATATYPES the CreateXML procedure

  • Generic datatypes allow you to create "anonymous" TYPES, useful for highly generic table functions

    • SYS.ANYTYPE: description of any SQL type

    • SYS.ANYDATA: instance of a given type, with data, plus a description of the type. Persists in DB

    • SYS.ANYDATASET: description of a given type plus a set of data instances of that type. Persists in DB

  • The DBMS_TYPES package provides a set of constants to use with these generic, opaque datatypes

  • Very minimal documentation but lots and lots of potential

    • First real "reflection" capability in PL/SQL since DBMS_SQL.DESCRIBE_COLUMNS

rdbms/admin/dbmsany.sql

dbmsany.sql (demo script)


So much to learn l.jpg
SO MUCH TO LEARN the CreateXML procedure

  • 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 (the latter being the most important)

  • On the other hand, you can do so much more from within PL/SQL than you could ever do before!


Slide63 l.jpg

QUESTIONS AND ANSWERS the CreateXML procedure

Don't forget to visit

www.quest-pipelines.com

and click on

Quest Experts Page


Slide64 l.jpg

THANK YOU the CreateXML procedure

FOR LISTENING


ad