You have two schemas on the same Oracle instance: schemaA and schemaB. You have a procedure called proc1 in schemaA...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.