I do lots of Oracle SQL queries using Microsoft Excel. The connection to Oracle is made via VBA using an ADODB.Connection object and a connection string with Provider=OraOLEDB.Oracle. It works very well. However, sometimes when constructing a new query, I make an error. I’m wondering if there is a way to extract more information (such as the line number) from the Oracle error message that is passed to VBA.
Example; this is a working query that gives me valid results:
SELECT owner, table_name, column_name FROM all_tab_columns WHERE upper(column_name) LIKE '%FRAME%'
Now, let’s introduce an error by forgetting the closing parenthesis for the UPPER() function in line 3:
SELECT owner, table_name, column_name FROM all_tab_columns WHERE upper(column_name LIKE '%FRAME%'
If I run this query in Oracle SQL Developer, I get the following error message:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action: Error at Line: 3 Column: 25
If I run the same query in the more basic Oracle SQL*Plus terminal, I also get the line number and a visual indicator of where the error has occurred:
WHERE upper(column_name LIKE '%FRAME%' * ERROR at line 3: ORA-00907: missing right parenthesis
However, in Excel, all I get is the basic Oracle error statement (ORA-00907: missing right parenthesis), without any indication of where the error is in my query.
(Excel VBA Run-time Error)
For this example, it’s easy enough to find, but for some complex queries, it can be tricky to find the location of the error.
Is there any way to get additional information such as the line number from the error message that is thrown by the ADODB Open method or OraOLEDB.Oracle provider?