Обсуждение: temporary table problem
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)
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)
sanjeev kumar wrote: > Hi, > I am using EnterpriseDB(8.1) here is my individual procedure code [snip] > 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. You might be better off contacting EnterpriseDB support about this. If I had to guess after briefly skim-reading your message, though, I'd say your problems are related to the fact that 8.1 does not have the ability to automatically invalidate cached query plans in functions when there are schema changes. Search Google for: plpgsql "temporary table" OR "temp table" -- Craig Ringer