Background information

In Oracle, a Varray can be nested in the definition, that is, an already defined Varray can be referenced in the Varray definition. For example:
declare
    TYPE VAR_TYP IS VARRAY(20) OF NUMBER;
    TYPE VAR_TYP_2 IS VARRAY(10) OF VAR_TYP;
begin
    return '';
END pkg_subtype;

In PolarDB O Edition, the definition of the Varray type is supported, but the syntax of the nested definition is not. In this case, you need to make simple modifications.

New solution

Definition of Vory in PolarDB O Edition:
TYPE varraytype IS { VARRAY | VARYING ARRAY }(maxsize)
  OF { datatype | objtype };
Note The specific type after OF only supports datatype and objtype, varraytype is not supported.

Examples

  • Transform the Vory with nested definitions:
    declare
    TYPE VAR_TYP IS VARRAY(20) OF NUMBER;
    TYPE VAR_TYP_2 IS VARRAY(10) OF NUMBER;
begin
    return '';
END pkg_subtype;
  • DECLARE
    TYPE dname_varray_typ IS VARRAY(4) OF VARCHAR2(14);
    dname_varray    dname_varray_typ;
    CURSOR dept_cur IS SELECT dname FROM dept ORDER BY dname;
    i               INTEGER := 0;
BEGIN
    dname_varray := dname_varray_typ(NULL, NULL, NULL, NULL);
    FOR r_dept IN dept_cur LOOP
        i := i + 1;
        dname_varray(i) := r_dept.dname;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('DNAME');
    DBMS_OUTPUT.PUT_LINE('----------');
    FOR j IN 1..i LOOP
        DBMS_OUTPUT.PUT_LINE(dname_varray(j));
    END LOOP;
END;