how to fetch cursor values into an object

manikandan picture manikandan · Jul 31, 2015 · Viewed 13.1k times · Source

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

Answer

Tharunkumar Reddy picture Tharunkumar Reddy · Jul 31, 2015

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;