|
File Name |
Chapter |
Description |
| Web special |
Generates a date range string to append to SQL statements |
|
| highrec.fp, highrec.doc | Web special | Techniques for highlighting a record in Oracle Forms (written for Forms 4.5) |
| index.html, index.doc | Web special | The list of files you are now reading. |
| preqry.fp, postqry.fp, ofquery.doc | Web special | Generic replacement for Pre-Query and Post-Query foreign key lookups (written for Forms 4.5) |
|
1 |
Performs timing comparisons between bulk processing (FORALL and BULK COLLECT) and row-by-row processing |
|
|
1 |
Demonstration of a technique using SQL*Plus substitution parameters for maintaining multiple sets of code in a single file |
|
|
2+ |
CREATE TABLE statement for books table used in several chapters. |
|
|
2 |
Function that retrieves a book’s title by its ISBN. |
|
|
2 |
Java version of program that calls the PL/SQL booktitle function. |
|
|
2 |
Pro*C version of program that calls the PL/SQL booktitle function. |
|
|
2 |
Perl version of program that calls the PL/SQL booktitle function. |
|
|
2 |
PL/SQL Server Pages version of program that calls the PL/SQL booktitle function. |
|
|
2+ |
Insert various rows into the books table |
|
|
2 |
Absolute minimal list of words to avoid using as PL/SQL identifiers |
|
|
2 |
Function to count the number of words in an input string |
|
|
3 |
Instructions for setting up the free "vim" editor with support for PL/SQL syntax highlighting and automatic indentation |
|
|
4 |
Simple CASE statement solution to the bonus problem. |
|
|
4 |
Searched CASE solution to the bonus problem. |
|
|
4 |
Simple CASE expression example, using boolean_to_varchar2. |
|
|
4 |
Searched CASE expression solution to bonus problem. |
|
|
4 |
IF-THEN-ELSIF example using the BETWEEN predicate, and demonstrating what happens when both an IF and an ELSIF evaluate to true. |
|
|
4 |
Same as ch04_if01.sql, but eliminates the overlap between conditions. |
|
|
4 |
Demonstrates the effects of NULLs in IF statements. Taken from the sidebar on that topic in Chapter 4. |
|
|
5 |
Directory containing code listings for the chapter on loops |
|
|
5 |
Replacement for TO_DATE that avoids the need to provide a format mask by trying a series of formats all stored in a collections. |
|
|
6 |
Working prototype of a generic error handling and raising package. The PL/Vision vision package, PLVexc, offers a much more robust implementation. |
|
|
6 |
Generic message table and supporting package, with a specific application for error message management. It even generates an error package with named constants for pre-defined application specific errors. |
|
|
6 |
Shows how to use v$nls_parameter and multiple language error message table to pass back language-appropriate error messages |
|
|
7 |
Package to make it easier to use the Oracle Advanced Queuing utility |
|
|
7 |
Demonstration of CAST operator to reference/manipulate PL/SQL-based collections from within an SQL statement |
|
|
8 |
Function that offers a spin on SUBSTR: returns the sub-string between start and end locations |
|
|
8 |
Shows the difference between blank-padded string comparisons and non-blank-padded string comparisons. |
|
|
8 |
Demonstrates that empty strings are NULL. |
|
|
8 |
The four Unicode-related examples of the built-in INSTR function. Check comments in file before executing these examples. |
|
|
8 |
Code example from the "Is Fixed-Length Really Fixed?" sidebar. Check comments in file before executing this example. |
|
|
8 |
The example for the built-in TO_MULTI_BYTE function. Check comments in the file before executing this example. |
|
|
8 |
Definitive source for information on Unicode (UTF-8, UTF-16, etc). |
|
|
8 |
The place to go to look up the value for a Unicode code point. |
|
| http://www.unicode.org/charts/PDF/U1D100.pdf |
8 |
The Unicode encoding chart that happens to show the musical G clef character |
|
10 |
Code from the "Getting Around ADD_MONTH's Month-end Quirk" sidebar. |
|
|
10 |
CAST examples |
|
|
10 |
Demonstrates various date and timestamp literals. |
|
|
10 |
The INTERVAL example involving the good_for table. |
|
|
10 |
Interval value expressions |
|
|
10 |
More examples of interval arithmetic |
|
|
10 |
Adding and subtracting intervals |
|
|
10 |
Multiplying and dividing intervals |
|
|
10 |
The length of service interval example. |
|
|
10 |
Examples of rounding date values using ROUND. |
|
|
10 |
The example of converting time zones to character strings. |
|
|
10 |
Examples of truncating date values using TRUNC. |
|
|
10 |
All the unconstrained interval examples |
|
| 10 |
The source for the time zone region definitions that Oracle uses. Look particularly at tzdata2002c.tar.gz. |
|
|
10 |
"What is Universal Time?", a useful article for anyone wanting to learn about UTC |
|
|
10 |
Another useful web page on UTC. |
|
|
10 |
Why the acronym for Coordinated Universal Time is UTC rather than CUT. |
|
|
10 |
Verifies actual valid range for dates in your installation of Oracle |
|
|
11 |
Implementation of an alternative, hash-based index for collections, including conflict resolution employing a linear probe algorithm. Most useful prior to Oracle9i R2 and the advent of VARCHAR2-indexed collections |
|
|
11 |
Example of VARCHAR2-indexed associative array. |
|
|
11 |
Demonstration of use of collections to emulate bi-directional (random access) cursors |
|
|
11 |
Demonstration of TABLE operator to order contents in PL/SQL collections |
|
|
11 |
Demonstration of use of collections and cursor variables |
|
|
11 |
Generates multi-level collections to determine how many levels of nesting is supported by PL/SQL |
|
|
11 |
Generates code needed to compare two records that have the same structure (%ROWTYPE) as the specified table |
|
|
11 |
Generates a package containing useful associative arrays for the specified database table. |
|
|
11 |
Demonstration of using collections to cache data for queries of static data, guaranteeing that the data is only queried once in a session |
|
| make_colors_superset.sp | 11 | Demonstration of merging contents of two collections into one. |
|
11 |
Demonstrate of Oracle9i Release 2 multi-level collection capabilities. |
|
|
11 |
Demonstrate of Oracle9i Release 2 multi-level collection capabilities. |
|
|
11 |
Replacement for TO_DATE that avoids the need to provide a format mask by trying a series of formats all stored in a collections. |
|
| nosuchrow.sql | 11 | Demonstration of behavior when referencing an undefined row in a collection |
|
11 |
Example of object type containing an attribute that is a collection |
|
|
11, 19 |
Example of use of associative arrays to cache data from a database table and access it very efficiently |
|
|
12 |
Package that demonstrates use of the new ANYTYPE datatype to dynamically determine if the data inside an ANYTYPE column (in the wild_side table) is numeric. |
|
|
12 |
Demonstrates the use of SYS.AnyData. |
|
|
12 |
Create a BFILE locator and access the data in the BFILE. |
|
|
12 |
Use a BFILE and the DBMS_LOB.LOADCLOBFROMFILE function to load data into a LOB column. |
|
|
12 |
Shows the difference between an empty LOB and NULL LOB. |
|
|
12 |
Writing directions for Munising Falls into a CLOB |
|
|
12 |
Read back the directions that ch12_clob02.sql wrote to the waterfalls table. |
|
|
12 |
Shows various examples of SQL semantics being applied to CLOB values. Be sure to run ch12_clob02.sql first in order to populate the waterfalls table with the data read by this example. |
|
|
12 |
This is the HttpUriType example. |
|
|
12 |
The two UROWID-related examples |
|
|
12 |
Creates the waterfalls table, and other objects used for all the LOB examples. READ COMMENTS IN FILE FIRST! |
|
|
12 |
Create and populate the falls table used in the XMLType examples. |
|
|
12 |
Extract and display the URL for Munising Falls. |
|
|
12 |
HTML file used by the examples of creating BFILE locators and of accessing BFILEs. |
|
| Tannery Falls_files | 12 | Contains image and other files referenced by "Tannery Falls.html". These files are necessary in order for the Tannery Falls web page to display correctly. |
|
12 |
Used by the example in which a BFILE is used to load data into a LOB column. |
|
|
13 |
Demonstration of how a deadlock can occur when working with autonomous transactions |
|
|
13 |
Simple demonstrations of what happens when you do not make sure to COMMIT or ROLLBACK at each exit point of an autonomous transaction. |
|
|
13 |
Demonstration of the impact of the request to serialize visibility of committed transactions when working with autonomous transactions |
|
|
13 |
Demonstration of impact of autonomous transactions with database triggers |
|
|
13 |
Demonstration of SAVE EXCEPTION syntax to perform enhanced error handling with the FORALL statement (Oracle9i Release 2 only) |
|
|
13 |
Logging package used autonomous transactions, and scripts to test the functionality |
|
|
13 |
Demonstrates use of autonomous transactions to implement a "three strikes and you're out" mechanism to access resources |
|
|
13 |
Demonstration deploying a trace function built as an autonomous transaction for use within queries |
|
|
14 |
Demonstration of FORALL with BULK COLLECT |
|
|
15 |
Programs that utilize dynamic SQL to compute COUNTs for the specified column in a table, with option HAVING clause. |
|
|
15 |
A utility built on the DESCRIBE_COLUMNS procedure of the DBMS_SQL package that returns column information about the specified cursor |
|
|
15 |
Example of performing dynamic PL/SQL: a dynamic calculator that will execute the specified function with parameters. |
|
|
15 |
Simple encapsulation of dynamic PL/SQL with EXECUTE IMMEDIATE. |
|
|
15 |
Package that implements "indirect referencing" (a la COPY and NAME_IN of Oracle Forms) for PL/SQL packaged variables. |
|
|
15 |
Simple encapsulation of EXECUTE IMMEDIATE (to execute a DDL statement) that demonstrates proper error handling procedure. |
|
|
15 |
Package that allows you to define and use paths when opening files with UTL_FILE. |
|
|
15 |
Version of filepath package that relies on dynamic PL/SQL (the str2list package) to parse the directory list. |
|
|
15 |
Demonstration of row level security, makes extensive use of dynamic SQL |
|
|
15 |
Single utility package that leverages Native Dynamic SQL to perform a variety of operations |
|
|
15 |
Demonstration of dynamic PL/SQL |
|
|
15 |
Utility that relies on native dynamic SQL to allow to view the WHERE-restricted contents of any column in any table. |
|
|
15 |
Utility that relies on native dynamic SQL to allow to view the WHERE-restricted contents of any date column in any table. |
|
|
15 |
Generic package that will parse any delimited string and deposit the parsed elements into your very own collection |
|
|
15 |
Dynamic SQL function that retrieves the count of rows in the specified table |
|
|
15 |
Demonstration of the flexibility of dynamic SQL: updates any column value in the specified table |
|
|
16 |
A substitute for (built on top of) DBMS_OUTPUT.PUT_LINE that offers additional overloadings and flexibility. |
|
|
16, 19 |
Simplified, stand-alone version of timer (elapsed time) package from PL/Vision. |
|
|
16 |
Demonstration of table function feature |
|
|
16 |
A demonstration of the use of the XMLType as a database table's column |
|
|
17 |
Directory containing a variety of scripts from the chapter on packages |
|
|
17 |
Demonstration of package with overloading to encapsulate a business rule. Notice that one overloading uses the other, so that the formula is never repeated. |
|
|
17 |
An encapsulation around the GET_PARAMETER_VALUE procedure of the DBMS_UTILITY package to make it easier to use. |
|
|
17 |
Scripts to demonstrate the improved retrieval perform with collections vs. an SQL query |
|
|
17 |
Package that demonstrates how to encapsulate business rules and formulas ("construct the full name as last comma first") behind procedures and functions |
|
|
17 |
Demonstration of a package initialization section to extract session-static information just once and cache it |
|
|
17 |
Package that demonstrates the kind of code you will want to write to manage the opening and closing of package-level cursors |
|
|
17 |
Demonstration of package-based cursors |
|
|
17 |
Example of a SERIALLY_REUSABLE package. |
|
|
17 |
Example of comprehensive table encapsulation package |
|
|
17 |
Package that caches the value returned by the USER function, and a test script to show how such a simple action can result in improved application performance |
|
|
17 |
Package with declaration that raises an exception; demonstrates that packages which fail to initialize are still marked as valid and initialized. |
|
|
18 |
Demonstrate firing of BEFORE and AFTER triggers |
|
|
18 |
One trigger handling INSERT, UPDATE and DELETE operations. |
|
|
18 |
Create tables for DML trigger examples. |
|
|
18 |
Demonstration of effect of DELETE CASCADE |
|
| central_error_log.sql | 18 | Centralized error logging with the AFTER SERVERERROR trigger. |
|
18 |
Demonstration of when statement and row level triggers fire |
|
|
18 |
Generic trigger to respond to Oracle Server errors |
|
|
18 |
Fully specified DDL triggering including REFERENCING and WHERE clause |
|
|
18 |
Template of package code to automatically fix space errors trapped by AFTER SUSPEND triggers. |
|
|
18 |
Demonstration of REFERENCING clause |
|
|
18 |
Generates code to use in a trigger to copy NEW and OLD contents to actual PL/SQL records that can be passed as parameters to other programs |
|
|
18 |
Generates a WHEN clause for each column in the specified table |
|
|
18 |
DML trigger setting :NEW values |
|
|
18 |
AFTER SUSPEND trigger to automatically allocate storage extents if required |
|
|
18 |
DDL trigger showing ORA% functions |
|
|
18 |
AFTER SUSPEND trigger to abort current transaction |
|
|
18 |
Multiple DML triggers of same type |
|
|
18 |
Package to work around mutating table error |
|
|
18 |
Trigger to prevent creating any objects |
|
| old_and_new.sql | 18 | Demonstration of use (and renaming) of OLD and NEW pseudo record in triggers. |
|
18 |
One trigger performing INSERT, UPDATE and DELETE |
|
|
18 |
One trigger for INSERT, one for DELETE and one for UPDATE |
|
|
18 |
Demonstration of specific column for DML UPDATE trigger |
|
|
18 |
Create tables for INSTEAD OF trigger demonstrations |
|
|
18 |
INSTEAD OF trigger examples |
|
|
18 |
DDL ALTER trigger preventing altering certain columns |
|
| saynothing.sql | 18 | Demonstration of INSTEAD OF triggers |
| set_score.sql | 18 | Demonstration of BEFORE INSERT trigger for bowling example |
|
18 |
Enable or disable all triggers for the specified table using dynamic DDL |
|
|
18 |
AFTER SUSPEND trigger to reset a users space quota if required |
|
|
18 |
Demonstration of AFTER SERVERERROR trigger |
|
|
18 |
Difference between row and statement level DML triggers |
|
| undroppable.sql | 18 | Creation of a schema-level trigger to halt ability to drop objects in the database |
|
18 |
Simple DDL trigger |
|
|
18 |
DML trigger used to validate record values |
|
|
18 |
Schema-level trigger that traces alterations to columns |
|
|
18 |
Schema-level trigger that demonstrations the use of IS_DROP_COLUMN to determine if a column is being dropped. |
|
|
18 |
Trigger that shows how to query information about privileges in an AFTER GRANT trigger |
|
|
19 |
Script that compares the performance of various ways to answer the question "Is there at least one row satisfying my requirement?" |
|
|
19 |
Displays the list of PL/SQL stored code objects in the data dictionary |
|
|
19 |
Quality control program that shows the names of all procedures and functions that do not have an exception section |
|
|
19 |
Sample query against DBMS_PROFILER results tables to show the slowest running lines of code |
|
|
19 |
Elapsed time stopwatch or timer built using Oracle object types. See thisuser.tst for an example of using these timers. |
|
|
19 |
Working prototype of a package that will scan the ALL_SOURCE data dictionary view for violations of coding standards |
|
|
20 |
Script demonstrating creation of a transient object type and a means of discovering the definition of a transient object type. |
|
|
20 |
Script demonstrating the differences between the invoker and definer rights execution models. |
|
|
20 |
Single script containing code listings and supplemental statements to demonstrate concepts in Chapter 20. |
|
|
20 |
Examination of the overhead of using invoker rights |
|
|
20 |
Demonstration of how definer rights takes precedence over invoker rights |
|
|
20 |
Demonstration of how invoker rights resolution takes roles into account |
|
|
20 |
Demonstration of how invoker rights works with dynamic SQL |
|
|
20 |
Smart utility that recompiles any invalid code objects in your schema |
|
|
20 |
Definers rights version of a procedure to execute a DDL statement supplied as an input string |
|
|
20 |
Invokers rights version of a procedure to execute a DDL statement supplied as an input string |
|
|
21 |
Function to return VARCHAR2 representation of an input variable of type ANYDATA |
|
|
22 |
Utility to delete files in specified directories that were last modified before a specified date |
|
|
22 |
Create a program to "drop anything" and demonstrate the kinds of errors raised (and handled, using the getErrorInfo procedure) |
|
|
22 |
PL/SQL wrapper for Java delete file functionality found in JDelete.java |
|
|
22 |
Retrieves the Oracle error from a long error stack returned from Java |
|
|
22 |
Deletes a file, used by the PL/SQL wrapper in fdelete.sf |
|
|
22 |
More comprehensive file manipulation class that can be referenced directly through simple PL/SQL wrappers (see xfile.pkg) |
|
|
22 |
Batch file to compile and then run loadjava on a Java class |
|
|
22 |
Data dictionary query to retrieve the full Java names for objects. |
|
|
22 |
Package that shows "my Java" -- Java-related objects in the data dictionary |
|
|
22 |
Utility that uses DBMS_JAVA and DBMS_LOB to display Java source |
|
|
22 |
SQL query that lists the Java objects stored in the database |
|
|
22 |
Shorthand SQL*Plus script for SET SERVEROUTPUT ON |
|
|
22 |
Utility that access Java functionality to execute any operating system command specified. |
|
|
22 |
Utility that access Java functionality to zip or compress the specified file. |
|
|
22 |
Package that acts as a thin PL/SQL wrapper on top of JFile.java, greatly improving the range of file I/O functionality available from within PL/SQL programs |
|
|
23 |
Builds a DLL using Cygwin’s gcc on MS-Windows for the external procedure "shell" demo. |
|
|
23 |
Builds a shared object library using gcc on Unix for the external procedure "shell" demo. |
|
|
23 |
Final version of C program that implements the extprocsh external procedure |
|
|
23 |
PL/SQL call spec for extprocsh external procedure. |
Back to: examples.oreilly.com/oraclep3/