Oracle - how to reference to a field%TYPE using database links? 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?
You can reference a remote table in a procedure or a package. Here's an example with a loopback link:

SQL&gt; SET SERVEROUTPUT ON
SQL&gt; 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.