1 / 64

NINE NEW and NIFTY NUANCES OF ORACLE 9 i PL/SQL by Steven Feuerstein steven.feuerstein@quest.com www.quest.com

NINE NEW and NIFTY NUANCES OF ORACLE 9 i PL/SQL by Steven Feuerstein steven.feuerstein@quest.com 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

arleen
Download Presentation

NINE NEW and NIFTY NUANCES OF ORACLE 9 i PL/SQL by Steven Feuerstein steven.feuerstein@quest.com www.quest.com

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. NINE NEW and NIFTY NUANCES OFORACLE 9i PL/SQL by Steven Feuerstein steven.feuerstein@quest.com www.quest.com

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  29. PASSING CURSORS AS ARGUEMENTS 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

  30. WORKING WITH 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

  31. OUTPUTTING ROWS ITERATIVELY 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

  32. PARALLEL EXECUTIONS 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]} )

  33. TIMESTAMPS & INTERVALS 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

  34. TIMESTAMPS PROVIDE PRECISION AND CONTEXT 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.

  35. INTERVAL COMPUTATIONS • 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;

  36. LOTS OF NEW FUNCTIONS • New conversion and "right now" capabilities: EXTRACT NUMTODSINTERVAL NUMTOYMINTERVAL TO_DSINTERVAL TO_YMINTERVAL TO_TIMESTAMP TO_TIMESTAMP_TZ FROM_TZ SESSIONTIMEZONE CURRENT_DATECURRENT_TIMESTAMP DBTIMEZONE LOCALTIMESTAMP SYSTIMESTAMP TZ_OFFSET extract.sql

  37. WORKING WITH TZs AND INTERVALS 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

  38. FULL SUPPORT FOR CASE STATEMENT 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;

  39. CASE EXAMPLE 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

  40. If 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;

  41. Returns the first non-NULL value in the list AND COALESCE l_income := COALESCE ( rec.commission_pct, rec. salary + (rec. salary * rec. commission_pct), salary, );

  42. MULTI-LEVEL COLLECTIONS 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

  43. 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); / multilevel_collections.sql Continued...

  44. COLLECTIONS WITH NAMED, 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

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

  46. Nifty 6 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

  47. RECORD-BASED DML 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

  48. RECORD-BASED INSERTS DECLARE TYPE book_list_t IS TABLE OF books%ROWTYPE; my_books book_list_t := book_list_t(); BEGIN my_books.EXTEND (2); my_books(1).isbn := '1-56592-335-9'; my_books(1).title := 'ORACLE PL/SQL PROGRAMMING'; my_books(2).isbn := '0-596-00121-5'; my_books(2).title := 'ORACLE PL/SQL BEST PRACTICES'; FORALL indx IN my_books.FIRST .. my_books.LAST INSERT INTO books VALUES my_books(indx); END; • This example shows a record-based insert inside the high-speed FORALL statement

  49. RECORD-BASED UPDATES DECLARE my_book books%ROWTYPE; BEGIN my_book.isbn := '1-56592-335-9'; my_book.title := 'ORACLE PL/SQL PROGRAMMING'; my_book.summary := 'General user guide and reference'; my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL'; my_book.page_count := 950; -- new page count for 3rd edition UPDATE books SET ROW = my_book WHERE isbn = my_book.isbn; END; • You can only update the entire ROW, and not a subset via, say, a programmer-defined record type

  50. Nifty 8 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

More Related