Monday, September 21, 2009

How to pass an object/table as an argument to a remote procedure

The only way to reference an object type between databases is via a database link. Note that it is not enough to just use "similar" type definitions.

Look at this example:

-- Database A: receives a PL/SQL table from database B
CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
BEGIN
-- do something with TabX from database B
null;
END;
/

-- Database B: sends a PL/SQL table to database A
CREATE OR REPLACE PROCEDURE pcalling IS
TabX DBMS_SQL.VARCHAR2S@DBLINK2;
BEGIN
pcalled@DBLINK2(TabX);
END;
/

No comments: