The archives are separated into the following sections:
 

Useful PL/SQL Code   Writings on PL/SQL Topics   Presentations   Miscellaneous	Pipeline Newsletter Articles
	Quest Experts Page

Got something that you want to share with your fellow Pipeliners? Send it in and you might see it next on the PL/SQL Pipeline Archives page!  Email your file as an attachment to plsqlarchive@quest-pipelines.com with a brief explanation of what it is. Please keep the file size as small as possible for easier downloading, (no files over 1MB in size). Use the archives below to better understand the type of material that we are looking for. Thank you.

• Code Search Utility (2/25/02)
• Package for Use with RAISE_APPLICATION_ERROR (12/11/01)
• Oracle PL/SQL Programming Companion Software
• Oracle Built-in Packages Companion Software
• A More Sophisticated Recompile Utility
• Knowledge Xpert Trial Software
• Add Value to DBMS_SESSION
• DBMS_PIPE Utility Programs
• Transpose Table Information
• Hex-Decimal Conversion
• Trace/Debug package
• Grants and Revokes
• Convert a Number to a String...
• Number To Words Conversion Function...
• Database Buffer Cache Package
• Execution Call Stack Analysis
• Free Easy to use 'measure CPU per Query' Utility
• Script to Transfer Source Code to Files
• Convert Base of Character using DUMP Function
• Table information
• Using Order By with Connect By
• Unix to Oracle Date Conversion
• Preserving Indentation in HTML
• Unload a Record to be a SQL INSERT Statement
• Generic String Parser
• Package to Convert Between Numeric Formats
• Utility for Table Comparison
• Create or Replace Procedure table_sizing
• Create or Replace Package Groups
• Passing NEW and OLD Pseudo Records to Procedures
• Build an Alternate Index into an Index-By Table
• Performance Testing and Table Types
• Schema Comparison Utility
• Source File Pre-Processing Utility
• Improved File I/O in PL/SQL Using Java
• CSV File Utility

Code Search Utility
By Michael Broughton (mwbroughton@rogers.com)

As a developer, you often inherit applications which have been documented poorly if at all. Regularly the first thing you need to figure out are things like "What sets this value". Of course, you can check dba_dependencies, but that doesn't get you to the column level of granularity, or to the Procedure level within a package. And if the original developer used "%type" specifications, there could be a whole lot of dependencies. You can quickly search all_source, but what if the value is set in a trigger? Besides, printing out the one line from all_source is rarely as informative as what you want and you wind up manually opening each stored procedure, searching for line 657, and seeing if that was the hit you wanted.

This code does the legwork for you. It checks all_source, trigger bodies, and view bodies for a case-insensitive match in either a single schema, or all of the schemas. When it finds a hit, it prints out enough of the surrounding code to give you a good sense of what it is being used for in this instance, and if the hit is in a package body it tries to figure out the subprogram name as well.

Format: sql file, 17K.
search.sql

Package for Use with RAISE_APPLICATION_ERROR
By Steven Feuerstein

The msginfo.pkg file contains a set of DDL statements that:

1. create a table to hold application-specific error information, such as error number (between -20999 and -20000), error name, message, etc.
2. create the msginfo package, which you can use to generate a best practice-based package containing named constants and exceptions for all of your application-specific exceptions.

Format: pkg file, 4K.
msginfo.pkg

Oracle PL/SQL Programming Companion Software
By Steven Feuerstein, Bill Pribyl, and Fred Polizo

This zip file contains all of the files referenced in the 2nd edition of Oracle PL/SQL Programming by Steven Feuerstein and Bill Pribyl. This file also contains the Shareware version of Arjy (the Record Group Debugger for Oracle Forms, from ArtForms) and XRay Vision (a debugger for SQL*Forms that lets you view and modify the variables in your programs) that was not able to be included on the book's diskette.

Format: pkzipped file, 505K.
oppfiles.zip

Oracle Built-in Packages Companion Software
By Steven Feuerstein, Charles Dye, and John Beresniewicz

This zip file contains all of the files referenced in Oracle Built-in Packages by Steven Feuerstein et al. The file offers point and click access to approximately 175 files of source code and documentation prepared by the authors.

Format: pkzipped file, 999K.
obip.zip

A More Sophisticated Recompile Utility
By Solomon Yakobson

Solomon has shared with us a robust, fully-developed utility to recompile any invalid PL/SQL program units. His solution makes extensive use of DBMS_SQL and also the Oracle dependency views so that programs are recompiled in the order necessary to resolve any inter-dependencies.

Format: zipped file, 7K.
syrecomp.zip

Knowledge Xpert Trial Software

Knowledge Xpert for PL/SQL (30 Day Trial Version)

This product contains over 1,400 topics covering the full lifecycle of Oracle PL/SQL development. Detailed topics are loaded with best practices for understanding and implementing syntax, triggers, stored procedures, DBMS_packages, and also includes compiling, testing, tuning and debugging rules. With over 1000 reusable code examples, this customizable online reference is designed to help you rapidly write and deploy SQL-based and event-triggered PL/SQL code on both the client- and server-side of Oracle applications. Click Here to Download.

Knowledge Xpert for Oracle (30 Day Trial Version)

Answers 70 to 80 percent of your daily Oracle administration questions with "how-to" techniques and insights on over 3,200 topics. Updated biannually with the knowledge of industry experts Mike Ault, Peter Corrigan, Mark Gurry, Hugo Toledo, and other renowned authors. Includes over 200 time-saving administration scripts and utilities, plus the companion product Instant Messages. Click Here to Download.

Add value to DBMS_SESSION

The session.zip file contains two files: mysess.spp and grantv$.sql. The mysess.spp file offers a very useful encapsulation around the DBMS_SESSION built-in package. Exerpted from "Oracle Built-in Packages" (O'Reilly and Associates, written by Steven Feuerstein, Charles Dye and John Beresniewicz), this package was created by John Beresniewicz to add value to DBMS_SESSION with procedures and functions that allow you to:

• Obtain the unique session ID function from within a function called in SQL.
• Close any open links which can be closed, or close all open links by issuing a COMMIT to close the current transaction and free the links for closure.
• Set SQL trace on or off for your session and also include a "tag" in the trace file so you locate your output easily.
• Reset your session state, setting all packages to their initial state and releasing any available memory.
• Display the amount of UGA (user global area) and PGA (program global area) memory utilized by the current session.

The grantv$.sql script, by the way, shows you how to grant access to the V$ tables that are needed to compile the my_session package.

Format: zip file, 3K.
session.zip

DBMS_PIPE utility programs
By John Beresniewicz

The dbpipe.zip file contains two files: dbpipe.sql and dbpipe2.sql. The dbpipe.spp file contains a package of interesting utilities illustrating use of DBMS_PIPE programs. These utilities include a forwarding program to pass pipe messages from one pipe to another, a peek program to inspect and replace pipe messages, and generic unpack and pack programs.

The dbpipe2.sql file illustrates the use of DBMS_PIPE to implement communications between a PL/SQL background server program and client programs. Clients communicate requests over a database pipe on which the server listens and receive responses on pipes unique to each session. The server can be set to place debugging infofmation into a table.

Both files are exerpted from "Oracle Built-in Packages" (O'Reilly and Associates, written by Steven Feuerstein, Charles Dye and John Beresniewicz). These packages was created by John Beresniewicz to demonstrate how to utilize database pipes and also make it easier to take advantage of DBMS_PIPE functionality.

Our thanks to John B of Savant Corporation for sharing this code. And when you get a chance, check out Oracle Built-in Packages in your nearest technical bookstore. You will find it immensely useful!

Format: pkzipped file, 7K.
dbpipe.zip

Transpose Table Information
By Steven Feuerstein and Solomon Yakobson

This month's archive features a useful procedure that arose from a thread on the PL/SQL Pipeline's Pipetalk bulletin board. A Pipeliner needed to transpose the rows and columns of a table into a different table. After a few back and forths from a number of people, the ever-present and ever-helpful Solomon Yakobson provided a generic solution using dynamic SQL (the DBMS_SQL) package. Steven Feuerstein took his version and "spruced it up" a bit by creating a stand-alone function that will calculate the number of rows in any table (might as well offer as many handy utilities as possible!). The archived zip contains the scripts to create the two procedures (transpose.sp), as well as a test script to demonstrate the core procedure's capability (transpose.tst). Enjoy!

Format: pkzipped file, 2K.
oct98.zip

Hex-Decimal Conversion
By Steven Feuerstein

The attached file contains two implementations of programs that allow you to convert betwen hexadecimal values and decimal/integer values.

Format: SQL file, 2K.
dec2hex.sql

Trace/Debug package
By Steven Feuerstein

This zip demonstrates how you can build a trace/debug package that utilizes either DBMS_OUTPUT (accessible in current session) or DBMS_PIPE (accessible from another session) to display information about progress of your program's execution. The watch package (watch.spp) uses package toggles to determine the target of the output and also whether or not you want to include the execution call stack in your trace. The p_and_l.spp and the watch.tst files will allow you to test the watch package.

Format: zipped file, 2K.
watch.zip

Grants and Revokes
By Dan Clamage
The grants.sql and revokes.sql files contain the definitions of two procedures, grants and revokes, written by Dan "If you haven't crashed the Server, you haven't been trying hard enough" Clamage. The grants procedure grants the specified object privileges to one or all objects in this user's schema of a given type to the given user (grantee). The revokes procedure revokes the specified object privileges from one or all objects in this user's schema of a given type to the given user (revokee). Both the grants/revokes scripts support roles and PUBLIC.

A cautionary note from Dan: "You should be cautious about granting privileges on all tables to all users. In our shop, we have 400+ users and ~400 tables * 4 grants (select/insert/update/delete) which put 640,000 entries into the data dictionary, which filled up the SYS tablespace. The DBA was rather displeased with me for doing this (fortunately on a staged development server). I wouldn't want anyone else to make the same mistake."

Format: SQL files, 6.2K, 6.2K.
grants.sql
revokes.sql

Convert a Number to a String
By Steven Feuerstein

Nitin Ghagare (nitin_ghagare@hotmail.com) has very generously allowed the Pipeline to add to its archive his to_words function, which converts a number to a string of words describing that number. Enjoy!

Format: sql file, 3K.
Amt-to-w3.sql

Number To Words Conversion Function
By Solomon Yakobson

This function converts into words any number with whole and fractional parts not exceeding 38 digits each. For converting dollar amounts, the fractional part must not exceed 2 digits.

Format: PLSQL Function, 1K.
to_words.plf

The Database Buffer Cache Package
By Darryl Hurley, dhurley@mdsi.bc.ca

The DBBC package provides a means to quickly view the current contents of Oracle's (D)ata(B)ase (B)uffer (C)ache. This memory resident cache is where Oracle keeps recently accessed database blocks for quick retrieval (consult the Oracle documentation for a more complete explanation).

About Darryl Hurley: Ten years of Oracle Development and DBA experience including two years as an active PipeLiner. Participated in the technical review of PL/SQL Built-ins Pocket Reference and 8i New Features books by Feuerstein et al as well as providing Canadian content for the upcoming Oracle PL/SQL Workbook. He puts his Oracle DBA Certification to good use during the day at Mobile Data Solutions (MDSI) and after hours at his consulting practice (ImpleStrat Solutions). He can be reached at dhurley@mdsi.bc.ca or implestrat@yahoo.com.

Format: zipped file, 9K.
dbbc.zip

  Execution Call Stack Analysis 
 

The attached "execution call stack analysis" package will help you analyze the contents of the execution call stack returned by the DBMS_UTILITY.FORMAT_CALL_STACK function. Provided by Darko Egersdorfer, the read_stack procedure of the callstack package parses the contents of the call stack and loads the information into an index-by table. You can then scan this table for the information you need, or simply display it.

Here is an example of using this package:

CREATE OR REPLACE PROCEDURE showstack
IS
	stack_tb  callstack.stack_tab_type;
	my_prog	VARCHAR2(100);
BEGIN
	callstack.READ_STACK(stack_tb);
	FOR i IN 1 .. stack_tb.COUNT
	LOOP
		my_prog := stack_tb(i).name;
		DBMS_OUTPUT.PUT_LINE(my_prog);
	END LOOP;
END;
/

create or replace procedure proc1 is  
begin 
   showstack;
end; 
/

create or replace procedure proc2 is 
begin 
   DBMS_OUTPUT.PUT_LINE ('calling proc1');
   proc1; 
end;
/

create or replace procedure proc3 is 
begin 
   DBMS_OUTPUT.PUT_LINE ('calling proc2');
   proc2; 
end;
/

exec proc3

Free Easy to use 'measure CPU per Query' utility
By Pipeliner Eric Aksomitis, eric.aksomitis@swp.com

This utility reads v$sesstat tables and nicely formulates and formats the results, allowing you to analyze the CPU used by a query. Eric maintains the most up to date version of this package at www.dlcwest.com/~jed/re_answer.shtml

Format: PKG file, 15KB
vsesstat.pkg

Script to Transfer Source Code to Files
By Dan Clamage

This utility doesn't actually use dbms_sql, but it does dynamically generate a script which dynamically generates scripts. Dan has used it to move large numbers of compiled PL/SQL between instances, capture code for wihich no source code exists, document existing program state for a schema, etc. He found it very handy, and interesting to write. Note that it does require 3 other routines, (PUT_LONG_LINE, RECOMPILE, and REV_ENG), which are contained in the SYSUTILS.zip file.

Format: SQL file, 8KB; ZIP file, 25KB
make.sql
sysutils.zip

Convert Base of Character Using DUMP Function
By Bob Rudolf, pcs@series2000.com

Use the DUMP function to convert the base of the specified character. Codes for the base are: 8 for octal, 10 for decimal, 16 for hex (same as their base.

Format: SF file, 1KB
base_convert.sf

Table information
By Dmitri Schaposchnikov, Moscow, Russia dmitri@jet.msk.su

This package shows information about tables and allows view and edit table comments using WEB interface. The program requires OWS 3.0 and testing in oracle 8.04 (Solaris).

Format: PKG file, 18KB
legend.pkg

Using Order By with Connect By

This archive contains two files: orderby_connectby.txt, which contains a very interesting thread regarding how to use an ORDER BY with a CONNECT BY query, and hierarchy.pkg, which contains a package designed by Solomon Yakobson to solve this problem.

Format: text document, 19KB; PKG file, 1 KB
orderby_connectby.txt
hierarchy.pkg

Unix to Oracle Date Conversion
By Giovanni Jaramillo

This archive contains a function enabling the user to quickly convert a Unix date to an Oracle date. This function will work for every timezone.

Format: SF file, 3KB
Unix_To_Oracle_Date.sf

Preserving Indentation in HTML
By Mark Fredericks, PROSOFT

In HTML pages, multiple spaces are displayed as a single space. Using spaces to indent hierarchies is often required. SQL CONNECT BY queries use space indentation to show levels of recursive relations between records. To preserve the correct indentation in HTML, spaces can be replaced with the non-breaking space tag  . The functions below can be used in producing query results that will display correctly when viewed by a browser.

Format: PLS file, 3KB
nbsp_indent.pls

Unload a Record to be a SQL INSERT Statement
By Sean Cassidy

This procedure accepts a table name as well as schema name and WHERE clause. It then generates an INSERT statement for that table and each row identified by the WHERE clause. Output is generated with DBMS_OUTPUT.PUT_LINE.

Format: PLS file, 7KB
unloadtab.sql

Generic String Parser
By Gautham Chandrasekaran

Generic String Parser: provide a delimiter and it returns an index-by table of the individual elements of a string that are separated by the specified delimiter.

Format: PLS file, 4KB
parsestring.pkg

Package to Convert Between Numeric Formats
By James Padfield

PL/SQL user James Padfield offers a handy set of functions to perform a variety of conversions, all bundled into a package. These conversions include:

• BINARY TO INTEGER CONVERSION
• INTEGER TO BINARY CONVERSION
• HEXADECIMAL TO INTEGER CONVERSION
• INTEGER TO HEXADECIMAL CONVERSION
• INTEGER TO ANY BASE CONVERSION
• ANY BASE TO INTEGER CONVERSION

Utility for Table Comparison
By Steven Feuerstein

Here's a handy Oracle8i utility that allows you to determine whether or not two different tables are exactly the same -- in structure and in content (data). It's good for 8i only because it relies on native dynamic SQL. You could easily convert it to use DBMS_SQL to run in Oracle7 and Oracle8. Perhaps a Pipeliner will do just that!

Format: zip file, 2KB
tabeq.zip

Create or Replace Procedure table_sizing
By Bob Rudolf, Precision Computer Systems

This code will compute the table size for unpopulated tables using the procedures outlined in Jason Couchman's DBA Certification Exam Guide. This procedure will fill in the table_size based on the number and types of rows. Modify the cur_in query to suit your needs. This code is contributed to the Pipeline and may be freely copied, altered and given to others.

Format: PLS file, 3KB
table_sizes.SQL

Create or Replace Package Groups
By Anunaya Shrivastava

Anunaya Shrivastava offers a very interesting package that performs grouping of data in the table of your choice and returns that information in an index-by table. The groups.pkg file contains the source code. Groups.doc offers some documentation. Thanks, Anunaya!

Format: PKG file, 8KB
groups.pkg
Format: Word file, 51KB
groups.doc

Passing NEW and OLD Pseudo Records to Procedures
By Steven Feuerstein

A common desire when writing database triggers is to pass the NEW and OLD pseudo records to procedures for processing. Even though NEW and OLD have the same structure as a table-based record (%ROWTYPE), you CANNOT pass them as parameters directly. Instead, you must copy the contents of those pseudo records field by field to a locally declared record and then pass that. Tedious! Boring! So why not generate the code that does the trick for you? This file contains a procedure called GenTrigRec that generates the code you can plug directly into your trigger that populates two local records (myNew and myOld) with the contents of NEW and OLD. If you have not done much code generation, I hope that this will encourage you to try it out. Code generation is fun and a great time saver!

Format: SP file, 2KB
gentrigrec.sp

Build an Alternate Index into an Index-By Table
By Steven Feuerstein

The altind.pkg contains a package that demonstrates how to use DBMS_UTILITY.GET_HASH_VALUE to build an alternate index into an index-by table. This allows you to avoid doing full-table scans of collections when you are searching for non-indexed data. This package also utilizes the "linear probe method" to perform conflict resolution. While the package is built around accessing the employee table data, you can easily adapt it to your own needs. The altind.tst file performs a test of the package, demonstrating the way that the conflict resolution algorithm works.

Format: PKG file, 8KB
altind.pkg

Format: TST file, 2KB
altind.tst

Performance Testing and Table Types
By Barry Etter (better@unifitech.com)

This document contains an analysis of index-by table behavior, performance and memory utilization. His conclusions (in part):

• As expected, PL/SQL tables are much faster when accessed through their indexes.
   
• But, PL/SQL tables become very slow if you have to "walk" them to find values using the .FIRST, and .NEXT operators.

Schema Comparison Utility
By Tom Niderost (tomniderost@yahoo.com)

I have created a tool called OraCompare that we use in-house for schema comparison. It is a beta product, and it has been posted for free download and use.

OraCompare will compare objects in two different schemas.  The schemas can reside on different servers or be on the same database.  It has been tested with Oracle 8 and 8i only.  At this time it does not compare Types and Type Bodies.

It is designed to run on a Win9x/WinNT/Win2K client. This is a work in progress, and more features will eventually be added.

The application was written in Delphi, with "Direct Oracle Access" components used to communicate directly to the OCI.

Format: Application
http://www.niderost.com/oracompare.zip

Source File Pre-Processing Utility
By Steven Baldwin (Steven.Baldwin@hancorp.com.au)

Have you ever wished you could include debug code in your PL/SQL package, but not execute it when you have finished debugging your package. One option is to take it all out, or comment it all out, but for large packages this can be quite a nuisance. Another option is to wrap all debugging code with an 'IF' statement, such as 'IF Debug THEN ...', and effectively include or exclude it at run-time. This will work, however it may incur an undesirable overhead. Another situation may see you writing some code that needs to be portable across multiple Oracle versions. If you wish to maintain a single source file, the only option (until now) is to go for the 'Lowest Common Denominator' approach. Or, you may have a module that is being used for different clients. The code is 90% common between the clients, but that 10% difference means you basically need to have a different copy of the module for each client - until now.

This small utility allows you to pre-process your PL/SQL source file. It takes as input your single source file, with sections of code wrapped by conditional expressions, using a syntax similar to the 'C' pre-processor. When you invoke the script, you define the values of the 'variables' referenced in the conditional expressions, and the utility creates an output file with code correspondingly included or excluded. You then simply run this output file through SQL*Plus (or whatever you use to create your stored PL/SQL objects) to create a 'version' of your module that is optimised to the target, but is still maintained through a single, common source file.

The zip file below contains two script files and one readme file. We recommend that you view the readme file first.

Format: Zip file, 5KB
opp1.zip

Improved File I/O in PL/SQL Using Java
By Steven Feuerstein

This zip contains two files: JFile.class and xfile.pkg. The Java class definition exposes several useful methods from the File fundamental class. The xfile package wraps those methods within a PL/SQL layer of code that allows PL/SQL developers to perform such operations as delete a file, make a directory and retrieve all the files found in a directory.

Please Note: This archive is dependent on packages found in PL/Vision. If you will have not already done so, you will need to download and install either PL/Vision Lite or PL/Vision Code Library, which is included with  Knowledge Xpert for PL/SQL.

Format: Zip file, 3KB
jfile.zip

CSV File Utility
By Anthony Whitehead (bricky@dircon.co.uk)

This process was designed to take a .CSV file as input and return the values in a nested table for programmatic control.

It caters with encompassed quotes, in the same way that SQL Loader has an 'Optionally enclosed by' option.

It's useful if you are reading in data which has an indeterminate number of columns, repeating groups for example.

declare
  l_array gen_util.array_t ;
  li_count binary_integer ;
begin
  gen_util.csv_to_array('"ABC,DEF",123,345,"DEF,GHI",456,', li_count, 
  l_array, ',') ;
  for a in 1..l_array.count loop
    dbms_output.put_line(to_char(a) || ' ' || l_array(a)) ;
  end loop ;
end ;

  ABC,DEF
  123
  345
  DEF,GHI
  456
 

• Solving "COMMIT business rules" on the Database Server (4/02/02)
• Strategies for Building a World Class Technical Architectures (12/07/01)
• Effective Application Source Code SQL Analysis for Change Impact, Performance, and Quality (12/07/01)
• Performance Tuning for Mixed-Case Queries (12/07/01)
• Building a 24x7 Database (12/07/01)
• Achieving Top-Notch Performance via SQL Tuning throughout the Application Lifecycle Part 1 (12/07/01)
• Achieving Top-Notch Performance via SQL Tuning throughout the Application Lifecycle Part 2 (12/07/01)
• Global Handling in Oracle Forms
• The Psychology of is_number
• The Troublesome COMMIT
• Scanning PL/SQL tables
• Write PL/SQL code within Oracle Developer/2000
• Managing -20NNN Error Codes
• Two-Dimensional Arrays

Solving "COMMIT business rules" on the Database Server
By Zlatko Sirotic, zlatko.sirotic@iii.hr

In recent years, software development has begun focusing on three-tier applications, mainly by separating the business rules layer from the presentation and data layer. There are two approaches concerning the physical implementation of the business rules layer:

1. On the application server
   
2. On the database server

Format: HTML, 10KB.
COMMIT_Rules.htm

Strategies for Building a World Class Technical Architecture
By Matt Holetz, Quest Software & Jeff Courier, Oracle Corporation

This paper is based on the experience of several large Oracle Application implementations and attempts to define some technical guidelines for architecting a world class global solution.

Format: Adobe PDF, 59KB.
World_Class_Architecture.pdf