Dear Experts,
Greetings!
I need to have a procedure that accepts as input cursor and returns output cursor. Both cursors has same column structure but output cursor has one extra column. To achieve this task i have done some work around and stuck where i am not able to copy pl sql table records into output cursor, please advice me if above task can be achieve in any manner.
Below is the sample code for your reference.
CREATE or replace TYPE EMP_REC AS OBJECT
(
EMPNO NUMBER,
SAL NUMBER,
COMM NUMBER,
TOTAL NUMBER
) ;
CREATE OR REPLACE TYPE EMP_TAB AS TABLE OF EMP_REC;
CREATE OR REPLACE PROCEDURE PRC_INPUTCURSOR (P_DECISION IN SYS_REFCURSOR,P_OUTPUT OUT SYS_REFCURSOR) AS
EMPNO NUMBER;
ROWTYPE1 EMP%ROWTYPE;
V_EMPNO EMP.EMPNO%TYPE;
V_SAL EMP.SAL%TYPE;
V_COMM EMP.COMM%TYPE;
TOTAL NUMBER;
V_EMP_TAB EMP_TAB:=EMP_TAB();
V_EMP_REC EMP_REC;
CNT NUMBER:=1;
BEGIN
--V_EMP_REC:=EMP_REC(NULL,NULL,NULL,NULL);
LOOP
FETCH P_DECISION INTO V_EMPNO,V_SAL,V_COMM,TOTAL;
EXIT WHEN P_DECISION%NOTFOUND;
BEGIN
V_EMP_TAB.EXTEND;
TOTAL:=NVL(V_SAL,0)+NVL(V_COMM,0);
V_EMP_TAB(CNT):=EMP_REC(V_EMPNO,V_SAL,V_COMM,TOTAL);
--DBMS_OUTPUT.PUT_LINE(V_EMP_TAB(CNT).EMPNO);
CNT:=CNT+1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION'||SQLERRM);
END;
END LOOP;
CNT:=1;
for c in v_emp_tab.first..v_emp_tab.last
LOOP
NULL;
DBMS_OUTPUT.PUT_LINE(V_EMP_TAB(CNT).EMPNO||' - Total Salary: '||V_EMP_TAB(CNT).TOTAL);
P_OUTPUT:=P_OUTPUT+V_EMP_TAB(cnt);
CNT:=CNT+1;
END LOOP;
--P_OUTPUT:=P_OUTPUT+V_EMP_TAB;
END;
/
I am running above procedure through anonymous block as follows
DECLARE
C1 SYS_REFCURSOR;
V_EMPNO EMP.EMPNO%TYPE;
V_SAL EMP.SAL%TYPE;
V_COMM EMP.COMM%TYPE;
TOTAL NUMBER;
C2 SYS_REFCURSOR;
BEGIN
OPEN C1 FOR SELECT EMPNO,SAL,COMM,NULL TOTAL FROM EMP WHERE ROWNUM<=2;
PRC_INPUTCURSOR(C1,C2);
END;
Apologies if done any mistake.
Thanks advance for your support.