insight 
Suppose you are the developer of a database service which can be called any number of times. This database service itself just registers the call, and is allowed to immediately make an outbound call to another service, for example to enhance information presented by the initial inbound call. Now suppose the interactions with this service are restricted by a limited outbound call rate, which means you should take care it doesn’t exceed a certain maximum per second. You’d then want to optimize the number of times the API for this service is called to exactly match this allowed maximum. For simplicity, let’s assume the service API should not be called more often than 25 times per second.
Requirements
u003culu003ern tu003cliu003eThere’s a stored procedure which represents the referenced outbound API, we will call it my_api_request();u003c/liu003ern tu003cliu003eThere’s a table registering each successfull call to the API by the timestamp of it’s call;u003c/liu003ern tu003cliu003eQuerying the table should reveal how many calls per second were made, which should ideally be optimized to 25 times per second.u003c/liu003ernu003c/ulu003e
Solution setup
The solution I present heavily relies on the ordered non-cached sequence feature. The idea is to construct an expression consisting of both a time representation including date, hour, minute and seconds complemented with a value which cycles through 25 different values using the modulo function. Of course, the value of 25 corresponds to the required maximum of 25 calls per second.rnrnHence, first we create an ordered sequencernu003cpreu003ecreate sequence my_ordered_seq order nocachern/u003c/preu003ernAnd then we create the API-calls logging tablernu003cpreu003ecreate table my_api_requestsrn( time_of_request timestamp default systimestamp not null rn, seq_id number(15) default my_ordered_seq.nextvalrn, req_id varchar2(21) generated always as rn ( substrrn ( to_char(time_of_request,’YYYYMMDD-HH24MISS’)rn ||’.’rn ||lpad(mod(seq_id,25),3,’0′)rn , 1rn ,21rn )rn )rn, constraint my_api_requests_limit unique (req_id)rn)u003c/preu003ernAs you will see, each time a value is inserted to the table, a subsequent number is derived from the ordered sequence.rnIf there a more than 25 inserts within the same second, the module function will result in a previously used expression. This will cause a unique key constraint violation, effectively prohibiting the outbound call rate to exceed the required maximum.
Solution test
Contents of timertest.sql, which is parameterized script to allow us to test for the specified N number of seconds:rnu003cpreu003edefine p_nrof_sec = u0026amp;1.rndeclarern c_nrof_seconds constant pls_integer := u0026amp;p_nrof_sec.;rn l_time_ends constant date := sysdate + c_nrof_seconds/(24*3600);rnrn l_succeeded_count pls_integer := 0;rn l_skipped_count pls_integer := 0;rn rn procedure do_api_requestrn isrn pragma autonomous_transaction;rn beginrn insert into my_api_requests (time_of_request) values (default);rn l_succeeded_count:=l_succeeded_count+1;rn u002du002d call my_api_request() here ;rn commit;rn exceptionrn when othersrn then u002du002d too many inserts per unit of timern l_skipped_count:=l_skipped_count+1;rn end do_api_request;rnrnbeginrn while sysdate u0026lt; l_time_endsrn looprn do_api_request;rn end loop;rn dbms_output.put_line ( ‘succeeded api requests: ‘||l_succeeded_count);rn dbms_output.put_line ( ‘skipped api requests: ‘||l_skipped_count);rnend;rn/rnrnselect to_char(time_of_request,’YYYYMMDD-HH24MISS’)rn, count(*)rnfrom my_api_requestsrngroup by to_char(time_of_request,’YYYYMMDD-HH24MISS’)rn/u003c/preu003e
Output
u003cpreu003eSQLu0026gt; @timertest 10rnsucceeded api requests: 250rnskipped api requests: 11679rnrnPL/SQL procedure successfully completed.rnrnTO_CHAR(TIME_OF_REQUEST,’YYYYMMDD-HH24MISS’) COUNT(*)rnu002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002d- u002du002du002du002du002du002du002du002drn20160530-154401 25rn20160530-154402 25rn20160530-154403 25rn20160530-154404 25rn20160530-154405 25rn20160530-154406 25rn20160530-154407 25rn20160530-154408 25rn20160530-154409 25rn20160530-154410 25rn10 rows selected.rnrnSQLu0026gt;u003c/preu003e
Conculsion
u003cpreu003eWe succesfully created a mechanism to both restrict and optimize the number of times the outbound service has been called!u003c/preu003e