create or replace package body block
as
procedure test
is
begin
dbms_output.put_line('Hello from test()');
end;
begin
dbms_output.put_line('Hello from begin; end');
exception
when others then
dbms_output.put_line('BEGIN:['||sqlerrm||']');
end;
/
This begin block is executed when the package is instantiated in a session:
C:\ >sqlplus orablog/orablog@//192.168.56.33:1539/xepdb1
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 20 16:14:03 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Tue Nov 18 2025 12:45:55 +00:00
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL>
Set serveroutput on and compile the code:
SQL> set serveroutput on
SQL> @pack
Package created.
Package body created.
SQL>
Now we can execute the code:
SQL> exec block.test;
Hello from begin; end
Hello from test()
PL/SQL procedure successfully completed.
SQL>
As we can see the output from the begin/end block is printed first then the print line from the procedure test().
If we execute again, what happens:
SQL> exec block.test;
Hello from test()
PL/SQL procedure successfully completed.
SQL>
The begin/end block is not executed again. This is a feature of PL/SQL packages and the begin/end block at the package level. The begin/end block will execute again if the package became invalid and was re-compiled dynamically before its run or if we log into a new session then its run again:
SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec block.test;
Hello from begin; end
Hello from test()
PL/SQL procedure successfully completed.
SQL>
Now; imagine that this package contains some sensitive procedures and functions and we want to implement a check/test to ensure that the user is allowed to execute the procedure(s) before he/she is allowed to do so.
Maybe we want to check the IP address or time of the day or date or role enabled or?? We can write a procedure that is executed that tests the security fields we choose and if we pass the test allow the execution to continue.
If we add this test/check to the begin/end block what happens. We will simulate this as a simple raise command in the begin/end block to simulate that the security check failed. We change the code as follows:
create or replace package body block
as
security_fail exception;
pragma exception_init(security_fail,-20001);
procedure test
is
begin
dbms_output.put_line('Hello from test()');
end;
begin
dbms_output.put_line('Hello from begin; end');
raise security_fail;
exception
when others then
dbms_output.put_line('BEGIN:['||sqlerrm||']');
end;
/
Now reinstall this:
SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> @pack.sql
Package created.
Package body created.
SQL>
Now we can execute it and simulate that the security check failed and therefore we are stopped from running the test() procedure or indeed any other procedure:
SQL> exec block.test;
Hello from begin; end
BEGIN:[ORA-20001: ]
Hello from test()
PL/SQL procedure successfully completed.
SQL>
Hmmm, we caught the exception that the user failed the simulated security check and this sent us to the main package exception block and printed its message BUT we also then ran the test() procedure. So that did not work.
What if we do a raise_application_error() in the exception block. Lets change the code to do that:
create or replace package body block
as
security_fail exception;
pragma exception_init(security_fail,-20001);
procedure test
is
begin
dbms_output.put_line('Hello from test()');
end;
begin
dbms_output.put_line('Hello from begin; end');
raise security_fail;
exception
when others then
dbms_output.put_line('BEGIN:['||sqlerrm||']');
raise_application_error(-20001,'Error in Security check');
end;
/
And install it:
SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> @pack
Package created.
Package body created.
SQL>
So now we can run the test() procedure:
SQL> exec block.test;
Hello from begin; end
BEGIN:[ORA-20001: ]
BEGIN block.test; END;
*
ERROR at line 1:
ORA-20001: Error in Security check
ORA-06512: at "ORABLOG.BLOCK", line 16
ORA-06512: at line 1
SQL>
That is better as the print message from the package begin/end block was printed and then the raise security_error took us to the package exception block which in turn printed out the sqlerrm error message before raise_application_error() took us out of the PL/SQL code before the procedure test() would have been executed.
So, we failed the security check and the code did not run. If run it again we get:
SQL> exec block.test;
Hello from begin; end
BEGIN:[ORA-20001: ]
BEGIN block.test; END;
*
ERROR at line 1:
ORA-20001: Error in Security check
ORA-06512: at "ORABLOG.BLOCK", line 16
ORA-06512: at line 1
SQL>
So, we can add a security check in the package begin/end block BUT we need to ensure that raise_application_error() throws us out of the package begin/end block back to the caller.
If we did not use raise_application_error() then we would have raised an error but initialised the package correctly and the next time we ran it the begin/end block would not be executed and the code would run BUT with this method even though we would catch the exception the package procedure still executes.
In summary if you wish to check security at the package level rather than in a single (or multiple) procedures then the package initialise MUST FAIL so that next time its executed then the security is guaranteed to run and fail again.
BUT, BUT, we missed a step; This is because the example is too simple. Yes, the raise_application_error() will kill the package initialise and even if its run again the begin/end block will execute again and fail again IF the simulated security check failed. This at first glance seems to work as a solution BUT if the security check in the begin/end block succeeded first time then the raise_application_error() is not fired so the initialisation does not fail and therefore this design does not work as next time no matter if the security check would pass or fail the begin/end block is not processed.
So, if on first execution the security check passed then on a second execution it would potentially fail then the security check DOES NOT WORK.
Let us create a simple example by modifying my code. I will add an integer at the start as a counter and set to zero. Then the test function will increment the integer which will happen after the begin/end block as the begin/end block is called. I will also change the simulated security check to test the integer. If the integer is greater than zero the security check fails and then raise_application_error() is called.
In summary on first execution the security check passes but subsequent checks would fail.
Here is the modified code:
create or replace package body block
as
--
i number:=0;
security_fail exception;
pragma exception_init(security_fail,-20001);
--
procedure test
is
begin
dbms_output.put_line('Hello from test() - value is ['||i||']');
i:=i+1;
end;
--
begin
dbms_output.put_line('Hello from begin; end, the check value is=['||i||']');
if(i>0) then
raise security_fail;
end if;
exception
when others then
dbms_output.put_line('BEGIN:['||sqlerrm||']');
raise_application_error(-20001,'Error in Security check');
end;
/
Now install in the database:
SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> @pack
Package created.
Package body created.
SQL>
And run the first time where the security check should succeed:
SQL> exec block.test;
Hello from begin; end, the check value is=[0]
Hello from test() - value is [0]
PL/SQL procedure successfully completed.
SQL>
This worked correctly. Now run a second time where the security check should fail:
SQL> exec block.test;
Hello from test() - value is [1]
PL/SQL procedure successfully completed.
SQL>
As we can see the integer is now 1 and the execution of the protected code succeeded even though it should have failed the security check because the package BEGIN/END block did not fire again.
In summary do not put security checks in the begin/end block of a package. It may work once or twice if the begin/end block is not completed and the package not initialised but will fail to check security if the package is initialised. This would give false security
#oracleace #sym_42 #oracle #database #security #checks #plsql #package
