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)
