Click Here to see the script.

Loading External XML Files (July 2002)
Topic Extracted from Knowledge Xpert for PL/SQL™ 

When a developer is setting out to store the contents of an XML document in the database to perform queries over its content and structure, one of the first questions that arises is, “How do I get XML files in there?” Here you’ll start simple and work through the steps of loading an existing XML file into a CLOB column.

CLOB (pronounced “klob” or “see-lob”) stands for C haracter L arge OB ject. Think of a column of type CLOB as a very, very large VARCHAR2. It can hold character-based data like XML documents as large as four gigabytes (4GB).   

Click Here to see the topic.

Using UTL_SMTP (October 2002)
By Michael Selvaggio

I wrote this package to send email messages from PL/SQL. Using existing examples, I added the simple ability to pass a query in and return data in the text of the email. I also allow the passing in of a PL/SQL table for an address list. Click on the links below for the procedure and a sample execution.

Click Here to see the procedure.
Click Here to see the sample execution.

Calculating the Buffer Cache Hit Ratio for Accurate Results (November 2002)
Extracted from the Quest Pipelines PL/SQL Pipetalk

This script is a PL/SQL procedure that can be used to periodically load hit ratio, usage, and number of users into a table for later review. This will provide valuable information about peak usage times and hit ratio at those peak times. This procedure/table can be used to store entire database hit ratio or per user hit ratios.

Click Here to see the script.

Alert Log Scanner (November 2002)
By Michael Selvaggio

The following Unix shell script was written for HP/UX to scan the alert log, checking if Oracle and the SQL*Net listener are up in an effort to be proactive regarding system errors. It's based on a script from www.dba-oracle.com but it has been enhanced to be submitted once and run every minute and end at a predefined time. It also doesn't resend the same error all day. It only sends messages since the last scan.

Click Here to see the script.

Procedure to Count the Number of Nulls Occurring in a Table (December 2002)
By Dan Clamage, Pipeline SYSOP

This stored procedure generates an anonymous PL/SQL block that calculates how many NULL values occur in each column for all rows of the given table. It returns a string of the form "col1,cnt1,col2,cnt,...,colN,cntN" where colN is the column name and cntN is the number of NULL values found for the column. It also displays the entire anonymous block so you can see what it is doing.

Click Here to see the script.

Procedure to Print the Difference Between Two Date Values (December 2002)
By Dan Clamage, Pipeline SYSOP

This stored procedure prints the absolute difference between two DATE values in hours-minutes-secs. It can also be turned into a function to return the string instead of printing it through dbms_output.

Click Here to see the script.

Get Working Days without Using the WORK_DAYS Function (January 2003)
By Michael Selvaggio

This function allows users to calculate the working days between two dates when they are unable to create the Oracle function WORK_DAYS. The same logic is implemented in a SELECT statement.

Click Here to see the script.

Connectionless Logging to Oracle on UNIX (January 2003)
By James Thorby

For Unix based console applications it is sometimes important to log messages to Oracle as well as to log files. Long running processes may require real-time logging for meaningful feedback and, with the prevalence of web applications against Oracle databases it is a convenient way of storing these messages for easy client access. This example demonstrates a method of allowing Unix applications to log to Oracle without incurring the time penalty of repeated connect/disconnects or the complexity of writing custom ProC/Java Daemons (i.e. a program running in the background that would handle all database logging requests for the application).

Click Here to see the paper.

Utility to Change Database Links (February 2003)
By Shankar Govindan

This is a small script that logs on to the database, checks if there are any links that use the specific password. The information is then spooled, the script logs in as the owner of the link, drops the database link and recreates it. It then tests the database link and sends a mail out to the DBA.

Click Here to see the script.

Utility to Check Database Links (February 2003)
By Shankar Govindan

This utility is a handy one to check all the private database links that exist in the database under different users. This script checks all the private database links available, identifies the user who owns the database links and connects as owner of the database link and tests the link for connectivity. It then spools the information and mails to the mailing address specified.

Click Here to see the script.

External Tables (March 2003)
By Dan Clamage

If all you need to do is read from a flat file, consider using external files instead of UTL_FILE. With UTL_FILE, you have to control file I/O yourself (open, get_line, close), plus write code to parse out the fields you want. Instead, using a SQL*Loader-like syntax, you can let Oracle handle all of that for you. Here is a very simple example. I've left out some error handling like bad record log files, etc. I ran this with Oracle 9.2 under NT 4.0, but it should work as well under UNIX.

Click Here to see the procedure.

Dynamic Method Dispatch and Method Overloading in a Subtype (May 2003)
By Zlatko Sirotic

Oracle 9i has support for inheritance, method overriding and "dynamic method dispatch" (or "dynamic binding", or "virtual"). When we have inheritance and overriding, a process of selecting a method implementation is called "dynamic method dispatch" because it is done at run time, not at compile time. A method call is dispatched to the nearest implementation, working back up the inheritance hierarchy from the current or specified type.

When child (type) overrides parent's method, dynamic method dispatch in PL/SQL works as expected. This simple example shows that after polymorphic assignment, child method is executed.

Generic Log Package (July 2003)
By Zlatko Sirotic

This script creates a generic procedure that can be used for maintaining a transaction log for audit purposes.

Click Here to see the package.

Creating XML Documents with PL/SQL (July 2003)
By Christoph Gächter

This article shows some of the possibilities how XML documents can be created with PL/SQL and Oracle 8i or 9i. The discussed topics are:

• XMLGEN Package
• XML-SQL Utility for PL/SQL
• XMLDOM Package

Click Here to see the article.

ORACLE 9i MERGE Statement - Decomposing SQL%ROWCOUNT (September 2003)
By Adrian Billington

The MERGE statement (AKA "UPSERT") released in Oracle 9i is possibly one of the most useful ETL-enabling technologies built into the Oracle kernel. For those who have missed Oracle's headlines for the last year and a half and are unaware of what the MERGE statement does, it simply enables us to either UPDATE or INSERT a row into a target table in one statement. You simply tell Oracle your rules for determining whether a target row should be UPDATEd or INSERTed from the source, and Oracle does the rest.

Click Here to see the paper.

Ad Hoc Queries Against Tables/Views (December 2003)
By Dave Keller

The adhoc_pak is a PL/SQL package geared towards allowing Ad Hoc queries against tables/views. It relies on Oracle iAS to provide the Web interface. To use, first alter the package specification by changing the schema owner variable as appropriate and compile it into the database. Then compile the package body into the database - no changes are needed.

Click Here to see the article.

Logminer in Action (January 2004)
By Frank Pantaleo

The body of work that follows was born out of a desire to replicate data out of production. Of course we needed to do this while minimizing the effect on production. The best way to do this was to leverage APIs that Oracle made available in 8i. Some or all of the logic provided becomes unnecessary in 9i with the introduction of Oracle streams. However, I would contend that the procedures below provide a better level of control.

Click Here to see the article.

Transaction, Heal Thyself! Part 1 (February 2004)
By Darryl Hurley

Oracle 9i introduces a long awaited feature that suspends certain transactions instead of failing them straight away. Starting with this article, Darryl will discuss how you can take advantage of resumable transactions to support sophisticated transaction processing requirements. He will also highlight shortcomings and remedy them with his custom-built solutions.

This article was originally published in the May, 2003 issue of Oracle Professional.

Click Here to see the article.

Transaction, Heal Thyself! Part 2 (March 2004)
By Darryl Hurley

Oracle 9i introduces a long awaited feature that suspends certain transactions instead of failing them straight away. In last month’s article Darryl discussed how to augment resumable statements to automatically resolve storage issues. In this second article of the series he focuses on a key area of resumable statements, namely, responding to timeouts and terminations.

This article was originally published in the June, 2003 issue of Oracle Professional.

Click Here to see the article.

Transaction, Heal Thyself! Part 3 (April 2004)
By Darryl Hurley

Oracle 9i introduces a long awaited feature that suspends certain transactions instead of failing them straight away. The first two parts of this series laid the groundwork for automatically fixing space related errors by augmenting Oracle 9i’s resumable statement functionality. This expansion comes complete with a catchy name, THY, which loosely stands for Transaction, Heal Thyself. It’s also the name of the single Oracle schema holding all of the working code described below.

This article was originally published in the August, 2003 issue of Oracle Professional.

Click Here to see the article.

Transaction, Heal Thyself! Part 4 (May 2004)
By Darryl Hurley

Oracle 9i introduces a long awaited feature that suspends certain transactions instead of failing them straight away. In this series of articles Darryl presents a methodology to take advantage of resumable transactions to support sophisticated transaction processing requirements. Part IV imparts more details of his custom built solution for handling objects exceeding their maximum extent allotment.

This article was originally published in the October, 2003 issue of Oracle Professional.

Click Here to see the article.

Transaction, Heal Thyself! Part 5 (June 2004)
By Darryl Hurley

Oracle 9i introduces a long awaited feature that suspends certain transactions instead of failing them straight away. In this series of articles Darryl presents a methodology to take advantage of resumable transactions to support sophisticated transaction processing requirements. Part V delivers his custom built solution for handling the most literal of space errors, NO MORE SPACE. Also in this final article in the series he concludes with general error handling and a look to the future including Oracle 10G.

This article was originally published in the January, 2004 issue of Oracle Professional.

Click Here to see the article.

PL/SQL Procedure - Write Records to Excel File (July 2004)
By Tom Kyte

This PL/SQL procedure will write records to an Excel File.

There is more then one format we can use to write an excel file -- from CSV to SYLK. I will demonstrate the SYLK format as I already have the code and it offers the ability to do much fancier stuff like fonts, headings, formulas and such. 

We will use UTL_FILE (see the supplied packages guide for setup info on that package. You need an init.ora parameter set for this to work correctly). UTL_FILE allows us to write a file on the server and since your workstation = server, this should work nicely for you.

Click Here to see the script.

DateADD (August 2004)
By George Joseph

This function will return a new date value based on adding an interval to the specified date.

Click Here to see the script.

Improving PL/SQL Performance By Using Collections (August 2004)
By Mark Rittman

Yesterday I looked at some code written in Oracle 7-style PL/SQL, and compared it with what is considered best practice with Oracle 8i and 9i. The more up-to-date code generally runs around 3-5 times faster than the old style code, and, as part of understanding where the changes to PL/SQL have occurred, I've started off by looking at how Oracle now handles a concept known as 'Collections'.

Click Here to see the article.

What's My Line? Tracking Exceptions in Oracle10g (September 2004)
By Adrian Billington

It has always been possible in PL/SQL to identify the source of an exception in a block of code; i.e. to know the precise point at which a block of code failed. However, until Oracle 10g, it has not been possible for developers to intervene in the exception in any way whilst retaining this information ( for example to record the exception and its source in application logs ). This has been the cause of many a frustration for developers.

Finally, with the release of 10g, Oracle has added provision for PL/SQL developers to trap AND log exceptions accurately for the first time. The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function gives us the flexibility we have been demanding for years and the information that the DBMS_UTILITY.FORMAT_ERROR_STACK function simply didn't provide.

Click Here to see the article.

Self-Managing PL/SQL (October 2004)
By Steven Feuerstein

Oracle has introduced significant management automation features in its flagship database product and has described the new Oracle Database 10g release as "the self-managing database." Given that achievement and direction, I thought it would make sense to talk about how to write one's PL/SQL application so that it is also self-managing.

Click Here to see the article.

Number to Words (December 2004)
By Dieter Oberkofler

This script converts numbers to their English equivalent and returns it as a string.

Click Here to download the script.

Bulk Bind Operations (January 2005)
Topic Extracted from Knowledge Xpert for PL/SQL

Bulk bind operations help to improve the performance of PL/SQL operations. Oracle 10g introduces two new types of functionality for bulk binds, including:

• Processing of sparse collections
   
• New exception handling

Click Here to read more on bulk bind operations.

Explain Plan's AUTOTRACE Facility (February 2005)
Topic Extracted from Knowledge Xpert for PL/SQL

The EXPLAIN PLAN command is not the only way to see an execution plan for a given SQL statement. SQL*Plus includes the AUTOTRACE facility. The problem with the EXPLAIN PLAN command is that the EXPLAIN PLAN statement has to be explicitly run for the query you want to examine, then the plan table has to be queried. Once done, the information may be removed from the plan table before the process is repeated.

The AUTOTRACE facility is an easier way to accomplish the same goal. The AUTOTRACE facility is only available in SQL*Plus. Unlike the EXPLAIN PLAN command, the AUTOTRACE facility must use a table called PLAN_TABLE. The EXPLAIN PLAN command can use a plan table of any name. The PLAN_TABLE table can be created with the ORACLE_HOME/rdbms/admin/utlxplan.sql supplied script. Additionally, before starting the AUTOTRACE facility, you must have been granted the PLUSTRACE role. This role is created with the ORACLE_HOME/sqlplus/admin/plustrce.sql supplied script.

Click Here to read more on the AUTOTRACE facility.

Refactoring for PL/SQL Developers (April 2005)
By Steven Feuerstein

What is refactoring?

Most PL/SQL developers stay very busy building applications. As a result, relatively few explore the wider world of software programming, in which the topic of refactoring is known.

Refactoring, to quote the book Refactoring: Improving the Design of Existing Code by Martin Fowler et al. (Addison-Wesley, 1999), "is the process of changing a software system in such a way that it does not alter the external behavior of the code yet improves its internal structure. . . . It is a disciplined way to clean up code that minimizes the chances of introducing bugs. In essence when you refactor you are improving the design of the code after it has been written."

Click Here to see the article.

Creating Stored Outlines - Part 1 (May 2005)
Topic Extracted from Knowledge Xpert for PL/SQL

Stored outlines bring stability to your application's SQL statements. It is possible to make a better decision than the CBO, and then to want the application use your decision over the CBO's decision. Additionally, changes in the data distribution can alter an execution plan of a SQL statement, once the CBO has new statistics on the new data. Stored outlines are also great when working with third party applications, where you cannot touch the SQL statements, but you'd like to alter how they run. You can provide execution plan stability through the use of stored outlines.

Click Here to see the topic.

Creating Stored Outlines - Part 2 (June 2005)
Topic Extracted from Knowledge Xpert for PL/SQL

Stored outlines bring stability to your application's SQL statements. It is possible to make a better decision than the CBO, and then to want the application use your decision over the CBO's decision. Additionally, changes in the data distribution can alter an execution plan of a SQL statement, once the CBO has new statistics on the new data. Stored outlines are also great when working with third party applications, where you cannot touch the SQL statements, but you'd like to alter how they run. You can provide execution plan stability through the use of stored outlines.

Click Here to see the topic.

Use of Grouping_ID in Summary Queries (July 2005)
By Keshav Chennakeshav

This is a way to analyze grouped data when grouping more than one column with GROUP BY ROLLUP(col1,col2,..) using GROUPING_ID(col1,col2,..).

Click Here to see the script.

Unloading Data Using External Tables in Oracle 10g (July 2005)
By Adrian Billington

This is an introduction to a new feature of external tables in 10g, namely the ability to unload data from the database using SQL. Introduced in 9i, external tables provided a new means of reading flat file data without first loading it into a staging table. They offered additional benefits over traditional SQL*Loader methods in that loading data became as simple as an INSERT..SELECT, with all the SQL processing and manipulation options that such a statement implies. It also meant that more of the ETL application could be self-contained in PL/SQL packages, as call-outs to SQL*Loader or batch scripts were no longer necessary to load data.

Oracle 10g has taken external tables a stage further by enabling an external table to be created as a CTAS ( Create Table As Select ) operation, which enables a one-time unloading of data. When I first saw the headlines about "writing" to an external table, I must admit to being excited by the possibilities of a native data unloader ( no more sqlplus spools, UTL_FILE writes, Pro*C unloaders etc ). Alas, this is not the case. Oracle has in fact supplied a new access driver known as "ORACLE_DATAPUMP" which means that Oracle will "unload" an external table to a proprietary format usable by the Data Pump set of utilities ( significant new 10g feature to replace imp and exp ). So, generating an ASCII, CSV file from external tables is not possible, but this new feature is exciting nonetheless.

Click Here to see the article.

Database Interaction with PL/SQL, Part 1 (August 2005)
By Jagadish Chatarji

This first article in a series focusing on database interactions with Oracle PL/SQL serves as a good introduction to the topic. It mainly focuses on the basics of retrieving information from the Oracle database using the SELECT..INTO statement, as well as two attributes, namely "%TYPE" and "%ROWTYPE." Readers should have some knowledge of RDBMS, Oracle SQL, and some very fundamental grounding in PL/SQL.

Click Here to see the article.

Is element found in collection? (September 2005)
By Steven Feuerstein

Oracle Database 10g allows you to use MEMBER OF syntax to determine if a particular values is a "member of" a nested table. Here is an example:

DECLARE
 TYPE clientele IS TABLE OF VARCHAR2 (64);
 client_list_12 clientele :=
 clientele ('Customer 1', 'Customer 2');
BEGIN 
 IF 'Customer 1' MEMBER OF client_list_12
 THEN
 DBMS_OUTPUT.put_line ('Customer 1 is in the 12 list');
 END IF;
 IF 'Customer 3' NOT MEMBER OF client_list_12
 THEN
 DBMS_OUTPUT.put_line ('Customer 3 is not in the 12 list');
 END IF;
END;
/

Click Here to see more.

For questions or comments on this site: webmaster@quest-pipelines.com
All content Copyright © Quest Software, Inc. All rights reserved.