bulk collect and record type in oracle example
DECLARE
CURSOR c_cust_cur IS
SELECT ENAME, EMPNO, SAL, DEPTNO FROM EMP; ---smple cursor
TYPE v_cust_cur IS RECORD(
ENAME VARCHAR2(60),
EMPNO VARCHAR2(60),
SAL VARCHAR2(60),
DEPTNO VARCHAR2(60)); ---record type
TYPE cust_cur_tbl IS TABLE OF v_cust_cur INDEX BY BINARY_INTEGER;
v_cust_cur_tbl cust_cur_tbl;
v_data_clob CLOB := EMPTY_CLOB();
v_from VARCHAR2(100);
v_to VARCHAR2(32700);
BEGIN
OPEN c_cust_cur;
FETCH c_cust_cur BULK COLLECT --Bulk collect
INTO v_cust_cur_tbl;
CLOSE c_cust_cur;
BEGIN
/* FOR i IN 1 .. v_cust_cur_tbl.COUNT LOOP
or we ca write also output will be same */
FOR i IN v_cust_cur_tbl.first .. v_cust_cur_tbl.last LOOP
v_data_clob := v_cust_cur_tbl(i).ENAME || '",' || '"' || v_cust_cur_tbl(i)
.EMPNO || '",' || '"' || v_cust_cur_tbl(i).SAL || '",' || '"' || v_cust_cur_tbl(i)
.DEPTNO;
dbms_output.put_line(v_data_clob);
END LOOP;
END;
END;
