Re: How to return array of values from procedure?

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.

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:
>
>>Thomas:
>>
>>Thanks for the reply. You've helped me out before; nice to hear from you
>>again.
>>
>>Now, to your answer. I appreciate the information. I was already able to
>>get the returned dataset to display via dbms_output, although the code you
>>sent is a much tighter way to get the same result. What I need to figure
>>out now is how to have the resulting dataset get returned to whatever
>>procedure called the "get children records" routine. That transfer of data
>>back to the calling procedure is the part I don't understand. Any
>>assistance you can provide would be greatly appreciated!
>>
>>Thanks again.
>>
>
>
>I thought that is what I showed?? I built 2 routines -- get_children_1 and
_2.
>Each return a result set of sorts to their caller. Then I used 2 anonymous
>pl/sql blocks to call each one. So, for example:
>
>SQL> create or replace
> 2 procedure get_children_1( P_parent_id in number,
> 3 p_child_codes out MY_TYPES.idArray )
> 4 as
> 5 begin
> 6 for x in ( select empno, rownum r from emp where mgr =
p_parent_id )
> 7 loop
> 8 p_child_codes( x.r ) := x.empno;
> 9 end loop;
> 10 end;
> 11 /
>
>Procedure created.
>
>
>is called from another procedure/block of code as such:
>
>SQL> declare
> 2 child_codes my_types.idArray;
> 3 begin
> 4 get_children_1( 7839, child_codes );
> 5
> 5 for i in 1 .. child_codes.count
> 6 loop
> 7 dbms_output.put_line( child_codes(i) );
> 8 end loop;
> 9 end;
> 10 /
>
>change the declare in the above snippet to "create or replace procedure foo
as"
>and it is another procedre that calls the get_children_1 procedure to get
the
>data and do something with it.
>
>The other example was a variation on a theme (instead of returning an
array, it
>returned a cursor to the answer)
>
>does that make sense?
>
>
>>
>>
>[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 Mon Mar 15 1999 - 18:31:11 CST