Re: returning array type from a function

From: Bobby Z. <vladimir.zakharychev_at_gmail.com>
Date: Mon, 23 Mar 2009 07:57:10 -0700 (PDT)
Message-ID: <2de2b62f-3fe9-4b0c-8e75-b913be6d53b9_at_j39g2000yqn.googlegroups.com>

On Mar 23, 1:01 pm, ciapecki <ciape..._at_gmail.com> wrote:
> I am wondering why following returns
> PLS-00382: expression is of wrong type
>
> I declares in package specification
>
> TYPE index_record is record (
>     index_type varchar2(2) := 'BT',
>     index_name varchar2(27) := '');
>
> type assoc_array_index_recs is table of index_record index by
> PLS_INTEGER;
>
> In my procedure I declared:
> ind_h assoc_array_index_recs;
>
> in procedure body:
> ind_h := mock_hash();
>
> where mock_hash() is a function returning assoc_array_index_recs type:
> function mock_hash return assoc_array_index_recs
> is
>     new_mock assoc_array_index_recs;
>     new_rec index_record;
> begin
>
>     new_rec.index_name := 'abc';
>     new_rec.index_type := 'aa';
>     new_mock(1) := new_rec;
>     new_rec.index_name := 'cde';
>     new_rec.index_type := 'bb';
>     new_mock(2) := new_rec;
>
>     return new_mock;
> end;
>
> When I try to compile I get the error message from the beginning of
> this post.
>
> When I replace the
> ind_h := mock_hash(); -- in my procedure with:
>
> new_rec.index_name := 'abc';
> new_rec.index_type := 'aa';
> new_mock(1) := new_rec;
> new_rec.index_name := 'cde';
> new_rec.index_type := 'bb';
> new_mock(2) := new_rec;
>
> ind_h := new_mock;
>
>  after declaring new_rec and new_mock
>  everything works fine
>
> Does it mean my function mock_hash() cannot return the
> assoc_array_index_recs type?
>
> is there any other array type that can be returned by a function?
>
> thanks,
> chris

Oracle version (up to, and including, the fourth digit?) The following compiles and runs just fine on my 9.2.0.8:

create or replace package test_rec as

  type index_record is record(

     index_type varchar2(2) := 'BT'
    ,index_name varchar2(27) := null);

  type assoc_array_index_recs is table of index_record index by pls_integer;

  procedure test;

  function mock_hash return assoc_array_index_recs;

end test_rec;
/

create or replace package body test_rec as

  procedure test is
    ind assoc_array_index_recs;
  begin
    ind := mock_hash();
    for i in ind.first .. ind.last loop
      dbms_output.put_line(ind(i).index_type || ' - ' || ind (i).index_name);

    end loop;
  end test;

  function mock_hash return assoc_array_index_recs is     new_rec index_record;
    new_mock assoc_array_index_recs;
  begin

    new_rec.index_name := 'abc';
    new_rec.index_type := 'aa';
    new_mock(1) := new_rec;
    new_rec.index_name := 'cde';
    new_rec.index_type := 'bb';
    new_mock(2) := new_rec;

    return new_mock;
  end mock_hash;

end test_rec;
/

Try the above and if it works compare to your own code and find where yours differ. If it doesn't - check your Oracle version and upgrade if necessary. And in the future please post full code that does not compile so that we could try it and reproduce your issue if it's not obvious, not just the fragments you think are relevant.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Mon Mar 23 2009 - 09:57:10 CDT