Home » SQL & PL/SQL » SQL & PL/SQL » update not happeing
update not happeing [message #206378] Wed, 29 November 2006 17:37 Go to next message
akorah
Messages: 1
Registered: November 2006
Junior Member
Hello

Pelase see a procedure below. The update is not happening,

CREATE PROCEDURE rateupdate AS
cld_area varchar2(24);
rate01 NUMBER(10,4);
countrow integer;
count_area_code integer;
CURSOR ratecur IS
SELECT CLD_AREA,RATE01 FROM rate_dummy;
BEGIN
OPEN ratecur;
LOOP
FETCH ratecur INTO cld_area, rate01;
EXIT WHEN ratecur%NOTFOUND;


select count(*) into countrow from rate_test where ((CLD_AREA = cld_area) or (CLD_AREA = concat('011',cld_area))) and RATE_ID = 12;
IF (countrow > 0) THEN
insert into test_dummy values(cld_area,rate01);
update rate_test set RATE01 = rate01 where ((CLD_AREA = cld_area) or (CLD_AREA = concat('011',cld_area))) and RATE_ID = 12;
ELSIF (countrow = 0) then
select count(*) into count_area_code from call_area_code where CALL_AREA_NUM = cld_area or CALL_AREA_NUM = concat('011',cld_area);
if (count_area_code > 0) then
insert into rate_test(RATE_ID,ACC_AREA,CLG_AREA,CLD_AREA,RATE01,UNIT,EFF_DATE,EXP_DATE,CREATED_BY,CREATION_DATE,LINE_CHG_NATURE) values(12,1,1,cld_area,rate01,60,'11/21/2006','11/01/2010','admin','11/21/2006','2');
elsif (count_area_code = 0) then
insert into area_code_missing values (cld_area,rate01);
END IF;
END IF;
END LOOP;
close ratecur;
COMMIT;
END;
/


But when I hard code a value for rate01, it is updating.
Also the insert, just above the update (first) is working ok.

Please help?
Re: update not happeing [message #206412 is a reply to message #206378] Thu, 30 November 2006 00:06 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

Change the definition of your rate01 variable:

CREATE PROCEDURE rateupdate AS
cld_area varchar2(24);
t_rate01 NUMBER(10,4);
countrow integer;
count_area_code integer;
CURSOR ratecur IS
SELECT CLD_AREA,RATE01 FROM rate_dummy;
BEGIN
-- Change all references to variable rate01 to t_rate01
...
END;

The problem of update command caused by identical names of your
variable and table column. In that case your SQL is actually updating the rate01 column with it's own value disregarding the variable.


Previous Topic: ORA 06502 error
Next Topic: To_CHAR Problem
Goto Forum:
  


Current Time: Sat Dec 10 03:34:30 CST 2016

Total time taken to generate the page: 0.08798 seconds