I rediscovered the joy of writing to myself a week ago, and I also was reminded of the danger of coding in isolation (that is, working by oneself).

I recently tangled with my implementation of column set management in Quest Code Tester.

"Column set management," you wonder, "what's that?"

In this amazing, automated code testing tool for PL/SQL, you can at a high level describe the tests you want Quest...

In this blog, I want to introduce you to a little known feature in Toad for Oracle called the PL/SQL Profiler. Believe it or not, the profiler has been in Toad since September 1999 (Version 6)!

So why would I want to Profile my Code?

Well, as developers write their code, they’re principally focussed on ensuring the code enables the required application functionality and does so consistently for all test cases....

In this installment I thought I'd write about the exciting, and soon to debut, Toad Data Modeler 3.0 release rather than yet another boring academic or technical topic on data modeling. TDM 3.0 is quite exciting. While it has not yet been made available for public beta – it will be very soon (so please email the product manager John.Pocknell@Quest.com to get signed up). And once...

On one of my first Oracle consulting assignments I had to come up with the sizing estimates for tables for a data warehouse for a telecommunications company in California. At that time, (1994) there were few products that provided sizing algorithms for Oracle tables and indexes. In fact, all that was available were the somewhat cryptic sizing formula provided by Oracle (discontinued from the manuals in version 9). Not being...

Some musings about the life of a programmer.... Two weeks ago, we held the second annual Oracle PL/SQL Programming conference (I talked about it in last week's blog on the Test-A-Thon). I gave the keynote speech on the first day. It was titled "Wake up and smell the methodologies". Generally, I urged PL/SQL programmers to take a look at the various methodologies out there, such as Extreme Programming, Structured Programming...

1. What is Team Coding Team Coding is a cooperative source control feature. You can use Team Coding alone, or in conjunction with a third party version control system. Team Coding works with the Editor to control access and development of functions, procedures, packages, triggers and types. It also supports scripts in the Editor. Some of its key features include:

·          Flexible Code Control Groups (CCGs), which...

A couple months ago as ODTUG was working out the final details for the Oracle PL/SQL Programming 2007 conference I came up with an idea for a Test-A-Thon: Contestants are handed a set of programs, and test cases for each of the programs. They must then (in the next hour) build test scripts for those programs that automatically verify whether or not the program works. Whoever gets furthest in building the test scripts...

I have been asked many times that why there are some dummy SQL transformations/rewrites that look meaningless, but it works in certain situations.  Let me give you some examples that may help to explain what theory on behind of Quest SQL Optimizer.

The following rules are implemented in the Quest SQL Optimizer for different platforms to deal with individual database SQL optimizer’s behavior. Some rules may look puzzling....

Test your code! Debug your code! Trace your code! We all hear about how we should or at least could perform these activities with our programs. And all too often the terms and usages seem to blur, and be confused. In reality, these three activities – test, debug, trace – are all quite different in nature and serve different purposes.

This blog entry offers my perspective on how they differ. I hope you find it useful...

One of my favorite things to do (when I am not tuning and managing Oracle databases) is to don a scuba tank, mask, and exposure suit and slip beneath the waters surface. For a long time I wondered how to reconcile these two interests and then it came to me.

In industry the one thing that keeps businesses moving, provides them fuel and keeps them from getting choked is data. In scuba, what keeps you moving, provides you fuel and keeps you from choking is air. So using simile data is to business is like air is to a diver. 

...

Over the decades I’ve worked with many data modelers – and I’ve learned something key to building a great model that is not always intuitively obvious: spend at least 50% of your data modeling time on the relationships. Often modelers are so overly concerned with the entities, attributes and unique identifiers or keys they don’t allocate enough time for the proper questioning, construction and review of relationships. Therefore,...

The Major Enhancements in the 1.5 Release

Of course, we spent lots of time just fixing bugs and making lots of small changes. But there were some very critical "big ticket" enhancements that were requested by users and prioritized for the commercial release.



The ToadWorld preview release, Quest Code Tester for Oracle 1.2.5.5,  offered a Test Builder that is very powerful, but it is also a bit quirky and...

In Quest SQL Optimizer, transformation rules relating to index usage are designed to guide the database SQL optimizer as to how it should use the indexes for a specific SQL statement.  Although those rules may familiar to most of SQL developers, but it is still worth to illustrate some commonly used rules for SQL beginners, of course, if you don’t want to do it by yourself or if you don’t have time to check every SQL in your...

As I write these very words, we are preparing to release the commercial release of Quest Code Tester for Oracle 1.5. I hope that you have already heard about this new testing tool. If not, I offer a quick intro/overview below.

I hope that you have already downloaded the special preview release (1.2.5.5) we made available at Oracle Open World in October 2006. If not, no problem....just wait a little bit and then download...

I recently spent two days training a group of about 30 developers and DBAs out east. As with any sizeable collection of technologists, the level of expertise and the years of experience varied greatly (and don't always go hand in hand!).

I was pleased to see that everyone seemed to get along well, there wasn't any great reluctance to ask questions (that is, admit ignorance), and several attendees didn't hesitate to challenge my ideas and suggest other ways of doing things. Just the way I like it!

...

Of the many databases I have examined on releases 9i and 10g, most are using the automatic undo management feature and on the whole it does a pretty good job of managing the undo segments (for you other old timers, rollback segments).

However, I have been noticing, shall I say, some rather retro behavior as a result of the automatic undo management. In the bad old days when we managed the undo segments manually we...

 

Now, for sure, we need really good tools to help get the job done, but we also need to think about the workflow we follow to write our code.  You will find my latest ideas about that below. From a methodological standpoint, it draws heavily on Test Driven Development.

I will be building out these ideas further throughout 2007, and also we are looking at ways at incorporating these ideas directly into...

Probably one of the most often discussed and hotly debated topics in both data modeling and database design is that of normalization. Many database designers, architects, DBAs and senior developers have differing positions and/or beliefs on the topic. However quite unfortunately, they often are not communicating with optimal effectiveness due to some fundamental differences in terminology and understanding. The simple solution...

Caveat Emptor (Buyer beware) has been around since, well, since time began. Whether barter or cash, when doing a deal the buyer has to careful, this is especially true in the complex area of software licensing.

A case in point, Oracle licensing, I hear they are offering a degree in it as at times it is as difficult to figure out as quantum string theory.  A prime example is the wonderful suite of monitoring and analysis...

In my last blog, I use two tables join to illustrate the simple path control, now let’s use a three table join SQL statement to demonstrate a more complicated scenario. Let’s assume that A.key, B.key and C.key are all indexed.

Many times I am asked how to tailor the initialization parameters for a specific process or user. Essentially the easiest way to set custom initialization parameters for a particular session is to use the ALTER SESSION SET command in a logon trigger keyed to a specific schema or schemas. For example, say we had an application where we wanted to setup cursor sharing and all of the users who will need it are prefixed with “APPL”...

I thought I would take a moment out of my busy Quest Code Tester development efforts (that is, heads-down coding, writing specifications, working with developers in Chicago, Columbus, St. Louis and Russia) to give thanks to Toad.

 

"Way back" in October (that's the way it feels, already), Quest released Toad 9. My initial reaction, probably like many other Toad users (and I do feel like I am quite the typical...

I recently went on a very nice vacation to Curacao. Before I went on vacation the Oracle 10g Grid Control (10.2.0.2) I had installed on my Laptop for monitoring my home office systems was working. I took my laptop with me to allow offload of pictures from my cameras and to track email while I was away. When I returned home and hooked my laptop back up to my home network Grid Control wouldn’t start and of course even with the...

In old version of Oracle database, it is easy to control the join path by reordering the tables in table list after the FROM clause, but it is getting difficult to control the join path in today’s cost based SQL optimizer, I am going to introduce method that is applicable in most databases such as Oracle, Sybase, DB2 and SQL Server in the following:

To control a join path, we cannot tell the database SQL optimizer which path is the best one to select. Instead, we add something to the syntax of the SQL statement that causes an increase to the cost of the current join path selected by database SQL optimizer. Let’s take a look at the following example of two table join scenarios.

You probably don't give this a second thought, but sometimes the question rears its ugly and bewildering head in the code you are writing – precisely because Oracle itself provides multiple answers to the question, right from inside its own code base. Let's take a closer look.

 

When NO_DATA_FOUND is raised, the error message is:

 

ORA-1403: no data found

 

So...is that character between...

Last month I wrote about “Why Reverse Engineering is Always Worthwhile.” So the logical next issue to examine is forward engineering – its different approaches and its many comparative benefits. The best way to do this is via an analogical example J

I live in Dallas-Fort Worth. Let’s assume that I drove down to Austin at the start of the this year’s college football season to watch my #1 Ohio State Buckeyes play against...

Oracle’s Oracle10g requires more memory than was ever required before. If you utilize any of the new features such as automatic storage management (ASM) and automatic shared memory management (ASMM) then you really need to pay attention to what memory is doing in 10g.

I remember reading “Brave New World” when I was in high school, we needn’t mention the year. I was fascinated by the system described in the book that seemed so logical on the surface but needed so many props underneath to keep it going. It reminds me at times of some of the Oracle management that goes on these days.

What exactly do I mean? Many times Oracle management tools that look good on the surface are actually...

The Nested Loop join operation is the basic join operation which is supported by most RDBMS, since it requires less memory and less temporary space. Normally, it can provide faster data response time than other join operations. But, the path of a Nested Loop join will significantly affect the speed of the join operation. Let’s use a two table join as an example to understand how this works.

When an exception is raised, one of the most important pieces of information a programmer would love to get hold of is the line of code that caused the exception to be raised.  Before Oracle 10g came long, the only way to get this information was to allow the exception to go unhandled and then view the error stack. The following chain of program calls shows you what I mean.

CREATE OR REPLACE PROCEDURE my_procedure_1...

I often see DBAs and developers who don’t know the first thing about tuning SQL. They ask “Why do I need to know that, can’t Oracle tune itself?” The truth of the matter is that Oracle is, for the most part, able to do a pretty decent job of tuning itself if it is given enough information and the queries or tuning tasks aren’t too complex. However, there are times when Johnny must tune.

What do I mean by “enough information”?...

All those big brain technoids always make such a big deal out of testing. Too many bugs in our software! Bugs cost money! Bugs make us look bad! Test your code before you give it to your users! Test your code before you write your code! Test your code before you learn how to write code! C'mon, I say, give us a break!

Here's the way I look at it: we could bust our buttons working extra special hard to get rid of lots...

With each new release of a database, be it Oracle or one of their competitors, we hear the cry that this release will make DBAs as we know them obsolete. We hear again and again how this automated feature set or that new GUI interface will automate the DBA job. So far, all of these claims of DBA obsolescence have been for naught, to mis-quote Samual Clements “The reports of the DBAs death are greatly exaggerated.” When...

Welcome to my Toad World blog! I will offer on this blog a wide variety of tips and incredibly deep and surprising insights on the Oracle PL/SQL language (the object of my obsessive personality for the last 10+ years).  I look forward to your responses and critiques. That is: I hope you will engage with these ideas and feel perfectly comfortable with disagreeing with me, offering other, perhaps better, ideas, and in general...

For years, commercial database manufacturers have fought an endless battle to improve the performance of inserting, updating, deleting, and retrieving information stored in the database. Despite their continual efforts and hard work, we have not seen a significant improvement in the performance of most Relational Database Management Systems (RDBMS). Users still suffer from under-performing SQL statements and database experts...

In many shops that I visit, neither the DBA’s nor the database developers place much value on performing data modeling. Often there are seemingly sound reasons for this exclusion: the historically high price of data modeling tools, data models are not required as a project milestone or deliverable, and quite often the database was simply either inherited or supports a third party application – and thus is not open for modification....

When I started working with Oracle in 1990 there weren’t many SQL tuning tools available. At most you had the Oracle supplied tools which allowed you to capture an explain plan and minimal tracing. Most SQL tuning consisted of either encouraging the use to a particular index by making sure the column in the index was on the leading edge of the index or, defeating the use of the index through null concatenation to character...