SQL runs but not in optimizer - need error details

SQL Optimizer SQL Optimizer for Oracle
1

I have a select statement with bind variables gathered using Oracle tooling while running a package via a Java Web application. I can run the SQL in Toad using the "Execute/Compile statement at caret" command, setting the variables. Here is the SQL:

ToadAutoOptimizeSql.jpeg
ToadAutoOptimizeSql.jpeg915×290 67.1 KB

Options screens:

ToadAutoOptimizeConfigDialog1.jpeg

(NOTE: I turned off "Refactor SQL before optimizing," and that got rid of a very unhelpful "Exception was thrown" error.)

ToadAutoOptimizeConfigDialog2.jpeg

When I try to run the same SQL using the "Auto-optimize SQL at caret using re-writes", I get this error message: "ORA-00905: missing keyword"

ToadAutoOptimizeSqlError2.jpeg

Is there any way I could get more detail on where this mysterious error is in the statement? I tried running it in SQL*Plus, but it works there.

Thanks in advance for any help you could offer.

Oracle 11.2.0.4.0

Toad 32-bit 13.0.0.80

Windows 10

2

The SQL seems corrected, how if you replace these bind variables with an actual value, does the problem still happened? The test can help us to define which the problem is happened on our parser or binding variables. (When optimize a SQL, we will read the SQL with our internal parser for further analyzing. In case a valid SQL cannot successfully go through the parser, it could be our bug or the syntax no supported.)

The other problem is about error message not clear. In this case I am absolutely agree. The error return from Oracle, and we should put more helpful information or advice on it. Actually, we have a task to enhance it already.

'Thanks,

Tony Ng

3

Yes, replacing the bind variables with literal values allows the optimizer to run. Thank you. It is running as I write this.

4

When I turn on “Refactor SQL before optimizing” and select “Correct where clause indentation level” and “Convert DECODE function to CASE statement”, I get an exception dialog, then it continues optimizing.

5

Replace the bind variables to literal values is not a solution here. The explain plan may change as Oracle works on bind sensitive. It just for helping us to identify the problem.

I need a bit more time to figure out what’s the problem, and will get back to you this few days.

Thanks,

Tony Ng

6

Please post your SQL so I can copy/paste it.

Thanks,

Michael

7

You can also spool SQL so we can see what statement is failing. From Toad’s Database menu choose Spool SQL|Spool SQL to Screen. Reproduce the issue and post the contents of the spooled SQL here. Also, go to Help|Support Bundle and post the contents of that here as well. If you’d prefer you can email me these things directly to michaelstaszewskiquest.com.

8

Thank you. The spooling is helpful. When and if it fails again, I will use the spool feature.

9

Perhaps the installed version of Xpert edition was slightly different from the trial version of the Developer edition. The “Auto-optimize SQL at caret using re-writes” seems to be working now on this query. However, if my network dropped out for a while, will that affect the timings of some of the re-writes, or does the tool recover from that? Should I restart it?

Here is the SQL:

select AF_TASK_ID TASK_ID, TASK_EST_PRICE PRICE, TASK_EST_LABOR LABOR
from AF_TASK
where AF_TASK_ID in
(select TASK_ID
from VIEW_DUE_LIST DUE_LIST
inner join DUE_LIST_AF T_AF_SERIALS
on T_AF_SERIALS.AF_SERIAL_NUMBER = DUE_LIST.AF_SERIAL_NUMBER
inner join DUE_LIST_AF_PROJ T_AF_PROJECTIONS
on ((T_AF_PROJECTIONS.TRACKING_UOM = 501 and (DUE_LIST.CL_UOM = 503 or DUE_LIST.CL_UOM = 504))
or (T_AF_PROJECTIONS.TRACKING_UOM = DUE_LIST.CL_UOM))
where ((T_AF_PROJECTIONS.PROJECTION_SCALAR is not null
and DUE_LIST.CURRENT_SCALAR + T_AF_PROJECTIONS.PROJECTION_SCALAR >= DUE_LIST.FRQ_NEXT_DUE_VALUE)
or (T_AF_PROJECTIONS.PROJECTION_DATE is not null
and T_AF_PROJECTIONS.PROJECTION_DATE >= DUE_LIST.FRQ_NEXT_DUE_DATE))
and (DUE_LIST.AF_DUE_LIST_CALC_NEGATIVE = ‘Y’
or (DUE_LIST.AF_DUE_LIST_CALC_NEGATIVE = ‘N’ and CT_ID is not null))
and T_AF_PROJECTIONS.TRANS_ID = :B2
and T_AF_SERIALS.TRANS_ID = :B2)
and ( :B1 is null or ( :B1 is not null and AF_TASK.TASK_CODE like ( :B1 || ‘%’)))

10

Could you write your last line
and ( :B1 is null or ( :B1 is not null and AF_TASK.TASK_CODE like ( :B1 || ‘%’)))
like:
and AF_TASK.TASK_CODE like nvl(:B1, 'some value-place something unreasonable') ||'%'
Is it better plan then?