Skip to Main Content
  • Questions
  • Opposite of PL/SQL package init method

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Matt.

Asked: February 11, 2025 - 3:06 pm UTC

Last updated: February 12, 2025 - 2:34 pm UTC

Version: 12C

Viewed 100+ times

You Asked

Oracle provides an initialization method that will automatically run when the package is first accessed within a session. My question, is there a method that gets called when the session ends?

The reason I am asking this is because I have a package that connects to LDAP. I have been having trouble because in batch jobs it is easy to overload on the number of connections. Something about the unbind does not execute and free the session right away.

To that end I am making a change to allow for the session to have just one connection, but it is possible someone does not call the close routine (to unbind). So, I was wanting a way to insure that the close routine would automatically get called at the end of the session.

--example of the initialization method
create or replace package body LDAP_Valmont is
    ldap_session DBMS_LDAP.SESSION;
    Session_Open BOOLEAN;

procedure SetSessionVariables is
begin
   Session_Open:= FALSE;
end;

begin
  SetSessionVariables;
end;

and Chris said...

If the sessions are disconnecting, you could create a logoff trigger to run any cleanup.

If you're returning them to a connection pool then dbms_session.modify_package_state may be what you're looking for - this can reset the

There is also a RESET_STATE service attribute, but that came in 21c.

For more on the difference between these two options see https://asktom.oracle.com/ords/asktom.search?tag=reset-state-vs-dbms-sessionmodify-package-state-dbms-sessionreinitialize

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library