Jump to content

error code 01422


kahunabee

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;

declare

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;

begin

if v_yearcheck < 2011 or v_yearcheck > 2013 then

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

else

promo_ship_sp(t_cutoffdate);

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

end;

/

 

this is my procedure (promo_ship_sp)

 

create or replace

procedure promo_ship_sp

(p_date in date)

is

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

begin

open cur_promo;

loop

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;

END PROMO_SHIP_SP;

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...