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));
>Solution :
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>