Re: running into problem with package.

From: Frank <fbortel_at_nescape.net>
Date: Sat, 20 Dec 2003 15:21:14 +0100
Message-ID: <bs1lbn$mrp$1@news3.tilbu1.nb.home.nl>

SP wrote:

> Sorry I didn't mention my problem. The 'create synonym...' should be
> running instead it's going to error handler. Any idea why it is so?
> SP
> "Frank" <fbortel_at_nescape.net> wrote in message
> news:brvejc$u56$1_at_news4.tilbu1.nb.home.nl...
>

>>SP wrote:
>>
>>
>>>Hi all,
>>>I'll explain this first.  First, synonym STATUS for userA is dropped if

>
> one
>

>>>exists for that user otherwise will go to exception handler.  After the
>>>first drop statement there shouldn't be any synonym named 'STATUS' owned

>
> by
>

>>>userA.  But on execution they both are being handled by exception

>
> handler.
>

>>>But when I run seperately they run just fine.  It's not the permission

>
> issue
>

>>>as this statement is being run by a account with DBA privilege and when

>
> the
>

>>>statements are run seperately (not from inside package) they just run

>
> fine.
>

>>>Any help would be appreciated.
>>>TIA,
>>>SP
>>>
>>>CREATE OR REPLACE PACKAGE TEST_Utilities
>>>AS
>>>PROCEDURE SwitchToSourceA;
>>>END TEST_Utilities;
>>>/
>>>CREATE OR REPLACE PACKAGE BODY TEST_Utilities IS
>>>PROCEDURE SwitchToSourceA IS
>>>BEGIN
>>>    BEGIN EXECUTE IMMEDIATE ('DROP SYNONYM userA.STATUS'); EXCEPTION

>
> WHEN
>

>>>OTHERS THEN NULL; END;
>>>    BEGIN EXECUTE IMMEDIATE ('CREATE SYNONYM userA.STATUS FOR
>>>userB.STATUS'); EXCEPTION WHEN OTHERS THEN NULL; END;
>>>END SwitchToSourceA;
>>>
>>>END;
>>>/
>>>
>>>
>>
>>What is the problem?
>>
>>-- 
>>Merry Christmas and a Happy New Year,
>>Frank van Bortel
>>

>
>
>

Yup - roles... As Michel said
You have to grant 'create any synonym' directlty to the user (if using invoker's rights) or the owner of the proc (when using owner rights - the default)

-- 
Merry Christmas and a Happy New Year,
Frank van Bortel

Received on Sat Dec 20 2003 - 08:21:14 CST