utilities for oracle developers
Below you'll find a small number of utilities that might come in handy from time to time. Feel free to use as you wish (please read this disclaimer first).
- Xplan Package
A package of pipelined functions over DBMS_XPLAN. The DBMS_XPLAN outputs are modified to include the plans' execution order. XPLAN includes DISPLAY, DISPLAY_CURSOR and DISPLAY_AWR wrappers for use in exactly the same way as their DBMS_XPLAN equivalents. Supports versions from 10g onwards. - Dictionary Long Application
Two versions of an application to encapsulate the data dictionary views that have LONG columns. LONGs are converted to CLOBs in pipelined functions, enabling us to search, copy and manipulate these columns with string functions. A static version supports 9.2 and 10.1 and a dynamic ANYDATASET version supports 10.2 onwards. - PL/SQL Timer
A simple package to output the elapsed time between two points. Supports versions from 8i onwards. - Showplan Utility
Simple sqlplus utilities to output the real execution plans used by running or cached SQL statements. Available for 9i Release 2 and 10g. - Runstats Utility
Three variations on Tom Kyte's invaluable runstats utility, designed to work under constrained developer environments and with enhancements such as "pause and resume" functionality. Runstats compares the resource consumption of two alternative units of work. - Mystats Utility
A variation on Jonathan Lewis's SNAP_MY_STATS package to report the resource consumption of a unit of work between two snapshots. Designed to work under constrained developer environments. - Data Dump Utility
Procedure to dump the contents of a table or the results of a query to flat file. - PL/SQL Counter
A simple package to start and maintain a session-based counter. Useful for debugging and instrumentation. - Merge Counter
A package to capture and report separate INSERT and UPDATE rowcounts from a MERGE statement. - Call Stack Parser
A small package of functions for parsing the PL/SQL call stack. Includes an implementation of "WHOAMI" and "CALLER". - IS_NUMBER
Two common, simple and effective IS_NUMBER functions for SQL and PL/SQL. - REPLACEF
A function to simplify string building and debugging by replacing multiple placeholders from a collection of inputs. - PUT_LINE
A simple wrapper to DBMS_OUTPUT.PUT_LINE to workaround the 255 byte limit (not needed from 10.2 onwards). - Export-Import Scripts
A collection of Korn shell scripts to export and import tables or schemas, with or without compression.