Sep
26
Written by:
StevenFeuersteinTW
Wednesday, September 26, 2007 10:23 AM
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:
And that, dear friends, is a very unproductive way to write code (from "scratch" each time), much less write code that meets the coding standards of your team, much less write code that incorporates best practices.
Fortunately, you can take advantage of Toad Code Templates to both improve productivity and increase code quality, when creating new packages.
Here are the steps I took to standardize package design and development:
1. Create a template for any new packages in my application.
2. Store the template in the Toad's Code Template library.
3. Invoke the template whenever I or anyone else on my team needs to create a new package.
The following sections show precisely how to perform each of these steps.
1. Create the template
The following template may also be found in the demo.zip file stdpkg.sql file. Here are some of the main features of this template:
-
It includes a package-level initialization program that is initially NULL and is called in the initialization section. This encourages consolidation of all package setup code into a single program, which may then also be called from outside the package.
-
Templates for procedures and functions within the package. For each of these....
-
Initialization and cleanup modules for each subprogram to encourage proper isolation of this logic, and invocations of cleanup on all exit points from the program.
-
A checklist of key coding best practices embedded as a comment in the subprogram bodies.
-
An exception section that repeats the cleanup and also reminds me to use the standard error management facility.
By placing such code and comments inside the
CREATE OR REPLACE PACKAGE pkg
/*
| Copyright Information Here
|
| File name:
|
| Overview:
|
| Author(s):
|
| Modification History:
| Date Who What
|
*/
IS
/* Decide whether or not you want to expose the package
initialization routine! */
PROCEDURE initialize;
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg
/*
| Copyright Information Here
|
| File name:
|
| Overview:
|
| Author(s):
|
| Modification History:
| Date Who What
|
*/
IS
PROCEDURE initialize
IS
BEGIN
NULL;
END initialize;
PROCEDURE procedure1
/*
| Copyright Information Here
|
| File name:
|
| Overview:
|
| Author(s):
|
| Modification History:
| Date Who What
|
*/
IS
PROCEDURE initialize
IS
BEGIN
NULL;
END initialize;
PROCEDURE cleanup
IS
BEGIN
NULL;
END cleanup;
BEGIN
initialize;
/* REMOVE AFTER REVIEW!
Main body of program. Don't forget:
** Use BULK COLLECT and FORALL for multi-row SQL.
** Hide complex rules behind functions.
** Hide SQL statements behind a table API.
** Think about error handling now!
** Keep your executable small (< 50 lines).
*/
cleanup;
EXCEPTION
WHEN OTHERS
THEN
/* Don't forget to clean up here, too! */
cleanup;
/* Use the standard error logging mechanism.
Example: Quest Error Manager, available at:
http://www.oracleplsqlprogramming.com/downloads/qem.zip
*/
q$error_manager.raise_error (error_code_in => SQLCODE
, name1_in => 'NAME1'
, value1_in => 'VALUE'
/* Up to five name-value pairs accepted! */
);
END procedure1;
FUNCTION function1
RETURN datatype
/*
| Copyright Information Here
|
| File name:
|
| Overview:
|
| Author(s):
|
| Modification History:
| Date Who What
|
*/
IS
/* The value returned by the function */
l_return datatype;
PROCEDURE initialize
IS
BEGIN
NULL;
END initialize;
PROCEDURE cleanup
IS
BEGIN
NULL;
END cleanup;
BEGIN
initialize;
/* REMOVE AFTER REVIEW!
Main body of program. Don't forget:
** Use BULK COLLECT and FORALL for multi-row SQL.
** Hide complex rules behind functions.
** Hide SQL statements behind a table API.
** Think about error handling now!
** Keep your executable small (< 50 lines).
*/
cleanup;
/* Just one return in the executable section! */
RETURN l_return;
EXCEPTION
WHEN OTHERS
THEN
/* Don't forget to clean up here, too! */
cleanup;
/* Use the standard error logging mechanism.
Example: Quest Error Manager, available at:
http://www.oracleplsqlprogramming.com/downloads/qem.zip
*/
q$error_manager.raise_error (error_code_in => SQLCODE
, name1_in => 'NAME1'
, value1_in => 'VALUE'
/* Up to five name-value pairs accepted! */
);
END function1;
BEGIN
initialize;
END pkg;
/
2. Store the template
So I like my template. Now it is time to turn it into a real Toad template. So I copy all of it into the clipboard and then right click inside the edit window. Way down at the bottom of the right click menu, you will see "Editing options." Choose that one.
Then you see the enormous, elaborate and quite wonderful World of Toad Options. Notice the bottom right corner lets you choose the language type to edit. Make sure PL/SQL is the current language and press the Edit button:
Then click on the Code Templates tab:
Then press the Add button to add a new one:
I used the string "stdpkg" as the name for my template:
And after pressing OK, I could paste in the contents of my template code:
Press OK, then OK a second time to return to the editor. The template is ready for use.
3. Use the template
And it sure is easy to use! The next time I open up a new editor to create a package, I simply type "stdpkg", then press Control-Space, and my phrase is replaced with all of my template package code.
Of course this is just one template of many you can and should create to standardize development and improve programmer productivity.