테이블 유형을 반환할 때 Oracle 함수의 잘못된 유형

이 함수를 호출하고 싶습니다.

해야한다

restaurants_table my_package.my_type;

대신에

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

나는 당신의 테이블이 없으므로 Scott의 것을 사용했습니다 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.

테스트:

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>

반면에 해당 목적으로 Oracle의 내장 유형을 사용할 수 있습니다 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>