In the past, data managers operating Oracle data systems had to rely on an unrelenting number of manuals and booklets to figure out how to efficiently maneuver around Oracle errors. The age of Google has transformed this quest, making the lives of countless developers far less painstaking. Now those facing an error that just does not seem to want to go away are also faced with a wealth of resources on the other side of a web search.
Several websites have dedicated databases and Oracle writers that work exclusively on developing content to streamline Oracle troubleshooting for individual errors. But why stop there? We compiled a list of the ten most searched Oracle errors on the Internet.
1. Invalid or Nonexistent Column Name – ORA 00904
This is a simple fix. The error is triggered by either an invalid or missing column name. An ORA-00904 can be evaded by making sure to begin a column name with a letter, only use alphabetical, numerical or special characters (and using quotations with other characters), and not exceeding thirty characters in length.
2. Unhandled Exceptions in PLSQL Code – ORA 06512
The description for this won’t bring much information; just an “ORA-06512: at line ” will show up. So what does it mean? The stack being unwound by unhandled exceptions in your coding brings about the error. There are a couple of options to managing this. You can correct whatever is causing the unhandled error, or you can write an exception handler for the error. However, due to the complexity of this, it may be best to refer to your database administrator for more help.
3. Failed to Connect with Listener – ORA 12154
For this error, the connecting name could not be located in the naming method repository, or for some reason the repository could not be found. There are a multitude of actions available to correct this, primarily involving the verification of file names, net service names and syntax.
4. Executing SQL Statement that Doesn’t Exist – ORA 00942
The basis of this message stems from attempting to execute a SQL statement that either references a table or view that does not exist or that you cannot access. You will need to check to see if the table exists, and if it does not you will need to create the table or view. If you don’t have access, speak with an administrator about this issue.
5. Invalid Username and/or Password – ORA 01017
The ORA-01017 is an error not exclusive to Oracle. All it means is that the username/password combination entered was incorrect. Checking your system parameters to make sure the ID is aimed at the proper location, and just simply re-entering the login information from there should do the trick.
6. Missing Expression Error – ORA 00936
Another quick syntax fix! This message is triggered from a required clause being left out, such as the SELECT statement being entered without an incomplete expression, or when a reserved word like SELECT TABLE is misused.
7. “Invalid Number” Error – ORA 01722
This error is being thrown due to a failed attempt to convert a character string to a number. This will happen because arithmetic functions in Oracle can only contain numeric data, and only numeric data can be added to or subtracted from dates. Find the invalid data, which sometimes can be hidden if part of a sub query, and correct it to fit within the Oracle numeric parameters.
8. Arithmetic or Numeric Value Mistake – ORA 06502
The ORA-06502 is triggered when a numeric, string, conversion, constraint or arithmetic error occurs. Many times this will result from assigning a value to a numeric variable that is larger than the variable permits, or from assigning a non-numeric value to a numeric-only variable.
9. Invalid Character Error – ORA 00911
This is a syntax error that is about as straightforward as it can get. The cause stems from a number of mistakes, such as starting an identifier with a character other than a letter or number, or not enclosing identifiers with special characters in double quotes.
10. Improper Clause for SQL Command – ORA 00933
The final error on our list, another syntax error, derives from an improper clause ending an SQL statement. For instance, an ORDER BY clause cannot be used to create an ordered view in Oracle, but perhaps the error was triggered when the ORDER BY clause was attached to a CREATE VIEW statement. Also, additional indented lines can trigger the problem.
Concluding thoughts
These are the most searched errors either because they are the most commonly occurring ones, or because they are difficult to resolve. The fortunate thing about Oracle is that so many errors derive from simple syntax mistakes that do not require a great deal of knowledge to mend. Of course, as you have seen with this list, working past them and preventing them requires a strong base understanding of how to operate efficiently within Oracle by keeping track of parameters and the proper protocol for actions.
The other prominent set of Oracle errors come from issues involving the network that Oracle is accessed through. By establishing a strong rapport with your network administrator and keeping aware of the details of your network (memory space on drives, who can access what, etc.) these kinds of errors can largely be circumvented.
While this list is not exhaustive by any means, it should help in not just providing brief descriptions and solutions for the ten errors mentioned, but also by putting you in the proper mindset to take on other Oracle errors beyond the list and work to operate your data with a superior adeptness.
About the Author/Judd Robins:
As Executive Vice President, Judd is instrumental in driving the overall direction and revenue growth of the consulting services division of TekStream Solutions. He works collectively with his team to launch territory campaigns to increase customer awareness, excel in quality of solution delivery, and established a presence in the region as a leading Gold Oracle Partner for Enterprise Portal and Enterprise Content Management technologies.