Window 2 World

---------------------------------------------
getting values from a cursor to a table type
--------------------------------------------

-- following things can be defined in package specification

-- define record

TYPE studentDetails IS RECORD(
v_student_id NUMBER(8),
v_student_name VARCHAR2(25));

-- define table [nested table]

TYPE studentDetailsTab IS TABLE OF studentDetails INDEX BY BINARY_INTEGER;


-- followng things will be inside package body and inside a procedure in a declaration section

-- define a cursor


CURSOR cur_student_details(p_class_id NUMBER)
IS
SELECT student_id,
student_name
FROM student_table
WHERE class_id = p_class_id;


-- define a variable of type studentDetailsTab

v_t_studentDetailsTab studentDetailsTab;


-- follwoing things comes in the procedure body
-- open the cursor and fetch the values into the table type

OPEN cur_student_details(v_t_classid);
FETCH cur_student_details BULK COLLECT INTO v_t_studentDetailsTab;
CLOSE cur_student_details;

-- looping throgh the table type

IF v_t_studentDetailsTab.COUNT > 0 THEN
FOR tempIter IN v_t_studentDetailsTab.FIRST..v_t_studentDetailsTab.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('student id ' || v_t_studentDetailsTab(tempIter).v_student_id);
DBMS_OUTPUT.PUT_LINE('student name ' || v_t_studentDetailsTab(tempIter).v_student_name);
END LOOP;
END IF;

--------------------------------------------------
getting values from a SYS_REFCURSOR to table type
--------------------------------------------------

-- cosider there is a function returnting the sys_refcursor in some other package

FUNCTION FNC_STUDENTDETAILS(p_class_id NUMBER,
p_sql_code OUT PLS_INTEGER,
p_sql_errm OUT VARCHAR2) RETURN sys_refcursor IS

v_cur_studentdetails sys_refcursor;
BEGIN
open v_cur_studentdetails FOR
SELECT student_id,
student_name
FROM student_table
WHERE class_id = p_class_id;

return v_cur_studentdetails;

END;
EXCEPTION
WHEN OTHERS THEN
p_sql_code := SQLCODE;
p_sql_errm := SQLERRM;
RETURN v_cur_studentdetails;
END FNC_STUDENTDETAILS;

-- how to use it
-- following things can be defined in package specification

-- define record

TYPE studentDetails IS RECORD(
v_student_id NUMBER(8),
v_student_name VARCHAR2(25));

-- define table [nested table]

TYPE studentDetailsTab IS TABLE OF studentDetails INDEX BY BINARY_INTEGER;

--- define the following in package body , package declaration section

v_t_student_details SYS_REFCURSOR;
v_t_studentDetailsTab studentDetailsTab;

-- call the following in the procdure body

v_t_student_details := .FNC_STUDENTDETAILS(v_t_classid,p_errcd,p_errm);

-- fetch student details
FETCH v_t_student_details BULK COLLECT INTO v_t_studentDetailsTab;
-- we can check
IF v_t_student_details%NOTFOUND THEN
null; -- do some thing
END IF;

# posted by srikanth : 12:00 PM