1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

throw no_data_found exception when MAX returns NULL

Discussion in 'SQL PL/SQL' started by hiswapna, Sep 24, 2009.

  1. hiswapna

    hiswapna Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    FUNCTION RETRIEVE_EMP_ID(emp_cd_in IN VARCHAR2) RETURN NUMBER AS
    v_emp_id NUMBER;
    BEGIN
    IF emp_cd_in IS NULL THEN
    RETURN NULL;
    END IF;
    SELECT MAX(emp_id) INTO v_emp_id from emp
    where upper(emp_cd)=upper(emp_cd_in) ;

    RETURN v_emp_id;
    EXCEPTION WHEN NO_DATA_FOUND THEN
    BEGIN
    SELECT emp_SEQ.nextval into v_emp_id from dual;
    INSERT INTO emp (emp_id , emp_cd) VALUES(v_emp_id,emp_cd_in);
    RETURN v_emp_id;

    WHEN OTHERS THEN
    RETURN NULL;
    END;
    END;



    This MAX() function will return NULL if the select fails and the emp_id is returned as NULL
    But I would like to insert into emp table and return the new emp_id.

    How do I modify the function to acheive this
     
  2. Sikkandar.S.P

    Sikkandar.S.P Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Chennai
    You would need to add a Group by Clause in your max query!

    Code (Text):
    SQL>CREATE OR REPLACE FUNCTION RETRIEVE_EMP_ID
      2       (EMP_CD_IN  IN VARCHAR2)
      3  RETURN NUMBER
      4  AS
      5    V_EMP_ID   NUMBER;
      6    V_EMP_ID2  NUMBER;
      7  BEGIN
      8    IF EMP_CD_IN IS NULL THEN
      9      RETURN NULL;
     10    END IF;
     11    
     12    SELECT   MAX(EMP_ID)
     13    INTO     V_EMP_ID
     14    FROM     EMP
     15    WHERE    UPPER(EMP_CD) = UPPER(EMP_CD_IN)
     16    GROUP BY EMP_ID;
     17    
     18    RETURN V_EMP_ID;
     19  EXCEPTION
     20    WHEN NO_DATA_FOUND THEN
     21      BEGIN
     22        SELECT EMP_SEQ.NEXTVAL
     23        INTO   V_EMP_ID2
     24        FROM   DUAL;
     25        
     26        INSERT INTO EMP
     27                   (EMP_ID,
     28                    EMP_CD)
     29        VALUES     (V_EMP_ID2,
     30                    EMP_CD_IN);
     31        
     32        RETURN V_EMP_ID2;
     33      END;
     34  END;
     35  /

    Function created.

    SQL>SELECT * FROM EMP
      2  /

    no rows selected

    SQL>VAR V NUMBER;

    SQL>EXEC :V := RETRIEVE_EMP_ID('Y');

    PL/SQL procedure successfully completed.

    SQL>SELECT * FROM EMP
      2  /

        EMP_ID EMP_CD
    ---------- ----------
            21 Y

    SQL>PRINT V

             V
    ----------
            21

    SQL>EXEC :V := RETRIEVE_EMP_ID('Y');

    PL/SQL procedure successfully completed.

    SQL>SELECT * FROM EMP
      2  /

        EMP_ID EMP_CD
    ---------- ----------
            21 Y

    SQL>PRINT V

             V
    ----------
            21