sakthi's blog

How to use Oracle Sequence for auto increment column

While reading through this thread http://forum.sun.com/jive/thread.jspa?threadID=99306 I had to check out how to invoke sequence.nextval for new row being appended.

Here's how i did achieve this:

1. Created a Sequence and a Table:
CREATE SEQUENCE userid_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 NOCACHE;
create table user_seq_test ( seq_col int not null primary key, char_col char(10) );

2. Creating a Page to append rows into this table:
Add a datadource (say ora9i) to access this database table in Servers Window. Create a New Project, drag and drop user_seq_test table onto page. Dropa text field and bind it to user_seq_test.char_col. Add a button for "Append Row". Add a message group to be able to see the error messages on the page, when it happens.

In the prerender, append a row to the dataprovider and get the sequence value for the new row and set it:

try {
    RowKey rk = user_seq_testDataProvider.appendRow();
    user_seq_testDataProvider.setCursorRow(rk);
            
    Connection conn = null ;
    Statement sqlStatement = null ;
    ResultSet rs = null ;
            
    javax.naming.Context ctx = new javax.naming.InitialContext() ;
    DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/ora9i") ;                      
    conn = ds.getConnection() ;                        
    // setup the connection             
    conn.setAutoCommit(true) ;                         
    // execute the query            
    sqlStatement = conn.createStatement() ;
    rs = sqlStatement.executeQuery("select userid_seq.NEXTVAL from dual" ) ;
    rs.next() ;
    int nextvalue = rs.getInt(1) ;
    user_seq_testDataProvider.setValue("seq_col", new Integer(nextvalue));
    rs.close();
    sqlStatement.close();
    conn.close();
        } catch (Exception ex) {
            error(ex.getMessage());
            log("Error Description", ex);
        }

In the append Row Button action:

try {
            user_seq_testDataProvider.commitChanges();
            user_seq_testDataProvider.refresh();
        } catch (Exception ex) {
            log("Error Description", ex);
            error(ex.getMessage());
        }

So, when we want to use Sequence.NEXTVAL, you need to fetch the value and set this value on the auto-increment column in the appended row.

We could use similar technique for calling functions and stored procedures.

Posted at 04:01PM Jun 23, 2006 by sakthi in Sun  |