Thanks! You're a fountain of knowledge.
Thomas Kyte wrote in message <36edbebf.1752129_at_192.86.155.100>...
>A copy of this was sent to "John Haskins" <76054.334_at_compuserve.com>
>(if that email address didn't require changing)
>On Mon, 15 Mar 1999 16:31:11 -0800, you wrote:
>
>>OK, I'm with you regarding replacing "DECLARE" with "CREATE OR
REPLACE...".
>>The problem I was running into is that both of your main examples
procedures
>>require that the tables they reference be in the same schema as the
>>procedure. The first time I tried them I was logged in under a different
>>name, and modified each occurence of EMP in your examples to SCOTT.EMP. I
>>got this in response:
>>
>>Warning: Procedure created with compilation errors.
>>
>>SQL> show errors
>>Errors for PROCEDURE GET_CHILDREN_1:
>>
>>LINE/COL ERROR
>>-------- -----------------------------------------------------------------
>>5/12 PL/SQL: SQL Statement ignored
>>5/40 PLS-00201: identifier 'SCOTT.EMP' must be declared
>>7/1 PL/SQL: Statement ignored
>>7/16 PLS-00364: loop index variable 'X' use is invalid
>>
>>SQL> select * from scott.emp where empno = 7900;
>>
>> EMPNO ENAME JOB MGR HIREDATE SAL COMM
>>DEPTNO
>>--------- ---------- --------- --------- --------- --------- --------- ---
--
>>----
>> 7900 JAMES CLERK 7698 03-DEC-81 950
>>30
>>
>>Clearly SCOTT.EMP is accessible to the SQL prompt, even when I'm logged
into
>>a different schema as a different user. Any idea why the procedure won't
>>comile under those conditions?
>>
>>Thanks for all your help.
>>
>>
>
>roles are never enabled during the execution/compilation 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 procedure.
>
>You probably have the privelege to do what you are trying to do in the
procedure
>via a role. Grant the privelege directly to the owner of the procedure and
>it'll work.
>
>grant select on scott.emp to <OWNER>;
>
>
>
>
>>
>>
>>
>>Thomas Kyte wrote in message <36f3846a.33501933_at_192.86.155.100>...
>>>A copy of this was sent to "John Haskins" <76054.334_at_compuserve.com>
>>>(if that email address didn't require changing)
>>>On Mon, 15 Mar 1999 13:08:35 -0800, you wrote:
>[snip]
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Service Industries
>Reston, VA USA
>
>--
>http://govt.us.oracle.com/ -- downloadable utilities
>
>---------------------------------------------------------------------------
-
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation
Received on Tue Mar 16 1999 - 11:52:08 CST
