Open and close connection in oracle procedure

Question

I have below procedure in which i am inserting the data into temp_soap_monitoring table using select statement. I have soap_monitoring table in FONIC_RETAIL database from where i want to take the data and insert into temp_soap_monitoring table. I am performing the scheduler job of every 5 minutes for this procedure so that i always get the latest data from soap_monitoring@fonic_retail table. The problem of this procedure is that it takes too much cost and query execution time to run this procedure. Every time it try to fetch the data from db link and save it my local database. I remember in jave, php and other programming languages we have method to open the db connection when we tried to fetch data from db link and then once the insertion is done we can close the db connection. So the performance improves and it also does not put load on db link. In this method we dont have to assign db link to fetch the data. So i belive is there any way to apply this logic in my oracle procedure. The question arises because right now i am hardcoding the db link but now i have 3 more db links and i dont want to duplicate the same procedure by assigning diffferent db links.

PROCEDURE  "EXT_SOAP_MONITORING" AS

LAST_SM_ID Number := 0;
LAST_CAPT_DATE DATE;

BEGIN

SELECT LAST_TASK_ID INTO LAST_SM_ID FROM CAPTURING where DB_TABLE='TEMP_SOAP_MONITORING';

insert into TEMP_SOAP_MONITORING(ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE)
select 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;

update
CAPTURING set LAST_TASK_ID=
CASE WHEN ((SELECT MAX(ID) from TEMP_SOAP_MONITORING) IS NULL) AND (LAST_TASK_ID  IS NULL)  THEN (SELECT MAX(ID) from SOAP_MONITORING@FONIC_RETAIL)
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, LAST_CAPTURED_DATE = LAST_CAPT_DATE where DB_TABLE='TEMP_SOAP_MONITORING';

END EXT_SOAP_MONITORING;

Answer 1

ALTER SESSION CLOSE DATABASE LINK <user_supplied>; 

Reference