asked on

How can i use synonym in procedure in oracle 11g?

I am using oracle 11g.
it have two schemas.
there is a tableA in schemaA.
and i created synonym named tableA for tableA(in schemaA) in schemaB.
then i created procedureA in schemaB. like below:

CREATE OR REPLACE PROCEDURE PROCEDURE1 AS
BEGIN
select * from tableA
END PROCEDURE1;

when i compile it ,it gave me error:
PL/SQL:ORA-00942:table or view does not exist.

if i excuted the sql
select * from tableA
in SQLPLUS (connect with schemaB)

it can excute.

So.my question is :
How can i use synonym in procedure?

wait for your help

Pratima

refer
http://dbaforums.org/oracle/index.php?showtopic=18607

Steve Wales

I tested the following scenario on 11.2.0.3 for Windos 64 Bit.

The error about the INTO is expected because you need an INTO with a select in a PL/SQL block.

The synonym works fine.

SQL> conn demo/demo
Connected.
SQL> create table demo (col1 char(10));

Table created.

SQL> conn demo1/demo1
Connected.
SQL> create table demo1 (col1 char(10));

Table created.

SQL> grant select on demo1 to demo;

Grant succeeded.

SQL> conn demo/demo
Connected.
SQL> create synonym demo1 for demo1.demo1;

Synonym created.

SQL> conn demo/demo
Connected.
SQL> create procedure proc1 as
  2  begin
  3    select * from demo1;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE PROC1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PLS-00428: an INTO clause is expected in this SELECT statement

Open in new window

I thought it may have been permissions related so tested that, but there's a different error message (which actually makes sense):

SQL> connect demo1/demo1
Connected.
SQL> revoke select on demo1 from demo;

Revoke succeeded.

SQL> conn demo/demo
Connected.
SQL> create or replace procedure proc1 as
  2  begin
  3    select * from demo1;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE PROC1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PL/SQL: SQL Statement ignored
3/17     PL/SQL: ORA-01031: insufficient privileges

Open in new window

Finally, I dropped the synonym:

SQL> conn demo/demo
Connected.
SQL> drop synonym demo1;

Synonym dropped.

SQL> create or replace procedure proc1 as
  2  begin
  3    select * from demo1;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE PROC1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PL/SQL: SQL Statement ignored
3/17     PL/SQL: ORA-00942: table or view does not exist
SQL>

Open in new window

This generated the error you're seeing.

Are you sure that your synonyms are setup correctly?

Could you post of the output of a query from dba_synonyms along the lines of:

select owner, synonym_name, table_owner, table_name
from dba_synonyms
where table_owner in ('schemaA','schemaB')
and table_name in ('tableA','tableB');

(working in abstract object names can get a little confusing, so show the definition of your synonyms).

Hopefully, the above is helpful.

mojian

ASKER

hello, sjwales,
Thanks for your helps.

what i did was:

SQL> conn user1/demo
Connected.
SQL> create table tableA (col1 char(10));

Table created.

SQL> conn user2/demo1
Connected.
SQL> CREATE OR REPLACE SYNONYM tableA FOR "user1"."tableA ";

SQL> conn demo/demo
Connected.
SQL> create or replace procedure proc1 as
2 begin
3 select * from tableA ;
4 end;
5 /

error:ORA-00942:table or view does not exist.

please help me.

ASKER CERTIFIED SOLUTION

Javier Morales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mojian

ASKER

Thanks for your help.
it exactly solved my problem.
what i need is to grant select on tableA to schemaB;