stored procedure that crosses schema

From: Jason Sharp <jdsharp_at_bigfoot.com>
Date: 16 Nov 1998 15:39:03 -0600
Message-ID: <36539b0e.897879_at_news1.newscene.com>

I'm trying to write a stored procedure that crosses schema boundaries. For example when I compile the following stored procedure I receive the error, PLS-00201: identifier 'IOR.PM_BUNDLE' must be declared. The RPC owner is "projtrak" and the table owner is "ior".

• Both schemas reside in the same database.
• Both schemas have select any table privileges via the same role.

If I explicitly grant schema "projtrak", select privileges on the "ior" table pm_bundle the RPC compiles and executes.

We are trying to avoid granting select, update, etc privileges to any non-developer either directly or through a role. All table access should be through stored procedures. Therefore, is there another way to accomplish this without granting select privileges to "projtrak"?

CREATE PROCEDURE projtrak.GET_PC_TEST

   (my_cur in out projtrak.pc_test.ior_rec)

AS
BEGIN         open my_cur for

         select a.bundle
           from ior.pm_bundle a;

END; Received on Mon Nov 16 1998 - 22:39:03 CET