Solved

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

Posted on 2013-01-09
5
1,818 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

910 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

23 Experts available now in Live!

Get 1:1 Help Now