{"id":321,"date":"2009-05-06T17:38:37","date_gmt":"2009-05-06T16:38:37","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=321"},"modified":"2009-05-06T17:38:37","modified_gmt":"2009-05-06T16:38:37","slug":"semaphores-revisited","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=321","title":{"rendered":"Semaphores (revisited)"},"content":{"rendered":"

In a previous<\/a> blog<\/a> I talked about turning triggers on and off using semaphores<\/a>. I created a semaphore<\/a> 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.<\/p>\n

<\/p>\n

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. \"9i<\/a>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<\/a>). 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<\/a> provides the possibility to index an associative array by varchar2 since version 9iR2. I have never really used this feature until now.<\/p>\n

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\u2019s own code, variable etc, I know have the possibility to create as many semaphores as I want, without having to change the code.<\/p>\n

What if I don\u2019t 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\u2026.<<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.<\/p>\n

I decided to use a GUID<\/a> 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<\/a> blogged about the GUIDs<\/a> before, but in this case it\u2019s 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.<\/p>\n

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\u2019t 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.<\/p>\n

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.<\/p>\n

The code is definitely not complete and open for improvements, but here it is.<\/p>\n

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