Re: stored function question
Date: 1998/08/13
Message-ID: <35d9e0f6.86506990_at_192.86.155.100>#1/1
A copy of this was sent to morris_newsgroup_at_yahoo.com (if that email address didn't require changing) On Wed, 12 Aug 1998 23:13:50 GMT, you wrote:
>I can do a select on a SYNONYM of a table
>at the sqlplus prompt successfully. However,
>when I referred to this SYNONYM in a stored
>function, Oracle had trouble in compiling the
>stored function. Suppose the name of the SYNONYM
>is called 'USERPRODUCT', I got the following error
>when compiling:
>
>SQL> show errors;
>Errors for FUNCTION SF_GET_SEARCH_CNT:
>
>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>6/1 PL/SQL: SQL Statement ignored
>6/31 PLS-00201: identifier 'USERPRODUCT' must be declared
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a stored procedure.
You probably have the privelege to do what you are trying to do via a role. Grant the privelege directly to the owner of the procedure and it'll work (grant select on TABLE to PROCEDURE_OWNER).
>Does anyone know why?
>
>Thanks in advance,
>Morris
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Aug 13 1998 - 00:00:00 CEST