Suppose that every time we add records into the T1 table we have to do some additional stuff.
One option to implement this is by using an AFTER INSERT trigger that will perform this additional stuff, but I really dislike this option (mainly because the code becomes hidden in a way, and there may be a negative impact on performance).
I prefer writing a procedure that inserts the records into T1 and performs this additional stuff.
CREATE OR REPLACE PACKAGE my_package AS
PROCEDURE insert_t1 (...);
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package AS
PROCEDURE insert_t1 (...) IS
BEGIN
INSERT INTO t1 ...;
-- do some additional stuff, that must be done
-- whenever inserting new records into T1
END insert_t1;
END my_package;
/
But what will happen if someone (perhaps even myself in one year from now) will insert records into T1 from another place (in another procedure or directly via an INSERT statement) and will not know (or maybe forget) about the mandatory additional stuff?
The best thing would be to make this procedure the one and only way to insert records into T1. But how to do it?
I like doing it in a way that I call “the guardian trigger”, using a combination of a global variable and a trigger (but this trigger doesn’t do actual stuff, it is only a gatekeeper).
I’m adding a Boolean global variable and initialize it to FALSE. I change it to TRUE at the beginning of the insert_t1 procedure, and make sure to set it back to FALSE when leaving the procedure.
CREATE OR REPLACE PACKAGE BODY my_package AS g_is_insert_into_t1_allowed BOOLEAN := FALSE; PROCEDURE insert_t1 (...) IS BEGIN g_is_insert_into_t1_allowed := TRUE; INSERT INTO t1 ...; -- do some additional stuff, that must be done -- whenever inserting new records into T1 g_is_insert_into_t1_allowed := FALSE; EXCEPTION WHEN OTHERS THEN g_is_insert_into_t1_allowed := FALSE; RAISE; END insert_t1; END my_package; /
Now I’m adding a function – a public function – to get the current value of the global variable:
CREATE OR REPLACE PACKAGE my_package AS FUNCTION is_insert_into_t1_allowed RETURN BOOLEAN; PROCEDURE insert_t1 (...); END my_package; / CREATE OR REPLACE PACKAGE BODY my_package AS g_is_insert_into_t1_allowed BOOLEAN := FALSE; FUNCTION is_insert_into_t1_allowed RETURN BOOLEAN IS BEGIN RETURN g_is_insert_into_t1_allowed; END is_insert_into_t1_allowed; PROCEDURE insert_t1 (...) IS BEGIN g_is_insert_into_t1_allowed := TRUE; INSERT INTO t1 ...; -- do some additional stuff, that must be done -- whenever inserting new records into T1 g_is_insert_into_t1_allowed := FALSE; EXCEPTION WHEN OTHERS THEN g_is_insert_into_t1_allowed := FALSE; RAISE; END insert_t1; END my_package; /
And as the final step I’m creating a statement level trigger – BEFORE INSERT ON T1; a trigger that doesn’t change anything, it just checks that the global variable is currently set to TRUE.
CREATE OR REPLACE TRIGGER t1_trig BEFORE INSERT ON t1 BEGIN IF NOT my_package.is_insert_into_t1_allowed THEN raise_application_error(-20000, 'INSERTs into T1 are allowed only via my_package.insert_t1'); END IF; END t1_trig; /
So if I’ll try to insert records into T1 not via the procedure the trigger will not allow it, it will raise an exception saying “INSERTs into T1 are allowed only via my_package.insert_t1”.
Obviously this pattern can be implemented for other operations, not only for INSERT.
Excellent!
Looks simple.
I will implement it on my work for sure.
Thanks for sharing it.
It’s important to take care of using global variables inside of packages when dealing with concurrency; and mainly if there’s a chance of this package being recompiled as stated here: https://markhoxey.wordpress.com/2013/09/17/avoiding-ora-04068-existing-state-of-packages-has-been-discarded/
According to PLSQL documentation for Oracle 11.2 (in recent version it changed a little bit), packages that keep state may be corrupted during recompilation:
“The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.”
Whenever a package is recompiled any package state held by a current session is invalidated and ORA-04068 occurs the next time the session tries to use the package again.
Thanks for your comment, Rafael.
ORA-04068 is yet another reason to use Edition-Based Redefinition, as I always preach 🙂
Thanks,
Oren.
EBR is such a great feature on Oracle. I’ve been using it in my automated tests to mock packages during the tests! It works like a charm!
But let me ask you, how have you been using EBR to deal with ORA-04068? I’m curious about it.
When practicing EBR, we never compile packages in “exposed” editions, only in a new edition that is still not used by the users/app (and we refrain from doing other things that may cause invalidation of objects in the exposed editions).
So, under the hood, the package in the exposed edition and the package in the new edition are two different objects, and therefore we don’t really compile the package that is in-use. And if we don’t compile it, there is no reason to get ORA-4068.
But when we apply this new edition, does Oracle avoid to invalidate the already open sessions?
After exposing the new edition, new sessions will use the new edition, while old sessions will continue using the old edition. This is the concept of “hot rollover”.
That’s awesome! It’s more powerful than I thought! Thanks for explanation, Oren.
As package spec variables are stored per session, unless im missing something here?
Couldn’t you use permissions to prevent people from inserting a new record in any other way than through the package procedure?
The idea is to restrict even the owner of the table.