Too Many Rows throwing but only one is selected

Question

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?

Is `Appl.ApplID` a primary key or has a unique constraint? Otherwise the error makes total sense. — The Impaler, Aug 21 '18 at 20:58
@TheImpaler `Appl.ApplId` has a unique constraint and the primary key for this table is `Appl.ApplNbr`. — Jimenemex, Aug 21 '18 at 21:01
Oracle and PL/SQL are **not case sensitive**. Therefore `ApplID` and `applId` are exactly the same identifier. The PL/SQL scoping rules work from the inside out, which means that both sides of the `=` are treated as being the column name. Effectively your WHERE clause is `where 1=1`. It is good practice to use unique identifiers for parameter names: a common convention is to prefix them with `p_` such as `p_applid`. — APC, Aug 21 '18 at 21:12

Barbaros Özhan · Accepted Answer · 2018-08-21T21:55:12.987

5

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.

edited Aug 21 '18 at 21:55

answered Aug 21 '18 at 21:06

Barbaros Özhan

59,113
10
31
55

1

Wow, I even confirmed it by running SQL inside the debugger session. It was confusing the parameter's name with the column name. I was essentially selecting **all** `ApplNbr` from the table `Appl`. – Jimenemex Aug 21 '18 at 21:09
`deleteAppl.applId` would refer to the parameter name, if you really don’t want to prefix the name. Plain `applId`, in a query of a table with an `applId` column, is going to refer to the column. – William Robertson Aug 21 '18 at 22:10

Too Many Rows throwing but only one is selected

1 Answers1