Changed handling of named variables from V11 to V12

[DvDavidson]

We updated from 11.x to 12.0.7.1837 a while ago; since that some of our SQLs don't work as before - this is the case when the statement uses a given named variable more than once.
Here a simple example:

SELECT *
  FROM myTable t
 WHERE ...
   &<NAME = BPKENN prefix = " AND t.bpkenn = '" suffix = "'">
   &<NAME = BPKENN prefix = " AND t.tranche = MOD('" suffix = "', 7)">

This used to work fine - the resulting statement for BPKENN = '001' was

SELECT *
  FROM myTable t
 WHERE ...
   AND t.bpkenn = '001'
   AND t.tranche = MOD('001', 7)

Of course the real BPKENNs are a lot longer (16 Digits) and the table so huge that carving tranches like this is actually usefull

With V12 the statement results in

SELECT *
  FROM myTable t
 WHERE ...
   AND t.bpkenn = '001'
   AND t.bpkenn = '001'

which is awkward as it leaves out the index on t.tranche

Even worse is this:

SELECT *
  FROM myTable t
 WHERE ...
 &< NAME = BPID_DUBL prefix = "AND (v.bp_id IN (SELECT dv.bp_id_out FROM dublette_view dv WHERE dv.bp_id_in = " suffix = ")">
 &< NAME = BPID_DUBL prefix = " OR v.bp_id = " suffix = ")" >

which used to result in (given a meaningful input for BPID_DUBL)

SELECT *
  FROM myTable t
 WHERE ...
   AND (v.bp_id IN (SELECT dv.bp_id_out
                      FROM dublette_view dv
                     WHERE dv.bp_id_in = 42)
        OR v.bp_id = 42)

whereas now it only produces "ORA-00907 missing right parenthesis"

In short: V12 replaces each appearance of a named variable with the result of it's first definition while V11 obviously evaluated each appearance by itself.

Of course there are workarounds, but we can't help wondering why this was changed - intention, side effect, ...?!?

Cheers, Dave

 

[Marco Kalter]

It's probably a side-effect. We'll check it out.

 

[cassiusdrow]

Marco, this continues to be an issue in version 15.

 

[Marco Kalter]

That is odd. We'll check again.