Search code examples
sqloracleoracle-sqldeveloper

How to get exact error line in an Oracle SQL query?

I'm working with an Oracle Database (SQL Developer as client) and long queries (most of them having between 5k and 10k lines, with the longest reaching 60k lines).

My objective would be that when running the queries on SQL Developer, I can see the exact line where the error occurs. Currently SQL Developer/Oracle DB shows the error type (ex. "missing expression" or "missing right parenthesis") but doesn't show where is the error, forcing me to analyze every bit of the query.

Is there a way to let the Database show the error line or there's nothing I can do? (Nope, I can't reduce the query's size since they come from an external source; I just need to make sure they work and fix any errors). I've also already tried with other clients (VSCode, Toad, DBeaver).

Example: Running the following query:

SELECT
'Line 1' as column_1,
'Line 2' as column_2,
CASE WHEN 1= THEN ELSE NULL as column_3,
'Line 4' as column_4,
'Line 5' as column_5
FROM dual;

would correctly show where the error is ("Error at Line: 4 Column: 14") but this does not happen when running long queries. If you want to test a longer query, I've used this 5000-lines query as test, which doesn't show where the error is (spoiler: line 2507).

Solution

  • Clicking on the Query builder in SQL developer will show you the exact line where the error has occurred. I used the sample SQL you provided and got the below output. Please also note query builder will not identify whether table or view does not exists or not, it will only validate the SQL syntax.

    enter image description here

    Edit1:-You can also use DBMS_SQL.LAST_ERROR_POSITION to identify the error line. The link below

    ORA-01722: Invalid Number, find specific line