"ORA-01427 single-row subquery returns more than one row" without having a subquery in procedure

Question

I have a procedure that throws the error "ORA-01427: single-row subquery returns more than one row". When I use the statemt as a simple SELECT I get 1 row per ID.
I researched the ORA-01427, however I couldn't really apply the answers to my error, since from what I understand I don't have a subquery.

CREATE OR REPLACE PROCEDURE CALC_SLOPE (TBL_NAME IN VARCHAR2) AS 
BEGIN

  EXECUTE IMMEDIATE 'ALTER TABLE '||TBL_NAME||' ADD (SLOPE_MEDIAN NUMBER(2,2),
                                                     SLOPE_75 NUMBER(2,2),
                                                     SLOPE_90 NUMBER(2,2))';

  EXECUTE IMMEDIATE 'UPDATE '||TBL_NAME||' a1 SET(SLOPE_MEDIAN, SLOPE_75, SLOPE_90)
  =(
    SELECT ROUND(MEDIAN(b.SLOPE),2) AS SLOPE_MEDIAN, 
    ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY b.slope DESC),2) AS SLOPE_75,
    ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY b.slope DESC),2) AS SLOPE_90
    FROM '||TBL_NAME||' a2,
    bbx_slope b
    WHERE SDO_CONTAINS(a2.GEOMETRY, b.POINT) = ''TRUE''
    GROUP BY a2.ID
    )';

END CALC_SLOPE;

Where lies the reason for the error and how can it be fixed?

I am working on WINDOWS 10 on ORACLE 12c Enterprise Edition.

Of course you have a sub-query set (...) = (select ...) - that select is a sub-query — a_horse_with_no_name

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2019-03-05T07:57:16

I think you wanted a correlated update

..
FROM '
 || tbl_name || ' a2 JOIN bbx_slope b
ON SDO_CONTAINS(a2.GEOMETRY, b.POINT) = ''TRUE''
WHERE a2.ID = a1.id --This one
GROUP BY a2.ID
..

"ORA-01427 single-row subquery returns more than one row" without having a subquery in procedure

2 Answers