ORA-02070:在这种情况下，数据库不支持通过DB LINK [英] ORA-02070: database does not support in this context Via DB LINK

问题描述

我有一个View，并且想要通过DB Link插入它.但是它给出了错误"ORA-02070:在这种情况下数据库不支持".我该如何解决这个错误?

I have a View and want to insert this via DB Link. But it's give error "ORA-02070: database does not support in this context". How can I solve this error ?

CREATE OR REPLACE FORCE VIEW V_TEST
(
   OBJECT_ID,
   SEQUENCE,
   SCHEMA_NAME,
   OBJECT_NAME,
   OBJECT_TYPE_NAME,
   LINE,
   POSITION,
   ERROR_MESSAGE,
   CREATE_DATE
)
AS
     SELECT dbaObjects.OBJECT_ID,
            dbaErrors.SEQUENCE,
            dbaErrors.OWNER AS SCHEMA_NAME,
            dbaErrors.NAME AS OBJECT_NAME,
            dbaErrors.TYPE AS OBJECT_TYPE_NAME,
            dbaErrors.LINE,
            dbaErrors.POSITION,
            dbaErrors.TEXT AS ERROR_MESSAGE,
            SYSDATE AS CREATE_DATE
       FROM SYS.DBA_OBJECTS dbaObjects, SYS.DBA_ERRORS dbaErrors
      WHERE     dbaObjects.OWNER = dbaErrors.OWNER
            AND dbaObjects.OBJECT_NAME = dbaErrors.NAME
            AND dbaObjects.OBJECT_TYPE = dbaErrors.TYPE
            AND dbaObjects.OWNER != 'SYS'
            AND dbaObjects.OWNER = 'HELLO'
            AND dbaObjects.STATUS = 'INVALID'
            AND dbaErrors.TEXT != 'PL/SQL: SQL Statement ignored'
            AND dbaErrors.TEXT != 'PL/SQL: Statement ignored'
   ORDER BY dbaErrors.OWNER,
            dbaErrors.NAME,
            dbaErrors.TYPE,
            dbaErrors.SEQUENCE;

视图和远程表类型相同

插入声明:

INSERT INTO HELLO.T_INVALID_OBJECT_2@VADA (OBJECT_ID,
                                                    SEQUENCE,
                                                    SCHEMA_NAME,
                                                    OBJECT_TYPE_NAME,
                                                    OBJECT_NAME,
                                                    LINE,
                                                    POSITION,
                                                    ERROR_MESSAGE,
                                                    CREATE_DATE)
   SELECT V.OBJECT_ID,
          V.SEQUENCE,
          V.SCHEMA_NAME,
          V.OBJECT_TYPE_NAME,
          V.OBJECT_NAME,
          V.LINE,
          V.POSITION,
          V.ERROR_MESSAGE,
          V.CREATE_DATE
     FROM V_TEST V;

插入错误:(

推荐答案

该问题与视图中使用的视图(DBA_SOURCES和DBA_ERRORS)有某种关系.请参见V$SESSION 此处 a>和此处.

The problem has somehow to do with the views used in your view (DBA_SOURCES and DBA_ERRORS). See the discussion on the same variation with V$SESSION here and here.

不幸的是，我没有看到与V $ SESSION相同的简单解决方案(如上面的链接中所建议)，而且在我看来，问题是由(嵌套视图的)中调用sys_context引起的. )查看DBA_OBJECTS.因此， proper 解决方案是在不使用sys_context/USERENV的情况下编写自己的DBA_OBJECTS版本，或者在创建此数据的本地副本(例如在GTT中)

Unfortunately I don't see a same simple solution as for V$SESSION (as proposed in the link above) and it seems to me that the problem is cause by the call of sys_context in the (nested view of the) view DBA_OBJECTS. So the propper solution is either in writing your own version of DBA_OBJECTS without using sys_context / USERENV or in creation a local copy of this data (e.g. in GTT)

无论如何，有两种解决方法:

Anyway there are two workaround:

1)恢复插入，即通过DB LINK从视图连接到远程DB ans插入.

1) revert the insert, i.e. connect to the remote DB ans insert from a view via DB LINK.

insert into t_demo
select * from v_demo@demo;

如果无法连接到远程数据库，则可以

if connection to remote DB is not an option, you may

2)或者在远程数据库上定义一个过程，然后从本地数据库调用

2) alternatively define a PROCEDURE on the remote DB and call it from the local DB

-- ON REMOTE DB
create procedure ins_remote   
AS
   BEGIN
    insert into t_demo
    select * from v_demo@demo;        
   END;
/
-- plus grants for execute


-- ON LOCAL DB
begin 
   ins_remote@demo;
end;
/

这篇关于ORA-02070:在这种情况下，数据库不支持通过DB LINK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！