ORA-22992 Remove references to LOB in remote table issue

Question

I am trying to insert into table CAPTURED_DATA_01 with multiple select statement.I am able to insert the value EVENT_ID,ENV_ID,BRAND_ID,BP_ID but now i also want to insert the SUBSCRIPTION_ID value which is coming i will get from using the select statement in remote table. The query i have tested and runs fine for getting the SUBSCRIPTION_ID. But when i try to use this select statement in order to insert the value of SUBSCRIPTION_ID into inside my insert query then i am getting the error where i have used cast function for the SUBSCRIPTION_ID inside my subquery as

SQL Error: ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables

Here is my Query:

Insert into CAPTURED_DATA_01(SUBSCRIPTION_ID) 
select WF.SUBSCRIPTION_ID 
   from 
   (select WF.SUBSCRIPTION_ID from WF_WORKFLOW@FONIC_RETAIL WF,CAPTURED_DATA_01 CP
where WF.SUBSCRIPTION_ID > CP.SUBSCRIPTION_ID and 
WF.SUBSCRIPTION_ID IN
( 
select iw.SUBSCRIPTION_ID
from (
   SELECT TO_NUMBER(REPLACE(REPLACE(REGEXP_SUBSTR(RESPONSE_XML, '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>\d+</ax2130:id>'), 
   '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>', ''), '</ax2130:id>', '')) 
   AS SUBSCRIPTION_ID , 
   CAST(REPLACE(REPLACE(
  REGEXP_SUBSTR(REQUEST_XML, '<ns7:orderType>.+</ns7:orderType>'),
    '<ns7:orderType>', ''), '</ns7:orderType>', '')
  AS VARCHAR(100)) AS order_type,
  TO_NUMBER(REPLACE(REPLACE(REGEXP_SUBSTR(RESPONSE_XML, '<ax2147:orderNumber>\d+</ax2147:orderNumber> '), 
   '<ax2147:orderNumber>', ''), '</ax2147:orderNumber> ', '')) 
   AS ORDER_NUMBER,
   CREATE_DATE
   FROM
   SOAP_MONITORING@FONIC_RETAIL 
   where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder' 
) iw
where iw.order_type='SELF_REGISTRATION'
)and WF.NAME='INITIATE_MANDATE' 
and WF.STATUS_ID=0)

Answer 1

As far as I understand it, the problem is that an insert is always run on the local database.

When you just run the select on its own Oracle can decide (or be hinted) to do some of the work on the remote database; in this case it's transforming the CLOB values into number and varchar2 types, and only those non-LOB values have to be transmitted across the network to your local database for further processing.

For the insert it will try to retrieve the whole LOB to transform it locally, and it stops that from happening - presumably due to the potential amount of data involved. The driving_site hint is ignored for an insert so you can't tweak that behaviour as you can for a select.

To work around it you can do the select and insert as two steps, via a cursor in a PL/SQL block. The general pattern would be:

declare
  type cp_tab_type is table of CAPTURED_DATA_01%ROWTYPE;
  cp_tab cp_tab_type;
  cur sys_refcursor;
begin
  open cur for
    select ... -- some value for every column in the table you're inserting
               -- into, in the same order they appear in the DDL
  loop
    fetch cur bulk collect into cp_tab;
    exit when cp_tab.count = 0;
    forall i in 1..cp_tab.count
      insert into CAPTURED_DATA_01 values cp_tab(i);
  end loop;
end;
/

Read more about bulk collect and forall to do batch query/inserts.

You could also, as you suggested, use a merge if you have something you can use for the on clause; for exmaple if event-id was not going to exist at all yet:

merge into CAPTURED_DATA_01 cp
using (
    select ..
) data
on (cp.event_id = data.event_id)
when not matched then
insert (EVENT_ID,SUBSCRIPTION_ID,EVENT_TIMESTAMP,ENV_ID,BRAND_ID,BP_ID)
values (data.event_id, data.subscription_id, data.start_date,
  data.env_id, data.brand_id, data.bp_id);

Your query gets more complicated with each question you post and could probably be simplified quite a bit.