Java stored procedure in Oracle

Recently, i got a very interesting project from a client. The guy wanted me to write a script which is fired at specific instances and sends SMS to the users. The interesting part was that neither the source code of the front-end application was available nor the interface. Only one thing was given and that was the database (now it's funny for me to see a person thinking that it's not safe to give the source code of the front-end and yet give database.. but clients are this way sometimes) .. So, i thought that the only approach left is to use Oracle triggers but obviously, one can't send SMS using SQL (atleast not using any method that i know of). So, I thought of using Java stored procedure and calling the procedure from a trigger. Not many people know about this functionality of Oracle that it allows you to write a stored procedure purely in Java language and embed that in the Oracle DB.
For the time being, I am sharing a couple of very nice and easy to learn tutorials on this particular topic. I'll try to write a tutorial on this myself if i get time.

http://www.developer.com/db/article.php/3337411/Oracle-and-Java-Stored-Procedures.htm
http://download.oracle.com/docs/html/A95261_01/jdgtut1.htm

Oracle sequence and cache option

Let's first see what is the syntax of a sequence;

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

In order to get the current value of a sequence, we have currval and in order to get the next value of a sequence, we have nextval.

Let's say we create a sequence using the following syntax;

CREATE SEQUENCE pubnum
    MAXVALUE 1000
    START WITH 1
    INCREMENT BY 1;

now, if I try to get the current and next sequence number using a single query, one might use a query similar to following;

select pubnum.currval, pubnum.nextval from dual;

However, the above query simply increments the sequence and gives you the incremented value for both current value and next value. This shows that we have to use some other mechanism to do this. Fortunately, oracle stores the details of sequences in a table called user_sequences which stores the details of all sequences created. This table has one column by the name of "LAST_NUMBER" which tells you the next number to be generated from the sequence. Now, let's say the last sequence number generated from pubnum sequence 2 and we execute the following query;

select last_number from user_sequences where sequence_name = 'PUBNUM';

The above should return 3, however, the above returns 21 which is surprising. The reason is hidden in the create statement of the sequence. Remember, the CACHE option of the sequence create statement. The CACHE option pre-allocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, Oracle reads another set of numbers into the cache. However, when you don't mention any value for the CACHE option, the default value of 20 is used. Now, read the bold part again. What Oracle does is that it stores the next 20 numbers in cache(assuming cache size is 20). The current value of the sequence was 2, however, due to the 20 numbers placed in cache, the last_number in the user_sequences table gave us 21 instead of 3. The value of last_number in the user_sequences will remain 21 till the actual sequence reaches 20. As soon as the current value of the sequence becomes 21, the last_number will start pointing to 41 (it will prefetch next 20 numbers). Hence, if you want this to work, you have to mention NOCACHE option during the sequence creation. Look at the following updated syntax;

CREATE SEQUENCE pubnum1
    MAXVALUE 1000
    START WITH 1
    INCREMENT BY 1

    NOCACHE;

Let's suppose the current value of the pubnum1 is 3. If you run the following query;

select last_number from user_sequences where sequence_name = 'PUBNUM1';

Now, the above will return 4 which is the correct value. So, in order to get current value and next value of the sequence using a single query, we can use the following query;


select last_number - 1 as "Current value", last_number as "Next Value" from user_sequences where sequence_name = 'PUBNUM1';