Q

Execute SQL statement from table in other schema

I want to execute SQL statements that use other schema tables from my stored procedure. The stored procedure is in one schema and the table is in the other schema. I am getting the "ORA-01031: insufficient privileges" error.

I want to execute SQL statements that use other schema tables from my stored procedure. The stored procedure is in one schema and the table is in the other schema. I am getting "ORA-01031: insufficient privileges" error. Please suggest how to execute statement from a table from a stored procedure of another user. Both the two users are in the same Oracle instance. The database is Oracle 10g.

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; 
This was last published in April 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close