PL/SQL problem with Updates that specify varchar

[Beelzehubub]

Hi,
I'm having a bit of trouble with an update performed by a PL/SQL
routine that is updating more rows than it should be. Here's some of
the code:

Note that ZENT.APPID is NUMBER and ZENT.UCLS is VARCHAR(12)

OPEN APPAccessCursor ;
LOOP
FETCH APPAccessCursor INTO appid,ucls;
EXIT WHEN APPAccessCursor %NOTFOUND;

UPDATE ZENT SET "ZENT"."ACCLEVE L"='N' WHERE "ZENT"."UCLS"=u cls AND
"ZENT"."APPKEY" =appid;

END LOOP;
CLOSE APPAccessCursor ;

appid and ucls are populated successfully by the fetch but when the
update is performed, all rows matching appid are updated and the value
of ucls is essentially ignored. I've tried =, like, and in for the
ucls portion of the update statement and they all produce the same
result. What am I doing wrong?

[Mark D Powell]

Re: PL/SQL problem with Updates that specify varchar

pat.kelly@sanch ez.com (Beelzehubub) wrote in message news:<df698d61. 0408051102.905f 683@posting.goo gle.com>...

Hi,
I'm having a bit of trouble with an update performed by a PL/SQL
routine that is updating more rows than it should be. Here's some of
the code:
>
Note that ZENT.APPID is NUMBER and ZENT.UCLS is VARCHAR(12)
>
OPEN APPAccessCursor ;
LOOP
FETCH APPAccessCursor INTO appid,ucls;
EXIT WHEN APPAccessCursor %NOTFOUND;
>
UPDATE ZENT SET "ZENT"."ACCLEVE L"='N' WHERE "ZENT"."UCLS"=u cls AND
"ZENT"."APPKEY" =appid;
>
END LOOP;
CLOSE APPAccessCursor ;
>
appid and ucls are populated successfully by the fetch but when the
update is performed, all rows matching appid are updated and the value
of ucls is essentially ignored. I've tried =, like, and in for the
ucls portion of the update statement and they all produce the same
result. What am I doing wrong?

By any chance does the table zent has a column named ucls. If so you
are comparing the column to itself, which always matches. In this
case rename the plsql variable to v_ucls.

That is all I can think of with the code segment provided.
HTH -- Mark D Powell --