@Test
  public void testBulkCollectFetchLoop() throws Throwable {
    String scriptText =
            "DROP TABLE IF EXISTS result;\n" +
            "CREATE TABLE result (s string);\n" +
            "DROP TABLE IF EXISTS emp;\n" +
            "CREATE TABLE emp (name string, age int);\n" +
            "INSERT INTO emp VALUES('e1', 1);\n" +
            "INSERT INTO emp VALUES('e2', 2);\n" +
            "INSERT INTO emp VALUES('e3', 3);\n" +
            "INSERT INTO emp VALUES('e4', 4);\n" +
            "INSERT INTO emp VALUES('e5', 5);\n" +
            "INSERT INTO emp VALUES('e6', 6);\n" +
            "TYPE t_rows IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;\n" +
            "DECLARE batch t_rows;\n" +
            "DECLARE cur SYS_REFCURSOR;\n" +
            "DECLARE s STRING = '';\n" +
            "OPEN cur FOR SELECT * FROM emp;\n" +
            "LOOP\n" +
            "   FETCH cur BULK COLLECT INTO batch LIMIT 2;\n" +
            "   EXIT WHEN batch.COUNT = 0;\n" +
            "   s = s || batch(1).name || '=' || batch(1).age || ' ' || batch(2).name || '=' || batch(2).age || ' ';\n" +
            "END LOOP;\n" +
            "CLOSE cur;\n" +
            "INSERT INTO result VALUES(s);\n" +
            "SELECT * FROM result;\n";
    testScriptFile(scriptText, args(), "e1=1 e2=2 e3=3 e4=4 e5=5 e6=6");
  }
Start up a local Hive Server 2 for these tests
itests/hive-unit/src/test/java/org/apache/hive/beeline/TestHplSqlViaBeeLine.java