In a previous blog I talked about turning triggers on and off using semaphores. I created a semaphore for every table I needed along with its programs to set and unset the semaphore and to get its current state. This was a rather cumbersome process, because for every new trigger I needed to add these programs to the package. Both the spec and the body need to be recompiled which renders all dependant code to become invalid. This week I came across a similar problem with a new project. I figured I could use roughly the same solution but I needed to find a better way to implement it.
I want the solution to be portable, independent of the schema on which it is deployed and flexible so I can use it as many times as I want. Actually I want the list of semaphores to be unlimited and I want to be able to reference them by name. Rereading this requirement pointed me in the direction of collections. I have a choice of several collection types (Varray, Nested Tables and Associative Arrays). The first two can also be created in the database as types, the latter just lives inside PL/SQL. Another requirement is that the items should be referenced using a name, rather than a number. Luckily Oracle provides the possibility to index an associative array by varchar2 since version 9iR2. I have never really used this feature until now.
I have come up with the following specification for the package: I must be able to set and clear a semaphore as well as check it’s current status. And the semaphore must not be unset if it is set twice and cleared once. This made me use a number instead of a boolean value to set and clear the semaphore. I have blogged about using a boolean that is really a number earlier. Where I used a separate semaphore for every semaphore I needed, along with it’s own code, variable etc, I know have the possibility to create as many semaphores as I want, without having to change the code.
What if I don’t supply a name for the semaphore at all (or call the set or clear procedure with a NULL-value parameter). In the first version I created this resulted in an error (NULL index….<<find exact error and perhaps link to error>>) so I decided to automagically create a name for this situation. Saving this name into a package variable makes it possible to reuse it, so all subsequent call to the set and clear procedures will use the same semaphore.
I decided to use a GUID for the name, which should result in a unique name for the semaphore. Chances this name is already used by some other semaphore are nearly zero. GUID stands for Globally Unique IDentifier. Steven Feuerstein blogged about the GUIDs before, but in this case it’s more of an escape route, so chances that we will have doubles are zero, especially because it will be generated only once per session. Normally the semaphores will be set and cleared using a name that will hopefully be unique. A good idea for a semaphore name is an object name. For instance, when I want to skip part of the code a trigger, based on a semaphore, I would use the tablename as the name for the semaphore.
Since the package is loaded only once per session, it might be a good idea to populate the escape name in the initialization part of the package, but I decided not to do this, since most of the time you don’t want a name created by the package, because you are providing your own name. This is the correct way to do things and populating the name anyway, would just create useless overhead.
If you are using a lot of semaphores in your code, please take into account that they are all stored in memory. If there are a lot of users/sessions connected at a certain time, this could cause a problem where the program is using up a lot of memory. That is why, when the semaphores is cleared as many times as it is set, it will be removed from the Associative Array to free up the associated memory.
The code is definitely not complete and open for improvements, but here it is.