How do you get a Oracle Sequence value with JPA?

I have to say, JPA 2.0 is pretty awesome – it does a great job of Entity Relation mapping. Overall I really like the ORM framework and I’m glad its built into Java now. However, its by far not a perfect solution to all database related tasks. For example, some of the generated SQL isn’t the best. But that can easily be solved by writing your own SQL using NamedQueries.

Another common task is to generate records with primary keys that are sequenced based. This is easily accomplished by using the @GeneratedValue tag and a @SequenceGenerator

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SeqGen")
@SequenceGenerator(name = "SeqGen", sequenceName = "SAMPLE_SEQUENCE", allocationSize = 1)

But what if you wanted to generate a value that is based off a Sequence? You don’t want to tie this value to a Primary Key, instead maybe you want to populate another field with it? Or generate a confirmation id, etc.

As far as I know, there is no annotation that will query a Sequence and simply return its value. I ended up using a query to return the next Sequence value:

Query q = em.createNativeQuery("SELECT SAMPLE_SEQUENCE.nextval from DUAL");
BigDecimal result=(BigDecimal)q.getSingleResult();   
return result.longValue();

Anyone know of a better way to accomplish this?

Oracle Optimizer Statistics

Everyone knows when you have Oracle performance problems, make sure you have indexes in the proper place(s). But it’s also equally important to have the Optimizer Statistics executed & refreshed.

I’ve had some performance problems on a specific table and I couldn’t figure out why the Query Optimizer refused to use an Index I created. I tested this with a simple query:

 SELECT * FROM table where NAME = 'abc' </code>

The Explain plan showed a FULL table scan instead of an Index scan even though there is an index on the ‘name’ column. Well the problem was that I loaded nearly 10,000 test records and never ran a Stat refresh!

You can use this command to refresh all table Statistics:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

Thanks to a good friend who helped me discover this 🙂

Java as Stored Procedures in the Oracle Database

I attended an Oracle Technology Network event in Reston, VA yesterday. Pretty cool event – they talked about the latest 11g related technology stacks. One of the more interesting techniques they talked about was Java Stored Procedures. If you haven’t seen this technique before, it actually loads and compiles Java source code into the Oracle DB.  Once Java source is loaded, you can reference and execute them via Stored Procedures or PL/SQL. They execute entirely inside Oracle’s JVM.

Here’s how you create a Test class with a single main function:

create or replace java source named “Test” as
public class Test{
public static void main(String args[]){
System.out.println(“Hello”);
}
}
/

You can also use the ‘loadjava’ utility to load entire source files(ex: loadjava Test.java).

Now, you need to wrap and expose the Java class via PL/SQL. Create a procedure named ‘TestProc’ as follows:

create or replace procedure TestProc as language java name ‘Test.main(java.lang.String[])’;

Execute the Java class:

set serveroutput on
call dbms_java.setoutput(50000);
call TestProc();

This will execute the main function in the Test class and print “Hello”. Now the question is:

Why would you ever want Java inside your Database? Doesn’t this break all sorts of design & logic rules?

The truth is, yes it does break many traditional design rules, mainly the one that says keep data and logic seperate.

However, Java is faster than SQL at certain things. These include analytical processing & almost any type of math. PL/SQL is faster at data manipulation and has direct access to the data. If you need to perform many commands that are performance enhanced by Java, then it may actually make sense to put the Java code inside the database!

The one clear advantage is that your Application will save the time needed to context switch when making a database call and waiting for it to return. Instead, you make 1 PL/SQL call which executes some Java and returns with results.

I wouldn’t go overboard with this technique, but there are some scenarios where it does make sense 🙂