Problem with ELSE SQL%NOTFOUND THEN !!! [message #331384] |
Thu, 03 July 2008 03:58  |
sammeras
Messages: 28 Registered: September 2007 Location: Israel
|
Junior Member |

|
|
Hello i have strang problem with ELSE SQL%NOTFOUND THEN OR using ELSE SQL%NOTFOUND THEN.
I have function, please see the code:
FUNCTION fn_InsertNewFeatureIntoTrOffer(
v_marketing_offer_id IN NUMBER,
v_feature_code IN NUMBER,
v_feature_value IN VARCHAR2,
v_is_active IN NUMBER,
v_start_date IN DATE,
v_end_date IN DATE
) RETURN VARCHAR2 IS
t_value NUMBER;
l_sqlerr VARCHAR2(190);
t_output VARCHAR2(400):='F';
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT marketing_offer_id INTO t_value FROM tr_offer_features
WHERE marketing_offer_id = v_marketing_offer_id
AND feature_code = v_feature_code
AND rownum <=1;
IF SQL%FOUND THEN
RETURN 'FOUND';
ELSE SQL%NOTFOUND THEN
RETURN 'NOTFOUND';
/* INSERT INTO tr_offer_features (
marketing_offer_id,
feature_code,
feature_value,
is_active,
start_date,
end_date
) VALUES (
v_marketing_offer_id,
v_feature_code,
v_feature_value,
v_is_active,
v_start_date,
v_end_date
);
COMMIT;
RETURN 'T'; */
END IF;
-- RETURN t_output;
END;
When i run script :
SELECT update_tables.fn_InsertNewFeatureIntoTrOffer(502334,100,'WE',1,sysdate,sysdate) return_val FROM dual;
(502334 dosn't exist in table tr_offer_features)
I got Null value' it must return "NOTFOUND".
When i run script :
SELECT update_tables.fn_InsertNewFeatureIntoTrOffer(502333,100,'WE',1,sysdate,sysdate) return_val FROM dual;
(502333 exist in table tr_offer_features)
I got "FOUND" Value.
I tried many solutions as:
IF (t_value > 0 ) THEN,
IF (t_value IS NOT NULL) THEN,
IF (t_value = null) THEN....
Alway's the esle condetion not working. why ??
Thanks.
|
|
|
|
|
|
|
|
|
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331425 is a reply to message #331384] |
Thu, 03 July 2008 05:17   |
sammeras
Messages: 28 Registered: September 2007 Location: Israel
|
Junior Member |

|
|
Working Now, and return:
'F', (false) if SQL%FOUND
'T', (True) if SQL%NOTFOUND
FUNCTION fn_InsertNewFeatureIntoTrOffer(
v_marketing_offer_id IN NUMBER,
v_feature_code IN NUMBER,
v_feature_value IN VARCHAR2,
v_is_active IN NUMBER,
v_start_date IN DATE,
v_end_date IN DATE
) RETURN VARCHAR2 IS
t_value NUMBER;
l_sqlerr VARCHAR2(190);
t_output VARCHAR2(400):='F';
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT marketing_offer_id INTO t_value FROM tr_offer_features
WHERE marketing_offer_id = v_marketing_offer_id
AND feature_code = v_feature_code
AND rownum <=1;
-- Return 'F' if SQL%FOUND
IF SQL%FOUND THEN
RETURN t_output;
END IF;
-- When row not found...insert value
EXCEPTION WHEN NO_DATA_FOUND THEN
BEGIN
INSERT INTO tr_offer_features (
marketing_offer_id,
feature_code,
feature_value,
is_active,
start_date,
end_date
) VALUES (
v_marketing_offer_id,
v_feature_code,
v_feature_value,
v_is_active,
v_start_date,
v_end_date
);
COMMIT;
RETURN 'T';
END;
END;
|
|
|
|
|
|
|
|
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331527 is a reply to message #331526] |
Thu, 03 July 2008 15:11   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I don't read Forms forum but 1 out of 41 is not a good hit ratio and as far I can read it is not an answer to another one's post but a follow-up to one of yours.
Well, thanks for this one, keep all the other ones like this and stop repeating others' answer or posting wrong answers when others have posted correct ones.
Regards
Michel
[Edit: correct typo]
[Updated on: Thu, 03 July 2008 15:18] Report message to a moderator
|
|
|
|