Oracle - how to reference to a field%TYPE using database links?
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.