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 helping me move forward the conversation within the PL/SQL developer community about how best to leverage this great language.

So…my "three tips most excellent" are…

1.  Take care of the host body.

2.  Stop writing SQL, or at least write lots less of it.

3.  Never declare any variable in your application code as VARCHAR2(N).

Below you will find an explanation of these tantalizing tidbits. Though I must admit, I am going to be brief – I am right in the middle of finalizing the freeze of Quest Code Tester for Oracle.

1.  Take care of the host body.

Isn't it just amazing that we get to sit around in a nice, air conditioned cubicle, think about stuff, type it into a computer, and get a fat paycheck?  That is a very good deal. We get to make a living from the product of our brains. Not digging ditches. Not doing repetitive factory assembly.  One problem with this job, however, is that we spend hours sitting in front of a computer, staring at a screen, and moving our fingers around in tiny, constrained movements.  How many of you suffer from lower back pain, upper back pain, stiff neck, carpal tunnel syndrome, problems with knees?  Let's face it: Our bodies did not evolve for this sort of activity. We are supposed to be hunting, gathering, etc. As a result, our bodies can very easily degrade "on the job".  And we might make our living from our brain, but that brain needs the host body to be healthy and strong in order for the brain to work effectively.

So I urge you to battle vigorously against any loss of mobility and any presence of pain. Get regular exercise, take lots of breaks. Even if you cannot get to a fitness center, you can always take a 10 minute break to line down on a mat, do some situps, stretching, pushups. Your body with thank you. Your mind will thank you. Your family will thank you (you will be much less grumpy).  Beyond dealing with the aches and pains, I have another very simple recommendation: Drink more water, drink less caffeine.  Most of us don't drink enough water. We get dehydrated (if you ever think to yourself "Gee, I am thirsty!" then you have already been dehydrated for quite a while) and when we are dry, our brain simply doesn't work as effectively.  So drink glass after glass of water, all day long. Cut back on the caffeine. You will find yourself more alert and better able to solve problems.

2.  Stop writing SQL, or at least write lots less of it.

One of the reasons developers like PL/SQL so much is that it is so easy to write SQL inside a PL/SQL block of code.  One of the most dangerous aspects of PL/SQL is that it is so easy to write SQL inside a PL/SQL block of code.  Paradox?  Irony?  SQL is, in fact, a sort of Achilles heel of PL/SQL development. Now, given that PL/SQL was first conceived as a procedural language extension to SQL, such a statement should raise eyebrows even further. The simple fact of the matter, however, is that if you are not careful about how and where you place SQL statements in your PL/SQL code, you will end up with applications that are very difficult to optimize, debug, and manage over time.  Know Thy SQL!

Take the "Know Thy SQL" test: Pick a table, any critical table in your application schema. Ask yourself this question: "Do I know where all or any of the INSERT statements for this table appear in my code? And did I remember to write exception handlers for DUP_VAL_ON_INDEX and other common errors associated with inserts?"  Chances are that you cannot answer very definitively, and that is because we PL/SQL developers are somewhat haphazard about managing our SQL statements. The result? Tremendous obstacles to performing accurate impact analysis on your code from database changes, among other things.  I am a great believer in what is called data encapsulation. For example, over the last year I have been hard at work creating a new tool, Quest Code Tester for Oracle, which I firmly believe is going to revolutionize the way we test our PL/SQL programs. We built in very quickly, in part because we were able to generate packages that took care of most of the basic SQL operations for us.

My front end developers never write SQL; they call procedures and functions that do the work for them.  My back end developers (including yours truly) wrote custom SQL statements occasionally, but only when the generated API could not handle our needs. It is a truly liberating way to build applications.  I can't imagine going back to the "old way" of hand-coding all my SQL statements, over and over again.   I could show you some examples of data encapsulation, but I have something much better to offer you: a free tool from Quest that will generate table API packages for your own tables. For the past several years, it has been Qnxo (Quality iN, eXcellence Out). Quest bought it in July and we are now issuing at as freeware through Toad World under the name Quest CodeGen Utility.  I urge you to check it out, and discover a new and better way to write code.

3. Never declare any variable in your application code as VARCHAR2(N).

I have lately gotten all excited about SPOD: Single Point of Definition.  It's a really fancy way (with a fun acronym) to say: Don't repeat anything when you write code.  Define it in one place and then reference that single point of definition throughout your application.  The process by which you achieve a single point of definition?  The spodification of your code base.  Oooooh, shivers down my spine!  Would you like an example or two of spodification? Consider this block of code:

     CREATE OR REPLACE PROCEDURE process_employee ( 
          employee_id_in IN number) 
     IS 
          l_full_name VARCHAR2(100); 
     BEGIN 
          SELECT last_name || ',' || 
                           first_name employee_full_name 
                 INTO l_name 
               FROM employees 
           WHERE employee_id = employee_id_in; 
               ... 
     END;

There are all sorts of problems with this code; allow me to point out a few of the most glaring concerns:

• I have hard-coded the length of the l_full_name variable. It seems safe enough; I looked up and added together the lengths of the last_name and first_name columns. Then I doubled that amount and came up with 100. Surely, the last-first combination is never going to get that big! 
• The formula to construct the "full name" is exposed and hard-coded. What if the user changes his or her mind (I know, I know, what's the chance of that happening?)? 
• The SELECT statement appears in the process_employee code. Surely this same query will be needed elsewhere?

Time to spodify!

I suggest that for each of your tables or business entities (which may be composed of several tables), you create a "rules package", a container for all the rules and formulas and related elements for a given entity. In the case of employees, you might have something like this:

     CREATE OR REPLACE PACKAGE employee_rp 
     AS 
          SUBTYPE fullname_t IS VARCHAR2 (200); 

          -- The formula 
          FUNCTION fullname ( 
               l employee.last_name%TYPE, 
               f employee.first_name%TYPE 
          ) 
               RETURN fullname_t; 

          -- Retrieval function 
          FUNCTION fullname ( 
               employee_id_in IN 
               employee.employee_id%TYPE 
          ) 
               RETURN fullname_t; 
     END employee_rp;

Notice that I have created a new datatype: fullname_t. All right, it's "just" a subtype – an alias for another, already existing type. But by hiding the explicit declaration behind a name, I can make sure that anytime I need to declare a variable to hold a full name, I just use that subtype and avoid hard-coding the VARCHAR2 length. If I ever need to increase the size, I change it in my SPOD (in this case, the package specification), recompile all invalidated programs, and I am using the new length.

In addition, I create functions that hide the formula for full name, and another to return the full name for a given employee ID. With all this in place, my process_employee procedure then looks like this:

     CREATE OR REPLACE PROCEDURE process_employee ( 
          employee_id_in IN number) 
     IS 
          l_full_name employee_rp.fullname_t; 
     BEGIN 
          l_name := 
               employee_rp.fullname (employee_id_in); 
          ... 
     END;

Now, that's better. No SQL, no hard-coded VARCHAR2 declaration, no hard-coded formula.  In fact, hmmm. I have heard all this talk about "SOA" – service oriented architecture. And I've been wondering how it fits in the world of PL/SQL. Well, here it is: for the developer writing the process_employee procedure, the employee_rp is a service of sorts, providing a pre-defined set of types and subprograms that can be instantly put to use in my code.

Well, that's all I've got time for right now. It's 11:18 PM, and that means it.s time to build some tutorials for the online T3 (Testing Tips and Tricks) library for Quest Code Tester for Oracle.  Till my next post, may your code bring peace and satisfaction!