Re: Why procedure cannot see table?

I'd like to thank the people that responded to my original question.

I got my stored procedure to work by granting explicit SELECT access to the table to MYUSER. I originally tried to grant SELECT privilege to the role MYUSER belongs to, but that did not seem to work.

Now that I got that working, would anybody mind telling me why granting SELECT to the ROLE does not work and granting SELECT to MYUSER work?

Again, thanks in advance.

Please send your response to zlm101_at_psu.edu

I created a stored procedure, LOAD_TOP_CUST, but I'm getting this error when I call it.

ERROR at line 1:

ORA-00942: table or view does not exist
ORA-06512: at "MYUSER.LOAD_TOP_CUST", line 3
ORA-06512: at "MYUSER.LOAD_TOP_CUST", line 8
ORA-06512: at line 2

The procedure looks like this

CREATE OR REPLACE
PROCEDURE load_top_cust IS
  CURSOR top_cust_cur IS
    SELECT
      customer_id
    FROM customer_sales;

  top_cust_rec top_cust_cur%ROWTYPE;

BEGIN /*load_top_cust*/
  OPEN top_cust_cur;
  LOOP
  . . .
  CLOSE top_cust_cur;

END load_top_cust;

One notable thing I'd like to mention is that the CUSTOMER_SALES table was created by a different user, i.e. OTHERUSER. I tried declaring it as OTHERUSER.customer_sales but then I cannot even compile the procedure.

I granted select, made a public synonym, but nothing seems to work.

Any information is very much appreciated.

Thanks in advance. Received on Tue Dec 31 1996 - 00:00:00 CST