Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How can i call one stored procedure from another one in oracle
- Thread starter venkat
- Start date
Hi
Just call it by its name,qualifying with the package name(if it belongs to a package) and passing the arguments(if any).
Say you have package pkgA and another pkgB.
You can call a procedure procB from procA like this
procedure procA(v_arg1 [IN|IN OUT|OUT] data_type1,v_arg2 [IN|IN OUT|OUT] data_type2,...)
IS
BEGIN
pkgB.procB(arg1,arg2,...);
END;
vgg
Just call it by its name,qualifying with the package name(if it belongs to a package) and passing the arguments(if any).
Say you have package pkgA and another pkgB.
You can call a procedure procB from procA like this
procedure procA(v_arg1 [IN|IN OUT|OUT] data_type1,v_arg2 [IN|IN OUT|OUT] data_type2,...)
IS
BEGIN
pkgB.procB(arg1,arg2,...);
END;
vgg
jimbopalmer
Programmer
First, if you have Procedures calling Procedure I very much reccomend you put them in Packages.
With stand alone procedures, every time you update one of them, all the others which call it need recompiled, then everything that calls them, and so on, and so on.
One of my vendors did that and needed a recursive procedure to re-compile them all, if any change was made, ewww.
In a package, unless the header changes, (number or type of parameters) none of that happens.
I found a (bad) sample of each in my code:
begin hours_holiday(l_person_id, l_check_date, l_holiday_hours_scheduled);
exception when others then null;
end;
if nvl(l_holiday_hours_scheduled, 0) > 0 then
begin l_next_date := ta_utility.next_scheduled_date(l_person_id,
l_check_date
);
exception when others then null;
end;
First I call the stand alone Procedure hours_holiday to find out if this employee gets a holiday today, then if he does I call the function next_scheduled_date in my ta_utility pakage, to see if he worked the next day after the holiday in his schedule (silly company rule is that you get holiday pay only if you work the day before and after in your schedule, had my snippit of code continued we would have check his attendance before)
I could change next_scheduled_date with out re-compiling this, but if I change hours_holiday, this needs recompiled I tried to remain child-like, all I acheived was childish.
With stand alone procedures, every time you update one of them, all the others which call it need recompiled, then everything that calls them, and so on, and so on.
One of my vendors did that and needed a recursive procedure to re-compile them all, if any change was made, ewww.
In a package, unless the header changes, (number or type of parameters) none of that happens.
I found a (bad) sample of each in my code:
begin hours_holiday(l_person_id, l_check_date, l_holiday_hours_scheduled);
exception when others then null;
end;
if nvl(l_holiday_hours_scheduled, 0) > 0 then
begin l_next_date := ta_utility.next_scheduled_date(l_person_id,
l_check_date
);
exception when others then null;
end;
First I call the stand alone Procedure hours_holiday to find out if this employee gets a holiday today, then if he does I call the function next_scheduled_date in my ta_utility pakage, to see if he worked the next day after the holiday in his schedule (silly company rule is that you get holiday pay only if you work the day before and after in your schedule, had my snippit of code continued we would have check his attendance before)
I could change next_scheduled_date with out re-compiling this, but if I change hours_holiday, this needs recompiled I tried to remain child-like, all I acheived was childish.
- Status
