kahunabee Posted March 24, 2014 Share Posted March 24, 2014 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now