Oracle - how to reference to a field%TYPE using database links?

Question

I have this database which includes a couple of schemas. Say I need to access schemaONE from schemaTWO. To do this, I have created a database link named "DB_ONE_LINK".

I am able to access to schemaONE's tables using the following syntax:

SELECT * FROM "Tbl1"@"DB_ONE_LINK";

Now, when creating SPs and functions in schemaONE, I can define variables like this:

tmp_amount "schemaONE"."Tbl1"."Amount"%TYPE;

The question is, how can I reference to a field type in schemaONE using "DB_ONE_LINK"? something like this:

tmp_amount "Tbl1"."Amount"@"DB_ONE_LINK"%TYPE;

Is it even possible?

Answer 1

You can reference a remote table in a procedure or a package. Here's an example with a loopback link:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     l dual.dummy@loopback%TYPE;
  3  BEGIN
  4     SELECT * INTO l FROM dual@loopback;
  5     dbms_output.put_line(l);
  6  END;
  7  /

X

PL/SQL procedure successfully completed

I think there are restrictions and you should probably use a workaround such as a local view that references the remote table or a local synonym.

In any case this should remain an exception, obviously the best place to store a procedure is in the database where the data is stored. If you find that a program frequently needs access to several databases, it might be a good idea to merge those databases.