Oracle 公共同义词不是使用立即执行编译

你好，

我在尝试使用块编译公共同义词时遇到问题。该块运行正常-但是，它不会编译公共同义词。当我运行查询select * from all_objects其中status = 'INVALID' 时，公共同义词仍显示为 “无效”。当我尝试手动编译它的工作原理。

你知道是什么问题吗？

declare 
  cursor l_c is (select * from all_objects where status = 'INVALID');
  l_sql varchar2(3500);
  l_type varchar2(3500);
begin 
  for i in l_c loop
    
      if i.object_name = 'SYNONYM' and i.owner = 'PUBLIC'
      then
        l_sql := 'alter PUBLIC SYNONYM ' || i.object_name || ' compile';        
      else
        select decode(i.object_type,'PACKAGE BODY','PACKAGE',i.object_type)
          into l_type
          from dual;
          
        l_sql := 'alter ' || l_type || ' ' || i.owner || '.' || i.object_name || ' compile';
      end if;
      
      execute immediate l_sql;  
 
  end loop;
end;
/

致以最诚挚的问候，
沃尔特

你的剧本错了

SQL> create public synonym xxx for mcdonac.t;

Synonym created.

SQL> select * from all_objects where status = 'INVALID' and object_name = 'XXX';

no rows selected

SQL> drop table t;

Table dropped.

SQL> select * from all_objects where status = 'INVALID' and object_name = 'XXX';

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SUBOBJECT_NAME
----------------------------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE             CREATED   LAST_DDL_ TIMESTAMP           STATUS  T
---------- -------------- ----------------------- --------- --------- ------------------- ------- -
G S  NAMESPACE
- - ----------
EDITION_NAME
----------------------------------------------------------------------------------------------------
SHARING            E O A
------------------ - - -
DEFAULT_COLLATION
----------------------------------------------------------------------------------------------------
D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID
- - ------------- ------------- -------------- --------------
PUBLIC                         XXX

     96571                SYNONYM                 21-AUG-18 21-AUG-18 2018-08-21:19:16:40 INVALID N
N N          1

NONE               N N N

N N


1 row selected.

SQL> set serverout on
SQL> declare
  2    cursor l_c is (select * from all_objects where status = 'INVALID' and object_name = 'XXX');
  3    l_sql varchar2(3500);
  4    l_type varchar2(3500);
  5  begin
  6    for i in l_c loop
  7
  8        if i.object_name = 'SYNONYM' and i.owner = 'PUBLIC'
  9        then
 10          l_sql := 'alter PUBLIC SYNONYM ' || i.object_name || ' compile';
 11        else
 12          select decode(i.object_type,'PACKAGE BODY','PACKAGE',i.object_type)
 13            into l_type
 14            from dual;
 15
 16          l_sql := 'alter ' || l_type || ' ' || i.owner || '.' || i.object_name || ' compile';
 17        end if;
 18
 19        dbms_output.put_line(l_sql);
 20        execute immediate l_sql;
 21
 22    end loop;
 23  end;
 24  /
alter SYNONYM PUBLIC.XXX compile
declare
*
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier
ORA-06512: at line 20
ORA-06512: at line 20


Elapsed: 00:00:00.05
SQL>

原因-检查第8行，它不是要检查的对象 * 名称 * :-)