1

I'm trying to use the SQL tuning advisor with SQL Developer (versions 4.1.3.20 and 17.2.0.188). Queries using more than one instance of a bind variable, for example:

select * from dual where :one = :one;

are causing an error in the SQL Tuning Advisor:

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-01008: not all variables bound

-------------------------------------------------------------------------------

Is there a workaround or some other method to get the same SQL tuning info for queries that have multiple instances of one or more bind variables?

hmqcnoesy
  • 4,165
  • 3
  • 31
  • 47
  • 1
    Even though you're naming then you can't reuse binds and they're always positionally based. You need to bind it twice, in order. – Ben Sep 18 '17 at 16:56
  • @Ben isn't that SQL Developer's problem? I'm not writing SQL developer here, I'm using it. – hmqcnoesy Sep 18 '17 at 17:10
  • I think you've found a bug, if i have 2 binds of different names, the advisor runs w/o a problem – thatjeffsmith Sep 18 '17 at 17:13

2 Answers2

2

Basically, the Tuning Advisor binds by position (not by name). So the workaround would be to rename all the repeated variable instances. Adding a numbered suffix would be enough. In your example, you would have to name your variables :one and :one_1.

select * from dual where :one = :one_1;

I know, it's not ideal, but it should work.

flaria
  • 195
  • 1
  • 6
  • 14
-1

Not sure why you want to use ":one = :one", you can replace it as "1 = 1", or add one more variable assigned by the same value, select * from dual where :one = :two;

WeDBA
  • 343
  • 4
  • 7