Re: Grant Execute to Packages

If I understand this correctly, you want a procedure to be able to work on the same table structure in different schemas. I believe that to do this you'll need to rewrite it as dynamic SQL and concatenate the schema name into the sql string before .table_name. Pass the schema name to the procedure as an additional parameter:

Division.Credit_Package.CreateDailyAdjustments (theSchema, AsOfDate);

Make sure the schema user that owns the package has permissions on the tables in other schemas it will be populating.

Best!

Randall

Sally Madeira <sallym1_at_home.com> wrote in message news:WP0U6.65120$v5.5166002_at_news1.rdc1.ct.home.com...
> I created a Role called StuckyProc so that I could share my packages
 across
> all users. The Division User is the users that manages all the procedures
> and packages:
>
> Grant Execute on Division.Credit_Package to StuckyProc
>
> I have several users but the users I am working with are Division and
> Credit - Division again being responsible for all the procedures and
> packages and the Credit user has the tables. I have other users that will
> need to also share the packages in The division user. I store everything
 in
> the division user because other users share the same procedures/packages
 and
> I do not want to maintain the code in every user (CREDIT, PLANNING,
 FINANCE
> for example will all share the
> Division.Credit_Package.CreateDailyAdjustments Procedure within the
> package).
>
> When I am in the Credit user I have a procedure that runs the following:
>
> CREATE OR REPLACE PROCEDURE CREDIT.EOMTEST (AsOfDate IN DATE)
> AS
>
> BEGIN
>
> --Libor for Web/Report 41/Yldmmyy Feed/Report 5
> Division.Credit_Package.CreateDailyAdjustments (AsOfDate);
>
> END;
>
> It compiles fine - What this is doing is refreshing a table and inserting
> daily adjustements. The problem I am having (and I thought it was sappose
> to work this way) is that the Credit users DAILYADJ table is still empty
> after I run this but the Division Table DAILYADJ gets populated. What I
> want to be able to do is share the procedures and be able to run them so
> that the USER CREDIT DAILYADJ Table get updated.
>
> Could anyone tell me what I am doing wrong-----
>
> Thanks
> S
>
>
>
>
>
Received on Fri Jun 08 2001 - 10:09:14 CDT