Ask the Expert

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 "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.

    Requires Free Membership to View

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 first published in April 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: