Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to pass year and month as parameter into a PL/SQL block

citicbjApr 26 2013 — edited Apr 26 2013
I 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;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 24 2013
Added on Apr 26 2013
11 comments
1,831 views