I want to fetch values from a cursor and store them in an object.... I tried doing the same with Record i got the output
DECLARE
CURSOR lc_emp_fetch
IS
SELECT emp_no,emp_name FROM maniemp;
TYPE r_emp_record IS RECORD (
eno maniemp.emp_no%TYPE,
ename maniemp.emp_name%TYPE
);
TYPE t_emp IS TABLE OF r_emp_record;
lt_emp_rcd t_emp;
BEGIN
OPEN lc_emp_fetch;
LOOP
FETCH lc_emp_fetch BULK COLLECT INTO lt_emp_rcd LIMIT 5;
EXIT WHEN lt_emp_rcd.COUNT=0;
FOR indx IN 1..lt_emp_rcd.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(lt_emp_rcd(indx).eno||lt_emp_rcd(indx).ename);
END LOOP;
END LOOP;
CLOSE lc_emp_fetch;
END;
/
but when i try doing the same in an object its not working... i surfed all the websites but didn't get proper example program. This is my object:
CREATE OR REPLACE TYPE Typename3 AS OBJECT (
eno number,
ename varchar2(500),
esal number);
SHOW ERRORS;
I am new to this i don't know how to do this can someone help me with this
If you want to try the above example with an object and type then you should create both are at schema level it means
CREATE OR REPLACE type R_EMP_OBJECT as object(
eno number,
ename varchar2(30)
);
and
`create or replace type t_emp IS TABLE OF r_emp_object`;
then
DECLARE
lt_emp_rcd t_emp;
BEGIN
select r_emp_object (emp,ename) bulk collect into lt_emp_rcd
FROM emp;
FOR indx IN 1..lt_emp_rcd.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(lt_emp_rcd(indx).eno||lt_emp_rcd(indx).ename);
END LOOP;
END;
Edit I have tried with cursors, the below code is working fine
DECLARE
CURSOR C1
IS
SELECT emp_no,emp_name FROM maniemp;
C2 C1%ROWTYPE;
LT_EMP_RCD T_EMP;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO C2 ;
SELECT R_EMP_OBJECT(C2.EMP_NO,C2.EMP_NAME) BULK COLLECT INTO LT_EMP_RCD FROM DUAL;
EXIT WHEN C1%NOTFOUND;
FOR INDX IN 1..LT_EMP_RCD.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(LT_EMP_RCD(INDX).ENO||' '||LT_EMP_RCD(INDX).ENAME);
END LOOP;
END LOOP;
CLOSE C1;
END;