Getting ORA-01422 error when only one record exists with condition

Question

I am attempting to select data in an Oracle PL/SQL procedure. When I select the data from the table directly like this

SELECT * FROM CUSTOMER_USERS
 WHERE customer_id = 'cust1234'
   AND user_id = 'cust1234user'

I get one row of data.

USERID	CUSTOMER_ID	DESCRIPTION	READ_ONLY	UPDATED_BY	CUST_API_KEY	LAST_ACCESS_DATE
cust1234user	cust1234	1234 Corporation	N	jdoe	[api_key]	3/19/2024 2:31:37 AM

However, when I put the same exact conditions in a select into query with variables, it causes an error with the description "ORA-01422: exact fetch returns more than requested number of row."

 customerID := 'cust1234';
 userID := 'cust1234user';
 SELECT CUST_API_KEY INTO v_encValue
   FROM CUSTOMER_USERS
  WHERE customer_id = customerID
    AND userid = userID;

How could this be happening? These two columns make up the PK, so there can't even be duplicates in this table based on that criteria. I can't tell what it thinks the additional rows are.

Answer 1

This is explained in the documentation:

If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.

So when you do:

AND userid = userID

both of those userid references are for the table column; neither is for the PL/SQL variable. So that condition evaluates to true, at least for every row where userid is not null. Note that by default the slightly different case makes no difference; you just avoided the same problem with customer_id and customerID by adding the underscore to that one.

Make the PL/SQL variable names distinct from the column names. It's common to prefix variables with something (like l_userid), and if you do that then establish a naming convention for your code. In a stored PL/SQL block you can also qualify the variables with the block name (e.g. the procedure name) and table name/alias - some people prefer that.