Oracle 存储过程 procedure 失效 分析

 

 

数据迁移完毕，对比数据库对象有效性的时候，发现有个存储过程是失效的，重新编译仍然失效

 

存储过程如下：

 

CREATEORREPLACEPROCEDUREPERM.P_YG_SWJG_DMas

 

BEGIN

 

  FORLS_KJYWRXXIN (SELECTt.swjg_dm || '00'SWJG_DM,

                           t.mcSWJG_MC,

                           t.mc_jSWJG_JC,

                           t.jgjcjg_bz,

                           t.swjg_dm ||'00'SWJG_DM_bj,

                           t.sjswjg_dm ||'00'swjg_dm_sj,

                           t.swbm_bjswbm_dm_bj,

                           ''ywhj_dm,

                           ''jc,

                           ''jg,

                           t.yb,

                           t.dz,

                           t.dh,

                           ''fax,

                           ''wzdz,

                           ''email,

                            t.xzqh_dmxzqh,

                           ''nsrsbh_ts,

                           t.xybz,

                           t.yx_bzyxbz

                      [e-mail]

                      WHERENOTEXISTS (SELECTA.SWJG_DM

                              FROMperm.dm_gy_swjgA

                             WHEREA.SWJG_DM = T.SWJG_DM ||'00')

                       ANDT.YX_BZ = 'Y'

                       ANDT.SWBM_BJ = '0'

                       andt.XYBZ = 'Y'

                       ANDLENGTH(T.SWJG_DM) = '9'

                    

                     ) LOOP

 

     

    INSERTINTOperm.dm_gy_swjg

      SELECTt.swjg_dm || '00'SWJG_DM,

             t.mcSWJG_MC,

             t.mc_jSWJG_JC,

             t.jgjcjg_bz,

             t.swjg_dm || '00'SWJG_DM_bj,

             t.sjswjg_dm || '00'swjg_dm_sj,

             t.swbm_bjswbm_dm_bj,

             ''ywhj_dm,

             ''jc,

             ''jg,

             t.ybyb,

             t.dzdz,

             t.dhdh,

             ''fax,

             ''wzdz,

             ''email,

             t.xzqh_dmxzqh,

             ''nsrsbh_ts,

             t.xybz,

             t.yx_bzyxbz

        [e-mail]

       WHERENOTEXISTS (SELECTA.SWJG_DM

                FROMperm.dm_gy_swjgA

               WHEREA.SWJG_DM = T.SWJG_DM ||'00')

         ANDT.YX_BZ = 'Y'

         ANDT.SWBM_BJ = '0'

         andt.XYBZ = 'Y'

         ANDLENGTH(T.SWJG_DM) = '9';

 

    COMMIT;

 

  ENDLOOP;

  commit;

ENDP_YG_SWJG_DM;

 

 

查看存储过程具体错误：

SQL>  show errors procedure  GSYY.P_YG_SWJG_DM;

Errors for PROCEDURE GSYY.P_YG_SWJG_DM:

 

LINE/COL ERROR

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

0/0      ORA-04052:在查找远程对象 [e-mail]时出错 ORA-00604: 递归 SQL级别 1出现错误 ORA-12170: TNS: 连接超时 

 

 

查看dblink ZGXT.REGRESS.RDBMS.DEV.US.ORACLE.COM

select * from dba_db_links where db_link= 'ZGXT.REGRESS.RDBMS.DEV.US.ORACLE.COM' ;

连接IP地址如下：

(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.20.1.21)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = cqods)))

目前数据库主机ip为：10.0.0.7   与  192.20.1.21   之间是不能通信的 1521端口没开

网络重新做策略，让10.0.0.7   可以访问   192.20.1.21    1521端口，重新编译存储过程即可。