Nov 28

Written by: StevenFeuersteinTW
Wednesday, November 28, 2007 1:24 PM 

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. I showed in a previous blog (Don't start with a blank slate!) how you can create a template of your own inside Toad.

In this blog entry I take a different approach: I offer (in the last section of the entry) an XML document that has within it a set of templates that I often use in my own development. The names of all these templates are prefixed with the letters "sf." You can then move these templates into your own template environment, whether in Toad or any other tool. Please see end of this blog entry for a challenge to my readers – you could win a signed copy of my brand new Oracle PL/SQL Best Practices, 2^nd Edition!

My templates range from simple shortcuts (I hate having to type "DBMS_OUTPUT.PUT_LINE" over and over again) to more elaborate, best-practice oriented templates, such as a standardized structure for new packages, procedures and functions. Here is the list of the current contents of this file:

• Call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
• Call DBMS_UTILITY.FORMAT_ERROR_STACK
• Call DBMS_UTILITY.FORMAT_CALL_STACK
• Call DBMS_OUTPUT.PUT_LINE
• Call DBMS_OUTPUT.PUT_LINE to display a Boolean
• Sparse Collection Scan: scan the contents of a collection that may be sparse, using a WHILE loop.
• A BULK COLLECT query with a LIMIT clause
• A standard package format that you can use as a starting point for development of new packages. Contains a standard header, initialization section, procedure and function templates.
• A standard procedure format that you can use as a starting point for development of procedures.
• A standard function format that you can use as a starting point for development of procedures.
• WHEN OTHERS clause that reminds you of all the information you should be gathering when you handle an exception. WHEN OTHERS clause that relies on the Quest Error Manager  to gather all necessary data. You decide how many name-value pairs you need.
• A FORALL statement that includes SAVE EXCEPTIONS and the code needed to iterate through that pseudo-collection
• Standardized approach to documenting workarounds, including what needs to be done to back out the workaround and implement the native, bug-fixed approach.

I encourage you to look through these see which of them you like the best or feel you can use. Then modify them to meet your own standards, and put them to work inside your editor.

Loading these templates into your editor

I chose am generalized XML document format so that these templates could be easily transformed into whatever format is supported by your IDE.

Here is the format I use in my XML document: 

Here is an example of one such template, a shortcut to avoid writing the 20 characters needed to ask PL/SQL to "show me" data:

Here is the format used by Toad (from the LexLib.lxl file in the User Files directory):

Obviously, it is different and I am not yet able to offer a utility to convert from my XML format to Toad's format.

But I do offer this challenge and reward: the first person who sends me a utility to convert an XML document with the structure shown above into both Toad's and SQL Navigator's template file formats will receive a signed copy of my brand new Oracle PL/SQL Best Practices, 2^nd Edition. And just to make this fair for those of you that might be reading this from a different time-zone, if you submit your answer and are not the first submission you will be entered in to a drawing to win a copy of my book. Your program must handle templates that have zero or more parameters and multiple lines of code. Please make sure you have tested it fully before passing it along.

Please send your implementation of this utility to me at steven.feuerstein@quest.com.

Thanks in advance!

The template text