CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
CM25
CM25

Reputation: 103

PLSQL Error when trying to insert record

I am getting this error when i try to insert data through my procedure:

Error starting at line : 48 in command -
BEGIN
  OPEN_CLASS('PRAC_4', 'ISYS224', 'Tue', '09:00', 'P', 'E6A123');
END;
Error report -
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "43053327.OPEN_CLASS", line 18
ORA-06512: at line 2
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

My procedure at the moment is (Works fine with all other tests iv run so far):

CREATE OR REPLACE PROCEDURE OPEN_CLASS(
    p_class IN TUTPRAC.CLASSID%TYPE,
    p_unitc IN TUTPRAC.UNITCODE%TYPE,
    p_classd IN TUTPRAC.CLASS_DAY%TYPE,
    p_classt IN TUTPRAC.CLASS_TIME%TYPE,
    p_classtp IN TUTPRAC.CLASS_TYPE%TYPE,
    p_roomnm IN TUTPRAC.ROOMNUM%TYPE)

    IS

    -- Variables    
    x number:=0;
    y number:=0;  
    CLASS_OVERLAPS EXCEPTION;

BEGIN
    -- checks
    SELECT nvl((SELECT 1 FROM TUTPRAC WHERE UNITCODE = p_unitc and CLASS_DAY = p_classd or CLASS_DAY = p_classd and CLASS_TIME = p_classt and ROOMNUM = p_roomnm) , 0) INTO x FROM dual;
    SELECT nvl((SELECT 1 FROM UNITSTREAM WHERE UNITCODE = p_unitc and DAY = p_classd or DAY = p_classd and TIME = p_classt and LOCATION = p_roomnm) , 0) INTO y FROM dual;

    -- insert
    IF (x = 0 and y = 0) THEN
      INSERT INTO TUTPRAC (CLASSID, UNITCODE, CLASS_DAY, CLASS_TIME, CLASS_TYPE, ROOMNUM) 
      VALUES (p_class, p_unitc, p_classd, p_classt, p_classtp, p_roomnm);
    ELSE
       RAISE CLASS_OVERLAPS;
    END IF;

    EXCEPTION
      WHEN CLASS_OVERLAPS
      THEN
      RAISE_APPLICATION_ERROR(-20001,'The class you have tried to insert is clashing with an existing class.');

END OPEN_CLASS;

My procedure checks to see if the class record being inserted clashes with any classes already in the table. This procedure has worked for every test case i'v tried so far except for this one.

Upvotes: 2

Views: 1390

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

ORA-01427: single-row subquery returns more than one row
ORA-06512: at "43053327.OPEN_CLASS", line 18

The error is due to the fact that your SELECT .. INTO statement gives multiple rows, however, it should generate only a single row.

Execute the SQL without INTO clause to check the number of rows returned, and put the required filer. If you want to pick a single row, then you could limit the rows to 1 using ROWNUM.

SELECT COUNT(*) FROM
(
SELECT nvl((SELECT 1 FROM TUTPRAC WHERE UNITCODE = p_unitc 
and CLASS_DAY = p_classd or CLASS_DAY = p_classd 
and CLASS_TIME = p_classt and ROOMNUM = p_roomnm) , 0) FROM dual
);

SELECT COUNT(*) FROM
(
SELECT nvl((SELECT 1 FROM UNITSTREAM WHERE UNITCODE = p_unitc 
and DAY = p_classd or DAY = p_classd and TIME = p_classt 
and LOCATION = p_roomnm) , 0) FROM dual
);

SELECT 1 doesn't mean it would return a single row, it would just return the value 1 for every row fetched. If you want to restrict the number of rows, then:

  • ROWNUM
  • Row-limiting feature FETCH in 12c.

UPDATE

Per OP's comment below, COUNT is needed.

SELECT COUNT(*) 
INTO x 
FROM TUTPRAC 
WHERE UNITCODE = p_unitc 
AND CLASS_DAY = p_classd or CLASS_DAY = p_classd 
AND CLASS_TIME = p_classt and ROOMNUM = p_roomnm;

SELECT COUNT(*) 
INTO y     
FROM UNITSTREAM 
WHERE UNITCODE = p_unitc 
AND DAY = p_classd or DAY = p_classd 
AND TIME = p_classt and LOCATION = p_roomnm;

Upvotes: 1

Related Questions