PL/SQL FOR DUMMIES

· Frequently refer to a copy of the system data model showing the relevant portion of the database. Understanding the data model is important because you need to understand where the code you are writing fits into the bigger system. By keeping a copy of the entire system data model handy, you can continually check to make sure you understand what your piece of code is supposed to do and what other portions of the system might be impacted by it.

2. Communicate effectively

As a developer, you probably spend no more than 30 percent of your time sitting alone, in front of a terminal, writing code. Most of the time, you are working with a second developer (or pair programming), talking to someone about getting the system requirements, or figuring out how to write the code.

In all three project failures mentioned earlier, one common mistake made was that people who were aware that the system failures were likely to occur either neglected to call this to the attention of the system architects or were ignored when trying to point out problems with the system architecture. As the rules of the system are captured and coded, you might discover that the architecture is inadequate to support the system requirements. PL/SQL programmers should recognize possible problems in the system architecture and point these out to the database designers and system architects so that the necessary changes can be made.

3. Create a code specification

Before you ever start writing code, you need written specifications. Writing good code specifications encourages developers to think about what the code does and puts this information on paper. Having this document makes talking to others about the code much easier and allows better sharing of information. In modern development environments, it is not uncommon to have Java and .NET and PL/SQL developers on the same team. However, all of these developers might be unable to read each others’ code. A specification written in English or pseudo-code allows the document to be readable by all members of the team.

A good code specification describes what the software or program modification entails at a reasonable level of detail. The specification document should describe the function of the code as well as outline key design decisions. For example, the document should address the following questions:

· Why is this code or modification being written (in business terms)?

· What procedures will be created?

· How will these procedures be named?

· What modifications to the database (new tables, columns, and so on) are required?

· What are the detailed design constraints, if any? (For example, “This is a rarely called routine from the user interface. As long as it executes in less than half a second, it is okay.” or “This is a critical batch routine that must execute in under an hour.”)

The specification should also include any special factors that people need to take into account when developing or testing. An example might be “This routine will be executed by many simultaneous users.”

By including all of this information in the code specification, the probability that the team will understand the requirements and write good code is significantly increased.

Remember that the goal is to create functioning code, and not to create a large pile of documentation that few will read. Do not assume that the code specification will be complete, accurate, or not undergo changes as the project moves forward. As more code is written and changes are needed, you might need to talk to users for additional clarification about some undiscovered requirement or subtle area of the program. Having the specification handy provides a starting point for discussion.

Integrating best practices into your code

When trying to decide how to proceed with coding a new project or even making changes to an existing software project, how do you determine the appropriate code structure? This section describes some of the things you can do to write effective PL/SQL code that is maintainable over time, as well as avoid some of the pitfalls common to many PL/SQL projects.

Stub out your code

Don’t just start writing code right from the beginning. First, figure out how the code should be structured and create the necessary procedure and function headers with no code in them. This gives you an idea of what information will be needed at each point in your routine and what each routine needs to return. These little “stubs” of code will help you see the overall routine. If the project is very large, it will be easier to pass parts of the code to someone else to write by using this “code outline.” By following this stubbing method, your code will naturally be well structured and easier to debug if something goes wrong.

Check the architecture as you go

Be sure that the underlying system architecture is sound before spending days, weeks, or even months writing code. For example, one large batch routine encountered was architected to make so many round trips to the database that, even if all the complex logic that the program needed to perform executed in zero time, the program would never execute within an acceptable time frame. It had to be almost entirely rewritten in order to perform adequately. In another situation, a program was designed to take precise code statements and translate them into business language statements. The first attempt to create the program was not able to logically manage the required elements. Although this early version worked in limited circumstances, the code had to be completely rewritten before it was usable in the larger system.

Following these guidelines will help ensure that the system architecture is sound:

1. Periodically take a step back and evaluate the system logic. Does the approach being used make sense? Draw the algorithm on a white board and discuss it with a colleague. Sometimes, the exercise of simply describing the algorithm to someone else can help clarify your thinking and prevent serious coding errors from occurring.

2. Have someone review your code with you and make sure that it works. Do not be afraid to take the time to run some tests on your code.

3. Check the performance time of your code and its memory requirements. Just because a particular architecture works well with a few sample data points and a single user, the same code will not necessarily work on a production system with 100 million records and 200 simultaneous users.

4. Be willing to discard code and start over. Despite the planning and discussions, you might create a bunch of code and still feel that something is not working right. Often, the pressure to covering ground is so great that no one bothers to notice that the project is headed for failure. Stop periodically and ask these questions:

· Is the team moving in the right direction?

· Will the direction in which the team is going ultimately result in a working system?

You might face an almost irresistible temptation to forge ahead because so much time and effort has been invested. Unfortunately, in many cases, if your intuition is telling you that the code will never work correctly, it is probably right. It is better to discard all of the old code and start over rather than trying to fix badly architected code.

“You can’t see the forest for the trees” is an important phrase to remember when writing PL/SQL code. Do not allow yourself get so lost in endless routines that you lose sight of the big picture. Every two weeks, you should climb to the top of the tallest tree around (figuratively speaking, of course) to see where you are, make sure that you are still going in the right direction, and look out for any obstacles between you and your goal. Then climb back down the tree, have a group meeting, and have the project manager clearly point in the direction where everyone should be heading. The number of huge project failures that could have been prevented by using this strategy is surprisingly large.

Prove sections of code work with test cases

The first time you use a feature that you have never used before, make sure you understand how it works by writing a separate, small, example program to demonstrate its functionality. Similarly, when you are embedding a complex expression such as a combination of INSTR and SUBSTR or regular expressions, isolate the piece of code in a simple SQL expression to prove that the code is correct. This can save you hours of debugging time later.

One way you can make sure that your code works is by setting up small test cases using DBMS_OUTPUT statements to print out interim results. Do this frequently for each section of code written.

Use code libraries

Although you may think that you are the only person who will ever need to use the code that you write, this is rarely the case. In any large system, the same code will have been written dozens of times (frequently by the same developer). If that code had been placed in a code library and referenced each time it was used, there would not only be less code, but the remaining code would be less prone to errors. Every time a piece of logic is rewritten, there is the chance that the code will be written slightly differently. This can cause code errors that are very difficult to find.

Code should be well documented and placed where it can be reused easily. Code that is used only once in a large system is the exception rather than the rule.

There are usually hundreds of reusable components in a large system, so you need to divide them into logical packages to avoid losing track of them.

Keep the code maintainable

The technology to support the myriad of information systems being used to work with databases seems to evolve faster and faster with each passing year. Designing and coding a system that can be used and easily maintained over time requires some thought and skill. Make sure that someone else down the road will be able to read and understand your code and find potential problem areas.

Think about performance

In addition to understanding what the program you are creating needs to do, you need to have some sense about how fast the code needs to execute and return the desired information. To create a month-end routine that must interact with other batch routines and execute within a 4-hour time window, your portion of the program might need to execute in 10-20 minutes. Understanding what constitutes acceptable performance in a given situation is very important.

You also need to know how often a given programming routine will be run. PL/SQL is capable of supporting a range of capabilities, some of which are used only once, such as data migration routines or low-level translations for changing system time into local time around the world that might be accessed millions of times a day. If a routine will be run only once, performance and maintainability of the code are not critical issues.

Be careful before deciding that a routine will never be used again and discarding the code. Very often, you will find that you need to run the same or a very similar routine to one you wrote a few months ago.

Compile as you go

Many inexperienced programmers create an entire first draft of a program (possibly hundreds of lines of code) without ever compiling it. When they do compile the code for the first time, hours of debugging are usually required. Writing more than a few lines of code without at least one mistake is very unusual, even for experienced programmers. Sometimes errors are nothing more than simple misspellings or typos, but errors are inevitable.

Compile your code every few minutes from the very beginning of the process. When writing a new function or procedure, create the function name with one line of code (which might even be NULL;) and save it before doing anything further. Every few lines, compile the code again to check for errors. Never write more than about ten lines of code without compiling it.

Debug efficiently

If your code does not work, how can you fix it? It might not compile or it might compile and not do what you expect it to do. The most important thing to remember when debugging is to always start with a piece of code that works. The first step to take when the code will not compile or behave as expected is not to look through the code to try to find the problem. Programs can be made up of thousands of lines of code. The problem might not be located in an obvious place. Comment out portions of the code until it runs successfully. This will help to pinpoint where the problem is occurring.

Commenting

Commenting is a very effective technique that allows you to remove selected portions of the code to help isolate problems quickly and efficiently. This same technique can be used for both compilation and logic errors.

The SQL compiler is not perfect. Sometimes it will indicate that an error exists in a place that is far from the actual mistake. Unfortunately, this often occurs in some of the most common types of errors, namely forgetting a semicolon, missing a comma in a SELECT statement, and missing an END statement.

With a compilation error messages may not be very helpful. Therefore you should not let your routines get too large in the first place. If you limit your routines to no more than a few hundred lines, even a problem that results in a misleading compilation error will not be as difficult to find.

When your routine is in a package, it is common for packages to contain hundreds, if not thousands, of lines of code. Finding an error will be more difficult without using the commenting technique to sequentially add portions of the routine until the error is found. In complex routines, it is helpful to comment out individual lines to narrow down where the compilation error is occurring.

The technique of commenting and un-commenting portions of a routine to help isolate a problem is very easy to use. A programmer should always have an idea about where to find the problem area in the code. It is acceptable not to know how to fix the problem, but even beginning programmers should be able to locate the precise trouble spot in the code.

Finding out what the program is doing at various points

Using a PL/SQL Integrated Development Environment (IDE) often includes some sophisticated debugging functionality that allows you to set watches (where you can see the values of variables) and breakpoints (places where you pause the program) in your code. Knowing how to use these features will greatly assist you in finding errors. Each IDE has its own debugging features. You might also want to use DBMS_OUTPUT or autonomous transactions to log information to a database table.

Testing your code

Often, the members of the Quality Assurance (QA) team who test the code are the most unpopular members of the development team. They are the people who get in the way of pushing things out the door. Inexperienced developers will do anything they can to avoid the QA process. Experienced developers recognize that no code is perfect. Having another set of eyes looking at your code greatly reduces the chance that errors will be missed.

If the QA team does nothing more than making sure you have filled out the proper paperwork and put a comment block at the top of your code, your QA process is not sufficient. The QA process helps to make sure that code is well written and that standards have been followed.

It is not sufficient to deliver a program after running it once without noticing any errors or problems. You must be much more thorough. You must make sure that your code does what it was intended to do. Proving that the code you have written works in the way you expect is just as important as writing it correctly in the first place. In recent years, software testing has become a much more disciplined practice.

Understanding the following basics of testing can improve the quality of your code:

· The essence of testing is the idea of an assertion. You assert that the software will do X when Y. Each test can be translated formally into such an assertion.

· There are different types of tests. Some involve a manual set of steps that need to be performed by a human tester. However, whenever possible, tests should be written as code scripts that can be easily run every time someone modifies the code.

· The cost of delivering bad software is much more expensive than testing. If your code isn’t tested carefully enough, you might think it works, and then later someone will discover that the system has a problem. The problem might require many hours (or weeks) to be tracked down and isolated. Then, many more hours (or weeks) will be spent figuring out how to fix the code. As a result of the problem, the database might have incorrect data in it that will require time to fix. Testing software is expensive, but not testing software is much more expensive.

· Even thorough testing does not guarantee perfect code. It is not possible to test everything. The most you can do is ensure core functionality. Anyone can easily miss subtle problems. Usually, the best approach is to test all the main functions and deal with the bugs when they are found.

If your software has to be perfect, you have a very difficult job. Ensuring perfection in software means that you will spend many times the development cost of the software in testing that software. When building software where bugs can result in serious consequences, count on spending lots of time testing. Software that controls medical devices, some military systems, and systems that control financial institutions all need to be tested differently from most business systems. Even then, it is very hard to find every problem. In such cases, the errors can have catastrophic effects on the organization. In one well-publicized software error, a simple coding error caused a multi-billion dollar loss to AT&T and an outage of phone service along most of the eastern seaboard.

Top 10 PL/SQL "Do's"

The following list includes a brief description of ten of the most important things to remember when working with PL/SQL

1. Use SELECT INTO correctly

Each time you use SELECT INTO, remember that your query might return more than one row, or no rows at all. Therefore, you need to include exception handlers to take care of these situations if there is any possibility of them occurring. The reason this is so important is that your code might execute correctly for many months before some unusual condition shows up that causes it to fail. Then you can spend days trying to figure out what went wrong.

When working with the BULK COLLECT clause, you need to check for the number of fetched records because the exception NO_DATA_FOUND is never raised, even when no rows were fetched.

2. Do not assume column order in DML statements

When you have a DML statement in your code, you should explicitly list the columns that you are referencing. Never assume that the number and order of the columns in a table or view are constant. Columns that are added to a table later will break your code. Rebuilding your database might result in the default column order being changed.

When working with a large number of columns, using variables of type RECORD is a better approach. With this approach, you do not have to list anything in the INSERT statement, because you are inserting a variable into the table that is defined as a row from the same table. There is no chance for inconsistency.

Another good reason for having a variable of type RECORD is that it is a much easier way of passing data to different routines. For example, you might have a procedure that will perform validation for the data you entered. You would need to pass only one variable and not a whole list of columns.

3. Use the NOCOPY command

Use NOCOPY when passing an IN/OUT parameter to avoid passing by value. This is mainly a performance rather than a logic issue.

Normally when you pass an IN/OUT parameter, Oracle makes a copy of the parameter in memory and uses that copy in the called program unit. When the program unit is executed, it copies the values back to the original variable. Performing this operation once does not take a lot of time, but in a procedure that is called thousands of times, the time can add up.

NOCOPY is just a hint, not a directive. This means that Oracle may ignore it. Also, depending upon the version of Oracle used, there are a number of restrictions on using NOCOPY. But if you violate one of those restrictions, there will be no raised error. Oracle will ignore your hint without notifying you.

Another thing to be aware of when using a NOCOPY hint is exception handling. If you are passing a variable by value to a subroutine and that routine fails, the original value of the variable does not change. But if you are passing the variable by reference, the failed subroutine might have changed the original variable’s value (before it failed), unless you have an exception-handling block in that subroutine.

4. Be careful of function calls that impact performance

If you have a loop and need to calculate some values, ask yourself whether you can perform the calculations before the loop, or reduce the number of times the calculations are performed within the loop. Otherwise, you might be running the same function many more times than needed. You can decrease that overhead in a number of ways. The idea is to cache the calculated data in the local variables.

The same logic of reducing the number of executions is true not only for user-defined functions and procedures, but for built-in ones. Even executing SYSDATE several hundred thousand times could cause some problems. If you need to move a few million records from a production table to an archive, use the following logic:

A function in an SQL statement will execute at least once for every row returned. However, sometimes in SQL queries joining multiple tables, the function might execute so many times that the query never returns. You have to look closely at the execution plan of the query to see how many times the function will execute.

5. Beware of implicit commits

Any DDL command causes an implicit commit. This is not a major issue because you will not usually have DDL commands in your code. But every experienced developer has encountered situations similar to the following when building a system: Imagine that you insert or delete a bunch of data, and then you recompile your procedure and your changes are instantly committed. The problem is that you might not need to commit (for example, you just discovered a bug in your code).Always think about any uncommitted changes whenever performing DDL operations.

Be careful! The TRUCNATE TABLE command is also DDL and forces an explicit commit, as well.

Each time you work with the data and the code simultaneously, try to do only one thing at a time. Always use an explicit commit or rollback before you start modifying your code.

The same problem of unintentional commits could happen if you are using Dynamic SQL to build DDL statements on the fly. What if you forgot to check whether the hard drive had enough space and the CREATE TABLE failed? Even a failed DDL command issues a commit. As a result this command updates the table even though the backup was not completed.

This problem has a number of solutions. The simplest is to modify the code so that, at the moment of DDL execution, there is no uncommitted data that would be rolled back in case of a fatal error.

An alternative and more efficient solution involves using autonomous transactions. Autonomous transactions do not see uncommitted parent changes. You have to either commit or roll back your changes before the return (whether the return is successful or not) from the function or procedure declared as an autonomous transaction to ensure that your function can be used appropriately.

6. Only use one RETURN statement per function

Keeping function returns to a minimum is important. One very useful technique is to use a single OUT point in the routine. This means that

· Functions have only one RETURN statement.

· The RETURN statement should be the last line before the exception block.

By remembering these rules, you can clearly see when a function expects to return a value.

7. Use WHEN OTHERS to save debugging time

Most programmers know better than to use WHEN OTHERS THEN NULL in their code because it hides errors and makes the code harder to debug. However, using the exception WHEN OTHERS can save you a lot of debugging time because it captures unpredictable events.

Many things can go wrong in your code, but explicitly placing exception handlers to cover all situations is impossible. On the other hand, debugging an exception raised somewhere deep in a chain of function calls is also difficult.

The exception WHEN OTHERS allows you to intercept these unspecified exceptions. But what do you do with them? One option is to log an error message. Although developers need to know what is going on in the system, it is not possible to return an error to the end user in all cases.

If you do not want to intercept the error, you can always re-raise an exception in the exception block. Using this approach, the routine raises the same error that it would raise without exception handling, but now you have a log record indicating the existence of an error. This log can be very useful in debugging.

8. Understand when to pass parameters or use global variables

In a stateless, Web-based environment, cursors, functions, and procedures sometimes are unable to reference values outside of themselves. Instead, they should pass parameters.

Although global variables might appear very convenient, there is a good reason for passing parameters into the routines instead of keeping global values. In an environment with stateless Web applications, you cannot be sure of getting the same session each time you request the data.

Code can be formatted for a client/server environment, where packaged variables can be used as session-level global variables. But in a Web-based environment, you cannot always guarantee that your second request will go to the same session. This means that, instead of keeping global variables in the session, the global variables become properties of the client. (Some Web developers store them as cookies in browsers.) Therefore, you need to pass all parameters explicitly. Before writing any APIs to support front-end developers, you need to understand the environment in which they are working. If you cannot guarantee that subsequent calls will be in the same session, you need to make sure that all necessary variable values are passed on each call to the program unit.

9. Use implicit cursors when appropriate

Implicit cursors are marginally faster than explicit cursors for single record fetches. But, the main reason to use them is that they make code easier to read. Instead of having a cursor in the declaration section of your code that is referenced in the body of the code (as with an explicit cursor), the cursor is defined right where it is executed.

Because there is now no good reason to use explicit cursors whenever you need to get a single record, use implicit cursors instead. Code using implicit cursors is usually cleaner, but keep the following issues in mind:

· You cannot simply replace explicit cursors with implicit ones. Explicit cursors do no raise the exceptions NO_DATA_FOUND (only the first fetch is null) and TOO_MANY_ROWS. If developers did not bother to fetch a second row in the cursor, this does not mean that there is no second row.

· To store a fetched value, you can reference an explicit cursor with %ROWTYPE, but with an implicit one you have to think about the appropriate variables into which to fetch the data.

10. Dates are numbers

Internally in Oracle, dates are really numbers, so you can perform numeric operations against dates. Oracle 9i and 10g have new datatypes to store dates: TIMESTAMP and INTERVAL. But good old dates are still very useful when you do not need to know the time zone or granularity below the time unit of a second. Internally, dates are stored as numbers, so you can do whatever you want with them. A common set of date tasks can be resolved if you think about dates as numbers.

Top 10 PL/SQL "Don’ts"

The following list includes a brief description of ten of the most important things NOT to do when working with PL/SQL. Be sure to preface each of the headings in this section with a big DON'T.

1. Catch an exception with WHEN OTHERS THEN NULL

Never catch an unidentified exception without logging information about it. The exception handler WHEN OTHERS is extremely useful, but it can also be the source of the most dangerous line of code that can exist in PL/SQL, as shown here:

begin

...

exception

when others then

null;

end;

Unless you are in the debugging or development mode, never use an exception handler like this, especially in production instances of a system. All exception handlers that have WHEN OTHERS without additional activity (you might need to have that exception) should not raise an exception if something goes wrong, but instead, returning and log an ERROR You can use this logic if, because of front-end restrictions, you cannot throw Oracle exceptions (for example, in a Web-based environment). This technique is a cleaner way of notifying the front end that something has gone wrong without destroying performance, and it also provides useful debugging information.

2. Forget to handle NULL values

Operating on variables or columns that might contain NULL values without explicitly handling these NULL values can cause problems and produce strange results. The reason for this is that NULL is handled differently from other values. You should keep the following rules in mind:

1. All logical operations (including NOT) that involve NULL values always return FALSE.

2. All operations (built-in functions, arithmetic) with NULL return NULL, with the following exceptions:

· Concatenations of strings ignore NULL.

· DECODE can compare values with NULL.

In grouping functions (SUM, AVG, COUNT), you also need to watch out for NULL values. The rule is that these functions process only not-NULL values; but if all values are NULL, the result is also NULL, as shown here:

3. Create unintended Boolean expressions

Be careful when building complex logical conditions. You need to group logical conditions appropriately so that others can maintain your code in the future. After grouping the rules, convert the group into logical operations. Each group of conditions should be enclosed in parentheses.

You should use the same syntax rule of enclosing condition groups in parentheses, not only in PL/SQL but in SQL, too. Remembering this could save you hours of debugging afterward.

4. Forget to close an explicit cursor

Each time you use an explicit cursor, don’t forget to close it. Using explicit cursors is good coding practice. Remember that the database parameter OPEN_CURSORS defines the maximum number of cursors that can be open at the same time. The value of the variable might change from one environment to another, but the point is that the number of cursors is limited. Forgotten cursors that are left open can bring a system to a halt.

But with dynamic SQL, you have to use explicit cursors. If you stick to the following rules, you should be able to use explicit cursors successfully:

· When you start typing a routine, immediately include both the OPEN and CLOSE cursor statements.

· Never add a RETURN clause before closing the cursor.

· In the exception-handling block, always check to see whether explicit cursors are open, and if so, close them.

If you are using recursive calls to the same routine, be very careful about using explicit cursors. In a structure with 20 levels of hierarchy, at some point, you are likely to have 20 cursors open simultaneously. If you have a large number of users, this could cause your system to reach or exceed the maximum number of cursors.

Oracle is fairly smart about closing cursors if you forget to do so. When a program unit terminates, all cursors that it opened are automatically closed. But relying on this capability is dangerous and can ultimately result in having too many cursors open at once, so close your cursors explicitly.

5. Start endless loops

Endless loops can cause endless problems including freezing your system. Each time you create a loop, think about how the code will exit from the loop.

The best way to avoid endless loops is to use CURSOR FOR loops or FOR loops whenever possible. If you do not need to interrupt processing, always use a FOR loop. This is much safer approach.

6. Reinvent the wheel

You should not try to create code structures that have already been developed for you by Oracle. Before you start coding, it is a good idea to review an Oracle manual with the list of built-in functions. This tip is especially true when working with strings.

There is one more reason to use built-in functions. Oracle has tuned them to improve performance speed. If you need to parse very large strings or even CLOBs, built-in functions can significantly improve performance.

7. Convert datatypes implicitly

Although Oracle sometimes can implicitly convert one datatype to another, this does not mean that you should trust implicit conversions of datatypes, especially dates. In fact, this is not a good idea at all. Another common problem with implicit conversion occurs when working with numeric values that are not exactly numeric. Not everything that looks like a numeric value is a numeric value. Oracle cannot differentiate these cases. You must define the appropriate datatypes.

8. Cut and paste code indiscriminately

Sooner or later, all developers are tempted to copy and paste an existing piece of code, modify it a bit, and be done with it. But a quick shortcut during development can cost more time and effort than you might think down the road.

Copying and pasting code does have some advantages:

· You are not touching the existing code, just adding code.

· The code has already been checked and therefore does not contain syntax errors.

· You do not need to retest the code that is dependent on the original code.

The drawbacks of cutting and pasting are:

· The same modification has to be replicated everywhere.

· The code becomes less readable and more spaghetti-like.

Ironically, the advantages are relevant only for developers, and the disadvantages are relevant for those who maintain the system. Although time spent by developers to create the code can be very expensive, the cost of ongoing errors when maintaining the code is hundreds of times higher.

Although there is technically nothing wrong with cutting and pasting code, a few development hours saved can mean hours of downtime for an entire organization, so cut and paste at your own risk.

9. Ignore code readability

The next person who looks at your code should not have to guess about your naming conventions or program structure. Ongoing maintenance can consume large portions of the total cost of building an information system. Your goal as a developer should be to think about the long-term maintenance of the system when you write code.

10. Assume code does not need comments

There is no such thing as self-documenting code. The mistake of thinking that working code is perfectly self-documenting has caused thousands of lost hours in organizations all over the world. Even with the best naming and coding conventions, you must still explicitly note many details. And you do that by adding comments.

In the many systems that require complex code, the trick to adding useful comments is to make sure that you (or someone else) will be able to understand the code a few months (or even years) later. Writing code that enables a system to be efficiently maintained is a critical part of building successful information systems.

All comments should include the following elements:

· A header that includes the following:

· Basic information (ownership and functionality)

· Functional comments that explain the implemented solution and possible issues with the code

· A change log to keep track of all changes to the routine

· Inline comments, which separate different parts of the code and explain specific code lines

You shouldn’t over comment your code. A comment on every line is not necessary. Use your judgment and plan an external code review to determine how much commenting your routines require.

Getting Help and Keeping up to Date

SQL and PL/SQL are constantly evolving. With every release of Oracle, new features are added and older features get better. The best practices of this year will be outdated coding next year. Features that have been added in version 9i and 10g of Oracle are used only by a minority of developers. The best ways to keep up to date are

Buy books

Fortunately, plenty of good PL/SQL books are available on the market. Three of the most important are:

· Oracle PL/SQL Programming, Steven Feuerstein & Bill Prybyl, 2005, O’Reilly

· Oracle PL/SQL for DBAs, Arup Nanda & Steven Feuerstein, 2005, O’Reilly

· Oracle Database 10g PL/SQL Programming, Scott Urman et.al, 2004, McGraw-Hill

· PL/SQL for Dummies, Rosenblum & Dorsey, 2006, John Wiley & Sons

After you have been coding for a while, you will easily be able to read a PL/SQL complete reference book cover to cover. No matter how much coding you have done, you will be amazed at how many things you did not know.

Go to conferences

One of the best-kept secrets in the industry is that you can see the same content at almost any of the large Oracle conferences. The same speakers tend to go to most of the national and regional conferences, and many of them frequent local Oracle user group meetings as well. The difference is that you will see more vendors at a large conference, and there will be more presentations from Oracle employees. Although large conferences offer a larger variety of topics and presentation levels, you will also pay a lot more to attend a large conference.

For developers, the two best conferences are the Oracle Development Tools User Group (ODTUG, www.odtug.com) and the Independent Oracle Users Group (IOUG, www.ioug.org) annual conferences. These are both technically focused events. ODTUG is geared for developers. If you are also interested in DBA topics, you should go to the IOUG conference. IOUG and ODTUG hold their conferences in different cities each year. Oracle OpenWorld in San Francisco is Oracle’s annual conference, which usually has more Oracle marketing presentations and fewer user papers; however, it has the most attendees and biggest vendor hall. If you want to find out about the latest that Oracle has to offer and hear about it directly from Oracle, this is the best conference to attend.

Regional conferences can provide almost as much technical content as the national conferences, and you might not have to travel as far or pay as much. The Rocky Mountain Oracle User Group (RMOUG), the New York Oracle Users Group (NYOUG), the Northern California Oracle User Group (NOCOUG), the Mid-Atlantic Oracle Users Group (MAOP-AOTC), and others all host annual conferences that have multiple tracks and provide excellent content.

Join your local Oracle user group

Join your local Oracle user group and get to know people. It is very helpful to know someone you can call when you have a question. You should also become an ODTUG and/or IOUG member. You will receive discounts for the conferences, access to conference papers and presentations online, and well-written journals with technical articles about a variety of Oracle-related topics.

Many large companies have their own internal user groups where you can exchange tips that you have discovered. Smaller companies might host brown bag lunches where you can take turns presenting useful tips. Be sure to take advantage of these resources, as well.

Use online resources

Surfing the Web is one of the best ways to find out about PL/SQL features. Most conference papers will be posted on one or more Web sites. You can also post questions to various Internet list-serves and get your questions answered (usually within a day). Probably the best list for PL/SQL questions is the ODTUG-SQLPLUS-L list. You can sign up for this free list (you do not even have to be a member of ODTUG) at www.odtug.com.

About the Authors

Dr. Paul Dorsey is the founder and president of Dulcian, Inc. an Oracle consulting firm specializing in business rules and web based application development. He is the chief architect of Dulcian's Business Rules Information Manager (BRIM^®) tool. Paul is the co-author of seven Oracle Press books on Designer, Database Design, Developer, and JDeveloper, which have been translated into nine languages as well as the Wiley Press book PL/SQL for Dummies. Paul is an Oracle Fusion Middleware Regional Director. He is the President of the New York Oracle Users’ Group and a Contributing Editor of the International Oracle User Group’s SELECT Journal. In 2003, Dr. Dorsey was honored by ODTUG as volunteer of the year, in 2001 by IOUG as volunteer of the year and by Oracle as one of the six initial honorary Oracle 9i Certified Masters. Paul is also the founder and Chairperson of the ODTUG Business Rules Symposium, (now called Best Practices Symposium), currently in its seventh year, and the J2EE SIG.

Michael Rosenblum is a Development DBA at Dulcian, Inc. He is responsible for system tuning and application architecture. He supports Dulcian developers by writing complex PL/SQL routines and researching new features. Mr. Rosenblum is the co-author of PL/SQL for Dummies (Wiley Press, 2006). Michael is a frequent presenter at various regional and national Oracle user group conferences. In his native Ukraine, he received the scholarship of the President of Ukraine, a Masters Degree in Information Systems, and a Diploma with Honors from the Kiev National University of Economics.