select query call a function which return varray in Oracle

Question

I want to create a function which return VArray and populate the values in select query.

My VArray is:

create  type array_squad_t is varray(2) of VARCHAR2(200);

My function is

CREATE OR REPLACE FUNCTION test_varray(
  id number
  )
    RETURN array_squad_t
IS
    v_array array_squad_t;
BEGIN
  v_array(1) := '1 Val';
  v_array(2) := '2 Val';
    RETURN v_array;
END;

I want to call it like

SELECT column_value1, column_value2, e.Name FROM employees e, TABLE ( test_varray(1) );

Expected Result:

column_value1 | column_value2 | Name
1 Val         | 2 Val         | SomeName

score 1 · Answer 1 · answered Dec 06 '17 at 19:03

1

Firstly, you must initialize your collection appropriately with a constructor array_squad_t().

CREATE OR REPLACE FUNCTION test_varray(
  id number
  )
    RETURN array_squad_t
IS
    v_array array_squad_t := array_squad_t() ;
BEGIN
  v_array.extend(2);
  v_array(1) := '1 Val';
  v_array(2) := '2 Val';
    RETURN v_array;
END;

You can then use the TABLE function and column_value to fetch from the varray in a select statement like this.

SELECT
    column_value,
    e.*
FROM
    employees e,
    TABLE ( test_varray(1) );

answered Dec 06 '17 at 19:03

Kaushik Nayak

30,772
5
32
45

It worked for me, thank you very much. but we are getting data in 1 column 2 rows (as array size is 2), can we get data in 2 separate columns. – Pramendra Raghuwanshi Dec 07 '17 at 11:17
@PramendraRaghuwanshi : Could you please elaborate? or better update the question with expected result. – Kaushik Nayak Dec 07 '17 at 11:25

select query call a function which return varray in Oracle

1 Answers1