Oracle, execute pl/sql code before stored procedure call

Question

Is there any possibility to automatically execute some PL/SQL code right before stored procedure? Something like "on-before" execute?

Following business logic, every stored procedure should be executed only after some steps named "Login". This is not login to DB but to product, and we can not change this behavior, because of system is developed by another team. We should not forget to call that "login" every time.

Is there any solution to automate such thing in Oracle? Does packages have something like "initialization" section?

Have you considered writing wrapper procedures which do the "Login" functionality and then call the 3rd Party procedures? — Del
Also, does this functionality need to only be run one time each session? Or before each procedure execution? If it is just one time, you could consider writing an AFTER LOGON trigger. — Del
The way I see it, this: We just must not forget to call that "login" every time. is what you'll be doing all the time. — Littlefoot
Regarding wrappers - yes, we are using that way now, but there are issues when newcomer forget them. AFTER LOGON trigger is interesting idea, I will try it. — Aleksandr Beliavski
@Del thanks for suggestion, but AFTER LOGON trigger cannot help me, because of we are always logged into db, but how can guarantee that we are logged into system? We need some steps literally right before executing procedure. — Aleksandr Beliavski

Belayer Belayer · Accepted Answer · 2020-10-30T04:30:49

It sounds like what you are doing is logging into a application, so is the requirement actually to call a LOGIN procedure within that application. The question posed by @Del is the pertinent question. If the answer is before each procedure execution to the application then you need to call that LOGIN procedure just before you call another procedure with in that application. If however it is a once per session requirement then there is effectively an 'automatic' process.
You need to put all your calling interface routines into a single package. In addition to defining procedures and functions the package itself has an execution section. This section is automatically executed when the package is loaded. The package loads with the first call to any of its functions or procedure.
The following shows a skeleton of your needed package. (body only)

create or replace package interface_other_app as 
   procedure1 ... end procedure1; 
   procedure1 ... end procedure2; 
   last_procedure ... end last_procedure; 
BEGIN   -- package initialization. The code in here runs once when the pacaage is loaded. 
   other_app.login(application login parameters); 
end interface_other_app;

Oracle, execute pl/sql code before stored procedure call

1 Answers