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.
1: CREATE OR REPLACE PACKAGE amis_semaphores
2: IS
3: -- Author : PATRICK BAREL
4: -- Purpose : Semaphores to turn things on and off
5:
6: -- Public type declarations
7: -- The actual semaphoretype
8: SUBTYPE semaphore_t IS PLS_INTEGER;
9:
10: -- Public constant declarations
11:
12: -- Public variable declarations
13:
14: -- Public function and procedure declarations
15: FUNCTION issemaphoreset(
16: semaphorename_in IN VARCHAR2 default null )
17: RETURN BOOLEAN;
18:
19: PROCEDURE setsemaphore(
20: semaphorename_in IN VARCHAR2 default null );
21:
22: PROCEDURE clrsemaphore(
23: semaphorename_in IN VARCHAR2 default null );
24: END amis_semaphores;
25: /
1: CREATE OR REPLACE PACKAGE BODY amis_semaphores
2: IS
3: -- Private type declarations
4: -- The type to index the AA by.
5: SUBTYPE semaphore_index_t IS VARCHAR2( 100 );
6: -- The AA of semaphores
7: TYPE semaphore_ct IS TABLE OF semaphore_t
8: INDEX BY semaphore_index_t;
9:
10: -- Private constant declarations
11:
12: -- Private variable declarations
13: -- The variable to hold the collection of semaphores
14: g_semaphores semaphore_ct;
15: -- The variable to hold the internal name if no name is specified.
16: g_internalname semaphore_index_t;
17: -- Function and procedure implementations
18: /******************************************************************************\
19: || procedure : initialization
20: || parameters :
21: ||
22: || purpose : Initialize the package variables. This runs when the package is
23: || first loaded.
24: ||
25: || author : Patrick Barel
26: || created : 2/25/2009
27: || (C) 2009 : AMIS Services
28: \******************************************************************************/
29: PROCEDURE initialization
30: IS
31: BEGIN
32: -- empty the entire collection
33: g_semaphores.DELETE;
34: END initialization;
35:
36: /******************************************************************************\
37: || function : getinternalname
38: || parameters : <none>
39: ||
40: || purpose : Return a name generated by a GUID. If it has already been
41: || generated before, then return that name.
42: ||
43: || author : Patrick Barel
44: || created : 5/6/2009
45: || (C) 2009 : AMIS Services
46: \******************************************************************************/
47: function getinternalname return semaphore_index_t
48: is
49: l_returnvalue semaphore_index_t;
50: begin
51: if g_internalname is null then
52: g_internalname := SYS_GUID; -- generate a name based on a GUID
53: end if;
54: l_returnvalue := g_internalname;
55: RETURN l_returnvalue;
56: end getinternalname;
57: /******************************************************************************\
58: || procedure : tryaaitem
59: || parameters : semaphorename_in IN VARCHAR2
60: ||
61: || purpose : try to get the value from a semaphore item. If this fails, then
62: || the item doesn't exist and it should be created.
63: ||
64: || author : Patrick Barel
65: || created : 2/25/2009
66: || (C) 2009 : AMIS Services
67: \******************************************************************************/
68: procedure tryaaitem(
69: semaphorename_in IN VARCHAR2 )
70: is
71: ldummy semaphore_t;
72: BEGIN
73: BEGIN
74: -- try to get the value from the global
75: -- just to mimic a try..except..end construction
76: ldummy := g_semaphores( semaphorename_in );
77: EXCEPTION
78: WHEN NO_DATA_FOUND THEN
79: -- if it doesn't exist, then create it
80: g_semaphores( semaphorename_in ) := null;
81: WHEN OTHERS THEN
82: RAISE;
83: END;
84: end tryaaitem;
85: /******************************************************************************\
86: || procedure : inc
87: || parameters : value_inout IN OUT PLS_INTEGER
88: ||
89: || purpose : Increase value_inout by 1
90: ||
91: || author : Patrick Barel
92: || created : 2/24/2009
93: || (C) 2009 : AMIS Services
94: \******************************************************************************/
95: PROCEDURE inc(
96: value_inout IN OUT PLS_INTEGER )
97: IS
98: BEGIN
99: value_inout := NVL( value_inout, 0 ) + 1;
100: END inc;
101:
102: /******************************************************************************\
103: || procedure : dec
104: || parameters : value_inout IN OUT PLS_INTEGER
105: ||
106: || purpose : Decrease value_inout by 1
107: ||
108: || author : Patrick Barel
109: || created : 2/24/2009
110: || (C) 2009 : AMIS Services
111: \******************************************************************************/
112: PROCEDURE dec(
113: value_inout IN OUT PLS_INTEGER )
114: IS
115: BEGIN
116: value_inout := NVL( value_inout, 0 ) - 1;
117: END DEC;
118:
119: /******************************************************************************\
120: || function : issemaphoresert
121: || parameters : semaphorename_in IN VARCHAR2
122: ||
123: || purpose : Check if a semaphore by the name of semaphorename_in is set
124: ||
125: || author : Patrick Barel
126: || created : 2/24/2009
127: || (C) 2009 : AMIS Services
128: \******************************************************************************/
129: FUNCTION issemaphoreset(
130: semaphorename_in IN VARCHAR2 )
131: RETURN BOOLEAN
132: IS
133: l_returnvalue BOOLEAN;
134: l_semaphorename semaphore_index_t;
135: BEGIN
136: -- if a null value is given, then create a guid, or use the last one
137: l_semaphorename := nvl(semaphorename_in, getinternalname);
138: BEGIN
139: l_returnvalue :=( g_semaphores( l_semaphorename ) > 0 );
140: EXCEPTION
141: WHEN OTHERS THEN
142: l_returnvalue := FALSE;
143: END;
144:
145: RETURN l_returnvalue;
146: END issemaphoreset;
147:
148: /******************************************************************************\
149: || procedure : setsemaphore
150: || parameters : semaphorename_in IN VARCHAR2
151: ||
152: || purpose : set a semaphore by the name of semaphore_in, i.e. increase
153: || it's number by 1
154: ||
155: || author : Patrick Barel
156: || created : 2/24/2009
157: || (C) 2009 : AMIS Services
158: \******************************************************************************/
159: PROCEDURE setsemaphore(
160: semaphorename_in IN VARCHAR2 )
161: IS
162: l_semaphorename semaphore_index_t;
163: BEGIN
164: -- if a null value is given, then create a guid, or use the last one
165: l_semaphorename := nvl(semaphorename_in, getinternalname);
166: tryaaitem( semaphorename_in => l_semaphorename );
167: inc( g_semaphores( l_semaphorename ));
168: END setsemaphore;
169:
170: /******************************************************************************\
171: || procedure : clrsemaphore
172: || parameters : semaphorename_in IN VARCHAR2
173: ||
174: || purpose : clear a semaphore by the name of semaphore_in, i.e. decrease
175: || it's number by 1. If it reaches the value of zero, then it is
176: || removed from the associative array.
177: ||
178: || author : Patrick Barel
179: || created : 2/24/2009
180: || (C) 2009 : AMIS Services
181: \******************************************************************************/
182: PROCEDURE clrsemaphore(
183: semaphorename_in IN VARCHAR2 )
184: IS
185: l_semaphorename semaphore_index_t;
186: BEGIN
187: -- if a null value is given, then create a guid, or use the last one
188: l_semaphorename := nvl(semaphorename_in, getinternalname);
189: tryaaitem( semaphorename_in => l_semaphorename );
190: DEC( g_semaphores( l_semaphorename ));
191: g_semaphores( l_semaphorename ) := greatest(g_semaphores( l_semaphorename ),0);
192: -- remove if the semaphore is 0 i.e. cleared
193: if g_semaphores( l_semaphorename ) = 0 then
194: g_semaphores.delete( l_semaphorename );
195: end if;
196: END clrsemaphore;
197: BEGIN
198: initialization;
199: END amis_semaphores;
200: /