Why does my oracle statement run differently on a

问题:

I don't know where to begin to debug this. Some developers have been writing some pl/sql code locally on their Windows machines that complete fine using Oracle 10.2.0.1.0 - 64bit. When it gets to production, which is Red Hat 5.3 and running 10.2.0.2.0, it gives me this error:

ORA-00904: "S"."BARSTREAMREFERENCEID": invalid identifier

Here is the gist of the code that is working in Windows:

EXECUTE IMMEDIATE('
  update candyman.CANDY_REFERENCES s
     set ( s.flavour, s.taste, s.colour, s.privateField3 ) =
         ( select * from
                ( select r.flavour, r.taste, r.colour, null
                    from candyman.FOO_REFERENCE_SET t
                    join candyman.FOO_REFERENCES r on r.fooReferenceID = t.fooReferenceID
                   where t.barStreamReferenceID = s.barStreamReferenceID
                order by r.colour )
            where rownum = 1 )
   where privateField3 is not null
     and exists
         ( select 1
             from candyman.FOO_REFERENCE_SET t
             join candyman.FOO_REFERENCES r on r.fooReferenceID = t.fooReferenceID
            where t.barStreamReferenceID = s.barStreamReferenceID )
');

I know... they should have updated their Oracle versions with the rest of the company and be developing on the same version as production, but it is too late and I can't control them...

Any ideas are greatly appreciated.

回答1:

See this AskTom thread:

ANSI SQL has table references (correlation names) scoped to just one level deep

...

In version 10.2.0.1.0 this query works:

select (select count(*) from (select * from scott.emp where ename = dual.dummy)) from dual;

...

that was a bug.

it works in none of the terminal releases of 9i, 10g, or 11g

I'm a huge fan of developing locally. One of the main benefits is that it's easier to configure and manage your local instance than a server. I'm surprised that a developer would not want to use a newer version.