Table not updated when value not set

lionelcyril

I have created a SP and using Oracle 11g. I have declared a variable in a procedure as:

v_juriscode billingcycle.juriscode%TYPE;

(an existing type) . . . I have a condition as

IF n_count > 1
THEN
v_juriscode := 'LODESTAR';
ELSE
IF n_count = 1
THEN
SELECT pm.dejuriscode
INTO v_juriscode
FROM pwrline.lsmdphysicalmeter pm
WHERE pm.decommdevid = V_COMMDEVID;
END IF;
END IF;
UPDATE DEBADGERPROGLOG SET JURISCODE=v_juriscode
WHERE UIDBADGERPROGLOG = V_UID;

When the condition c_count = 0 is encountered I have not set the value for juriscode (ie i hv not mentioned that condition). I have noticed that when no value is set to the v_juriscode variable the table row is not updated, I wanted to know why this happens. Thanks in advance.

Lionel Noronha

Md Marufuzzaman

IF n_count > 1
THEN
v_juriscode := 'LODESTAR';
ELSE
IF n_count = 1
THEN
SELECT pm.dejuriscode
INTO v_juriscode
FROM pwrline.lsmdphysicalmeter pm
WHERE pm.decommdevid = V_COMMDEVID;
END IF;
END IF;

IF v\_juriscode IS NOT NULL THEN  
UPDATE DEBADGERPROGLOG SET JURISCODE=v\_juriscode 
            WHERE UIDBADGERPROGLOG = V\_UID;
    END IF;

Thanks Md. Marufuzzaman

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

jschell

lionelcyril wrote:

When the condition c_count = 0 is encountered I have not set the value for juriscode (ie i hv not mentioned that condition).
I have noticed that when no value is set to the v_juriscode variable the table row is not updated, I wanted to know why this happens.

Precluding the previous answer that statement is contradictory. If the update runs, the where condition is satisfied and no error occurs then an update did happen. There is no alternative. If an update occurs then a value will be updated. There is no such thing a "no value" unless you mean null (presumably you don't mean spaces.) If you didn't set a value then null is used. If you don't want an update if there is no value then use the code from the other response. If you want an update with a different value then set v_juriscode to a default value before you execute the rest of the code.