return a varray from a function oracle

Question

I want to return a varray from oracle function but I don't know how to do it. Here is so far what I have tried.

set serveroutput on;
declare
   type array_t is varray(2) of number;
CREATE OR REPLACE FUNCTION func() return array_t
begin
  array array_t :=array_t(0,0);   
  array(1):=3;
  array(2):=20;
  return array;
end;

Answer 1

hier is the right syntax for your function

 create  type array_t is varray(2) of number;
 /

 CREATE OR REPLACE FUNCTION func return array_t
 IS
  v_array  array_t;
begin
  v_array  :=array_t(0,0);   
  v_array(1):=3;
  v_array(2):=20;
  return v_array;
end;
/

you can call the function for example in an anonymous plsql block

declare
  v_func_result array_t;
begin

 v_func_result := func();

 dbms_output.put_line(v_func_result(1));
 dbms_output.put_line(v_func_result(2));
 end;
/

Answer 2

You do not need to declare a procedure in the SQL scope; you can declare it as a nested sub-program in an anonymous PL/SQL block:

SET SERVEROUTPUT ON;

DECLARE
  TYPE array_t IS VARRAY(2) OF NUMBER;

  data array_t;

  FUNCTION func RETURN array_t IS BEGIN RETURN array_t( 3, 20 ); END func;
BEGIN
  data := func();
  DBMS_OUTPUT.PUT_LINE( '(' || data(1) || ', ' || data(2) || ')' );
END;
/