Home » SQL & PL/SQL » SQL & PL/SQL » Problem with ELSE SQL%NOTFOUND THEN !!! (plsql, oracle 9)
Problem with ELSE SQL%NOTFOUND THEN !!! [message #331384] Thu, 03 July 2008 03:58 Go to next message
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 #331389 is a reply to message #331384] Thu, 03 July 2008 04:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ELSE does not take a condtion as an argument.
The whole purpose of ELSE is to catch all other (non-mentioned) cases.
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331397 is a reply to message #331384] Thu, 03 July 2008 04:17 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Haven't you heard about ELSIF??
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331413 is a reply to message #331397] Thu, 03 July 2008 04:44 Go to previous messageGo to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Yes, i also tried it before i submit topic
IF SQL%FOUND THEN 
    RETURN 'FOUND';
ELSIF SQL%NOTFOUND THEN 
    RETURN 'NOTFOUND';
   
END IF;


Not working Sad

[Updated on: Thu, 03 July 2008 04:45]

Report message to a moderator

Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331417 is a reply to message #331413] Thu, 03 July 2008 04:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please define "Not working"

Note that a 'select into' for a query returning no rows will raise a NO_DATA_FOUND exception. It will NOT reach the if sql%notfound part.
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331423 is a reply to message #331417] Thu, 03 July 2008 05:08 Go to previous messageGo to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Ok, sorry, not working mean return NULL, it wolud return "NOTFOUND"

Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331424 is a reply to message #331423] Thu, 03 July 2008 05:16 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Please read the second line of the text that Frank posted. You will get hint.
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331425 is a reply to message #331384] Thu, 03 July 2008 05:17 Go to previous messageGo to next message
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 #331429 is a reply to message #331425] Thu, 03 July 2008 05:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I bet the calling package had an Exception handler that did something like 'WHEN OTHERS THEN NULL;' or 'WHEN OTHERS THEN dbms_output.put_line();', and didn't re-raise the exception.
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331508 is a reply to message #331384] Thu, 03 July 2008 12:54 Go to previous messageGo to next message
mandeepmandy
Messages: 79
Registered: May 2008
Location: USA
Member

You should use EXCEPTION handler rather than IF SQL%NOTFOUND because its a SELECT statement and it will through NO_DATA_FOUND instead of SQL%NOTFOUND.
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331518 is a reply to message #331508] Thu, 03 July 2008 13:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please read the complete thread before replying.
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331521 is a reply to message #331518] Thu, 03 July 2008 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This guy is an expert in wrong or useless post.
Actually I don't think I saw one post from him that is useful.

Regards
Michel
Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331526 is a reply to message #331384] Thu, 03 July 2008 14:58 Go to previous messageGo to next message
mandeepmandy
Messages: 79
Registered: May 2008
Location: USA
Member

Isn't this usefull Michel?
http://www.orafaq.com/forum/m/330066/123968/#msg_330066

http://www.orafaq.com/forum/m/324940/123968/#msg_324940

[Updated on: Thu, 03 July 2008 15:10]

Report message to a moderator

Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331527 is a reply to message #331526] Thu, 03 July 2008 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Problem with ELSE SQL%NOTFOUND THEN !!! [message #331528 is a reply to message #331526] Thu, 03 July 2008 15:16 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reported Messages
mandeepmandy On: Thu, 03 July 2008 22:11 In: SQL & PL/SQL SQL & PL/SQL Problem with ELSE SQL%NOTFOUND THEN !!!
Reason Michel Should not write like that.I posted some good replies.How can he say that i am posting wrong

I just say what I saw.

http://www.orafaq.com/forum/m/331510/102589/#msg_331510
http://www.orafaq.com/forum/m/331509/102589/#msg_331509
http://www.orafaq.com/forum/m/331508/102589/#msg_331508
http://www.orafaq.com/forum/m/330676/102589/#msg_330676
http://www.orafaq.com/forum/m/323195/102589/#msg_323195
http://www.orafaq.com/forum/m/322409/102589/#msg_322409

Regards
Michel

[Updated on: Thu, 03 July 2008 15:16]

Report message to a moderator

Previous Topic: Error checking in procedure
Next Topic: Checking Time in and out vs others
Goto Forum:
  


Current Time: Sat Dec 03 08:12:17 CST 2016

Total time taken to generate the page: 0.19637 seconds