Re: Function returns a PL/SQL table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 04 Dec 1998 03:59:47 GMT
Message-ID: <366c5d8a.4142636@192.86.155.100>

A copy of this was sent to dp <dp_at_asimba.com> (if that email address didn't require changing) On Thu, 03 Dec 1998 17:42:20 -0800, you wrote:

>Here's the problem. table_1 is a table of integers, and function_table
>is a function that returns the same type as table_1. And I want to
>assign table_1 from the result of function_table, like:
>table_1 := function_table(parameter);
>
>but pl/sql 2.3.4 always complains, something like
>
>Message 6550: ORA-06550: line 16, column 8:
>PLS-00382: expression is of wrong type
>ORA-06550: line 16, column 1:
>PL/SQL: Statement ignored
>
>but if I call it like function_table(parameter)(i), it would work. How
>come?
>
>Thanks
>

Are you using the SAME type in all places. Is table_1 declared with the SAME type (not another type declared elsewhere that might be the same base type, but the same exact type).

Consider the following example:

SQL> select * from v$version
  2 /

BANNER

---

Oracle7 Server Release 7.3.4.0.1 - Production PL/SQL Release 2.3.4.0.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for Solaris: Version 2.3.4.0.0 - Production NLSRTL Version 3.2.4.0.0 - Production

SQL> @test
SQL> create or replace package types
  2 as
  3 type myIntArray is table of number index by binary_integer;   4 end;
  5 /

Package created.

SQL>
SQL> create or replace function get_a_table( x in number ) return types.myIntArray
  2 is
  3 l_tbl types.myIntArray;
  4 begin

  5          for i in 1 .. x loop
  6                  l_tbl(i) := x;
  7          end loop;
  8          return l_tbl;

  9 end;
 10 /

Function created.

SQL> 
SQL> 
SQL> declare
  2          l_another_tbl types.myIntArray;
  3  begin
  4          l_another_tbl := get_a_table(5);

  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> declare

  2          type myIntArray is table of number index by binary_integer;
  3          l_another_tbl myIntArray;
  4  begin
  5          l_another_tbl := get_a_table(5);

  6 end;
  7 /
declare
*
ERROR at line 1:

ORA-06550: line 5, column 19:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 2:

PL/SQL: Statement ignored

see the first block works since it used the SAME exact type as the function. the second fails since the types are different -- myIntArray in the local block is NOT the same as the type returned by the function.  

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 Dec 03 1998 - 21:59:47 CST