CREATE OR REPLACE PROCEDURE cursor_test_1 (colname IN varchar2, tab_name IN varchar2)
IS
test_cur sys_refcursor;
v_value varchar2(32);
sql_stmt VARCHAR2(1000);
BEGIN
 sql_stmt := 'SELECT  '||colname||' FROM '||UPPER(tab_name);
OPEN test_cur FOR sql_stmt;
    LOOP
        FETCH test_cur INTO v_value;
    EXIT when test_cur%notfound;
    DBMS_OUTPUT.PUT_LINE(v_value);
    END LOOP;
   CLOSE test_cur;
END;

PACKAGE TYPES as
type cursor_type is ref cursor;
end types;

PROCEDURE CURSOR_TEST_1 (colname IN varchar2, tab_name IN varchar2)
IS
test_cur types.cursor_type;
v_value varchar2(32);
sql_stmt VARCHAR2(1000);
BEGIN
 sql_stmt := 'SELECT  '||colname||' FROM '||UPPER(tab_name);
OPEN test_cur FOR sql_stmt;
    LOOP
        FETCH test_cur INTO v_value;
    EXIT when test_cur%notfound;
     DBMS_OUTPUT.PUT_LINE(v_value);
    END LOOP;
CLOSE test_cur;
END;

CREATE OR REPLACE 
PROCEDURE cursor_test_1 (colname IN varchar2, tab_name IN varchar2,
emp_code In varchar2,tr_type in varchar2,tr_drcr in varchar2
,tr_date IN varchar2
) is
test_cur types.cursor_type;
v_value varchar2(60);
sql_stmt VARCHAR2(1000);
L_yearob number;
L_trdate date;
l_trtype varchar2(2);
l_drcr   varchar2(1);

BEGIN
 sql_stmt := 'SELECT ' ||colname||','||emp_code||
 ','||tr_type||','||tr_drcr||
 ','||tr_date||
 ' FROM '||UPPER(tab_name);--||'  where empcode='|| emp_code;
OPEN test_cur FOR sql_stmt;
    LOOP
 
        FETCH test_cur INTO L_yearob,v_value,l_trtype,l_drcr
        ,l_trdate
        ;
        IF l_trtype='L' then
           l_trtype:='OB';
           l_drcr:=NULL;
        END IF;   
    insert into afpfslip (amount,empcode,transactiontype,debitcredit
    ,transactiondate
    )  values (L_yearob,v_value,l_trtype,l_drcr
    ,L_trdate
    );

    EXIT when test_cur%notfound;
      DBMS_OUTPUT.PUT_LINE(L_yearob);
    END LOOP;
    commit;
CLOSE test_cur;
END;
/