insert /*+ append */ into x
select * from table(pip)

select .. from view@remote where col in (..,..)

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
Connected as xxx
 
SQL> drop database link pip_test_link
  2  /
 
Database link dropped
 
SQL> drop package pip_remote_test
  2  /
 
Package dropped
 
SQL> drop table pip_test_tab
  2  /
 
Table dropped
 
SQL> create database link pip_test_link connect to ... identified by ... using '...:1521/orcl'
  2  /
 
Database link created
 
SQL> 
SQL> create or replace package pip_remote_test is
  2  type rec_t is record(dummy varchar2(4000));
  3  type tab_t is table of rec_t;
  4  function pip return tab_t pipelined;
  5  end;
  6  /
 
Package created
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    v_cur sys_refcursor;
  4    v_row rec_t;
  5  begin
  6    open v_cur for 'select * from dual@pip_test_link';
  7    fetch v_cur into v_row;
  8    pipe row(v_row);
  9    close v_cur;
 10  end;
 11  end;
 12  /
 
Package body created
 
SQL> select * from table(pip_remote_test.pip)
  2  /
 
DUMMY
--------------------------------------------------------------------------------
X
 
SQL> create table pip_test_tab(dummy varchar2(4000))
  2  /
 
Table created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
 
ORA-12840: cannot access a remote table after parallel/insert direct load txn
ORA-06512: at "DAVID.PIP_REMOTE_TEST", line 6
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    fetch v_cur into v_row;
  9    pipe row(v_row);
 10    close v_cur;
 11  end;
 12  end;
 13  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
 
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "DAVID.PIP_REMOTE_TEST", line 9
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    fetch v_cur into v_row;
  9    commit;
 10    pipe row(v_row);
 11    close v_cur;
 12  end;
 13  end;
 14  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
1 row inserted
 
SQL> rem в общем, ситуация более-менее ясна.. но в общем случае pipe row идет внутри цикла с fetch
SQL> rem и коммит там делать совершенно неохота (snapshot too old). если же делать commit до открытия курсора
SQL> rem - ошибка 06519 не исчезает
SQL> 

SQL> rem то есть
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    commit;
  8    open v_cur for 'select * from dual@pip_test_link';
  9    fetch v_cur into v_row;
 10    pipe row(v_row);
 11    close v_cur;
 12  end;
 13  end;
 14  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
 
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "DAVID.PIP_REMOTE_TEST", line 10
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    commit;
  9    fetch v_cur into v_row;
 10    pipe row(v_row);
 11    close v_cur;
 12  end;
 13  end;
 14  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
1 row inserted
 
SQL> rollback
  2  /
 
Rollback complete
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    fetch v_cur into v_row;
  9    commit;
 10    pipe row(v_row);
 11    close v_cur;
 12  end;
 13  end;
 14  
 15  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
1 row inserted
 
SQL> rollback
  2  /
 
Rollback complete
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    fetch v_cur into v_row;
  9    pipe row(v_row);
 10    commit;
 11    close v_cur;
 12  end;
 13  end;
 14  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
 
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "DAVID.PIP_REMOTE_TEST", line 9
 
SQL> 

SQL> connect scott@orcl
Enter password: *****
Connected.
SQL> create or replace
  2    procedure do_commit
  3      is
  4      begin
  5          commit;
  6  end;
  7  /

Procedure created.

SQL> connect scott@sol10
Enter password: *****
Connected.
SQL> drop table emp1 purge
  2  /

Table dropped.

SQL> create table emp1
  2    as
  3      select  *
  4        from  emp
  5        where 1 = 2
  6  /

Table created.

SQL> insert
  2    into emp1
  3    select  *
  4      from  emp
  5  /

14 rows created.

SQL> exec do_commit@orcl

PL/SQL procedure successfully completed.

SQL> rollback
  2  /

Rollback complete.

SQL> select  count(*)
  2    from  emp1
  3  /

COUNT(*)
--------
      14

SQL> 

create table xt_test as select * from all_users where 1=0;
insert into xt_test select * from all_users@remote;
select * from v$lock l where l.type='DX' and sid=sys_context('userenv','sid');
rollback;
insert/*+ append */ into xt_test select * from all_users@remote;
select * from v$lock l where l.type='DX' and sid=sys_context('userenv','sid');
rollback;