how to handle 'no data found' in function??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: how to handle 'no data found' in function??

  1. #1
    Join Date
    Oct 2001
    Posts
    7

    Angry

    Please help! I cannot figure out how to handle 'no data found' in this function. This is called from a procedure and the intent is to check to see if this user name exists in the database before continuing with the procedure. I pass in the user name and if they do exist, I exit the procedure (from which this function is called.) If the user does NOT exist, I want to continue with the calling procedure and enter some information into the database. It seems to be working properly if the user DOES exist... the procedure is exited. If the user does NOT exist, I get an 'ORA-01403: no data found' error and the procedure does not complete. Here is the problem function:

    CREATE OR REPLACE FUNCTION USERNAMEEXISTS
    (ai_user_name password_hist.user_name%TYPE)
    RETURN number
    IS

    v_passwd_id password_hist.passwd_id%TYPE;
    v_return_value number;

    BEGIN
    SELECT passwd_id into v_passwd_id
    FROM password_hist
    WHERE UPPER(user_name) = UPPER(ai_user_name);

    IF v_passwd_id is NULL
    THEN v_return_value := 0;
    ELSE
    v_return_value := 1;
    END IF;

    RETURN v_return_value;

    END UserNameExists;

    Any suggestions? Thank you for any help!!

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Use exceptions.

    CREATE OR REPLACE FUNCTION USERNAMEEXISTS
    (ai_user_name password_hist.user_name%TYPE)
    RETURN number
    IS

    v_passwd_id password_hist.passwd_id%TYPE;
    v_return_value number;

    BEGIN
    SELECT passwd_id into v_passwd_id
    FROM password_hist
    WHERE UPPER(user_name) = UPPER(ai_user_name);

    IF v_passwd_id is NULL
    THEN v_return_value := 0;
    ELSE
    v_return_value := 1;
    END IF;

    RETURN v_return_value;

    EXCEPTION
    when NO_DATA_FOUND then return null;

    END UserNameExists;

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    just a little adjustment ...

    You probably don't want the return value to be three-state (0,1,null).
    Assuming passw_id is a NOT NULL column you can change the function in this way:
    Code:
    CREATE OR REPLACE FUNCTION USERNAMEEXISTS 
    (ai_user_name password_hist.user_name%TYPE) 
    RETURN number 
    IS 
    v_passwd_id password_hist.passwd_id%TYPE; 
    BEGIN 
      SELECT passwd_id into v_passwd_id 
        FROM password_hist 
       WHERE UPPER(user_name) = UPPER(ai_user_name); 
    
      RETURN 1; 
    
    EXCEPTION 
      when NO_DATA_FOUND then return 0; 
    
    END UserNameExists;

  4. #4
    Join Date
    Oct 2001
    Posts
    7
    that worked! thank you so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width