为什么我的oracle语句在Windows数据库上与Linux上的运行方式不同? [英] Why does my oracle statement run differently on a windows database than linux?

问题描述

我不知道从哪里开始调试它. 一些开发人员已经在其Windows机器上本地编写了一些pl/sql代码，这些代码使用Oracle 10.2.0.1.0-64bit可以很好地完成.进入生产环境，即Red Hat 5.3并运行10.2.0.2.0时，它给了我这个错误:

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":无效的标识符

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

以下是在Windows中运行的代码的要点:

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 )
');

我知道...他们应该与公司的其他部门一起更新其Oracle版本，并在与生产版本相同的版本上进行开发，但是为时已晚，我无法控制它们...

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.

推荐答案

请参见

ANSI SQL的表引用(相关名称)的作用域仅限于一个 深层次

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

...

在版本10.2.0.1.0中，此查询有效:

In version 10.2.0.1.0 this query works:

选择(从scott.emp中选择*选择count(*)，其中ename = dual.dummy))来自double;

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

...

那是个错误.

它不能在9i，10g或11g的最终版本中使用

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.

这篇关于为什么我的oracle语句在Windows数据库上与Linux上的运行方式不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！