Hi, I am using EnterpriseDB(8.1) here is my individual procedure code written as like: ---------------------------------------- Create or replace Procedure sp_leaveSummary( op_viewSummary OUT sys_refcursor, op_errormessage OUT varchar ) is tCasual bigint:=0; tSick bigint:=0; tEarned bigint:=0; uCasual bigint:=0; uSick bigint:=0; uEarned bigint:=0; aCasual bigint:=6; aSick bigint:=8; aEarned bigint:=15; lCasual bigint:=0; lSick bigint:=0; lEarned bigint:=0;
Begin Execute immediate 'Create temporary table viewsummary(legend varchar,casual bigint,sick bigint,earned bigint)'; for i in 1..4 loop if i=1 then insert into viewsummary(legend,casual,sick,earned) values ('Total',tCasual,tSick,tEarned); elsif i=2 then insert into viewsummary(legend,casual,sick,earned) values ('Used',uCasual,uSick,uEarned); elsif i=3 then insert into viewsummary(legend,casual,sick,earned) values ('Available',tCasual-uCasual,tSick-uSick,tEarned-uEarned); elsif i=4 then insert into viewsummary(legend,casual,sick,earned) values ('Loss Of Pay',lCasual,lSick,lEarned); end if; end loop; Open op_viewSummary for select legend,casual,sick,earned from viewsummary; Exception WHEN OTHERS THEN -- DBMS_OUTPUT.PUT_LINE('Error Message : '||SQLERRM||'Error Code : '||SQLCODE); op_errormessage:=('Error Message :'||Sqlerrm); dbms_output.put_line(op_errormessage); End; -------------------------- Here I am executing the procedure as follows: ----------------------------------- declare opr sys_refcursor; legend varchar; casual bigint; sick bigint; earned bigint; opm varchar; begin sp_leaveSummary(41,opr,opm); loop fetch opr into legend,casual,sick,earned; exit when opr%notfound; dbms_output.put_line(legend||' '||casual||' '||sick||' '||earned); dbms_output.put_line(opm); end loop; end; ------------------------------------------- Out put is : --------------- INFO: Total 6 8 15 INFO: INFO: Used 38 12 58 INFO: INFO: Available -32 -4 -43 INFO: INFO: Loss Of Pay 0 0 0 INFO: EDB-SPL Procedure successfully complete ------------------------------------------------------- Now my question is from DB side there's no error, But from UI (java) side while calling the procedure they are getting the null refcursor and as well as "op_errormessage" out parameter getting the error message like viewsummary table is already exists. 1) How to destroy the temporary table. 2) How to return the values to the refcursor with out any errors. Thanks & Regards, -Sanjeev (MIT)