Troubleshooting SQL Errors

An earlier chapter mentioned that the semi-colon should not be included in the region source for a report. Well, on any given day, one could forget and include the semi-colon. The following sections show how to troubleshoot that and other SQL problems.

The following scenario involves working with the report built in the Conference RSVP application in an earlier chapter of this book. To create a problem, all I did was to add the semi-colon to the region source. Since the report is attempting to add an ORDER BY clause onto the SQL statement, it will cause an ORA-00911: invalid character error. What to do when that happens? Put the page in debug mode, of course.

Figure 17.1 shows that the select statement failed. Further examination of the SQL statement reveals there is a semi-colon located before the ORDER BY clause. The SQL statement can be modified, and the problem will be corrected.

More About Troubleshooting

Is it not frustrating to work on an application page that was working fine, just to have it fail after one little change? Well, luckily there is a mechanism in HTML DB that shows the History of a page. This can help narrow down the change that caused the problem. To view the history of a page, navigate to the page definition screen, change the View to History, and click the GO button. This will reveal recent changes to the application page. It is not perfect, but sometimes it is the best place to start.