Search code examples
sqloracle-databaseplsqlselect-intoora-01422

PL/SQl sqldeveloper want to output multiple rows in plsql


I have a procedure and anonymous block, where I enter in two dates and it searches the tickets table for the tickets that have been resolved during those two dates and outputs em. But I can't seem to figure out how to output multiple rows in pl/sql.

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYSTEM.JOBS_DONE", line 9
ORA-06512: at line 8
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested


CREATE OR REPLACE PROCEDURE jobs_done(
  month_start    IN  tickets.date_reported%TYPE,
  month_end      IN  tickets.date_resolved%TYPE,
  userid         OUT tickets.user_id%TYPE,
  supportstaffid OUT tickets.support_staff_id%TYPE,
  resolution     OUT tickets.resolution_details%TYPE)
AS
  BEGIN

    SELECT user_id, support_staff_id, resolution_details
      INTO userid, supportstaffid, resolution 
      FROM tickets
     WHERE date_resolved >= month_start AND date_resolved <= month_end;

    dbms_output.put_line('userid, supportstaffid, resolution');
    dbms_output.put_line(userid || supportstaffid || resolution);

  END jobs_done;
/

SET serveroutput ON

DECLARE
  month_start    tickets.date_reported%TYPE := &Enter_date_start;
  month_end      tickets.date_resolved%TYPE := &Enter_date_end;
  userid         tickets.user_id%TYPE;
  supportstaffid tickets.support_staff_id%TYPE;
  resolution     tickets.resolution_details%TYPE;
BEGIN
  jobs_done(month_start, month_end, userid, supportstaffid, resolution);
END;
/

Solution

  • Based on new information: your query returns multiple records. With this type of select you can only fetch one row. Try using a cursor. e.g. structure like this:

    declare
      cursor c1 is <select your thing from table>;
    begin
     for r1 in c1
    loop
      dbms_output.put_line (r1.firstcolumn|| r2.anothercolumn );
    end loop;
    end;