Skip to Main Content
  • Questions
  • how to debug the SQL logic execution ?

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, PV.

Asked: July 07, 2016 - 7:54 am UTC

Answered by: Chris Saxon - Last updated: July 08, 2016 - 3:54 am UTC

Category: Database - Version: 12c

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Partitioning an existing index

You Asked

AskTom,

I have a question about the 'SQL logic execution':

Can Oracle Database log the logic of the SQL execution ? (which condition of the WHERE clause failed ?)
example:

select 1
from Dual
WHERE
1 = 1
AND 1 = 1 + 1 --line 5
;

Can we know the above sql statement failed at the line number 5 ?


Thanks & Regards.
PV.

and we said...

Your statement doesn't "fail" anywhere. It simply has a condition that returns false.

That's the point of SQL. It only returns rows where all the conditions are true. So the "failing" rows are the ones it doesn't return!

If you're comparing constants and the result is false, the query returns nothing. You need to inspect your statements manually to find these.

If you have invalid comparisons, then you'll get an exception and details of the line that caused it. For example, you can't add a number to a letter:

SQL> select 1 from dual
  2  where 1 = 1
  3  and 1 = 'a' + 1;
and 1 = 'a' + 1
        *
ERROR at line 3:
ORA-01722: invalid number

and you rated our response

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

I use decommenting occasionally

July 07, 2016 - 8:12 am UTC

Reviewer: Lars Sjöström from Linköping, Sweden

Hi,
In order to see what condition in a WHERE-clause that is "failing" in some way or not getting you the correct (or expected) result set I use decommenting.
Decomment each one of the conditions and study the result.

July 07, 2016 - 8:26 am UTC

Reviewer: A reader

Chris Saxon,

Appreciate your quickly response. Perhaph, I've used the wrong word in my question: fail !

I think the right question should be:

which condition of the WHERE clause return FALSE ( or TRUE) ?


Thanks & Regards.
PV.




Chris Saxon

Followup  

July 07, 2016 - 9:49 am UTC

"which condition of the WHERE clause return FALSE ( or TRUE) ? "

That's evaluated on a per row basis. So for row 1 it could be true, row 2 false, etc.

The fact that a row is returned means it was true for that row. If it wasn't, it was false.

All your predicates should reference at least one column from a table. If it doesn't why have you included it in your SQL?

Your example of :

1 = 1 + 1

Or any variation of "constant = formulas using constants" doesn't make any sense in the real world. Either it's always true or always false. So you get all the rows or none.

(caveat: when writing dynamic SQL I tend to use "where 1=1" to start the where clause. This is simply because it makes writing the extra conditions easier. No faffing with trailing/leading ANDs).

July 07, 2016 - 8:29 am UTC

Reviewer: A reader

Lars Sjöström,

I do agree with you, but do we have another better (automatic) ways ?


Rgds.
PV.

July 07, 2016 - 10:13 am UTC

Reviewer: A reader

"which condition of the WHERE clause return FALSE ( or TRUE) ? "

That's evaluated on a per row basis. So for row 1 it could be true, row 2 false, etc.

The fact that a row is returned means it was true for that row. If it wasn't, it was false.

All your predicates should reference at least one column from a table. If it doesn't why have you included it in your SQL?

Your example of :

1 = 1 + 1

Or any variation of "constant = formulas using constants" doesn't make any sense in the real world. Either it's always true or always false. So you get all the rows or none.

(caveat: when writing dynamic SQL I tend to use "where 1=1" to start the where clause. This is simply because it makes writing the extra conditions easier. No faffing with trailing/leading ANDs).


The practical example is:

I try to put all the logic of the pl/sql into a SQL statement (as the advise of asktom before), so my SQL is quite complex and sometime it not produces the result as expected, then so I dont know why and which condition not meet ?, if we know which condition in the WHERE clause return FALSE, we can check & fix it faster.

The more complex logic in SQL-statement the more time to debug.(de-comment or comment each condition, line by line consume much time).


Do we have other tools (ex: SQL Developer 4.1) to support this ?


Thanks & Rgds.
PV.

Chris Saxon

Followup  

July 07, 2016 - 12:25 pm UTC

What do you mean:

"I try to put all the logic of the pl/sql into a SQL statement"

?

Show us your code!

If you have multiple conditions and you're not sure which is returning false, you'll need to remove them until you get the result you want!

put conditions on select

July 07, 2016 - 12:31 pm UTC

Reviewer: MatteoP from Italy

let's say you have a query like this

with a as (
select *
from   user_objects
where rownum <=10)
select object_type, status
from a 
where object_type in ('TABLE','INDEX')
or    status != 'VALID'


If you want to know which condition is false then move it to the returned columns

with a as (
select *
from   user_objects
where rownum <=10)
select object_type, 
       status, 
       case when object_type in ('TABLE','INDEX') then 'ThisIsTrue' else 'ThisIsFalse' end cond1,
       case when status != 'VALID' then 'ThisIsTrue' else 'ThisIsFalse' end cond2
from a 


results in
OBJECT_TYPE STATUS COND1 COND2
TABLE VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse
INDEX VALID ThisIsTrue ThisIsFalse
INDEX VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse
INDEX VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse
INDEX VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse

Chris Saxon

Followup  

July 07, 2016 - 2:24 pm UTC

Yes, that's one way to do this.

July 08, 2016 - 1:54 am UTC

Reviewer: A reader


MatteoP, your way can meet our expect.

Does Oracle Database have any built-in solution to do this? or an automation way ?

Oracle Database has many built-in features for tuning SQL performance, and not for SQL*logic*analyzer yet. I think Oracle should consider this feature in the next release.


The SQL execute logic is quite important, especially with Analytic SQL statement.


/*
Show us your code!

If you have multiple conditions and you're not sure which is returning false, you'll need to remove them until you get the result you want!
*/
I mean we transform the logic of IF...THEN, FOR..LOOP or WHILE..LOOP into SQL Statement.


Thanks & Rgds.
PV.

Connor McDonald

Followup  

July 08, 2016 - 3:54 am UTC

We encourage people to log enhancement requests. If there is something you want in the database, but it is not there today...log a request, and maybe it will be there in the next release.

Cheers,
Connor