How to insert CLOB data type column value locally from remote database in oracle

Question

I am performing data extraction from remote database into my local database using database link.The extraction works fine for the other remote database tables but when i try to insert to fetch the data from remote database SOAP_MONITORING@FONIC_RETAIL table and try insert the data locally then i am getting an error as :

EXCEPTION START EXT_SOAP_MONITORING 20.04.15 09:16:02,070559000 +02:00
EXCEPTION ORA-03113: end-of-file on communication channel
ORA-02063: preceding
line from FONIC_RETAIL
ERROR CODE-3113

ERROR:
ORA-00600: internal error code, arguments: [729], [33464], [space leak], [],
[], [], [], [], [], [], [], []

This is serious issue i am facing and did not find any way from this. The SOAP_MONITORING@FONIC_RETAIL has REQUEST_XML,RESPONSE_XML columns which contains large xml string value and it has CLOB datatype. And i think the problem is because of this 2 columns as it is clob data type and contains large xml strings and clob has problems when working with remote database.The SOAP_MONITORING@FONIC_RETAIL contains ID column which is sequence generated and i am saving the max ID in CAPTURING table and then during next extraction 15minute scheduling job time, i am getting the data where ID is greater than the last saved ID. Here is my procedure:

PROCEDURE "EXT_SOAP_MONITORING" AS 
LAST_SM_ID Number := 0;

BEGIN

--DELETE DATA FROM TEMP_SOAP_MONITORING
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SOAP_MONITORING';

-- first retrieve the last id (of the newest record) which has been imported at last extraction
SELECT LAST_TASK_ID INTO LAST_SM_ID FROM CAPTURING where DB_TABLE='TEMP_SOAP_MONITORING';

-- retrieve all new records from remote SOAP_MONITORING@FONIC_RETAIL and insert it into TEMP_SOAP_MONITORING using MERGE statement
-- MERGE statement is able to retrieve CLOB fields:

merge into TEMP_SOAP_MONITORING TSM
using (
   select * from
(select DISTINCT(ID),REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE from 
SOAP_MONITORING@FONIC_RETAIL WHERE WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder' and ID > LAST_SM_ID order by ID desc) where rownum <=1000
) data
ON (TSM.ID = data.ID)
when not matched then
insert(ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE)
values(data.ID,data.REQUEST_XML,data.RESPONSE_XML,data.WEB_SERVICE_NAME,data.WEB_METHOD_NAME,data.CREATE_DATE,data.ERROR_CODE,data.ERROR_MESSAGE);


-- Set the newest extracted ID as LAST_TASK_ID for TEMP_SOAP_MONITORING in the Capturing table
update
CAPTURING set LAST_TASK_ID= 
CASE WHEN (SELECT MAX(ID) from TEMP_SOAP_MONITORING) IS NULL THEN LAST_TASK_ID + 1 ELSE (SELECT MAX(ID) from TEMP_SOAP_MONITORING) END,
CAPTURING_DATE = CURRENT_TIMESTAMP where DB_TABLE='TEMP_SOAP_MONITORING';

-- Extract SUBSCCRIPTION_ID,  ORDER_NUMBER and ORDER_TYPE from XML (CLOB) fields and store it separately in TMP_SOAP_MONITORING_IDS table:

-- first delete the last extraction data
DELETE FROM TMP_SOAP_MONITORING_IDS WHERE CREATE_DATE < SYSDATE - 7;

COMMIT;  
END EXT_SOAP_MONITORING;

Answer 1

As Rene states, you should contact Oracle Support and/or read some support documents pertaining to this error. It could be a bug that can be solved by doing an upgrade