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 to anbob 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;