From: boston103@hotmail.com   
      
   Hello Andrew,   
      
   A privilege ('select/update/delete') has to be granted directly to the   
   procedure executor,  not via a role.   
      
   sqlplus can read the table because the access is granted via a role.   
      
   Rgds.   
      
   "Andrew Metcalfe"  wrote in message   
   news:a2588ec3.0312120944.ad3188f@posting.google.com...   
   > I'm having trouble writing a simple procedure.   
   >   
   > I'm trying to simply select against a table in a different schema.   
   >   
   > I first wrote a simple procedure that selected against a table in my   
   > schema, which worked.   
   >   
   > CREATE OR REPLACE PROCEDURE test   
   > IS   
   >    CURSOR cur_test   
   >    IS   
   > select *   
   > from  tablename;   
   > BEGIN   
   >      FOR itemrec IN cur_test   
   >   LOOP   
   >    DBMS_OUTPUT.PUT_LINE('record!');   
   >   
   >     END LOOP;   
   > END test;   
   >   
   > A simple change to this is to change my select line to:   
   >   
   > select *   
   > from  otherTablespace.tablename;   
   >   
   > This gives me an error saying "otherTablespace.tablename must be   
   > declared."   
   >   
   > My first thought was to create a public synonym, so I did.   
   >   
   >         CREATE PUBLIC SYNONYM mySyn FOR otherTablespace.tablename   
   >   
   > I also  change my select line to read:   
   >   
   > select *   
   > from  mySyn   
   >   
   > PLS-00201: identifier mySyn' must be declared.   
   >   
   > What am I missing?  Why can't I "see" the other schemas table in a   
   > procedure even when explicitly referenced using the   
   > tablespace.tablename?   
   >   
   > I am able to access the table through SQLPlus...?   
   >   
   > Thanks.   
   >   
   > _Am   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)