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:


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?

  • 1
    Firstly, do you understand that you don't actually need to use a database link to access other schemas in the same database? Database links are used to access other databases. (See dba.stackexchange.com/questions/37012/… )
    – Philᵀᴹ
    Commented Nov 5, 2013 at 8:36
  • @Phil: Thank you for your reply, well I know that I can grant select to the other user in the same database and resolve this issue for now, but we might wanna split these schemas to different databases. what if then?
    – SJ.Jafari
    Commented Nov 5, 2013 at 8:51
  • 1
    As far as I can see, you're going to have to define the type locally. Could be wrong, but I can't see a way to do it.
    – Philᵀᴹ
    Commented Nov 5, 2013 at 15:05

1 Answer 1


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

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


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.

  • Now, I thought that he wanted to use %TYPE against a user-defined type that didn't exist on the local database. Must have read into the question too much.
    – Philᵀᴹ
    Commented Nov 18, 2013 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.