Solved

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

Posted on 2013-01-09
5
1,870 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
ID: 38761978
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38761994
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
ID: 38762076
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
ID: 38762080
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
ID: 38762144
Thanks for your help.
it exactly solved my problem.
what i need is to grant select on tableA to schemaB;
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
return value in based on value passed 6 37
RAISERROR WITH NOWAIT 2 24
SQL - Curser to do an insert based on a select 2 24
add criteria to query in VB, Access 2003 2 29
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

685 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