SELECT into with varray column : VARRAY Column « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. VARRAY Column

SQL>
SQL>
SQL> CREATE OR REPLACE TYPE numberVarryType AS VARRAY(10)OF NUMBER(10);
  2  /

Type created.

SQL>
SQL> CREATE TABLE address_list (
  2      list_id VARCHAR2(6)PRIMARY KEY,
  3      direct_addresses numberVarryType
  4  );

Table created.

SQL>
SQL>
SQL> DECLARE
  2    v_add_varray address_list.direct_addresses%TYPE;
  3  BEGIN
  4    SELECT direct_addresses INTO v_add_varray FROM address_list WHERE list_id ='OFF101';
  5
  6    FOR idx IN 1..v_add_varray.COUNT LOOP
  7      DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_add_varray(idx)));
  8    END LOOP;
  9  EXCEPTION WHEN OTHERS THEN
 10    DBMS_OUTPUT.PUT_LINE(SQLERRM);
 11  END;
 12  /
ORA-01403: no data found

PL/SQL procedure successfully completed.

SQL>
SQL> drop table address_list;

Table dropped.