Loading and Registering Drivers

In our sample project, we had to add the Oracle JDBC thin driver to our classpath (see here for more information). This driver is a Type 4 driver. Type 4 drivers are portable because they are written completely in Java and are ideal for applets because they do not require the client to have an Oracle installation. For a description of other driver types, click here.

Here is the code that loads the driver and registers it with the JDBC driver manager:

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
OR you could use
Class.forName("oracle.jdbc.driver.OracleDriver");.

If there are errors loading or registering the driver, the first method throws an SQLException and the second throws a ClassNotFoundException. If you are unfamiliar with exceptions, click here to learn what they are and how to handle them.

The purpose of a driver manager is to provide a unified interface to a set of drivers (recall that JDBC allows for simultaneous access to multiple database management systems). It acts as a "facade" to the drivers, which are themselves interfaces, by ensuring that the correct driver function is called. You will discover that no other functions in this tutorial other than those for loading the driver and connecting to a database include the name of the driver or DBMS as an argument. The driver manager automatically handles the mappings from JDBC functions to driver functions.

Connecting to a Database

The DriverManager class provides the static getConnection() method for opening a database connection. Below is the method description for getConnection():

public static Connection getConnection(String url, String userid, String password) throws SQLException

The URL is the DBMS-specific part. For the Oracle thin driver, it is of the form: "jdbc:oracle:thin:@host_name:port_number:sid", where host_name is the host name of the database server, port_number is the port number on which a "listener" is listening for connection requests, and sid is the system identifier that identifies the database server. The URL that we are using is jdbc:oracle:thin:@dbhost.students.cs.ubc.ca:1522:stu, so our connection code is.

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@dbhost.students.cs.ubc.ca:1522:stu", "username", "password");

If you are planning to run your code on your local machine (as opposed to running it off of the department's servers), be sure to change the connection URL from jdbc:oracle:thin:@dbhost.students.cs.ubc.ca:1522:stu to jdbc:oracle:thin:@localhost:1522:stu. Don't forget you will need to tunnel to the department's servers (see here for more information)

The Connection object returned by getConnection() represents one connection to a particular database. If you want to connect to another database, you will need to create another Connection object using getConnection() with the appropriate url argument (consult the driver's documentation for the format of the url). If the database is on another DBMS, then you will also need to load and register the appropriate driver.

To disconnect from a database, use the Connection object's close() method.

Converting between Java and Oracle Datatypes

Generally, Oracle datatypes and host language datatypes are not the same. When values are passed from Oracle to Java and vice versa, they need to be cast from one datatype to the other. The JDBC driver can automatically convert values of Oracle datatypes to values of some of the Java datatypes. The "Using Prepared Statements" section will cover conversions in the other direction.

The table below shows the getXXX() methods that can be used for some common Oracle datatypes. An * denotes that the getXXX() method is the preferred one for retrieving values of the given Oracle datatype. An x denotes that the getXXX() method can be used for retrieving values of the given Oracle datatype. Use your own judgement when deciding on which getXXX() method to use for NUMBER. For example, if only integers are stored in a NUMBER column, use getInt(). For the DATE datatype, if a DATE column only stores times, use getTime(). If a DATE column only stores dates, use getDate(). If the column stores both dates and times, use getTimeStamp(). Note that getString() can be used for all the Oracle datatypes; however, use getString() only when you really want to receive a string.

Char Varchar2 Long Number Integer Float Date Raw Long Raw
getByte() x x x x x x
getShort() x x x x x x
getInt() x x x x * x
getLong() x x x x x x
getFloat() x x x x x x
getDouble() x x x x x *
getBigDecimal() x x x x x x
getBoolean() x x x x x x
getString() * * x x x x x x x
getBytes() * x
getDate() x x x x
getTime() x x x x
getTimestamp() x x x x
getAsciiStream() x x * x x
getUnicodeStream() x x * x x
getBinaryStream() x *
getObject() x x x x x x x x x

Source: Hamilton, Graham, and Rick Cattell. Passing parameters and receiving results. JDBC: A Java SQL API. 10 June 2001.

Creating and Executing Statements

A Statement object represents an SQL statement. It is created using the Connection object's createStatement() method.

// con is a Connection object
Statement stmt = con.createStatement();

The SQL statement string is not specified until the statement is executed.

Executing Inserts, Updates, and Deletes

To execute a data definition language statement (e.g., create, alter, drop) or a data manipulation language statement (e.g., insert, update, delete), use the executeUpdate() method of the Statement object. You usually don't define data definition language statements in a Java program. For insert, update, and delete statements, this method returns the number of rows processed. Here is an example:

// stmt is a statement object
int rowCount = stmt.executeUpdate("INSERT INTO branch VALUES (20, 'Richmond Main', " +
"'18122 No.5 Road', 'Richmond', 5252738)");

Notes:

Executing Queries

To execute a query, use the executeQuery() method. Here is an example:

stmt.executeQuery("SELECT branch_id, branch_name FROM branch " +
"WHERE branch_city = 'Vancouver'");

The executeQuery() method returns a ResultSet object, which maintains a cursor. excuteQuery() never returns null. Cursors were invented to satisfy both the SQL and host programming languages. SQL queries handle sets of rows at a time, while Java can handle only one row at a time. The ResultSet class makes it easy to move from row to row and to retrieve the data in the current row (the current row is the row at which the cursor is currently pointing). Initially, the cursor points before the first row. The next() method is used to move the cursor to the next row and make it the current row. The first call to next() moves the cursor to the first row. next() returns false when there are no more rows. getXXX() methods are used to fetch column values of Java type XXX from the current row (you will learn more about the getXXX() methods in the "Converting between Java and Oracle Datatypes" section). For specifying the column, these methods accept a column name or a column number. Column names are not case sensitive, and column numbers start at 1 (column numbers refer to the columns in the result set).

You can refer to the ResultSet API for more information on all the available getter methods. The Java Tutorials page on ResultSets by Oracle may also be helpful.

Here is an example of using a ResultSet object to retrieve the results of a query:

int branchID;
String branchName;
String branchAddr;
String branchCity;
int branchPhone;

. . .

// con is a Connection object
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM branch");

while(rs.next()) {
  branchID = rs.getInt(1);
  branchName = rs.getString("branch_name");
  branchAddr = rs.getString(3);
  branchCity = rs.getString("branch_city");
  branchPhone = rs.getInt(5);
  . . .
}

Checking for Null Return Values

Although the above code snippet did not check for null return values, you should always check for nulls for all nullable columns. If you don't, you may encounter exceptions at runtime. The ResultSet class provides the method wasNull() for detecting fetched null values. It returns true if the last value fetched by getXXX() is null.

There are a few important things to consider when checking for nulls. The SQL NULL value is mapped to Java's null. However, only object types can represent null; primitive types, such as int and float, cannot. These types represent null as 0. Thus, when NULL is fetched, methods like getByte(), getShort(), getInt(), getLong(), getFloat(), and getDouble() return 0 instead of null. Moreover, the getBoolean() method returns false if NULL is fetched. What if the value stored in the database is actually 0 or false? To avoid this problem, use the wasNull() method to check for null values.

You cannot insert null using Statement objects. You have to use PreparedStatement objects instead (see "Using Prepared Statements" and "Inserting Null Values").

Closing Statements

After you are done with a Statement object, you can free up memory by using close() to close the statement. When you close a query statement, the corresponding ResultSet object is closed automatically. A ResultSet can be closed explicitly by calling close().

Using Prepared Statements

A PreparedStatement represents a precompiled SQL statement that contains placeholders to be substituted later with actual values. Being precompiled means that a prepared statement is compiled at creation time. The statement can then be executed and re-executed using different values for each placeholder without needing to be recompiled. Unlike a prepared statement, an SQL statement represented by a Statement object is compiled every time it is executed.

Because PreparedStatement inherits methods from Statement, you can use executeQuery() and executeUpdate() to execute a prepared statement; however, these methods are redefined to have no parameters as you will soon see. You can also use the close() method to close a prepared statement, and the wasNull() method to check for fetched null values.

Similar to a Statement object, a PreparedStatement is created using the Connection object returned by getConnection(). However, unlike a Statement object, the SQL statement is specified when the prepared statement is created and not when it is executed. Here's an example of creating a prepared statement:

// con is a Connection object created by getConnection()
// note that there is no 'd' in "prepare" in prepareStatement()
PreparedStatement ps = con.prepareStatement("UPDATE branch SET " +
  "branch_addr = ?, branch_phone = ? WHERE branch_city = 'Vancouver'");

Each placeholder is denoted by a ?. A ? can only be used to represent a column value. It cannot be used to represent a database object, such as a table or column name. To build an SQL statement containing user supplied database object names, you will have to use string routines on the SQL string. You will see an example of in the "Dynamic SQL" section of this tutorial.

The setXXX() methods are used to substitute values for the placeholders. setXXX() accepts a placeholder index and a value of type XXX. The first placeholder has an index of 1. The table below lists the valid setXXX() methods for some of the common Oracle datatypes:

Oracle Datatype setXXX()
CHAR setString()
VARCHAR2 setString()
LONG setString()
NUMBER setBigDecimal()
setBoolean()
setByte()
setShort()
setInt()
setLong()
setFloat()
setDouble()
INTEGER setInt()
FLOAT setDouble()
RAW setBytes()
LONGRAW setBytes()
DATE setDate()
setTime()
setTimestamp()

Unlike getXXX(), the setXXX() methods do not perform any datatype conversions. You must use a Java value whose type is mapped to the target Oracle datatype. Therefore, to input a Java value that is not compatible with the target Oracle datatype, you must convert it to a compatible Java type. The setObject() method can be used to convert a Java value to the format of a JDBC SQL type. JDBC SQL types are constants that are used to represent generic SQL types; they are not actual Java types. Like Java types, JDBC SQL types are also mapped to Oracle datatypes. For information on setObject() see the Java API documentation. Refer to this table to see how Oracle, JDBC, and Java data types are mapped in relation to each other.

Here is an example of using a prepared statement:

// con is a Connection object created by getConnection()
PreparedStatement ps = con.prepareStatement("INSERT INTO branch " +
  "(branch_id, branch_name, branch_city) VALUES (?, ?, 'Vancouver')");

int bid[5] = {1, 2, 3, 4, 5};
String bname[5] = {"Main", "Westside", "MacDonald", "Mountain Ridge", "Valley Drive"};

for (int i = 0; i < 5; i++) {
  ps.setInt(1, bid[i]);
  ps.setString(2, bname[i]);

  ps.executeUpdate();
}

Note: Once the value of a placeholder has been defined using setXXX(), the value will remain in the prepared statement until it is replaced by another value, or when the clearParameters() method gets called.

Inserting Null Values

The setNull() method is used to substitute a placeholder with a null value. setNull() accepts two parameters: the placeholder index and the JDBC SQL type code. SQL type codes are found in the java.sql API. Make sure you refer to this table to ensure that you choose a JDBC SQL type that is compatible with the target Oracle datatype. Alternatively, for setXXX() methods that accept an object as an argument, such as setString(), you can use null directly. The sample program we have provided contains examples of both methods.

Dynamic SQL

As opposed to specifying SQL statements as string parameters to functions, a program can also have stand alone SQL statements. The former method, which is employed by JDBC, is known as a call level interface. Stand alone SQL statements are called embedded SQL statements. Here is an example of an embedded SQL statement:

#sql { INSERT INTO branch VALUES (99, 'Central', '321 W. 5 Ave.', 'Vancouver', 7458222) };

Embedded SQL statements are known as static SQL statements because their structure is determined at precompile time, i.e., before running javac. Java's version of embedded SQL is called SQLJ. A program with embedded SQL statements needs a precompiler (translator), which translates the SQL statements into calls to a DBMS-specific runtime library. In SQLJ, a JDBC driver is used to access a database. For information on how an embedded SQL program is compiled, click here. For information on how a DBMS processes an SQL statement, click here.

The counterpart to embedded SQL is dynamic SQL. Unlike embedded SQL statements, dynamic SQL statements can be built "on the fly" at runtime rather than being defined at precompile time. This means that dynamic SQL does not require a precompiler. You can do almost everything with dynamic SQL as you can with embedded SQL. In addition, dynamic SQL allows you to make queries where the number of select-list items, number of input host variables, and the datatypes of the input host variables are unknown until runtime. Although dynamic SQL is more flexible than embedded SQL, there are benefits to using embedded SQL. For example, in embedded SQL, errors are checked earlier: at precompile time rather than at runtime. A program can have both embedded and dynamic SQL statements. For information on how a dynamic SQL statement is processed, click here.

As you will soon see, JDBC and thus call level interfaces have dynamic SQL capabilities. Like dynamic SQL, call level interfaces pass SQL statements to the DBMS for processing at runtime. However, with JDBC, there is no easy means to specify database object names at runtime because you cannot use ? in a prepared statement to represent a table or column name. You will need to use string manipulation routines to dynamically build the SQL string. The next section provides an example.

Specifying Database Object Names at Runtime

The example below is a function that is called when the OK button is clicked in a window that allows a user to select which columns in the branch table to view. This function constructs the query string based on the columns that were selected. It then calls a function named executeQuery() to execute the query. The variables branchIDCBox, branchNameCBox, branchAddrCBox, branchCityCBox, and branchPhoneCBox are check boxes that allow the user to select the branch id, branch name, branch address, branch city, and branch phone columns respectively (these variables are declared somewhere else in the program). For example, if the user wants to see the branch name and branch city columns, he/she would select the branchNameCBox and branchCityCBox. It is not necessary for you to know Swing in order to understand this example. The point of this example is to help you understand the capabilities of dynamic SQL. The code is pretty much self-explanatory.

/*
* Constructs the query based on which columns were selected.
* Returns true is the query is valid and successfully executed by
* the executeQuery() method; otherwise returns false.
*/
private boolean constructQuery() {
  StringBuffer statement = new StringBuffer("SELECT");

  int numBoxesSelected = 0;

  if (branchIDCBox.isSelected()) {
    statement.append(" branch_id");
    numBoxesSelected++;
  }

  if (branchNameCBox.isSelected()) {
    if (numBoxesSelected > 0) {
      statement.append(", branch_name");
    } else {
      statement.append(" branch_name");
      numBoxesSelected++;
    }
  }

  if (branchAddrCBox.isSelected()) {
    if (numBoxesSelected > 0) {
      statement.append(", branch_addr");
    } else {
      statement.append(" branch_addr");
      numBoxesSelected++;
    }
  }

  if (branchCityCBox.isSelected()) {
    if (numBoxesSelected > 0) {
      statement.append(", branch_city");
    } else {
      statement.append(" branch_city");
      numBoxesSelected++;
    }
  }

  if (branchPhoneCBox.isSelected()) {
    if (numBoxesSelected > 0) {
      statement.append(", branch_phone");
    } else {
      statement.append(" branch_phone");
      numBoxesSelected++;
    }
  }

  statement.append(" FROM branch");

  if (numBoxesSelected > 0) {
    eturn executeQuery(statement.toString());
  }

  return false;
}

Transactions

Transaction Processing

Any changes made to a database are not necessarily made permanent, right away. If they were, a fatal error halfway through the update would leave the database in an inconsistent state (we will learn more about this in class and in the textbook). For example, when you transfer money from one bank account to another, you do not want the bank to debit one account and not credit the other because of an error (unless the error benefits you). You want the debit and credit SQL calls to be treated as one atomic unit of work (all or none principle), so either both the debit and credit are canceled if an error occurs, or both the debit and credit are made permanent if the transfer is successful. Thus you should group your SQL statements into transactions in order to ensure data integrity.

To make changes to the database permanent and thus visible to other users, use the Connection object's commit() method like this:

// con is a Connection object
con.commit();

By default, data manipulation language statements, such as insert, delete, and update, issue an automatic commit. You should disable auto commit mode so that you can group statements into transactions. To disable auto commit, use the setAutoCommit() method like this:

con.setAutoCommit(false);

When you disable auto commit, you must manually issue commit() after each transaction. However, if you do not issue a commit or rollback for the last transaction and auto commit is disabled, then a commit is issued automatically for you when the connection is closed. As a general rule, commit often. This is an analogous to the "save often" rule used when editing any file.

To enable auto commit, use setAutoCommit(true).

Note: Data definition statements, such as create, drop, and alter, issue an automatic commit regardless of whether or not auto commit is off or on. This means that everything after the last commit or rollback is committed (you'll encounter rollback next).

To undo changes made to the database by the most recently executed transaction, use the Connection object's rollback() method like this:

con.rollback();

rollback() is usually used in error handling code.

Structuring Transactions

This section briefly discusses two ways that transactions could be structured in a program. The first way represents related transactions as methods in a class. The second way represents each transaction as a class. The first way is sufficient for transactions that are relatively short and only access a single table, such as those in the sample program above. The sample program we provide also uses this method. However, there are a few problems with this method. For one thing, it is not easy to group transactions that access different and/or multiple tables. Should a transaction that access both the branch and driver tables be placed in the branch class or the driver class? Another problem is that existing classes may have to be updated to accommodate new transactions. Can you think of any more pros and cons of this method?

The second method is a more object-orientated solution. It decouples objects that use the transaction from the details of the transaction itself. The details of each transaction is encapsulated and hidden. Existing classes do not have to be modified when you add a new transaction. For example, you could declare an abstract class or interface called Transaction that contains the method execute() whose implementation is empty. You could then define concrete transaction classes that implement this method. To execute a transaction, you would simply call the transaction's execute() method. Parameters for the transaction could be passed in when the transaction is constructed. Another benefit is that this method supports undo, redo, and logging. For this to work, a transaction would have to store the current state before executing the transaction. For multiple undos, you could construct a history list of transactions.

Batch Updates

When you are making many different types of updates to the database (i.e., updating, deleting, or inserting rows), sending each statement individually can take a long time due to the number of network trips and communication overhead. In these situations, you may want to consider batching (i.e., grouping) these updates together to send to the database. Note that if you choose to perform a batch update, you will only see a performance improvement with prepared statements because Oracle does not implement true batch updates for statements.

Using a Statement Object to Perform a Batch Update

try {
  . . .
  // con is a Connection object
  con.setAutoCommit(false);

  // stmt is a Statement object
  stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
   ResultSet.CONCUR_READ_ONLY);

  stmt.addBatch("INSERT INTO branch VALUES (10, 'Main', "+
  "'1234 Main St.', 'Vancouver', 5551234)");
  stmt.addBatch("INSERT INTO branch VALUES (20, 'Richmond', "+
  "'23 No.3 Road', 'Richmond', 5552331)");
  stmt.addBatch("DELETE FROM branch WHERE branch_id = 50");

  int[] updateCounts = stmt.executeBatch();
  con.commit();

  ResultSet rs = stmt.executeQuery("SELECT * FROM branch");
  . . .
} catch (BatchUpdateException ex) {
  System.out.println("Message: " + ex.getMessage());

  int[] updateCounts = ex.getUpdateCounts();

  System.out.println("Update Counts:");

  for (int i = 0; i < updateCounts.length; i++) {
    System.out.println(updateCounts[i]);
  }
} catch (SQLException ex) {
  System.out.println("Message: " + ex.getMessage());
}

Since auto commit is enabled by default, you should disable it to give yourself control over the transactions so if an error occurs, you can rollback all executed statements in the batch. If auto commit is not disabled, each statement is committed automatically after it is executed, so a rollback will only rollback a single statement.

The Statement method addBatch() is used to add an SQL statement to the batch.

The method executeBatch() submits the batch of statements to the database for execution. The statements are executed in the order that they were added to the batch. executeBatch() returns an array of integers representing the status of each statement execution. The array is ordered according to the order in which the statements were added. This method throws a BatchUpdateException (a subclass of SQLException) when any statement in the batch fails to execute properly.

The BatchUpdateException method getUpdateCounts() returns an array of integers containing status information. After the batch is executed, you can reuse the Statement object for more batch updates, nonbatch updates, or queries.

If you need to clear a batch, you can use the clearBatch() method. The batch is automatically cleared after executeBatch() is executed.

Using a PreparedStatement Object to Perform a Batch Update

try {
  . . .
  // con is a Connection object
  con.setAutoCommit(false);

  // ps is a PreparedStatement object
  ps = con.prepareStatement("UPDATE branch "+
  "SET branch_phone = ? WHERE branch_id = ?");

  ps.setInt(1, 6042552715);
  ps.setInt(2, 10);
  ps.addBatch();

  ps.setInt(1, 6047330880);
  ps.setInt(2, 20);
  ps.addBatch();

  int[] updateCounts = ps.executeBatch();
  con.commit();
  . . .
} catch (BatchUpdateException ex) {
  System.out.println("Message: " + ex.getMessage());

  int[] updateCounts = ex.getUpdateCounts();

  System.out.println("Update Counts:");

  for (int i = 0; i < updateCounts.length; i++) {
    System.out.println(updateCounts[i]);
  }
} catch (SQLException ex) {
  System.out.println("Message: " + ex.getMessage());
}

The batch uses the same SQL statement but with different placeholder values. Contrast this with the example performing batch updates with a Statement object where multiple SQL statements are batched. Also, unlike the other example, the addBatch() method does not contain an SQL statement argument because it was specified when the PreparedStatement object was created.

Error Handling

Exceptions

When a database access error occurs, such as a primary key constraint violation, an SQLException object is thrown. You must place a try{} block around database access functions that can throw an SQLException and a corresponding catch{} block after the try{} block to catch these exceptions. Alternatively, you can place a throws SQLException clause in the function header. For example, the registerDriver() method can throw an SQLException, so you must do one of the following:

try {
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
} catch (SQLException ex) {
  . . .
}

OR

public void someFunction() throws SQLException {
  . . .
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  . . .
}

Note: If you don't catch or throw SQLExceptions, you won't be able to compile your code.

Within the catch{} block, you can obtain information on the SQLException that was just caught by calling its methods. The getMessage() method returns the error message. If the error originated in the Oracle server instead of the JDBC driver, then the message is prefixed with ORA-#####, where ###### is a five digit error number. For information on what a particular error number means, check out the Oracle Error Reference at the Oracle Documentation Library. The error reference manual describes the cause of the error and suggests a course of action to take in order to solve the problem. Another useful method is printStackTrace(), which prints the stack trace to the standard error stream so that you can find out which functions were called prior to the error.

Warnings

When a database access warning occurs, an SQLWarning object is thrown. SQLWarning is a subclass of SQLException. However, unlike regular exceptions, SQLWarnings do not stop the execution of an application; you do not need to place a try{} block around a method that can throw an SQLWarning. SQLWarnings are actually "silently" attached to the object whose method caused it to be thrown. If more than one warning occurred, the warnings are chained, one after the other. The following code retrieves the warnings from a ResultSet object and prints each warning message:

// rs is a ResultSet object
SQLWarning wn = rs.getWarnings();

while (wn != null) {
  System.out.println("Message: " + wn.getMessage());

  // get the next warning
  wn = wn.getNextWarning();
}

SQLWarnings are actually rare. In fact, the Oracle JDBC drivers generally do not support them. The most common warning is data truncation. When a value read is truncated, a DataTrunction warning is reported (DataTrunction is a subclass of SQLWarning). When a value written is truncated, a DataTrunction exception (not warning) is thrown. The methods in this class allow you to find out the number of bytes actually transferred and the number of bytes that should have been transferred (see the API for details).

Sequences

In most cases, tuples in relations are uniquely identified by numbers. Branches in our branch relation are uniquely identified by the branch id. So far, we have been entering these numbers ourselves. However, Oracle provides a function which can automatically generate unique numbers. This is done with the following command:

CREATE SEQUENCE <sequence_name>

Therefore, to create a sequence called branch_counter, we specify:

CREATE SEQUENCE branch_counter

We normally define sequences after we define the relation which uses the sequence, but before we insert any tuples into that relation. Sequences are normally not created in a Java program. To start generating sequence numbers, we do the following in our INSERT statements:

INSERT INTO BRANCH (branch_id, branch_name, branch_addr, branch_city, branch_phone) VALUES (branch_counter.nextval, 'West', '7291 W. 16th','Coquitlam', 5559238)

Every time the NEXTVAL variable is accessed, the sequence number corresponding to branch_counter increases by 1. Therefore, the sequence numbers generated by branch_counter for branch_id are 1, 2, ... (Note: multiple accesses to NEXTVAL within the same SQL statement result in the same value).

NEXTVAL can be used only in the following cases:

A sequence does not necessarily have to increment by 1 and start at 1. We have the following options:

START WITH <integer>
INCREMENT BY <integer>
MAXVALUE <integer>
MINVALUE <<integer>
CYCLE | NOCYCLE
ORDER | NOORDER

The semantics of these options should be self-explanatory. To illustrate:

CREATE SEQUENCE branch_counter
START WITH 10
INCREMENT BY 2
MAXVALUE 20
CYCLE

results in the sequence: 10, 12, 14, 16, 18, 20, 10, 12 ...

Of course, if we use sequences for primary key fields, Oracle will not allow the CYCLE option to be part of the definition of the sequence.

Another useful variable is the CURRVAL variable, which returns the most recently generated value by NEXTVAL.

Here is an example of the use of CURRVAL:

SELECT *
FROM BRANCH
WHERE branch_id = branch_counter.currval

which selects the most recently generated branch record.

To alter or delete sequences, we use the commands:

ALTER SEQUENCE <sequence-name> [<sequence options>]

and

DROP SEQUENCE <sequence-name>

respectively.

For example, to alter the branch counter sequence to increment by 100, to a maximum value of 1000:

ALTER SEQUENCE branch_counter
INCREMENT BY 100
MAXVALUE 1000

The only option that we cannot alter after a sequence has been created is START WITH. To change the START WITH value, we would have to delete the sequence and create it again with the new value.

To delete the branch_counter sequence:

DROP SEQUENCE branch_counter

You can query the settings of your sequences by referencing the SEQ table, which contains fields such as SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, LAST_NUMBER, INCREMENT_BY, and C (for cycle).

Below is an example of how to use a sequence in Java. A branch tuple is inserted and then returned in the query that follows.

// stmt is a Statement object
// branch_counter is a sequence
stmt.executeUpdate("INSERT INTO branch VALUES (branch_counter.nextval, 'West', " +
  "'7291 W.16th', 'Coquitlam', 5559238)");

ResultSet rs = stmt.executeQuery("SELECT * FROM branch WHERE " +
  "branch_id = branch_counter.currval");

Note: Not all DBMSs support sequences.

Resources