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');