how to run stored procedure in oracle with loop

Question

i want to call the procedure through anon block like begin t_maha_del_22_06_p('22.06.2020');end; but i run it once and want call with loop to take a large date time like from first 1-st to 15-th august. How can i do it ?


create table t_maha_delete_22_06
(dt date,text varchar2(100));


create or replace procedure my_sch.t_maha_del_22_06_p(p_dt in date default trunc(sysdate) -1) as
begin
delete from t_maha_delete_22_06
where trunc(dt) = p_dt;
commit;

insert into t_maha_delete_22_06
select 
trunc(p_dt) dt,
'blablabla' text from dual 
commit;
end;

Popeye · Accepted Answer · 2020-06-22 05:34:38Z

2

You can do it in loop as follows:

begin 
For dt in (select date '2020-08-01' + level - 1 as dates
           From dual 
           Connect by level <= date '2020-08-15' - date '2020-08-01')
Loop
t_maha_del_22_06_p(dt.dates);
End loop;
end; 
/

answered Jun 22, 2020 at 5:34

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to run stored procedure in oracle with loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related