Hi I have a stored oracle procedure - which has a select query which is , returning the data to OUT SYS_REFCURSOR. This procedure is called by Java programs and hence OUT SYS_REFCURSOR is returned to Java application. This was working fine for years. BUT I recently modified the stored proc code as I want to see what values are getting returned to Java application. So I fetched the OUT SYS_REFCURSOR values into record type variable. And this small change is failing this program . When I run my proc on DB using TOAD , it runs fine. It does not give any error but it is giving error to Java application. Please advise - what is the issue here
------------------------------------------------------------------------------------------------------
Eg my code is something like this
PROCEDURE UPRFETCH( IN_BOOKDATE IN VARCHAR2, OUT_CURSOR OUT SYS_REFCURSOR)
IS
V_rec R_CRUISE_PROMO_PREBOOK_TYPE; --- type is defined in spec
v_cnt number :=0;
BEGIN
OPEN OUT_CURSOR FOR
SELECT
DISTINCT A.LINENUMBER,
A.PROMOTYPECODE,
A.CHARTEREDSCHEDULED,
FROM EMP ;
LOOP
FETCH OUT_CURSOR
INTO v_rec;
EXIT WHEN OUT_CURSOR%NOTFOUND;
v_cnt := v_cnt + 1;
dbms_output.put_line('OUT_CURSOR Record no -'||v_cnt);
dbms_output.put_line('LINENUMBER '||v_rec.LINENUMBER );
dbms_output.put_line('PROMOTYPECODE '||v_rec.PROMOTYPECODE );
dbms_output.put_line('PRICE '||v_rec.PRICE );
dbms_output.put_line('CHARTEREDSCHEDULED '||v_rec.CHARTEREDSCHEDULED );
END LOOP;
CLOSE OUT_CURSOR;
EXCEPTION
WHEN OTHERS
THEN
plsqllog_error('Unhandled Exception'||sqlerrm);
Raise;
END UPRFETCH;