| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to return array of values from procedure?
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
 |
 |