SQL & PL/SQL

    dimension_id    number,

    label_en        varchar2(300)

);

CREATE TYPE dim_T AS TABLE OF dim_O;

DECLARE

    dims_t              dim_T;

BEGIN

    SELECT  CAST(MULTISET(

                    SELECT  DIMENSION_ID, LABEL_EN

                    FROM    DIMENSIONTABLE    --  actual physical table in oracle

                    WHERE   DIMENSION_ID IN (3001,3002,3003)

                    ) AS dim_T) "dim_rec"

    INTO    dims_t

    FROM    DUAL;   

    FOR i IN dims_t.FIRST..dims_t.LAST LOOP

        DBMS_OUTPUT.PUT_LINE(dims_t(i).dimension_id);

    END LOOP;

--    output from above loop is

--    3001

--    3002

--    3003

--    The following statement fails : cannot access rows from a non-nested table item

    UPDATE TABLE( SELECT dimension_id FROM TABLE(dims_t) )

    SET dimension_id = 3004 WHERE dimension_id = 3003;

--    The following statement fails : cannot access rows from a non-nested table item

  UPDATE TABLE( SELECT dimension_id FROM TABLE( CAST(dims_t as dim_T) ) )

    SET dimension_id = 3004 WHERE dimension_id = 3003;

END;