Wrong type in Oracle function when returning table type

user_10354066 0
Viewed 30

I have my package header:

CREATE OR REPLACE PACKAGE my_package is 
    TYPE my_type IS
       TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
    FUNCTION my_func return my_type;
END my_package;

And body with function my_func from which I return collection of restaurants:

CREATE OR REPLACE PACKAGE BODY my_package is
    FUNCTION my_func RETURN my_type IS
        restaurants_table my_type; 
    BEGIN
        select ADRESS
         BULK COLLECT  INTO restaurants_table
        from restaurants 
        FETCH NEXT 3 ROWS ONLY;
        
        RETURN restaurants_table;
    END my_func;
END my_package;

I want to call this function:

declare 
    TYPE my_type IS
       TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
   restaurants_table my_type; 
begin
    restaurants_table := my_package.my_func();
end;

But I get error:

PLS-00382: expression is of wrong type

This happens when I try to assign return value from function to variable:

restaurants_table := my_package.my_func();

How can I call function correctly so I can have return value in my table type variable restaurants_table? Later I want to print it by index:

dbms_output.put_line(restaurants_table(1));
plsql collections oracle sql
1 Answers

I want to call this function:

should be

restaurants_table my_package.my_type;

instead of

TYPE my_type IS
   TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
restaurants_table my_type;

I don't have your table so I used Scott's DEPT:

SQL> CREATE OR REPLACE PACKAGE my_package is
  2      TYPE my_type IS
  3         TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
  4      FUNCTION my_func return my_type;
  5  END my_package;
  6  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY my_package is
  2      FUNCTION my_func RETURN my_type IS
  3          restaurants_table my_type;
  4      BEGIN
  5          select dname
  6           BULK COLLECT  INTO restaurants_table
  7          from dept
  8          FETCH NEXT 3 ROWS ONLY;
  9
 10          RETURN restaurants_table;
 11      END my_func;
 12  END my_package;
 13  /

Package body created.

Testing:

SQL> declare
  2    restaurants_table my_package.my_type;
  3  begin
  4      restaurants_table := my_package.my_func();
  5      for i in 1 .. restaurants_table.count loop
  6        dbms_output.put_line(restaurants_table(i));
  7      end loop;
  8  end;
  9  /
ACCOUNTING
RESEARCH
SALES

PL/SQL procedure successfully completed.

SQL>

On the other hand, you could've used Oracle's built-in type for that purpose - sys.odcivarchar2list:

SQL> CREATE OR REPLACE PACKAGE my_package is
  2      FUNCTION my_func return sys.odcivarchar2list;
  3  END my_package;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY my_package is
  2      FUNCTION my_func RETURN sys.odcivarchar2list IS
  3          restaurants_table sys.odcivarchar2list;
  4      BEGIN
  5          select dname
  6           BULK COLLECT  INTO restaurants_table
  7          from dept
  8          FETCH NEXT 3 ROWS ONLY;
  9
 10          RETURN restaurants_table;
 11      END my_func;
 12  END my_package;
 13  /

Package body created.

SQL> set serveroutput on
SQL> declare
  2     restaurants_table sys.odcivarchar2list;
  3  begin
  4      restaurants_table := my_package.my_func();
  5
  6      for i in 1 .. restaurants_table.count loop
  7        dbms_output.put_line(restaurants_table(i));
  8      end loop;
  9  end;
 10  /
ACCOUNTING
RESEARCH
SALES

PL/SQL procedure successfully completed.

SQL>
user_90979060
Related