Cannot use "?" in Explain Plan

Answered

I cannot use "?" while doing Explain Plan. For example, if we do "Explain Plan" on the following

select * from DUAL WHERE DUMMY = ?;

it only returns ORA-00911: invalid character.

Does anyone know how to fix this?

0
8 comments

Please share the SQL logs (Help | Show SQL Logs in Finder) after reproducing the issue.
There should be something like 


sql> SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 FROM dual
[2015-10-01 23:11:07] 1 row retrieved starting from 1 in 583ms (execution: 515ms, fetching: 68ms)
[2015-10-01 23:11:13] completed in 122ms
sql> EXPLAIN PLAN SET statement_id='oxe/U0rhXwQ58Swo+0VA7Q==' FOR SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 FROM dual; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','oxe/U0rhXwQ58Swo+0VA7Q=='))
[2015-10-01 23:11:18] [22019][911] ORA-00911: invalid character
0

HR> EXPLAIN PLAN SET STATEMENT_ID = 'oj0tgh/14pN9ZXg4J/hA6Q==' INTO "PLAN_TABLE" FOR select * from DUAL where dummy = ?
[2022-11-09 11:42:20] [22019][911] ORA-00911: invalid character
[2022-11-09 11:42:20] Position: 114

0
Hello,

Apologies for the delay in your request.

Please be informed that we're looking into your issue and we'll do our best to reply with a solution soon.
0

Thank you for being so patient.

After performing some tests, we are confirming that this is a bug. As a workaround, please substitute the question sign (?) with (:1), and it should give you the desired result. See screenshot



As soon as our developer will create a bug report on youtrack, we'll provide you with a link to follow this issue.

0
Finally, here's a link to this issue so you can follow the progress:

https://youtrack.jetbrains.com/issue/DBE-16769
0

Sadly, using ":1" instead of "?" does not work properly. It seems that when using ":1" the order of the values in the WHERE clause is not preserved, thus leading to wrong cost in explain plan. Using "?" works for a co-worker of mine, and we replicate the results there.

0

FYI, we have found out that EXPLAIN PLAN works properly using "?" in IntelliJ 2021.2 Build #IU-212.4746.92, but this is as far as we can see the last version where it works. It does not work in IntelliJ 2021.2.4 Build #IU-212.5712.43 or any later build we've tried. Will you be able to fix this?

0
Hi,

Thank you for informing us of your issue.

Please can you confirm if you were able to spot the issue with the order of values in the Where Operator when running Explain Plan in IntelliJ IDEA 2022.3 (223.7571.182 build)? Please also provide a sample of the query so we can have a look.
0

Please sign in to leave a comment.