Re: proc won't compile - privilege issue?

"Ms. D.H. Harvey" <qq45_at_liverpool.ac.uk> wrote in message news:c6j5v5$p5f$1_at_news.liv.ac.uk...
> ,niz <niz_at_infidel.freeserve.co.uk> wrote:
> : i have a proc, sp_moveacct, in the SLIME schema, which doesnt compile.
> : when trying to compile it with the SLIME user, it gives this error:
>
> : PROCEDURE SLIME.SP_MOVEACCT
> : On line: 11
> : PLS-00201: identifier 'LEMON.EXTERNAL_ID_ACCT_MAP' must be declared
>
> : so it seems it can't see lemon.external_id_acct_map table. but it can!
> : that table has select/insert/delete/update permissions to LEMONROLE
> : role (a role SLIME belongs to), and the SLIME user has a private
> : synonym on this table.
>
> : i'm sure there is some obvious oracle thing i'm missing but i can't
> : see what it is....
>
> : here is the proc code:
>
> : CREATE OR REPLACE procedure sp_MoveAcct (ExtId varchar,
> : ExtIdType int,
> : FromServerId varchar,
> : ToServerId varchar) is
> : actn number(10);
> : r_ctr number;
> : begin
>
> : select account_no into actn from external_id_acct_map
> : where external_id = ExtId
> : and external_id_type = ExtIdType;
> : commit;
> : end;
> : /
>
> Grant privileges on LEMON.EXTERNAL_ID_ACCT_MAP directly to SLIME.

Why do you have a commit after the select? This isn't SQL Server or DB2 where you need to release read locks.
Jim
>
> Grants via roles are ignored when creating/altering compiled objects
> eg packages, procedures, functions, views etc.
>
> Helen
>
> /\/\ /\/\ /\/\ /\/\ /\/\ /\/\ /\/\ /\/\ /\/\ /\/\
/\/\
> ( oo ) ( oo ) ( oo ) ( oo ) ( oo ) ( ** ) ( oo ) ( oo ) ( oo ) ( oo ) (
oo )
> =^^= =^^= =^^= =^^= =^^= =^^= =^^= =^^= =^^= =^^=
=^^= Received on Mon Apr 26 2004 - 09:31:28 CDT