I am performing data extraction using oracle job scheduler 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 to insert the data locally then i am getting an error as :
ORA-02050: transaction 2.22.1104 rolled back, some remote DBs may be in-doubt
ORA-03114: not connected to ORACLE
ORA-02063: preceding line from FONIC_RETAIL
ORA-03113: end-of-file on communication channel
ORA-02063: preceding line from FONIC_RETAIL
ORA-03113: end-of-file on communication channel
ORA-02063: preceding line from FONIC_RETAIL
I think this issue only happening when i am using Oracle in Linux OS. In window i used Oracle for the same procedure,scheduling job and it works absolutely fine without causing any issue for several days.This error are occured occasionally. The extraction for SOAP_MONITORING@FONIC_RETAIL runs fine for sometimes and generates the error again and then it will continue to give the same error. 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. But i am not sure about this issue. 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);
COMMIT;
END EXT_SOAP_MONITORING;