CODEWITHSUNDEEP
oracle-databaseplsqlpackage
Taurus22
Taurus22

Reputation: 89

Automatically execute code on package startup

I have an exam coming up and have a specification which I'm not sure what it means. Can please give me someone a hint or give me a short example for it.

Specification: Automatically execute code on package Startup:

If criteria met: Code written that automatically initialises variable without explicit call from user.

I'm not sure if this is asked but can it be that I have to call the package like this: PACKAGE_NAME.function_name so a call without a value? Is that what is asked?

If so, you don't need to help me further because I know what to do .

Upvotes: 1

Views: 1643

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You can have code in the package body that is not part of any of the private or public functions or procedures; that code is executed when the package is instantiated by each session.

As a contrived example, you might want to populate a variable with the initialisation time, which you could do with:

create package p42 as
  procedure some_proc;
end p42;
/

create package body p42 as
  init_time timestamp;

  procedure some_proc is
  begin
    -- do something
    null;
  end some_proc;

-- run on package instantiation
begin
    init_time := systimestamp;
    -- just as a demo
    dbms_output.put_line('init_time is ' || init_time);
end p42;
/

That last block is run when the package is instantiated for each session, i.e. when the first procedure or function is called by that session, and not when it is compiled:

Package body P42 compiled

set serveroutput on
exec p42.some_proc;

PL/SQL procedure successfully completed.
init_time is 19-MAY-15 15.41.29.179387

... and not when other procedures are called from the same session:

exec p42.some_proc;

PL/SQL procedure successfully completed.

That variable could then be referenced later. Of course having that variable means you now have package state, which causes its own problems. But you could just call other procedures during initialisation instead.

Read more in the documenation; about instantiation, state, and the initialisation part of the package body:

Finally, the body can have an initialization part, whose statements initialize public variables and do other one-time setup steps. The initialization part runs only the first time the package is referenced. The initialization part can include an exception handler.

Upvotes: 7

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

You can do it like this:

CREATE OR REPLACE PACKAGE MY_PACKAGE AS

FUNCTION GiveMeFive RETURN NUMBER;

END MY_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY MY_PACKAGE IS

    five NUMBER;

FUNCTION GiveMeFive RETURN NUMBER IS
BEGIN
    RETURN five;
END GiveMeFive;

-- By this syntax the procedure is running once when this package is executed first time within a session 
BEGIN
    five := 5;
END;

/

Note, the package body has to close just by /, do not use 

BEGIN
   five := 5;
END;

END MY_PACKAGE;
/

Upvotes: 1

Related Questions