How to make Oracle error messages more verbose?

Question

The message that drives me crazy is ORA-01008 - Not all variables bound.

Is there a way to know which one of the 42 possible variable names I have misspelled without staring at the monitor till my eyes pop out?

Update: I use ADO.NET to access the database. Perhaps it does lose some information in Oracle exceptions, as Justin Cave has suggested. But I'm positive that the parameter name never appears even in SQL Plus.

Answer 1

I don't know of any way to get Oracle to make the error more specific. Maybe some future version will improve this error message.

Instead of just staring at it, though, there are other things you can try. For example, convert each variable in the SQL statement to a literal one at a time, until the error goes away. If possible, generate the list of variable names instead of typing them manually.

Answer 2

In general, Oracle provides the line and column number of any errors, but it is up to the particular API you are using (unless you happen to be writing an OCI application, which is probably unlikely) as to whether and how those APIs are called. Since the answer is likely to end up being API-specific, what API are are you using and what does your code look like when the error occurs (i.e. JDBC, ODBC, OLE DB, etc)?

As an example, if I write a PL/SQL block with a misspelled variable name, SQL*Plus will report the line and column number of the error in addition to the error message. Many APIs, on the other hand, will just report the PLS-00201 error by default.

SQL> declare
  2    i integer;
  3  begin
  4    j := 1;
  5  end;
  6  /
  j := 1;
  *
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00201: identifier 'J' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

Similarly, if you execute a SQL statement with an invalid variable name, SQL*Plus will get the column and line position and put a * under the offending character, i.e.

SQL> create table a( col1 number );

Table created.

SQL> insert into a( colN ) values ( 1 );
insert into a( colN ) values ( 1 )
               *
ERROR at line 1:
ORA-00904: "COLN": invalid identifier

Most PL/SQL IDE's (TOAD, SQL Developer, etc.) will do something similar by interrogating the appropriate OCI APIs under the covers. Precisely how this is done, however, will depend on the API.