Oracle cursor Program

Oracle CURSOR program declare cursor EMP_CURSOR is select FIRST_NAME from employees; -- TYPE NAME_ARRAY_TYPE IS varchar (5) OF VARCHAR2 (10); TYPE NAME_ARRAY_TYPE is varray (5) OF VARCHAR2 (10 ); NAME_ARRAY NAME_ARRAY_TYPE; rows int: = 2; V_COUNT INT: = 0; begin if not EMP_CURSOR % ISOPEN -- check whether the cursor opens then open emp_cursor; end if; -- OPEN EMP_CURSOR; loop fetch EMP_CURSOR bulk collect into NAME_ARRAY limit rows; -- If limit rows is not added, extract all data from cursor DBMS_OUTPUT.PUT_LINE ('############' | EMP_CURSOR % ROWCOUNT ); -- The total number of cursor retrieved each time DBMS_OUTPUT.PUT_LINE ('***********************'); for I IN 1 .. (EMP_CURSOR % ROWCOUNT-V_COUNT) LOOP -- remove the processed cursor data DBMS_OUTPUT.PUT_LINE (NAME_ARRAY (I); END LOOP; DBMS_OUTPUT.PUT_LINE ('&'); V_COUNT: = EMP_CURSOR % ROWCOUNT; exit when EMP_CURSOR % NOTFOUND; end loop; CLOSE EMP_CURSOR; END; the following IS a simple cursor processing data: declare cursor EMP_CURSOR is select FIRST_NAME, LAST_NAME from employees; V_FIRSTNAME EMPLOYEES. FIRST_NAME % TYPE; V_LASTNAME EMPLOYEES. LAST_NAME % TYPE; begin open EMP_CURSOR; loop fetch EMP_CURSOR INTO V_FIRSTNAME, V_LASTNAME; exit when EMP_CURSOR % NOTFOUND; values (V_FIRSTNAME | ':' | V_LASTNAME); end loop; CLOSE EMP_CURSOR; END;