Doing Remote Operations
As the following example shows, PL/SQL subprograms can execute dynamic SQL
statements that refer to objects on a remote database:
\nPROCEDURE delete_dept (db_link VARCHAR2, dept_id INTEGER) IS\nBEGIN\nEXECUTE IMMEDIATE \ufffdDELETE FROM dept@\ufffd || db_link ||\n\ufffd WHERE deptno = :num\ufffd USING dept_id;\nEND;
Also, the targets of remote procedure calls (RPCs) can contain dynamic SQL
statements. For example, suppose the following standalone function, which returns
the number of rows in a table, resides on the Chicago database:
\nCREATE FUNCTION row_count (tab_name VARCHAR2) RETURN INTEGER AS\nrows INTEGER;\nBEGIN\nEXECUTE IMMEDIATE \ufffdSELECT COUNT(*) FROM \ufffd || tab_name INTO rows;\nRETURN rows;\nEND;
From an anonymous block, you might call the function remotely, as follows:
\nDECLARE\nemp_count INTEGER;\nBEGIN\nemp_count := row_count@chicago(\ufffdemp\ufffd);