Hello,
we build a java based system where users can enter where-conditions. Before execuing, we want to evaluate if the queries would be valid, but without executing them (they might be long running queries).
So far, I build the sql, added the where condition "where 0=1" (to prevent a the execution of the query) and executed the sql via a jdbc statement. In case of an exception, the query was invalid.
But obviously, not everything is checkd by Oracle, since the query "select * from hr.locations where postal_code = 1234 and 0=1" works (this is our test), but "select * from hr.locations where postal_code = 1234" does not (throwing ORA-01722: invalid number, becauase postal_code is a varchar field).
I already tried
explain paln .... (no error)
using a prepared statement, (where .., 0 = ?) (no error).
Does any one have an idea how to get the query checked without actually running it? Or using a SQL-construct that makes the running query end immediately without using many resources (as I mentioned the real tables might be very huge).
Or any Oracle parameter perventing the behaviour not completely checking the query as our database shows?
We are using Oracle XE on windows.
Any help would be highly appreciated.
thanks a lot in advance,
kind regards,
Alex