ORACLE stored procedure returning too many rows

Question

I'm attempting to write a stored procedure in Oracle (which I have come to hate (beside the point)) When executing the stored proc I'm told I've retrieved too many rows (eg. more than 1), but when querying the data via text, it tells me clearly that only one row matches this criteria.

create or replace
PROCEDURE GETADDRESSCOORDS 
(
  HOUSE IN VARCHAR2
, STREET IN VARCHAR2
, X OUT NUMBER
, Y OUT NUMBER
) AS
BEGIN
SELECT X_COORD, Y_COORD INTO X,Y FROM MASTER_ADDRESS
 WHERE HOUSE=HOUSE AND STR_NAME=STREET AND PRE_DIR IS NULL;
END GETADDRESSCOORDS;

When run, I receive this error msg:

SQL> execute getaddresscoords('1550', 'BEDFORD', :X, :Y)
BEGIN getaddresscoords('1550', 'BEDFORD', :X, :Y); END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TAXLOTS.GETADDRESSCOORDS", line 9
ORA-06512: at line 1

So I got too many rows...but when I execute this:

SQL> SELECT MAX(rownum) from MASTER_ADDRESS where HOUSE='1550' 
AND STR_NAME='BEDFORD' AND PRE_DIR IS NULL;

MAX(ROWNUM)
-----------
          1

what am I missing here?

Justin Cave Justin Cave · Accepted Answer · 2011-11-15T02:28:40

Your problem is related to variable scoping. In your SELECT statement, HOUSE will always refer to the column in the table, not to the parameter of the same name.

Generally, when writing PL/SQL, you use some sort of naming convention to differentiate parameters and local variables from columns in tables in order to make this more obvious. In your case, you probably want something like

create or replace
PROCEDURE GETADDRESSCOORDS 
(
  P_HOUSE IN VARCHAR2
, P_STREET IN VARCHAR2
, P_X OUT NUMBER
, P_Y OUT NUMBER
) AS
BEGIN
  SELECT X_COORD, Y_COORD 
    INTO P_X,P_Y 
    FROM MASTER_ADDRESS
   WHERE HOUSE=P_HOUSE 
     AND STR_NAME=P_STREET 
     AND PRE_DIR IS NULL;
END GETADDRESSCOORDS;

If you were to declare local variables, you would similarly use some sort of naming convention to differentiate them from columns in tables (i.e. l_local_variable).

You could explicitly specify the scope resolution for variables that match the names of columns as well but that tends to get much uglier (and you have to be very careful that you don't miss any situations where a column name and a variable name match and the scope resolution isn't explicitly specified). It would be legal to write

create or replace
PROCEDURE GETADDRESSCOORDS 
(
  HOUSE IN VARCHAR2
, STREET IN VARCHAR2
, X OUT NUMBER
, Y OUT NUMBER
) AS
BEGIN
  SELECT X_COORD, Y_COORD 
    INTO X,Y 
    FROM MASTER_ADDRESS ma
   WHERE ma.HOUSE=getAddressCoords.HOUSE 
     AND ma.STR_NAME=getAddressCoords.STREET 
     AND ma.PRE_DIR IS NULL;
END GETADDRESSCOORDS;

but this would not be very conventional.

ORACLE stored procedure returning too many rows

1 Answers