CODEWITHSUNDEEP
sqloracle-databaseplsqlselect-intoora-01422
itadaki
itadaki

Reputation: 5

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;
/

Upvotes: 0

Views: 1332

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

    Try this. Returning refcursor is the best way for this kind of resultset.

    CREATE OR REPLACE PROCEDURE jobs_done(
    month_start IN tickets.DATE_REPORTED%TYPE,
    month_end IN tickets.DATE_RESOLVED%TYPE,
    cur_out OUT sys_refcursor
    )
     AS
     OPEN cur_out FOR
     select user_id, support_staff_id, resolution_details 
     from tickets where DATE_RESOLVED >= month_start AND DATE_RESOLVED <= month_end;
     END jobs_done;

------------------------execute-----------------------------------------

var ls refcursor;
EXEC jobs_done(input1,input2,:ls);
print ls;

------------------------------------------------------------------------

Upvotes: 0

Non Plus Ultra
Non Plus Ultra

Reputation: 906

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;

Upvotes: 0

Mohamed Bathaoui
Mohamed Bathaoui

Reputation: 340

The problem is that will not work like that. If your objectif is to see the output, you must iterate on the list of results. What you wrote has no meaning to PL/SQL block. You must create a cursor that keeps the results and iterate into it.

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
  DECLARE
  CURSOR V_CURS IS
  select user_id, support_staff_id, resolution_details 
  where DATE_RESOLVED >= month_start AND DATE_RESOLVED <= month_end;
  BEGIN
      DBMS_OUTPUT.PUT_LINE('userid, supportstaffid, resolution');
      FOR V_CURS_RES IN V_CURS LOOP
       DBMS_OUTPUT.PUT_LINE(V_CURS_RES.userid || V_CURS_RES.supportstaffid || V_CURS_RES.resolution);   
      END LOOP;
  END;      


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;

Upvotes: 0

Related Questions