PL/SQL retrieve multiple rows using SELECT INTO in Oracle
<p>I'm trying to create a trigger, that fires when a new row is being inserted into a table. The trigger should check if that table have any rows, that have a value in a column that equals the value from the same column in the inserting row. Then I need to update rows in the original table that have the same ID as the selected ones. Now I need to  make a request to retrieve the data (all the columns), and that's when I get in troubles.</p>

<p>The problem is that I need to declare a variable, that will be used to store result of that request. That variable should have the same type as the table I retrieve data from does, but when I declare it like that (REC_ROAD_INT is my table):</p>

<p><code>DECLARE REQUEST_RESULT REC_ROAD_INT%TYPE;</code></p>

<p>I get </p>

<p><code>%TYPE must be applied to a variable, column, field or attribute, not to "REC_ROAD_INT"</code></p>

<p>I actually can retrieve data I need, but only in those cases when the table have only 1 row that meets a requirement. Here's a sample:</p>

<pre><code>create or replace TRIGGER INSERT_ROAD_TRIGGER 
BEFORE INSERT ON REC_ROAD_INT
FOR EACH ROW
DECLARE
  ROAD_ID NUMBER;
  ROAD_NAME VARCHAR2(20);
  ROAD_START_KM NUMBER;
  ROAD_END_KM NUMBER;
BEGIN 
    SELECT ID, NAME, START_KM, END_KM INTO ROAD_ID, ROAD_NAME, ROAD_START_KM, ROAD_END_KM
    FROM REC_ROAD_INT
    WHERE (:NEW.START_KM BETWEEN START_KM AND END_KM) OR 
      (:NEW.END_KM BETWEEN START_KM AND END_KM);
    IF INSERTING 
    THEN 
      DBMS_Output.Put_Line('INSERTING');
      DBMS_Output.Put_Line('Inserting road: ' || :NEW.ID || ' ' || :New.NAME || ' ' || :New.START_KM || ' ' || :New.END_KM);
      DBMS_Output.Put_Line('Crossing road: ' || 'ID = ' || ROAD_ID || ', NAME = ' || ROAD_NAME || 
              ', START_KM = ' || ROAD_START_KM || ', END_KM = ' || ROAD_END_KM);
    END IF;
END;
</code></pre>

<p>How can I get all the rows? Thanks.</p>