7 Functions Of Oracle SQL You Might Not Have Discovered

7 Functions Of Oracle SQL You Might Not Have Discovered

You may have come across PL/SQL run time errors on several occasions. These errors can arise from several different sources such as hardware faults, design issues, coding issues or any other sources. These errors cannot be anticipated and when encountered can create plenty of problems. However, it is possible to handle these errors by coding exception handlers into the program. These exception handlers will make sure that the program can continue to operate even when the errors are present.

Internal and user-defined exceptions

A condition that allows the program to run even when an error has encountered in PL/SQL is called as an exception. These exceptions can either be defined internally or they can be user-defined. Exceptions such as ORA-27102 and ORA-22056 are good examples of internally defined exceptions. There are other internally defined exceptions too such as STORAGE_ERROR and ZERO_DIVIDE that serve a specific purpose and help keep the program running. Oracle also allows you to define exceptions within the declarative part of a PL/SQL subprogram, package or a block. These exceptions will be called whenever the program encounters a specific error. However, you need to know that the internally defined exceptions are implicit and the user-defined exceptions are explicit in nature.

Including an error checking code wherever bad input may occur

Reliability is the most crucial aspect of any database program and as a result, it is very important to check the errors as well as handle exceptions to ensure that your program can handle all possibilities of encountering an error. One of the areas that can cause a lot of problem includes the input data error. Whenever the input is bad, there is a good chance that your code may run with null parameters or the code may not fully execute. This can lead to the database returning your queries with either more number of rows or not returning anything. Try this link to more detailed documentation on handling errors: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS007

Placing exception handlers

The Oracle SQL allows you to place suitable error exceptions throughout the program. If you place these exceptions at the right places, you can avoid any issues with your queries. It is a well known fact that errors usually take place in the arithmetic calculators or database operators or string manipulators. Other instances where errors may occur include whenever there is a shortage of disk storage memory. Even though this may not have any relationship with your code, it is a good idea to take preventive measures wherever possible.

WHEN OTHERS in exception handlers

Oracle SQL allows you to provide handlers for certain operations that may cause any ORA error. It is a good practice to be aware of the causes and also the names of all these frequently occurring errors and predefined exceptions. If a particular database operation is causing the ORA error to take place, you can simply write a suitable handler for each of these errors using the WHEN OTHERS function.

Reference: http://docs.oracle.com/cd/E11882_01/timesten.112/e21639/exceptions.htm#TTPLS191

Handling issues with database

In many cases, an error may occur because the database may not be in it’s ideal state. In this situation, it is very important to make sure that your program is robust enough to work even with an unstable database. This means, the queries you send to the database may have changed and modified either by adding or removing rows and columns. The Oracle SQL allows you to avoid this issue by allowing you to declare the variables such as %TYPE and %ROWTYPE and avoid this problem.

Debugging information in the handlers

It is very important to debug all the exception handlers that you have placed in a program. This can be done with the help of a subprogram known as PRAGMA AUTONOMOUS_TRANSACTION. When you invoke this program, the debugging information would be committed even when the subprogram is rolled back from performing it’s function. This will make sure that all the debugging information from the exception handlers will be extracted at all times.

Other functions

You can also make use of several other functions to keep your database free from error and avoid any instance of storing bad data. Other than these functions, it is also possible to trap exceptions. It is possible to trap all the predefined TimesTen errors and any user-defined errors with a range of predefined exceptions such as the ZERO_DIVIDE. It is also possible to use the RAISE statement when trapping any user-defined exception along with the RAISE_APPLICATION_ERROR in the executable or the exception section or both the parts of your program.

The following two tabs change content below.


Seth Williams has been writing with Firebox Training, http://www.fireboxtraining.com since July, 2011. He writes on many topics across IT programming, training and developments in the industry. Through spreading wisdom across forums and tech blogs, Seth has realized an open source approach to training professionals across the globe is the way to go.

Latest posts by swilliams (see all)

Comments are closed.