Hi Everyone,
My DB version is
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Please do have a look at the procedure, and my question is how can I display the results of ref cursor here when I'm invoking it from an anonymous block. Please suggest something.
CREATE OR REPLACE
PROCEDURE MODIFY_ANNUAL_CTC_SP(EMP_REC OUT SYS_REFCURSOR)
AS
VN_DEPTNO EMP.DEPTNO%TYPE := 30;
VN_EMP_REC SYS_REFCURSOR;
VN_MODIFIED_SAL EMP.SAL%TYPE;
VN_EXISTING_CTC NUMBER := 0;
VN_MODIFIED_CTC NUMBER := 0;
BEGIN
FOR I IN (SELECT * FROM EMP WHERE DEPTNO = 30) LOOP
VN_EXISTING_CTC := I.SAL*12;
VN_MODIFIED_CTC := (VN_EXISTING_CTC + VN_EXISTING_CTC * 0.15);
VN_MODIFIED_SAL := VN_MODIFIED_CTC /12;
-- DBMS_OUTPUT.PUT_LINE('EmpNo:'|| I.EMPNO||'Existing_Sal:'||I.SAL||' Existing_ctc:'||VN_EXISTING_CTC ||' Modified_Sal:'|| VN_MODIFIED_SAL|| ' Modified_ctc:'|| VN_MODIFIED_CTC);
UPDATE EMP SET SAL = VN_MODIFIED_SAL WHERE EMPNO = I.EMPNO;
END LOOP;
OPEN EMP_REC FOR
SELECT EMPNO, SAL, SAL*12 AS ANNUAL_SAL FROM EMP WHERE DEPTNO = 30;
--EXCEPTION
-- WHEN OTHERS THEN
-- DBMS_OUTPUT.PUT_LINE('Error_Msg :'||SQLERRM);
END MODIFY_ANNUAL_CTC_SP;
Invoking from anonymous block
DECLARE
TYPE T_REC IS REF CURSOR;
v_rec t_rec;
BEGIN
MODIFY_ANNUAL_CTC_SP(
EMP_REC => V_rec
);
-- Below lines didn't work so commented.
-- FOR I IN 1..V_REC.COUNT LOOP
-- DBMS_OUTPUT.PUT_LINE('EMPNO :'||V_REC(i).empno);
-- end loop;
END;
/
Regards,
BS2012.