Looks fine to me...
create table my_table (
business_name varchar2(100),
business_id int,
city_name varchar2(100),
city_id int,
start_date date,
end_date date
);
insert into my_table (Business_Name,Business_id,City_name,City_id,Start_date,End_Date)
values ('test',1,'test',1,date'1999-12-31',date'9999-12-13');
commit;
create or replace procedure prc_update (p_Business_Name varchar2,
p_Business_id int,
p_City_name varchar2,
p_City_id varchar2,
p_Start_date date,
p_End_Date date)
AS
v_end_date date;
begin
update my_table
set end_date = sysdate
where Business_id = p_Business_id
returning end_date into v_end_date;
if sql%rowcount = 0 then
raise_application_error (
-20001,
'Row start date mismatch'
);
end if;
insert into my_table (Business_Name,Business_id,City_name,City_id,Start_date,End_Date)
values (p_Business_Name,p_Business_id,p_City_name,p_City_id,v_end_date,p_End_Date);
end prc_update;
/
exec prc_update ( 'test', 1, 'new city', 1, sysdate, date'9999-12-31' );
select * from my_table;
BUSINESS_NAME BUSINESS_ID CITY_NAME CITY_ID START_DATE END_DATE
test 1 test 1 31-DEC-1999 00:00:00 24-MAR-2021 09:10:07
test 1 new city 1 24-MAR-2021 09:10:07 31-DEC-9999 00:00:00
What exactly happens when you call the code like this?