| 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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
