Solved

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

Posted on 2013-01-09
5
1,803 Views
Last Modified: 2013-01-10
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
0
Comment
Question by:mojian
5 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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.
0
 

Author Comment

by:mojian
Comment Utility
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.
0
 
LVL 6

Accepted Solution

by:
Javier Morales earned 500 total points
Comment Utility
Hi Mojian,

you just need to give privs to SchemaA for tableA from SchemaB explicitly.

Synonym is just making the name being translated to point another object, but privileges have to be given for each table by the owner.

Here's an example:

1.- The two users are created with CONNECT and RESOURCE privilege. User schema_b has also privileges to create synonyms.

SQL> create user schema_a identified by schema_a;

User created.

SQL> create user schema_b identified by schema_b;

User created.

SQL> grant connect, resource to schema_a, schema_b;

Grant succeeded.

SQL> grant create synonym to schema_b;

Grant succeeded.

Open in new window


2.- User schema_a creates a table, and user schema_b creates a synonym to this table.

SQL> connect schema_a/schema_a
Connected.
SQL> create table table1 (id number);                      

Table created.

SQL> insert into table1 values (1);

1 row created.

SQL> insert into table1 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> grant select on table1 to schema_b;

Grant succeeded.

SQL> connect schema_b/schema_b
Connected.
SQL> create synonym table1 for schema_a.table1;

Synonym created.

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCHEMA_B                       CONNECT                        NO  YES NO
SCHEMA_B                       RESOURCE                       NO  YES NO

SQL> column privilege format a15
SQL> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE       GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------- --- ---
SCHEMA_B                       SCHEMA_A                       TABLE1                         SCHEMA_A                       SELECT          NO  NO

SQL> select count(*) from table1;

  COUNT(*)
----------
         2

SQL> declare    total number;
  2  begin
  3         select count(*) into total from table1;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> create procedure test as
  2   total number;
  3  begin
  4         select count(*) into total from table1;
  5  end;
  6  /

Procedure created.

SQL> exec test

PL/SQL procedure successfully completed.

Open in new window


It works for me. May you show us the values for USER_ROLE_PRIVS and USER_TAB_PRIVS ?

By the way, It happened me something similar with privileges granted from a role and directly to user, and I wrote an article in EE you may find it reproduces your issue!

The strange case of the insufficient privileges error when creating a materialized view using execute immediate inside a PL/SQL procedure.

Hope it helps,
0
 

Author Closing Comment

by:mojian
Comment Utility
Thanks for your help.
it exactly solved my problem.
what i need is to grant select on tableA to schemaB;
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now