Jump to content

error code 01422


Recommended Posts

I am trying to write a PL/SQL program that will call a procedure. The user will enter a cutoff date in the calling program and pass it to the procedure where it is being used to check in the bb_basket table to pull shoppers who have not been to the website since before the cutoff date.

The shoppers that the select retrieves will be inserted into the promolist table.


I am getting the ora-01422 error exact fetch returns more than requested rows. Code is below, any help would be appreciated.



This is the code for my PL/SQL block that is calling my procedure:



set serveroutput on;

set verify off;


t_cutoffdate date := '&v_cutoffdate';

v_yearcheck number(4,0) := to_number(to_char(t_cutoffdate, 'yyyy'));

v_mon char(3) := (to_char(t_cutoffdate, 'mon'));

v_idshopper bb_promolist.idshopper%type;

v_month bb_promolist.month%type;

v_year bb_promolist.year%type;

v_promoflag bb_promolist.promo_flag%type;

v_used bb_promolist.used%type;


if v_yearcheck < 2011 or v_yearcheck > 2013 then

dbms_output.put_line ('Please enter a year between 2011 and 2013');



end if;

select idshopper,month,year,promo_flag,used

into v_idshopper,v_month,v_year,v_promoflag,v_used

from bb_promolist;

dbms_output.put_line (v_idshopper);




this is my procedure (promo_ship_sp)


create or replace

procedure promo_ship_sp

(p_date in date)


cursor cur_promo is

select idshopper, max(dtcreated) createdate

from bb_basket

group by idshopper

having max(dtcreated) < p_date;

type t_rec is record

(shopperid bb_basket.idshopper%type,

createdate bb_basket.dtcreated%type);

rec_promo t_rec;

v_month char(3) := 'APR';

v_year number(4,0) := 2012;

v_promoflag number(1,0) := 1;

v_used char(1) := 'N';


open cur_promo;


fetch cur_promo into rec_promo;

exit when cur_promo%notfound;

insert into bb_promolist

values (rec_promo.shopperid, v_month,v_year,v_promoflag,v_used);

end loop;

close cur_promo;


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...