The Oracle Lite Blog

The Rekounas Blog about Oracle Lite and Mobile Server Technologies and Solutions

The Oracle Lite Blog header image 2
← Oracle OpenWorld 2009 Live Stream Gaining performance out of Queue-Based refresh →

How to create an auto increment field in Oracle Lite

October 24th, 2009 · 10 Comments

I bet you thought you could make an auto increment field in Oracle Lite the same way you make it in Oracle right? Well, you can’t do it the same way because Oracle Lite does not support PL/SQL. It does however support C, C++, C#, and Java stored procedures. We will review creating auto-increment fields in both the Enterprise and the Lite databases.

Lets review the way you create a auto-increment field in Oracle:

//Step 1:
CREATE TABLE TEST_TABLE (
COL1 NUMBER,
COL2 VARCHAR2(20),
CONSTRAINT TEST_TABLE_PK PRIMARY KEY (COL1)
);


//Step 2:
CREATE SEQUENCE TEST_SEQ
START WITH 1
INCREMENT BY 1;


//Step 3:
CREATE TRIGGER TEST_TRIGGER
BEFORE INSERT ON TEST_TABLE
FOR EACH ROW
BEGIN
SELECT TEST_SEQ.NEXTVAL INTO :NEW.COL1 FROM DUAL;
END;

And we are done.

Now here are the steps for doing it in Oracle Lite:

//Step 1: Create and Compile the Java Code.  Call the Java file JavaTrigger.java.  You may also have to compile using JDK 1.4 as even Oracle 10g is known not to support 1.5 yet.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JavaTrigger {
   public static void autoIncrement(Connection conn, int newNum[]) throws SQLException {
      try {
         Statement stmt = null;
         ResultSet rs = null;
         stmt = conn.createStatement();
         rs = stmt.executeQuery(”SELECT TEST_SEQ.NEXTVAL FROM DUAL”);
         if (rs.next()) {
            newNum[0] = rs.getInt(1);
         }
      } catch (SQLException e) {
         System.err.println(e.getMessage());
      }
   }
}


//Step 2: Load the file into the database
CREATE JAVA CLASS USING BFILE ('C:\OracleLiteJava\StoredProcedure\Sample\classes\','JavaTrigger.class');


//Step 3: Create a Stored Procedure in the database
CREATE OR REPLACE PROCEDURE AUTO_INCREMENT(NEW_ID IN OUT INT) AS LANGUAGE JAVA NAME 'JavaTrigger.autoIncrement(java.sql.Connection, int[])’;


//Step 4:
CREATE OR REPLACE TRIGGER TEST_TABLE_INSERT_TRIG BEFORE INSERT ON TEST_TABLE FOR EACH ROW AUTO_INCREMENT( NEW.COL1);


//Working Example:
SQL> INSERT INTO TEST_TABLE( COL2) VALUES ('GREG');
1 row(s) created
SQL> DIR TEST_TABLE;
COL1 | COL2
-----+-----
6340 | GREG

For more documentation on Stored Procedures in Oracle Lite, check out the online documentation here.

Tags: Oracle Lite

10 responses so far ↓

Leave a Comment