OraFAQ Forum: SQL & PL/SQL » Use of public synonyms in Stored Procedures?

Home » SQL & PL/SQL » SQL & PL/SQL » Use of public synonyms in Stored Procedures?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Use of public synonyms in Stored Procedures? [message #16274]

Fri, 19 November 2004 09:14

K. McGrath
Messages: 2
Registered: November 2004

Junior Member

I'm having a problem with creating a stored procedure that references public synonyms for tables owned by another schema.

Here's an example:

SQL> create table a_user.test_table
2 (a_column number(6));

Table created.

SQL> create public synonym test_table for a_user.test_table;

Synonym created.

SQL> grant all on test_table to appDev;
(appDev is a role that contains both a_user and b_user).

Grant succeeded.

SQL> create or replace package testpkg as
2 TYPE testType IS REF CURSOR RETURN test_table%ROWTYPE;
3 END;
4 /

Package created.

SQL> 1
1* create or replace package testpkg as
SQL> c/testpkg/b_user.testpkg
1* create or replace package b_user.testpkg as
SQL> /

Warning: Package created with compilation errors.

SQL> show errors
Errors for PACKAGE B_USER.TESTPKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: Declaration ignored
2/36 PLS-00201: identifier 'TEST_TABLE' must be declared

-----

As long as the package is being created in the schema which contains the real table, it compiles fine. If I try to create the package in any other schema, it doesn't compile because it cannot find the synonym-ed table. I have gone into the B_USER schema and desc'd the table, and added rows, etc. so I know the public synonym is working. Is it just a limitation of PL/SQL stored procedures that they cannot reference public synonyms?

--KM

Report message to a moderator

Re: Use of public synonyms in Stored Procedures? [message #16275 is a reply to message #16274]

Fri, 19 November 2004 11:40

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

It's not the synonym that is an issue - it's that roles are not active in procedures. User B will need a direct grant on that table (not through a role).

http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

Report message to a moderator

Re: Use of public synonyms in Stored Procedures? [message #16276 is a reply to message #16274]

Fri, 19 November 2004 11:44

andrew again
Messages: 2577
Registered: March 2000

Senior Member

For some reason you can't use role based privs. Make the grant directly to the user.
http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html

Report message to a moderator

Re: Use of public synonyms in Stored Procedures? [message #16304 is a reply to message #16275]

Mon, 22 November 2004 05:24

K. McGrath
Messages: 2
Registered: November 2004

Junior Member

Ah, okay, that works. :)

Thanks for the heads up.

--KM

Report message to a moderator

Previous Topic:	Please help with trigger
Next Topic:	Dates finding the latest

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 17 08:35:48 CDT 2025