July, 2004  

  In This Issue

All articles have been reprinted with the written consent of their respective authors.

Free Utilities

Numeric Data Comparison Script
 Extracted from the Script Exchange Conference of Quest's Oracle Pipeline Forum

Question: I am trying to write a query with numeric data comparison.

For example:

SELECT * FROM TAB1 WHERE TO_NUMBER(COL1) IN (1234,5596);

However, the COL1 has a VARCHAR2 datatype. It contains not just numbers, it has some alpha characters and some special characters in it as well. My challenge is to filter out this column to extract all the rows that have ONLY numeric data. 

Answer:

scott@ORA92> CREATE TABLE tab1 (col1 VARCHAR2(4))
2 /

Table created.

scott@ORA92> INSERT ALL
2 INTO tab1 VALUES (1234)
3 INTO tab1 VALUES (5596)
4 INTO tab1 VALUES ('ABCD')
5 SELECT * FROM DUAL
6 /

3 rows created.

scott@ORA92> CREATE OR REPLACE FUNCTION is_number
2 (p_string IN VARCHAR2)
3 RETURN VARCHAR2
4 AS
5 v_number NUMBER;
6 BEGIN
7 v_number := TO_NUMBER (p_string);
8 RETURN 'Y';
9 EXCEPTION
10 WHEN value_error THEN RETURN 'N';
11 END is_number;
12 /

Function created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SELECT *
2 FROM (SELECT *
3 FROM TAB1
4 WHERE is_number (col1) = 'Y')
5 WHERE TO_NUMBER(COL1) IN (1234,5596)
6 /

COL1
----
1234
5596

 

Oracle Administration

Fine-Grained Auditing for Real-World Problems, Part 3
By Arup Nanda

In the previous two installments of this series, I introduced you to the concept of Fine-Grained Auditing (FGA) used to track select statements in Oracle9i Database and up. I also explained how to use the feature in complex environments such as within a web application via application contexts and client identifiers. These two articles have enough information for you to build a FGA setup for almost all types of database systems, however complex. In this third and final installment, I will explain the FGA enhancements Oracle Database 10g brings to the table. 

Click Here for the article.

Note: This article was previously published on OTN .
 

Oracle Development

PL/SQL Procedure - Write Reocords to Excel File
By Tom Kyte, http://asktom.oracle.com

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 for the script.  

Have you written a Users Group paper or presentation that you would like to share with your colleagues around the world?  Send your paper to newsletter@quest-pipelines.com for possible publication in the Pipeline Newsletter.  If your article is accepted, it will be mailed to over 28,000 readers!
Project Management Tips & Techniques
Estimating Productive Time Per Day
Tom Mochal, www.tenstep.com 
Each month, Tom Mochal presents a set of project management tips and techniques for handling various aspects of planning and managing a project. Tom has over 23 years of IT experience. He has developed a comprehensive, scalable project management process called TenStep (www.TenStep.com). He has also developed PMOStep (www.PMOStep.com), which is focused on building, implementing and supporting project management methodology through a Project Management Office. Tom also has a comprehensive application support methodology called

It can be challenging to provide project estimates for effort hours, duration and cost. Of the three estimates, you must start off with an estimate of effort hours. Without an idea of the effort hours, you cannot accurately estimate duration or cost. 

Click here for more information on estimating time.
 

News & Events

Free Webcasts
For detailed information on these free webcasts, please visit our News and Events page.

Tips of the Month
Oracle DBA Tip of the Month:  Duplicating a Database using RMAN
PL/SQL Tip of the Month:  Pseudo-IF and CASE Statements In SQL*Plus Scripts
DB2 Tip of the Month:  Identifying Candidate Dimension Columns
SQL Server Tip of the Month: Programmatically Save DTS Packages to File
MySQL Tip of the Month: Managing Processes
Puzzle

Interactive Crossword Puzzle: "Word Jumble - Weather"
 

 
1 Down - Oracle, for Example		 Test your knowledge with the Pipeline Newsletter's Monthly Crossword Puzzle. 

Click here to Play!
Regular Features

We love getting white papers, tips, articles, and code examples/archives from our readers around the world.  Send your submission to newsletter@quest-pipelines.com.  If your article is published, it will be mailed to over 28,000 subscribers.

Our mission at Quest Software - RevealNet Labs is to anticipate the daily responsibilities and challenges faced by database professionals.  Our products help thousands of people solve problems and implement solutions every day.  This newsletter is designed to help facilitate the sharing of information among database professionals.  

About the Newsletter

This newsletter is distributed to Quest Software - RevealNet Labs customers, prospects and friends who have subscribed to it from our website. If you would like to unsubscribe, please visit http://qlist01.quest.com/UnsubMailingList/

Subscribe a Friend!

Do you know someone who would like to receive the Pipeline Newsletter? If so, please enter their email address in the box below and click SUBMIT.
  
Subscribe to re:Quest, Quest's Quarterly newsletter with articles, new product announcements, success stories and much more.
 

Past Issues of the Pipeline Newsletter