Notes

Outline

Oracle 8i and 9i PL/SQL Collections and Records A Component of Elegant Database Applications

"Give a man a fish" Give a man a fish, and you'll feed him for a day; give him a religion, and he'll starve to death while praying for a fish.  ~ Unknown Give a man a fish, and he can eat for a day.  But teach a man how to fish, and he'll be dead of mercury poisoning inside of three years.  ~ Charles Haas There's a fine line between fishing and just standing on the shore like an idiot.  ~ Steven Wright Give a man a fish and you feed him for a day; teach him to use the Net and he won't bother you for weeks. ~ Unknown Give a man a fish and you feed him for a day. Teach him how to fish and you feed him for a lifetime. ~ Lao Tzu

Teach them how to fish… http://docs.oracle.com http://pipetalk.quest-pipelines.com/~plsql http://asktom.oracle.com http://otn.oracle.com http://metalink.oracle.com http://www.orafaq.net http://www.experts-exchange.com And Google, of course!

Agenda Past PL/SQL Limitations Record Syntax & Usage Collection Syntax & Usage Collections & Records in Data[base] Design Collections & Records in Application Design Collections & Records in SQL Statements Tips, Tricks and Traps

Past Limitations Storing lists with the related row. Using lists in SQL statements Hash-tables using string key DML using the whole record Native Dynamic SQL hindrances Multi-dimensional arrays Row-by-row fetching and DML using lists Row-by-row fetching and DML using records

Slide 6

Slide 7

Naming Conventions

Record Syntax & Usage Explicit Definition Programmer-defined Explicit Declaration Variables, parameters, function return types

Collection Syntax & Usage Associative Arrays (formerly known as index-by tables or PL/SQL tables) Numerical Index String Index Nested Tables & Variable-size Arrays Local Definition Global Definition Collections in action… Collection Methods

Collections in Data Design Repeating attributes can be stored along with the instance of the entity to which they belong. Attributive entities can be avoided. Collection attributes can be simple or complex. Lean toward relational, using views or object views to “nest” the collection with each row. Due to 3rd party and driver limitations, need a good reason to use collection attributes.

Collections for the DBA Varrays limited and tricky. Ideal use is permanently bounded list whose aggregate bytesize is less than 4000. Varrays cannot be extended beyond their initially defined maxsize. Varray columns can’t be modified using SQL. Nested table is really a physical child table. Both parent and child table have hidden columns and hidden constraints. No inherent order to rows in nested table, unless you add it yourself (object attribute). Nested table can be index-organized. Nested_table_id should be indexed.

Collections & Records in Application Design Package of generic, common types and subtypes, many of which are generic collections and an empty instance of each. Global, generic collections of number, string, date, etc. Utilities for collections: parsing delimited lists and returning collections; iterating collections and returning delimited lists, boolean COUNT check function, etc.

Collections & Records in Application Design API generator: DML APIs use cursors and records as the interface to encapsulate select, insert and delete operations. http://www.stevenfeuerstein.com/puter/gencentral.htm Data Access layer in PL/SQL Least Common Denominator utilities and business logic in PL/SQL Use records as much as possible for PL/SQL to PL/SQL interfaces (except UPDATE APIs) Non-PL/SQL interfaces

Nested vs. Varray vs. Associative Collections Forget Varray is there ;-) Nested More flexible; use in objects, tables and SQL statements Little more cumbersome to use Great for joins, IN/NOT IN, and synchronization Associative Maps, Hash Tables, Dictionaries, intelligent key PK stores, etc. Bi-directional, random and multiple scans. Index can be zero, negative or string Can be created initially sparse

Collections in SQL Nesting MULTISET Unnesting (TABLE operator) CAST COLUMN_VALUE Implicit Joins and Outer Joins NOT IN pitfall SQL*Plus tips

DML with Records DML API routines without records. DML API routines with records. DML API routines with 9i records!

DML with Collections Retrieve nested table Retrieve single entry in nested table Insert nested table Insert single entry into nested table Update nested table Update single entry into nested table Delete nested table Delete single entry from nested table

Bulk Ops with Collections BULK fetching into collections BULK fetching into collections of record BULK DML with collections BULK DML with collections of record

Tips Use aliases for the tables and nested tables when unnesting Treat a nested table as a normal child table in joins Ensure collection has content before accessing it Use coll.FIRST..coll.LAST, not 1..coll.COUNT Use RETURN AS LOCATOR if collection is large and accessed infrequently Use a column alias for COLUMN_VALUE when unnesting anonymous collections Index associative arrays using PLS_INTEGER and %TYPE

Tips If it can be done in straight SQL, do it. 8i: Need to CAST local variables of global collection types back to global in SQL.

Tricks Use SET DESCRIBE DEPTH ALL in SQL*Plus to reveal all the fields in composite datatype columns. /*+ NESTED_TABLE_GET_REFS */ hint. Use at your own risk to directly query and manipulate the contents of a nested table store table. Meant for returning locators to large collections. Instead of local counters, use cursor%ROWCOUNT, coll.COUNT, coll.COUNT+1 or NVL(coll.LAST,0)+1 when looping, extending and populating collections.

Traps Can’t use RETURNING with INSERT Cannot access types owned by other schemas through synonyms (prior to 9.2) Remember that with NOT IN, if any member of the list is NULL, the result is NULL 3rd party support for collections is scanty Bulk Ops w/Collections of Record (COR): No RETURNING * INTO COR , no access to in-bind COR fields in SQL statement, no NDS except SELECT No bulk collecting into associative arrays indexed by strings.

Tools Displaying and editing tables containing: LOBs, objects, scalar collections, composite collections. Debugging and code-generating for routines containing parameters or variables of: objects, records, scalar collections, composite collections.

Contact Info If you’d like to: Correct my errors Give me an earful Download this ppt or the white paper OR, just want to chat, please visit