Oracle Anonimous Block using cursor

oracle_cursor.sql

/**
 * Allow server to debug information
 */
SET SERVEROUTPUT ON;
DECLARE
    crCursor SYS_REFCURSOR;

    res VARCHAR2(200);
BEGIN

    OPEN crCursor FOR
        SELECT USERENV('LANGUAGE') "Language" FROM DUAL;

  LOOP
      FETCH crCursor INTO res;
      EXIT WHEN crCursor%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(' res: ' || res);
  END LOOP;
  CLOSE crCursor;
--rollback;
END;

/**
 * Create a record
 */
SET SERVEROUTPUT ON;
BEGIN
    DECLARE
        TYPE rec IS RECORD (
            id_uf uf.id_uf%TYPE,
            uf uf.uf%TYPE,
            nome uf.nome%TYPE,
            label varchar2(100),
            id_region uf.id_regiao%TYPE);

        rRecord rec;

        cCursor PKG_UTIL.cRefCursor;
        status varchar2(100);
    BEGIN

        PKG_DELIVERY.GetListStates(cCursor, status);

        LOOP
            FETCH cCursor INTO rRecord;
            EXIT WHEN cCursor%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE(rRecord.id_uf
                                ||','||rRecord.uf
                                ||','||rRecord.nome
                                ||','||rRecord.label
                                ||','||rRecord.id_region);
        END LOOP;

        CLOSE cCursor;
    END;
END;

/**
 * Create a record and a list of records to work after fetch
 */
SET SERVEROUTPUT ON;
BEGIN
    DECLARE
        TYPE rec IS RECORD (
            id_uf uf.id_uf%TYPE,
            uf uf.uf%TYPE,
            nome uf.nome%TYPE,
            label varchar2(100),
            id_region uf.id_regiao%TYPE);

        TYPE typeRec IS TABLE OF rec INDEX BY PLS_INTEGER;

        tTable typeRec;
        iIndex PLS_INTEGER := 1;

        cCursor PKG_UTIL.cRefCursor;
        status varchar2(100);
    BEGIN

        PKG_DELIVERY.GetListStates(cCursor, status);

        LOOP
            FETCH cCursor INTO tTable(iIndex);
            EXIT WHEN cCursor%NOTFOUND;
            iIndex := iIndex + 1;
        END LOOP;

        CLOSE cCursor;

        FOR x IN 1..tTable.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(tTable(x).id_uf
                                ||','||tTable(x).uf
                                ||','||tTable(x).nome
                                ||','||tTable(x).label
                                ||','||tTable(x).id_region);
        END LOOP;
    END;
END;

/**
 * Create six lists and fetch into them over 100 records
 */
SET SERVEROUTPUT ON;
BEGIN
    DECLARE
        lIdUf  dbms_sql.NUMBER_table;
        lUf  dbms_sql.VARCHAR2_table;
        lNome dbms_sql.VARCHAR2_table;
        lLabel dbms_sql.VARCHAR2_table;
        lIdRegion  dbms_sql.NUMBER_table;

        cCursor PKG_UTIL.cRefCursor;
        status varchar2(100);
    BEGIN

        PKG_DELIVERY.GetListStates(cCursor, status);

        LOOP
            FETCH cCursor BULK COLLECT INTO lIdUf, lUf, lNome, lLabel, lIdRegion LIMIT 100;
            EXIT WHEN cCursor%NOTFOUND;
        END LOOP;

        CLOSE cCursor;

        FOR x IN 1..lIdUf.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(lIdUf(x)
                                ||','||lUf(x)
                                ||','||lNome(x)
                                ||','||lLabel(x)
                                ||','||lIdRegion(x));
        END LOOP;
    END;
END;