Calling Remote Procedure in PLSQL

1. Create the database link to the remote server (replace service name with your remote service name):

DROP DATABASE LINK db1;

CREATE DATABASE LINK db 1 CONNECT TO scott IDENTIFIED BY tiger USING '<service name>';

2. Create the remote function

CREATE OR REPLACE FUNCTION f_num(id number) return number is

result number;

BEGIN

SELEC T deptno INTO result FROM emp WHERE empno = id;

RETURN result;

END;

/

3. Create the call to the remote function:

DECLARE

v_num n umber;

BEGIN

v_num := f_num@db1(7900);

END;

/

4. Select from v$dblink to find out if the database link is currently in a transaction

select db_link, in_transaction from v$dblink where db_link like '%DB1%';

5. Issue the rollback or commit to end the transaction. After this you can then recheck v$dblink.

rollback;

6. Close the database link

exec dbms_session.close_database_link('db1');