由于oracle错误1775,ora-942 or ora-1775 or PLS-00201 When using a database link in a stored procedure...

今天有网友在群里咨询一个问题，很有意思记录一下，希望别人遇到时可以得到提示。

Question：

有一个procedure中一段SQL用到了synynom,而synonym指向一个dblink所对应的表，其中是两个子查询做了full join,在procedure 编译时提示ORA-00942: table or view does not exist，而把那段plsql 查询语句块拿出来，做为单条SQL执行时完全正常且有返回结果，从user_errors可以得到错误定位指向synonym对象，问是不是full join和synonym配合出了问题？

Answer：

当然首先排除了procedrue授权的问题，其实后来也排除了与synonym和full join没有任何关系，问题是出在database link。

下面我来还原这个问题,测试是在11.2.0.3 for linux x86 64bit

1，第一步先演示ora-1775, 从168.231 db231 创建dblink到191.3 devdb

[oracle@db231 ~]$ /sbin/ifconfig eth0

eth0 Link encap:Ethernet HWaddr 00:22:19:5B:85:66

inet addr:192.168.168.231 Bcast:192.168.168.255 Mask:255.255.255.0

inet6 addr: fe80::222:19ff:fe5b:8566/64 Scope:Link

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:8637 errors:0 dropped:0 overruns:0 frame:0

TX packets:567 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:652529 (637.2 KiB) TX bytes:75401 (73.6 KiB)

Interrupt:36 Memory:d6000000-d6012800

[oracle@db231 ~]$ tnsping d1913

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 03-JUL-2014 15:39:04

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.191.3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb)))

OK (0 msec)

[oracle@db231 ~]$ sqlplus icme/icme@d1913;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 3 15:40:48 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

icme@DEVDB>

system@DEVDB>show parameter case

NAME TYPE VALUE

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

sec_case_sensitive_logon boolean FALSE

[oracle@db231 ~]$ sqlplus anbob/anbob

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 3 15:41:23 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

anbob@ANBOB>create database link dl_1913_icme connect to "icme" identified by "icme" using 'd1913';

Database link created.

anbob@ANBOB>select count(*) from icme_org@dl_1913_icme;

COUNT(*)

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

230957

anbob@ANBOB>create or replace procedure ptest

is

v_n number;

begin

select count(*) into v_n from icme_org@dl_1913_icme;

dbms_output.put_line(v_n);

end;

/

Warning: Procedure created with compilation errors.

anbob@ANBOB>show error

Errors for PROCEDURE PTEST:

LINE/COL ERROR

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

5/1 PL/SQL: SQL Statement ignored

5/1 PL/SQL: ORA-01775: looping chain of synonyms

10046 event

=====================

PARSE ERROR #139759914257600:len=50 dep=1 uid=84 oct=3 lid=84 tim=1404380353047265 err=942

SELECT * FROM "icme"."ICME_ORG"@"DL_1913_ICME.COM"

#甚至更离谱是有,虽然远端的确存在icme6 schema，不知O在解析时怎么遍历的到其它SCHEMA的。

=====================

PARSE ERROR #139759914257600:len=51 dep=1 uid=84 oct=3 lid=84 tim=1404380353053239 err=1031

SELECT * FROM "ICME6"."ICME_ORG"@"DL_1913_ICME.COM"

note:

我找了另外几个库10.2.0.5 ,10.2.0.4 连接到devdb结果同上，暂时未找到原因。

解决方法1

anbob@ANBOB>create database link dl_1913_icme2 connect to icme identified by "icme" using 'd1913' ;

Database link created.

anbob@ANBOB>create or replace procedure ptest

is

v_n number;

begin

select count(*) into v_n from icme_org@dl_1913_icme2;

dbms_output.put_line(v_n);

end;

/

Procedure created.

anbob@ANBOB>exec ptest;

230957

PL/SQL procedure successfully completed.

anbob@ANBOB>select count(*) from icme_org@dl_1913_icme2;

COUNT(*)

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

230957

Note:

注意创建DBLINK 时相比以前在用户上少了双引号。

解决方法2

anbob@ANBOB>create or replace procedure ptest

is

v_n number;

begin

select count(*) into v_n from icme.icme_org@dl_1913_icme;

dbms_output.put_line(v_n);

end;

/

Procedure created.

anbob@ANBOB>exec ptest;

230957

PL/SQL procedure successfully completed.

Note: 还是原来的DB link,只是在对象前加了link 对象的用户名， 不知道这算不算一种解决方案，不过更建议用第一种。

2，第二步先演示ora-942, 从191.3 devdb 创建dblink到168.231 db231

[oracle@dev-db admin]$ tnsping d168231

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 03-JUL-2014 18:34:58

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.231)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = anbob.com)))

OK (10 msec)

anbob@ANBOB>show parameter case

NAME TYPE VALUE

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

sec_case_sensitive_logon boolean TRUE

system@DEVDB>create database link dl_231_anbob connect to "anbob" identified by "anbob" using 'd168231';

Database link created.

system@DEVDB>select count(*) from t@dl_231_anbob;

COUNT(*)

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

851968

system@DEVDB>create or replace procedure ptest

2 is

3 v_n number;

4 begin

5 select count(*) into v_n from t@dl_231_anbob;

6 dbms_output.put_line(v_n);

7 end;

8 /

Warning: Procedure created with compilation errors.

system@DEVDB>show error

Errors for PROCEDURE PTEST:

LINE/COL ERROR

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

5/4 PL/SQL: SQL Statement ignored

5/34 PL/SQL: ORA-00942: table or view does not exist

system@DEVDB>create or replace procedure ptest

2 is

3 v_n number;

4 begin

5 select count(*) into v_n from anbob.t@dl_231_anbob;

6 dbms_output.put_line(v_n);

7 end;

8 /

Procedure created.

system@DEVDB>exec ptest;

851968

PL/SQL procedure successfully completed.

system@DEVDB>create database link dl_231_anbob2 connect toanbob identified by "anbob" using 'd168231';

Database link created.

system@DEVDB>create or replace procedure ptest

2 is

3 v_n number;

4 begin

5 select count(*) into v_n from t@dl_231_anbob2;

6 dbms_output.put_line(v_n);

7 end;

8 /

Procedure created.

system@DEVDB>exec ptest;

851968

PL/SQL procedure successfully completed.

Note:

最后发现MOS note 1353142.1 记录了ora-942 这个问题影响版本是10.2.0.4 and later，有意思是据记录曾经在版本8.1.7定位是Bug 2348742并在当时版本修复, 不知为何再次被唤醒(猜测是当时的开发出于马虎了事，挖坑给了后人)。另外还有一种情况没有测试，据网上收集资料发现因为11G 密码默认区分大小写，如果密码有时不带引号时也会有可能遇到密码错误。

Summary:

1,用户名和密码都带双引号，可能会：SQL 正常 ，PL/SQL(Procedure)不正常；

2，用户名密码都不带双引号，可能会：SQL密码错误，PL/SQL 正常

3，用记名不带双引号，密码带双引号，SQL 和PL/SQL 都正常

养成一个良好的语法习惯是多么的重要。今天看到Oracle还可以像MySQL一样的创建用户的语法：grant connect, resource to usr1 identified by usr1;

打赏

微信扫一扫，打赏作者吧～