Oracle SQL Error Trapping

This may be something I missed in the user documentation, but I can't seem to find how to expand the error trapping in the SQL Editor. I am connecting to an Oracle database and in other tools when I get an "ORA-" type error I am presented with the line and column where the error was found in the SQL code. In Toad Data Point I see the ORA error code, but I am not presented with any information on where in my SQL code the error is at. Is there a setting somewhere that will activate expanded error trapping to see this information? Or maybe just a functionality I am not seeing?

Any help is appreciated, Thanks.

We have two types of error locating. The first comes from our parser. As you type or open a SQL file we perform syntax checking. If we find an issue we mark the line with a red icon and a squiggle under the location.
When executed, Oracle will also provide error location. See this example of both.

here is the option you should have on for this.

Thank you. I will take a look and see if changing that setting will get me what I need. If not, I will post a screenshot of what I am seeing to give an example.

Debbie, here are some screenshots showing the issue. In this case I purposely excluded a column from a Group By statement to demonstrate the issue.

Toad Data Point:

Here is the missing column



As you can see nowhere in the error trapping does it call out which Line/Column where the error is coming from. I am only presented with the Oracle error message. The last screenshot shows the Parser did not pick up on this either and there is not a red-X showing the error in the code. The error is generated when I try to run the SQL. I did verify I have the Tools option you gave activated.

When the same SQL is dropped into Oracle SQL Developer and I try to run I get an error like this:


As you can see in this case I am presented not only with the Oracle error, but the Line/Column position in the SQL code.

I just wanted to see if this is something that can possibly be considered for an enhancement for the SQL Editor or if there is something I am still missing.

Thanks again for your assistance. :smiley:

I see that Toad for Oracle gives you the line number (not column). We should be able to do the same so I entered TMB-1310 for this

1 Like

Because reliability is crucial for database programs, use both error checking and exception handling to ensure your program can handle all possibilities:

Add exception handlers whenever there is any possibility of an error occurring. Errors are especially likely during arithmetic calculations, string manipulation, and database operations. Errors could also occur at other times, for example if a hardware failure with disk storage or memory causes a problem that has nothing to do with your code; but your code still needs to take corrective action.

Add error-checking code whenever you can predict that an error might occur if your code gets bad input data. Expect that at some time, your code will be passed incorrect or null parameters, that your queries will return no rows or more rows than you expect.

Make your programs robust enough to work even if the database is not in the state you expect. For example, perhaps a table you query will have columns added or deleted, or their types changed. You can avoid such problems by declaring individual variables with %TYPE qualifiers, and declaring records to hold query results with %ROWTYPE qualifiers.

Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers. Learn the names and causes of the predefined exceptions. If your database operations might cause particular ORA- errors, associate names with these errors so you can write handlers for them. (You will learn how to do that later in this chapter.)

Test your code with different combinations of bad data to see what potential errors arise.

Write out debugging information in your exception handlers. You might store such information in a separate table. If so, do it by making a call to a procedure declared with the PRAGMA AUTONOMOUS_TRANSACTION, so that you can commit your debugging information, even if you roll back the work that the main procedure was doing.

Carefully consider whether each exception handler should commit the transaction, roll it back, or let it continue. Remember, no matter how severe the error is, you want to leave the database in a consistent state and avoid storing any bad data.

Rachel Gomez