How to pass year and month as parameter into a PL/SQL block
citicbjApr 26 2013 — edited Apr 26 2013I am populating a time dimension table. One job is to assign business day with sequence number on monthly basis and by year. Business day does not include weekends and all federal holidaies.
I created a PL/SQL block to handle this job. It works. However, I haven't figure out how to pass the month number and year number into PL/SQL block automatically. Now I have to manually enter year and month number (on Toad) to pass to block to make it work. If I create it as stored procedure and write another block to call it, I can pass year and month number into parameter there like this:
exec my_sp_name ('1', '2013');
But all of these are not good enough. I want to use code to automatically pass yesr and month number into PL/SQL block. Can any guru in this Forum give me some help or tips? So that I
can make it better. Thanks for your advice. here is the PL/SQL block I created.
declare i number := 1;
begin
for r in (select * from time_dim where calendar_month_number = &month and calendar_year= &year and business_day_flag = 'B' order by date_sk_id)
loop
update time_dim set business_day = i where date_sk_id = r.date_sk_id;
i := i+1;
end loop;
commit;
end;
/