"Error running query Oracle" does not show the line with error

[lscapim]

Hi,
When we run a query in Redash, with errors, the error message does not show the line, as in the example:

Can we find a why to solve this?
When we have a big query this can be a big problem for us.

Thank you

[justinclift]

@snickerjp As our resident Oracle specialist, what do you think? 😄

[snickerjp]

@lscapim
This report does not show the "location of the error" in the error message,
You don't know where the error occurred.

"I would like you to show me the location of the error."
Is that correct?

Certainly, I agree with you.

[snickerjp]

How can I make it so that I can output the same error messages as in SQL*Plus?
I have looked into this.

Redash uses the Python library "cx_Oracle" to connect to Oracle Database.

It is not possible to output the same error message as sqlplus using cx_Oracle. The error message from cx_Oracle contains the Oracle Database error number and error message. On the other hand, the error message from sqlplus contains the message corresponding to the Oracle Database error code.

The Python module "python-oracledb" was the same.

If there is a better way, please let me know.

I hope this helps.

The following was used as a reference
https://cx-oracle.readthedocs.io/en/latest/api_manual/module.html#exceptions

[snickerjp]

In the course of my investigation, I made some modifications to the Python code.
I was able to display the Offset number.

diff --git a/redash/query_runner/oracle.py b/redash/query_runner/oracle.py
--- a/redash/query_runner/oracle.py
+++ b/redash/query_runner/oracle.py
@@ -176,7 +176,8 @@ class Oracle(BaseSQLQueryRunner):
                 json_data = json_dumps(data)
                 connection.commit()
         except cx_Oracle.DatabaseError as err:
-            error = "Query failed. {}.".format(str(err))
+            err_args, = err.args
+            error = "Query failed. {}.".format(str(err)) + "| Error Offset num: {}.".format(str(err_args.offset))
             json_data = None
         except (KeyboardInterrupt, JobTimeoutException):
             connection.cancel()

However, perhaps this is not enough.

[justinclift]

With that offset number, I'm guessing that's the number of (unicode?) characters into the query text?

Although it's not exactly at the helpfulness of something like "line 52, column 10", it still sounds like an improvement over the existing situation.

Did you want to make a PR for that change? 😄

[lscapim]

@justinclift It's exactly the same situation you mentioned. We need to have the line and column returned with error, similar to the example that showed and SQLPlus: "line 52, column 10"

[justinclift]

@snickerjp With that offset number that it returns... that might actually be useful "enough" for our purposes if we're clever.

@eradman If a data source tells us which character # in our query is the error point, do you reckon we could (accurately) work our the line and column numbers from that? I'm thinking it's probably not too hard to write a function for calculating that (front end?) if such a function doesn't already exist.

Thoughts?

[justinclift]

Not sure. I'd imagine it wouldn't be unique to us, as it seems to be information being provided back to us via the Oracle data source library.

[snickerjp]

I think this case depends on the Oracle data source library (cx_Oracle).

I don't think this is a Redash unique problem.

[snickerjp]

To display the offset number
I will try to create an pull request.

[snickerjp]

Created pull request. #6373

[lscapim]

@snickerjp Thank you very much... if we can somehow display the line where the script error is it will help a lot. In oracle we can see the line with the error.

[justinclift]

Thanks to @snickerjp for creating the PR to add the line and column location in the error message output (#6373). It's been merged now.

Next, we need to figure out the release process for creating Redash builds people can use in production. 😄