Understanding How to Prevent Common Oracle Errors

According to a study conducted by Oracle expert and blogger David Njoku, the top forty most commonly searched Oracle errors on Google have generated over half a million hits in the course of one month. There is not much of a pattern to the list; some of the errors are there because they occur frequently, but are fairly simple to manage, while others are there because of the level of difficulty involved with resolution.

According to a study conducted by Oracle expert and blogger David Njoku, the top forty most commonly searched Oracle errors on Google have generated over half a million hits in the course of one month. There is not much of a pattern to the list; some of the errors are there because they occur frequently, but are fairly simple to manage, while others are there because of the level of difficulty involved with resolution. Instead of the content of the errors sticking out, the sheer volume of traffic directed towards troubleshooting is what is most likely to draw your attention.

The problem with many online Oracle forums is similar to issues seen in the healthcare industry: a focus on reactionary care over preventative care. For many Oracle errors such as the most commonly searched error of all: the ‘ORA-12154: TNS: could not resolve the connect identifier specified’, there is a wealth of information available regarding approaches to quickly correct the error. What is lacking in these forums is a greater depth of attention aimed at remedying the types of habits that lead to frequently encountering this error and those of a similar origin. 

So, instead of listing the most common errors and how to correct them, we have decided to discuss some of the more common preventative measures that develop when working in Oracle. These habits will help users avoid errors that have the potential to halt their database system. Whether you are an experienced programmer or just getting started in the field, these common themes can go a long way in not just preventing one error one time, but providing the tools necessary to making your life a whole lot easier.

1. Tracking your data limits – This will typically fall into the hands of the database manager, but, even if you are just working in Oracle from time to time with your company, it is still a good idea to keep in mind that your database has storage limits. Keeping tabs on where your storage is versus where the limit is set will save you from seeing your database come to a complete stop due to a storage error.

The ORA-00257 is a case where checking on the storage space can avoid the error. The problem is too little space has been set aside for the archived log data. By running an SQL check of the db_recovery_file_dest, you will be able to see the size of the recovery destination for your database. Then, by using the parameters SPACE_USED and SPACE_LIMIT, you will have a very clear picture of where you sit in relation to your limits.

2. Familiarize yourself with proper syntax and basic functions – Any Oracle user must become acquainted with the basic formatting of the database in order to avoid large stacks of errors. This includes syntax, such as what types of characters can be used in what actions, as well as how to properly start and end a procedure.

Date and time prompts can be another source of frustration if they are not properly learned. For instance, as in the ORA-01858 error, certain facets of Oracle require specific formats to run smoothly. What will often trigger this error is when the user attempts to pass a date using alphabetical terms (i.e., “AUG” for August) in a setting that is formatted for numerical values only (i.e., “08” for August). There are several errors that result simply from improperly formatting date and time structures. 

Another example is the ORA-01400. For this error, the user attempts to insert a NULL statement into a column that does not accept NULL values. This error has a quick fix, but, by familiarizing yourself with the format of your database and with certain procedures, it can be avoided altogether. To correct this type of error, you should also learn how basic SQL statements and functions operate. For the ORA-01400, you will have to run a couple of INSERT statements to both get the program back on track and to build the framework. This will prevent the error from occurring again.

3. Be aware of copying & pasting mistakes – Again, this may seem pretty obvious, but it can be the source of a great deal of strife when working in Oracle or in any other type of programming for that matter. You must remain cognizant of where procedures and phrases start and end when you are copying and pasting into another section. When working with the construction of a database from the ground floor, take special care to denote procedures and actions that you feel will rear its head again in the future.

The danger of copying and pasting is making a mistake once then noticing no immediate effect on the system. However, over time, you may continue to use the same phrase and paste it over and over throughout your system, spreading the mistake like the flu.

The ORA-00911 is a perfect example of an error that results from not monitoring copying and pasting practices. The error, which signals a variety of syntax mistakes, can be prompted by copying and pasting SQL language between programs. If there are non-printable characters in the language that have been pasted from the foreign program, the ORA-00911 can be triggered. If it is a vital function, it might be worthwhile to take the time to go through and type it out so you can get a hands-on feel for which characters may be prompting the error.

Building upon these types of safety measures will save you and your colleagues a lot of unnecessary grief that stems from highly preventative errors. A lot of it may seem remarkably simple, almost to the extent of where it goes without saying. For some of you, these issues may not have been raised for so long that a refresher was necessary. Either way, the benefits are clear: strong, basic habits will lead to a more efficient Oracle database. These habits will then transfer across programs to make for a better programmer and database manager.