0

I ran into a problem today but I can't see what's wrong with my code. I made a procedure which contains a SELECT INTO which fetches 2 rows, and 2 variables. I took out some parts of the procedure otherwise it would get too long:

In the AS I declare a CURSOR, this selects all the records in table WINKEL.

  CURSOR v_winkels IS
    SELECT W.id
    FROM WINKEL W;

Oracle then later throws an ORA-01422 EXCEPTION here:

  FOR w IN v_winkels LOOP
    dbms_output.put_line('ID is ' || w.id);

    SELECT P.lat, P.lon
    INTO v_lat_winkel, v_lon_winkel
    FROM POSTCODE P, WINKEL W
    WHERE W.postcode_ID_FK = P.id
    AND W.id = w.id;

I don't see what's wrong here. I'm using Oracle XE 11.2 in Oracle SQL Developer.

EDIT: I already used a SELECT INTO with multiple variables, so I don't get why it doesn't work here. Example code:

  SELECT P.id, P.street, P.city
    INTO o_ID, o_straat, o_woonplaats
    FROM POSTCODE P
    WHERE P.postcode = i_postcode
    AND i_huisnummer BETWEEN P.minNumber AND P.maxNumber
    AND (P.numberType = v_even OR P.numberType = 'mixed')
    AND ROWNUM = 1;
Guido
  • 1,161
  • 3
  • 12
  • 33
  • 1
    You can't select two rows into a variable. Need a cursor for that. Also, why not just join the two tables and have one query/loop then? – OldProgrammer Dec 29 '15 at 18:27
  • @OldProgrammer I'll add some more info to the question. – Guido Dec 29 '15 at 18:28
  • @OldProgrammer Yes you're right, I just fixed it by adjusting the CURSOR itself to hold all the 3 variables. Thanks! – Guido Dec 29 '15 at 18:47

0 Answers0