Would you like to increase productivity, improve code quality, and ensure that standards are being followed? Of course you would! And one of the best ways to do that is to rely heavily on Toad's Code Templates (similar functionality exists in SQL Navigator as well).

Code Templates are chunks of code that you can assign a name and description. You then use the name to quickly copy the associated code into your editor....

Sorry about the late posting, but if you are interested in attending my presentations, here's the info: Session ID: S290723 Session Title: Break Your Addiction to SQL! Track: Database Room: Grand Ballroom A Date: 2007-11-12 Start Time: 12:30

Session ID: S290725 Session Title: Collect Yourself: Optimize PL/SQL Code with Collections Track: Database Room: Yosemite Room B Date: 2007-11-12 Start Time:...

All files referenced in document available from: www.oracleplsqlprogramming.com/downloads/demo.zip.

Introduction

Very, very few of us write perfect programs the first time, or the second time, or…. You get the idea. Our code is never perfect and can always be improved. Martin Fowler developed a technique he calls "refactoring," and it has become quite popular in the world of Java. Here is Mr. Fowler's description of refactoring:

...

Use templates to accelerate high quality coding. When you open a new edit window in Toad, it looks like this:



Well, of course it does....it's an empty, new edit widow!

That's fine, but suppose I now need to create a new package for my application. Sadly, what many of us do is start typing:



...

What's the point? This utility will make it easy for you to read in the contents of DDL statements (like CREATE OR REPLACE PACKAGE) and execute them within Oracle.

Show me the code! It's not a terribly long program, so I will include it right in this posting:

CREATE OR REPLACE PROCEDURE exec_ddl_from_file (

   dir_in IN VARCHAR2

 , file_in IN VARCHAR2

)

AUTHID CURRENT_USER

...

I get this question a lot. I am teaching a class and a student asks: "When did Oracle add autonomous transactions?" Or the INDICES of clause. Or – who knows what? Seeing as I am totally obsessed with PL/SQL, I usually actually remember the answer to such questions.

Occasionally, however, I do not. And of course I cannot always be there for you to answer such a question when you have it.

So I thought I would pass along a tip – a way to quickly determine the version in which a feature was introduced to Oracle.

...

I don't know about you, but I sometimes write code that (inadvertently, not on purpose) contains an infinite loop. So I run my program and Toad goes off into never-never land, with Oracle chewing up CPU cycles so intently that it is hard to connect as SYS and kill the session.

I hate that, don't you?

Now, there are two ways to address this problem:

1.       Don't write code that contains infinite loops....

Kudos to Alex Nuijten for bringing this gem to light, he wrote in his latest AMIS blog entry that:

"If you are a regular user of the FORALL statement, you are probably also familiar with this message:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

While I was playing around on...

You will find in this Quseful a package that will generate/return collections of random values of strings, numbers and dates. It also contains a "self-test" random_verifier procedure that you can run to verify "at a glance" that the values being generated seem random. As a bonus, I include the pick_winners_randomly procedure, which I use in my seminars to pick the winners in raffles for my books and other goodies. I wrote...

I offer in this Quseful (Quick and Useful) a package that you can use to determine if a string contains a valid integer, number, binary_float or binary_double (note: if you are not running Oracle 10g, you will need to comment out the binary_* versions in this package). It is based on code I wrote about back in 1997 (available here, along with the article...

I offer in this Quseful (Quick and Useful) a package that you can use to dynamically retrieve the value of almost any column from any table. I created this package as a "helper" utility for Quest® Code Tester users. Here's the problem that I was solving with this package:

We added support for automated testing of XML documents in Quest Code Tester 1.6, which will be released in a month or so (a very solid beta is...

That's me. A hypocritical programmer. And I am here to admit it, to make a confession. Hypocrisy has got to be one of the most infuriating traits of human beings.

Definition:

"A pretense of having a virtuous character, moral or religious beliefs or principles, etc., that one does not really possess." http://dictionary.reference.com/browse/hypocrisy

...

Qusefuls #1 and #2 were published on Steven Feuerstein's personal blog at feuerthoughts.blogspot.com.

About Qusefuls A Quseful is a Quick and Useful (as opposed to Quick and Dirty) tip on now to write programs in the Oracle PL/SQL language more effectively. I originally published Qusefuls on my blog (feuerthoughts.blogspot.com), but am now switching the "home" for Qusefuls to my ToadWorld blog. Each Quseful contains...

It is Wednesday, May 09, 2007 and I am sitting in the Admiral's Club at the Buenos Aires airport. I just finished two days in this lovely city. My first day was spent enjoying the chilly, but very sunny afternoon, walking for four hours around the city. It is a busy, busy place with many buses throwing way too much gritty exhaust into the air (hey, but they are Mercedes Benz buses! I think that Americans would be generally...

I am visiting this, well, very artificial paradise dropped into the middle of a desert, so as to participate in Collaborate 07. It's been an interesting and fun several days. I have a room on the 26th floor of the Mandalay Bay hotel, so I have a fantastic view looking out over the wave pool, lazy river and assorted pools between the hotel and the convention center. Better yet, I can see mountains right there on the horizon....

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...

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...

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...

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...

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...

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!

...

 

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...

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...

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...

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...

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...

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...