locked
No Data Found Exception RRS feed

  • Question

  • User1447854015 posted

    Hey everyone,

    So here's the deal. I have a Procedure I created and it either updates, inserts, or deletes records in a table depending on what the user inputs. I insert when the record doesnt exist, update if it does, and delete it if the user is updating with a blank variable.

    PROCEDURE SP_INSERTORUPDATEPAINTCODE(STR_PAINTCODE IN VARCHAR2, STR_QC IN VARCHAR2, INT_ALTERNATEFLAG IN INTEGER)
    IS
    v_count NUMBER;
    v_PaintID NUMBER;
    
    BEGIN
    
    		SELECT COUNT(*) 
    		INTO v_count 
    		FROM BAY_QC_PAINT 
    		WHERE QC_NUMBER_ID = STR_QC 
    		AND PC_ALTERNATE_FLAG = INT_ALTERNATEFLAG;
    	
    		SELECT PT_ID 
    		INTO v_PaintID
    		FROM BAY_PAINT 
    		WHERE PAINT_CODE = STR_PAINTCODE;
    	EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_PaintID:= NULL;
    
    IF (v_count) = 0 THEN
    
    	IF v_PaintID IS NOT NULL THEN
    		INSERT INTO BAY_QC_PAINT(QC_NUMBER_ID, PT_ID, PC_ALTERNATE_FLAG)
    		VALUES (STR_QC, v_PaintID, INT_ALTERNATEFLAG);
    	END IF;
    
    ELSE
    	IF v_PaintID IS NOT NULL THEN
    		UPDATE BAY_QC_PAINT
    		SET PT_ID = v_PaintID
    		WHERE PC_ALTERNATE_FLAG = INT_ALTERNATEFLAG
    		AND QC_NUMBER_ID = STR_QC;
    	ELSE
    		DELETE FROM BAY_QC_PAINT
    		WHERE QC_NUMBER_ID = STR_QC
    		AND PC_ALTERNATE_FLAG = INT_ALTERNATEFLAG;
    	END IF;
    
    END IF;
    
    END SP_INSERTORUPDATEPAINTCODE;

    When I put in a blank field it deletes it fine. So I dont think thats the issue. Put when I either insert or update it doesnt work. Which makes me think the exception is being initiated all the time. I know for a fact that it is retrieving data because when I run just the Select statements I get data back. I am new to oracle so any help would be much appreciated :)

    Thanks!

    Monday, September 19, 2011 1:20 PM

Answers

  • User269602965 posted

    For starters, I would control the user logic in the application code behind page or form and save some PL/SQL processing time.

    Then I would call SQLl from within the application code behind form as the ODP.NET SQL will always be faster than PL/SQL.

    For decision on UPDATE or INSERT, study the Oracle SQL MERGE clause to execute either in one SQL statement,

    depending on bind variables you put in the SQL statement.

    If the user is updating a BLANK variable, I would capture that in code behind form and not delete anything from the database.

    (unless I am missing the need to delete an existing record)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 19, 2011 1:51 PM

All replies

  • User269602965 posted

    For starters, I would control the user logic in the application code behind page or form and save some PL/SQL processing time.

    Then I would call SQLl from within the application code behind form as the ODP.NET SQL will always be faster than PL/SQL.

    For decision on UPDATE or INSERT, study the Oracle SQL MERGE clause to execute either in one SQL statement,

    depending on bind variables you put in the SQL statement.

    If the user is updating a BLANK variable, I would capture that in code behind form and not delete anything from the database.

    (unless I am missing the need to delete an existing record)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 19, 2011 1:51 PM
  • User1447854015 posted

    Nevermind I found the error. I just had the exeption handling in the wrong place... for hours... :P

    Here's the new code if anyone think it might help them.

    PROCEDURE SP_INSERTORUPDATEPAINTCODE(STR_PAINTCODE IN VARCHAR2, STR_QC IN VARCHAR2, INT_ALTERNATEFLAG IN INTEGER)
    IS
    v_count NUMBER;
    v_PaintID NUMBER;
    
    BEGIN
    
    		SELECT COUNT(*) 
    		INTO v_count 
    		FROM BAY_QC_PAINT 
    		WHERE QC_NUMBER_ID = STR_QC 
    		AND PC_ALTERNATE_FLAG = INT_ALTERNATEFLAG;
    	
    		SELECT PT_ID
    		INTO v_PaintID
    		FROM BAY_PAINT 
    		WHERE PAINT_CODE = STR_PAINTCODE;
    	
    
    IF (v_count) = 0 THEN
    
    	IF v_PaintID IS NOT NULL THEN
    		INSERT INTO BAY_QC_PAINT(QC_NUMBER_ID, PT_ID, PC_ALTERNATE_FLAG)
    		VALUES (STR_QC, v_PaintID, INT_ALTERNATEFLAG);
    	END IF;
    
    ELSE
    	IF v_PaintID IS NOT NULL THEN
    		UPDATE BAY_QC_PAINT
    		SET PT_ID = v_PaintID
    		WHERE PC_ALTERNATE_FLAG = INT_ALTERNATEFLAG
    		AND QC_NUMBER_ID = STR_QC;
    	ELSE
    		DELETE FROM BAY_QC_PAINT
    		WHERE QC_NUMBER_ID = STR_QC
    		AND PC_ALTERNATE_FLAG = INT_ALTERNATEFLAG;
    	END IF;
    
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_PaintID:= NULL;

    END SP_INSERTORUPDATEPAINTCODE;
    Monday, September 19, 2011 1:52 PM
  • User1447854015 posted

    Thanks Lannie, I will take a look into the Merge :)

    Monday, September 19, 2011 1:53 PM
  • User269602965 posted

    I just used it today.  Poorly documented is you can use it for INSERTS ONLY, UPDATES ONLY, or BOTH.

    Monday, September 19, 2011 7:37 PM