Remote Operations:

As the illustration shows below, the PL/SQL subprograms can execute the dynamic SQL statements which refer to the objects on a remote database:

PROCEDURE delete_dept (db_link VARCHAR2, dept_num INTEGER) IS

BEGIN

EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link ||

' WHERE deptno = :n' USING dept_num;

END;

The targets of remote procedure calls (RPCs) can also contain the dynamic SQL statements. For illustration, assume that the following stand-alone function, that returns the number of rows in the table, reside on the Chicago database:

CREATE FUNCTION row_count (tab_name CHAR) RETURN INT AS

rows INT;

BEGIN

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;

RETURN rows;

END;

From an unspecified block, you may call the function remotely, as shown below:

DECLARE

rows INTEGER;

BEGIN

rows := row_count@chicago('emp');