Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
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/… ) –  FreshPhilOfSO 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
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. –  FreshPhilOfSO Nov 5 '13 at 15:05
add comment

1 Answer

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.

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. –  FreshPhilOfSO Nov 18 '13 at 19:20
add comment

Your Answer

 
discard

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.