What's on the Companion Disk?

The disk that accompanies this book is a PC disk (in MS-DOS 
format) containing source code (much of it cross-referenced
to specific code examples in the book), as well as some accompanying 
documentation files and a few full software packages that you may 
find helpful. The goal of providing this material in electronic 
form is to give you a leg up on the development of your own 
PL/SQL programs.

The following table lists the contents of the disk, organized by 
chapter. Most of the files listed here contain examples that 
are duplicates or extensions of examples included in the book. We 
didn't have room for every possible example in the book, however, 
so we've included some additional examples, along with accompanying 
descriptions, on the disk. These are also shown in the following 
table within the appropriate chapter; for example, additional 
character function examples are listed with the other examples 
for Chapter 11, Character Functions. 

For each file, the table shows:

Chapter -- The chapter in which the contents of the file are shown 
or described in the book.

Filename -- The name of the MS-DOS file containing the code/text. 
This filename is also shown in the comment line that begins each 
specific code example in the book.

Description -- A brief description of the contents of the file.


The filenames have extensions that categorize their contents as 
follows:

doc -- Microsoft Word document with text and code 

ff -- Oracle Forms function 

fp -- Oracle Forms procedure 

fpp -- Oracle Forms package 

rpp -- Oracle Reports package

sf -- Stored function 

sp -- Stored procedure 

spb -- Stored package body; specification is in separate file 

spp -- Stored package specification and body, together in one file 

sps -- Stored package specification; body is in separate file 

sql -- SQL script to be executed in SQL*Plus or SQL*DBA 

txt -- Text file

zip -- PKZipped file 


The files are listed below, first by chapter and then in alphabetical 
order.


Chapter 6

varcurs.doc -- Emulation of cursor variable with local modules. 
(Disk only.)

unquein.ff -- Guarantee unique entry with cursors. (Disk only.)

unquein.doc -- Explains how to use unquein.ff in Oracle Forms. 
(Disk only.)

fkval.fp -- Foreign key validation with double fetch.

selupdt.sf -- SELECT FOR UPDATE example.


Chapter 7

highrec.doc -- Explanation of code in highrec.fp file. (Disk only.)

highrec.fp -- Highlight a record in Oracle Forms. (Disk only.)

ofquery.doc -- Explanation of code in postqry.fp and preqry.fp files.
(Disk only.)

postqry.fp -- Generic replacement for Post-Query foreign key lookups.
(Disk only.)

preqry.fp -- Generic replacement for Pre-Query processing of foreign 
keys.  (Disk only.)


Chapter 10

bizdays.sp -- Place business days into a PL/SQL table.

rowexist.sf -- Return TRUE if row exists in PL/SQL table, FALSE 
otherwise.

db2tab1.sql and db2tab2.sql -- Move information from database to PL/SQL 
table.

seqretr.sp -- Demonstration of sequential retrieval of data from PL/SQL 
table.

indretr.sp -- Demonstration of indexed retrieval of data from PL/SQL 
table.

disptabl.sp and disptab2.sp -- Generic display table program; the 
second handles a wider variety of circumstances and display formats.

array.sps and array.spb -- Traditional array emulation with package.

selftune.spp -- Self-tuning foreign key lookup function.


Chapter 11

bitesw.sf -- Bite-and-switch program which pulls the middle word out 
of a three-part string.

betwnstr.sf --  Returns the portion of the string between the 
specified start and end points. A variation on SUBSTR.

parsenm1.sp -- Parse_name procedure which does not handle middle 
names.

parsenm2.sp --  Parse_name procedure which handles middle names.

wordwrap.spp -- Package containing function which takes a long line of 
text and wraps it to a specified length.

testwrap.sql --  PL/SQL script which demonstrates/tests wordwrap.spp.

filltext.sf -- Fill text with spaces to fill a line of specified length.

freqinst.sf -- Counts the number of occurrences of 
a substring in a string.

compundr.doc -- Example of When-Button-Pressed trigger which relies 
on the name of the item pressed. It handles the situation where 
you need to perform comparisons of strings which contain underscores. 
(Disk only.)

framed.sf -- Function to frame string with suffix and prefix to 
specified length. (Disk only.)


Chapter 12

addmths.sf -- Replacement for built-in add_months.

tz.spp -- Package to create a substitute for SYSDATE which uses 
NEW_TIME and time zones to adjust for time differences between 
client and server machines.

sysdate.doc -- Accesses SYSDATE in client-server Oracle Forms 
environment. (Disk only.)


Chapter 14

daternge.sf -- Creates a date range string from two dates.

dmcnvrt.sf -- PL/SQL-only function to convert a string to a date 
using hardcoded formats.

dmcntab.sf -- PL/SQL-only function to convert a string to a date 
using table-based formats.

dmcnvrt.spp -- PL/SQL table version of dm_convert (two programs 
included), which converts a string to a date.

dmcnvrt.doc -- Word document containing explanation of code in the 
various dmcnvrt files. (Disk only.)

dmcnvrt.fp -- Oracle Forms globals-based version of dm_convert (two 
programs included), which converts a string to a date. (Disk only.)


Chapter 15

locklim.spp -- Overlay package for DBMS_LOCK which limits the lock 
numbers that can be used.

commchk.sf -- Function using DBMS_LOCK to determine whether a 
commit was performed.

emptyppe.sp -- Empty the specified pipe.

pipeloop.sp -- Loop which checks for message in pipe every hour.

pipe2tbl.sp -- Copies message from pipe into PL/SQL table.

parallel.sql -- Set of modules which illustrate parallelization of 
code using DBMS_PIPE.

newindex.sp -- Dynamic SQL creates an index.

fillblck.fp --  Fill an Oracle Forms block using dynamic SQL.

openprse.sf -- Generic open and parse function.

dropobj.sp -- Generic procedure to drop an object using dynamic SQL.

fkname.sf -- Generic foreign key lookup using dynamic SQL (multiple 
versions).

showcomp.sp -- Show components of an object's name using built-in 
NAME_RESOLVE.

file2tab.sp -- Load contents of file to PL/SQL table.

getnext.sp -- Get next line, check for exception.

fil2tab2.sp -- Load contents of file to PL/SQL table, this time using 
the get_nextline procedure (getnext.sp).

crefile.sp -- Create a file using UTL_FILE.

filexist.sf -- Function which tells you whether or not a file exists.

linetext.sf -- Search a file to find the line that contains the 
specified text.

nthline.sf -- Get the nth line from a file.

textio.doc -- Explanation and code for the Oracle Developer/2000 
TEXT_IO package. (Disk only.)


Chapter 16

parmlist.doc -- Section on module overloading containing example of 
how to initialize parameter list and overload the add procedure 
calls for Oracle Forms parameters (Disk only.)

parmlist.fpp -- Code for parmlist.doc section. (Disk only.)


Chapter 17

curs_ret.sp -- Package containing numerous examples of package-
based cursors.

Chapter 18

sptimer.sps and sptimer.spb -- Package which allows you to 
calculate elapsed time of actions in PL/SQL programs down to 
the 100th of a second.

odtimer.doc --  Explanation of how to build overlays to the sp_timer 
package for Oracle Forms and Oracle Reports. (Disk only.)

oftimer.fpp -- Oracle Forms package to perform and display subsecond 
timings. (Disk only.)

ortimer.rpp -- Oracle Reports package to perform and display 
subsecond timings. (Disk only.)

do.sps and do.spb -- Overlay package for DBMS_OUTPUT. Offers enhanced 
tracing and debugging of PL/SQL programs.

exchdlr.spp -- Exception handler package containing predefined errors 
and a "cover" module for RAISE_APPLICATION_ERROR.

psparse.sps and psparse.spb --  String parsing package.

pslist.sps and pslist.spb -- List manager package.

psstack.spp --  Stack manager package.

psglobal.sps and plsglobal.spb -- PL/SQL-based globals package.

psgarray.sps and psgarray.spb -- PL/SQL table-based arrays which make 
use of the ps_global package.


Chapter 19

checks.spp -- Example of PL/SQL function in SQL and code to convert 
number to string "naming" that number.


Chapter 20

functmpl.sf -- Standard template for function which reinforces "Single 
RETURN" guideline.

progress.fpp -- Package which implements an Abstract Data Type for a 
progress box in Oracle Forms.


Chapter 21

userdpnd.sql --  SQL*Plus script which shows all objects which are 
dependent on the object you specify.

psobj.sql --  SQL*Plus script which shows all PL/SQL objects stored in 
the database.

recomp.sql -- Script to generate the SQL statements necessary to 
recompile PL/SQL objects stored in the database.

pssize.sql -- SQL*Plus script which displays the contents of the USER_
OBJECT_SIZE view for larger code objects.

srcline.sf -- Returns the source code for the nth line of the 
specified stored program.

srcupd.sp -- Procedure which updates the source in the data dictionary 
tables.


Chapter 22

xrayvizn.zip -- Xray Vision, the better debugger for SQL*Forms. Even 
if you have upgraded to Oracle Forms, you will find the PL/SQL 
code inside XRay Vision of interest. Unzip and follow instructions 
in readme.doc or readme.txt. (Disk only.)

arjy.zip -- Shareware version of Arjy, the Record Group Debugger 
for Oracle Forms, from ArtForms. Unzip and follow instructions 
in readme.doc or readme.txt. (Disk only.)

dbg.doc -- Document describing the dbg package (see dbg.sps and 
dbs.spb) and how to use DBMS_PIPE to build your own debugger 
for PL/SQL. (Disk only.)

dbg.sps and dbg.spb -- Package which uses DBMS_PIPE to provide 
debugging capabilities for PL/SQL. (Disk only.)

custdbg.sp -- Procedure which customizes the debug action. 
(Disk only.)




Alphabetical Listing:

addmths.sf (12) -- Replacement for built-in add_months.

arjy.zip (22) -- Shareware version of Arjy, the Record Group Debugger 
for Oracle Forms, from ArtForms. Unzip and follow instructions 
in readme.doc or readme.txt. (Disk only.)

array.sps and array.spb (10) -- Traditional array emulation with package.

betwnstr.sf (11) --  Returns the portion of the string between the 
specified start and end points. A variation on SUBSTR.

bitesw.sf (11) -- Bite-and-switch program which pulls the middle word 
out of a three-part string.

bizdays.sp (10) -- Place business days into a PL/SQL table.

checks.spp (19) -- Example of PL/SQL function in SQL and code to 
convert number to string "naming" that number.

commchk.sf (15) -- Function using DBMS_LOCK to determine whether a 
commit was performed.

compundr.doc (11) -- Example of When-Button-Pressed trigger that relies 
on the name of the item pressed. It handles the situation where 
you need to perform comparisons of strings which contain underscores. 
(Disk only.)

crefile.sp (15) -- Create a file using UTL_FILE.

curs_ret.sp (17) -- Package containing numerous examples of package-
based cursors.

custdbg.sp (22) -- Procedure which customizes the debug action. 
(Disk only.)

daternge.sf (14) -- Creates a date range string from two dates.

dbg.doc (22) -- Document describing the dbg package (see dbg.sps and 
dbs.spb) and how to use DBMS_PIPE to build your own debugger 
for PL/SQL. (Disk only.)

dbg.sps and dbg.spb (22) -- Package which uses DBMS_PIPE to provide 
debugging capabilities for PL/SQL. (Disk only.)

db2tab1.sql and db2tab2.sql (10) -- Move information from database 
to PL/SQL table.

dmcntab.sf (14) -- PL/SQL-only function to convert a string to a date 
using table-based formats.

dmcnvrt.doc (14) -- Word document containing explanation of code in the 
various dmcnvrt files. (Disk only.)

dmcnvrt.fp (14) -- Oracle Forms globals-based version of dm_convert 
(two programs included), which converts a string to a date. (Disk only.)

dmcnvrt.sf (14) -- PL/SQL-only function to convert a string to a date 
using hardcoded formats.

dmcnvrt.spp (14) -- PL/SQL table version of dm_convert (two programs 
included), which converts a string to a date.

disptabl.sp and disptab2.sp (10) -- Generic display table program; the 
second handles a wider variety of circumstances and display formats.

do.sps and do.spb (18) -- Overlay package for DBMS_OUTPUT. Offers 
enhanced tracing and debugging of PL/SQL programs.

dropobj.sp (15) -- Generic procedure to drop an object using 
dynamic SQL.

emptyppe.sp (15) -- Empty the specified pipe.

exchdlr.spp (18) -- Exception handler package containing predefined 
errors and a "cover" module for RAISE_APPLICATION_ERROR

fil2tab2.sp (15) --  Load contents of file to PL/SQL table, this time 
using the get_nextline procedure (getnext.sp).

file2tab.sp (15) -- Load contents of file to PL/SQL table.

filexist.sf (15) -- Function which tells you whether or not a file exists.

fillblck.fp (15) -- Fill an Oracle Forms block using dynamic SQL.

filltext.sf (11) -- Fill text with spaces to fill a line of specified 
length.

fkname.sf (15) -- Generic foreign key lookup using dynamic SQL 
(multiple versions).

fkval.fp (6) -- Foreign key validation with double fetch.

framed.sf (11) -- Function to frame string with suffix and prefix to 
specified length. (Disk only.)

freqinst.sf (11) -- Counts the number of occurrences of 
a substring in a string.

functmpl.sf (20) -- Standard template for function which reinforces 
"Single RETURN" guideline.

getnext.sp (15) -- Get next line, check for exception.

highrec.doc (7) -- Explanation of code in highrec.fp file. (Disk
only.)

highrec.fp (7) -- Highlight a record in Oracle Forms. (Disk only.)

indretr.sp (10) -- Demonstration of indexed retrieval of data from PL/SQL 
table.

linetext.sf (15) -- Search a file to find the line that contains the 
specified text.

locklim.spp (15) -- Overlay package for DBMS_LOCK which limits the lock 
numbers that can be used.

newindex.sp (15) -- Dynamic SQL creates an index.

nthline.sf (15) -- Get the nth line from a file.

odtimer.doc (18) --  Explanation of how to build overlays to the 
sp_timer package for Oracle Forms and Oracle Reports. (Disk only.)

ofquery.doc (7) -- Explanation of code in postqry.fp and preqry.fp 
files.  (Disk only.)

oftimer.fpp (18) -- Oracle Forms package to perform and display 
subsecond timings. (Disk only.)

openprse.sf (15) -- Generic open and parse function.

ortimer.rpp (18) -- Oracle Reports package to perform and display 
subsecond timings. (Disk only.)

parmlist.doc (16) -- Section on module overloading containing example 
of how to initialize parameter list and overload the add procedure 
calls for Oracle Forms parameters (Disk only.)

parmlist.fpp (16) -- Code for parmlist.doc section. (Disk only.)

parallel.sql (15) -- Set of modules which illustrate parallelization 
of code using DBMS_PIPE.

parsenm1.sp (11) -- Parse_name procedure which does not handle middle 
names.

parsenm2.sp (11) --  Parse_name procedure which handles middle names.

pipe2tbl.sp (15) -- Copies message from pipe into PL/SQL table.

pipeloop.sp (15) -- Loop which checks for message in pipe every hour.

postqry.fp (7) -- Generic replacement for Post-Query foreign key 
lookups.  (Disk only.)

preqry.fp (7) -- Generic replacement for Pre-Query processing of foreign 
keys.  (Disk only.)

progress.fpp (20) -- Package which implements an Abstract Data Type 
for a progress box in Oracle Forms.

psgarray.sps and psgarray.spb (18) -- PL/SQL table-based arrays which 
make use of the ps_global package.

psglobal.sps and plsglobal.spb (18) -- PL/SQL-based globals package.

pslist.sps and pslist.spb (18) -- List manager package.

psobj.sql (21) --  SQL*Plus script which shows all PL/SQL objects 
stored in the database.

psparse.sps and psparse.spb (18) --  String parsing package.

psstack.spp (18) --  Stack manager package.

pssize.sql (21) -- SQL*Plus script which displays the contents of the 
USER_ OBJECT_SIZE view for larger code objects.

recomp.sql (21) -- Script to generate the SQL statements necessary to 
recompile PL/SQL objects stored in the database.

rowexist.sf (10) -- Return TRUE if row exists in PL/SQL table, FALSE 
otherwise.

selftune.spp (10) -- Self-tuning foreign key lookup function.

selupdt.sf (6) -- SELECT FOR UPDATE example.

seqretr.sp (10) -- Demonstration of sequential retrieval of data from PL/SQL 
table.

showcomp.sp (15) -- Show components of an object's name using built-in 
NAME_RESOLVE.

sptimer.sps and sptimer.spb (18) -- Package which allows you to 
calculate elapsed time of actions in PL/SQL programs down to 
the 100th of a second.

srcline.sf (21) -- Returns the source code for the nth line of the 
specified stored program.

srcupd.sp (21) -- Procedure which updates the source in the data 
dictionary tables.

sysdate.doc (12)  -- Accesses SYSDATE in client-server Oracle Forms 
environment. (Disk only.)

testwrap.sql (11) --  PL/SQL script which demonstrates/tests 
wordwrap.spp.

textio.doc(15)  -- Explanation and code for the Oracle Developer/2000 
TEXT_IO package. (Disk only.)

tz.spp (12) -- Package to create a substitute for SYSDATE which uses 
NEW_TIME and time zones to adjust for time differences between 
client and server machines.

unquein.doc (6) -- Explains how to use unquein.ff in Oracle Forms. 
(Disk only.)

unquein.ff (6) -- Guarantee unique entry with cursors. (Disk only.)

userdpnd.sql (21) --  SQL*Plus script which shows all objects which are 
dependent on the object you specify.

varcurs.doc (6) -- Emulation of cursor variable with local modules. 
(Disk only.)

wordwrap.spp 11) -- Package containing function which takes a long 
line of text and wraps it to a specified length.

xrayvizn.zip (22) -- Xray Vision, the better debugger for SQL*Forms. 
Even if you have upgraded to Oracle Forms, you will find the PL/SQL 
code inside XRay Vision of interest. Unzip and follow instructions 
in readme.doc or readme.txt. (Disk only.)