CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqlora-01422
Jimenemex
Jimenemex

Reputation: 3186

Too Many Rows throwing but only one is selected

I have this procedure which just deletes a row based on a column field called AppID. This procedure get's a value from another column called AppNbr based on that rows AppID column. The procedure is failing with a TOO_MANY_ROWS exception when it tries to SELECT a row. This is the PL/SQL:

DECLARE
    lvnApplNbr NUMBER;

    PROCEDURE deleteAppl(applId IN VARCHAR2) IS
    BEGIN
        BEGIN
            SELECT ApplNbr -- Exception thrown here
            INTO lvnApplNbr
            FROM Appl
            WHERE ApplID = applId;
        EXCEPTION
            WHEN TOO_MANY_ROWS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
        END;
        -- ... Delete it after some logic
    END; -- End Procedure
BEGIN
    ...
    deleteAppl('571E00BA-70E6-4523-BEAC-4568C3DD1A7D');
    ...
END;

The TOO_MANY_ROWS exception is thrown when it SELECT INTOs. I have no idea why it is throwing that error because if I just query this:

SELECT ApplNbr FROM Appl WHERE ApplId = '571E00BA-70E6-4523-BEAC-4568C3DD1A7D';

Only one row will come back with the correct ApplId.

What is going on?

Upvotes: 2

Views: 1176

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

Just use an alias for the related table (Appl):

   PROCEDURE deleteAppl(applId IN VARCHAR2) IS
   .....
   .....
   SELECT ApplNbr 
    INTO lvnApplNbr
    FROM Appl a
    WHERE a.ApplID = applId;
   ......

or change your parameter's name (applId) to another name such as i_applId :

   PROCEDURE deleteAppl(i_applId IN VARCHAR2) IS
   .....
   .....
   SELECT ApplNbr 
    INTO lvnApplNbr
    FROM Appl
    WHERE ApplID = i_applId;
   ......

Because in your case multiple matching perceived, if your parameter's name and column name are identical.

Upvotes: 5

Related Questions