[GENERAL] temporary table problem

[sanjeev kumar]

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;
uSickbigint:=0;
uEarned  bigint:=0;
aCasual  bigint:=6;
aSickbigint:=8;
aEarned  bigint:=15;
lCasual  bigint:=0;
lSickbigint:=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)

[GENERAL] getting no days problem

[sanjeev kumar]

Hi all,

I have written procedure as follows:
-
--procedure begin
Create Procedure sp_getNoOfDays(
 ip_startDateIN date,
 ip_endDate  IN date,
 op_noofdays OUT bigint
)
is
v_DOBMonthnumber(20);
v_currMonth   number(20);
v_absMonthsDiff   number(20);
cnt_noOfDays  bigint;
Begin
dbms_output.put_line('1 '||ip_startDate);
dbms_output.put_line('2 '||ip_endDate);
 v_DOBMonth:=to_char(ip_startDate,'MM');
dbms_output.put_line('3 ');
  v_currMonth:=to_char(ip_endDate,'MM');
dbms_output.put_line('4 ');
 v_absMonthsDiff:=abs(to_number(v_currMonth-v_DOBMonth));
dbms_output.put_line('5 '||v_absMonthsDiff);

if v_absMonthsDiff<1
then
dbms_output.put_line('6 ');
select extract(day from ip_endDate)-extract(day from ip_startDate)
into   cnt_noOfDays
from   dual;
dbms_output.put_line('7 ');
else
select ip_endDate-ip_startDate
into cnt_noOfDays
from dual;
--cnt_noOfDays:=0;
end if;

op_noofdays:=cnt_noOfDays;
dbms_output.put_line('8 ');
Exception
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Message : '||SQLERRM||'Error Code :
'||SQLCODE);

End;
--procedure end
-
--Here I am executing

declare
opn bigint;
begin
sp_getNoOfDays('02-Feb-2009','29-Jan-2009',opn);
dbms_output.put_line('No of Days is =>'||opn);
end;
--
--I am not getting required results but getting error as follows:

INFO:  1 02-FEB-09 00:00:00
INFO:  2 29-JAN-09 00:00:00
INFO:  3
INFO:  4
INFO:  5 1
INFO:  Error Message : EDB-22P02: invalid input syntax for integer: "@ 4
days ago"Error Code : 22P02
INFO:
-
Please any one can help me

-- 
Thanks & Regards,
-Sanjeev (MIT)