Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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… ) – Phil Nov 5 '13 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 Nov 5 '13 at 8:51
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 Nov 5 '13 at 15:05

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.

share|improve this answer
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 Nov 18 '13 at 19:20

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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