You have two schemas on the same Oracle instance: schemaA and schemaB. You have a procedure called proc1 in schemaA and you have a table emp on schemaB that you want to read from schemaA. Perform the following two steps:
Step 1: Log onto schemaB. Grant references and select on emp to schemaA as follows:
grant references, select on emp to schemaA;
Step 2: While logged in as schemaB, create a public synonym as follows:
create PUBLIC synonym emp for emp;
Now log onto schemaA and create proc1, using emp from schemaB as:
create or replace procedure proc1 as v_emp_no number; begin select emp_no into v_emp_no from emp where emp_no = 1; end;
Dig deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.