MyBatis and Oracle Database

While I was using MyBatis with the Oracle Database, I ran into two problems:

1. Generating primary keys when inserting database rows
2. Mapping Java boolean type to a database type

The next sections describe how I solved these problems.

Inserting new rows

Unlike other databases like MySQL, the Oracle database does not provide the functionality to generate primary keys automatically when inserting rows. Normally, I would create a sequence together with a trigger in the Oracle database. The sequence is a thread-safe number generator, and the trigger is custom code that is called on certain type of events. In this case an "insert" event, which will update the row with the primary key.

With MyBatis, we still need the sequence. The trigger functionality is provided by MyBatis. Let's get started by creating an example class in Java. I left the "getters" and "setters" out, but we still need them in the actual code ofcouse.

public class Client {
    private Integer id;
    private String name;

    // Setters and Getters for the attributes here.
}

Next, we have to prepare the database by create a table that corresponds with the "Client" class, and a sequence.

CREATE TABLE client (
    id INTEGER primary key,
    name VARCHAR2(100)
);

CREATE SEQUENCE client_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

MyBatis uses "mapper" interfaces (defined and provided by us) to map the class to actual SQL-statements. Additional configuration can be provided by either XML, or annotations. When the configuration is simple, I prefer using annotations. Complex configurations are often only possible by using XML. The following mapper interface provides mapping for inserting the "Client" class. The interface is only used by MyBatis, we don't have to provide an actual implementation of it.

public interface ClientMapper {
  @Insert("INSERT INTO client (id, name) VALUES (#{id}, #{name})")
  @SelectKey(
            keyProperty = "id",
            before = true,
            resultType = Integer.class,
            statement = { "SELECT client_seq.nextval AS id FROM dual" })
  public Integer insertClient(Client client);
}

The "@SelectKey" annotation tells MyBatis to get the primary key value from the sequence, and use it as primary key when executing the insert statement.

Mapping boolean types

The final example illustrates how we deal with boolean types when using MyBatis. We extend the "Client" class with a boolean attribute "admin".

public class Client {
    private Integer id;
    private String name;
    private boolean admin;

    // Setters and Getters for the attributes here.
}

Add a "VARCHAR(1)" column that acts like a boolean attribute to the corresponding "Client" database table. The column value "Y" corresponds to boolean value true, and "N" to boolean false.

CREATE TABLE client (
    id INTEGER primary key,
    name VARCHAR2(100),
    admin varchar(1)
);

MyBatis doesn't know how the Java boolean is mapped to a database type, as there's no primitive database boolean type. We have to provide MyBatis with a converter, which implements the "TypeHandler" interface. This converter will then be used to convert a Java boolean to a database type, and vice versa.

package com.javaeenotes;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

public class BooleanTypeHandler implements TypeHandler {

    private static final String YES = "Y";
    private static final String NO = "N";


    @Override
    public Boolean getResult(ResultSet resultSet, String name)
            throws SQLException {

        return valueOf(resultSet.getString(name));
    }


    @Override
    public Boolean getResult(CallableStatement statement, int position)
            throws SQLException {

        return valueOf(statement.getString(position));
    }


    @Override
    public void setParameter(
            PreparedStatement statement,
            int position,
            Boolean value,
            JdbcType jdbcType) throws SQLException {

        statement.setString(position, value.booleanValue() ? YES : NO);
    }


    private Boolean valueOf(String value) throws SQLException {

        if (YES.equalsIgnoreCase(value)) {
            return new Boolean(true);
        } else if (NO.equalsIgnoreCase(value)) {
            return new Boolean(false);
        } else {
            throw new SQLException("Unexpected value "
                    + value
                    + " found where "
                    + YES
                    + " or "
                    + NO
                    + " was expected.");
        }
    }
}

Now, configure MyBatis to use the type handler for booleans in the mapper interface where needed.

    @Insert("INSERT INTO client (id, name, admin) VALUES (#{id}, #{name}, #{admin,typeHandler=com.javaeenotes.BooleanTypeHandler})")
    @SelectKey(
            keyProperty = "id",
            before = true,
            resultType = Integer.class,
            statement = { "SELECT client_seq.nextval AS id FROM dual" })
    public Integer insertClient(Client client);

    @Select("SELECT * FROM client WHERE id = #{clientId}")
    @Results(
            value = {
                @Result(
                    property = "id",
                    column = "id"),

                @Result(
                    property = "name",
                    column = "name"),

                @Result(
                    property = "admin",
                    column = "admin",
                    typeHandler = BooleanTypeHandler.class)
             })
    public Client getClient(int clientId);

    @Update("UPDATE client SET name = #{name}, admin = #{admin,typeHandler=com.javaeenotes.BooleanTypeHandler} WHERE id = #{id}")
    public void updateClient(Client client);

Adding Oracle JDBC driver to Maven 2 repository

I like my library JAR-files to be managed by Maven 2. It makes life much easier. Sadly, due to the binary license there is no public Maven 2 repository with the Oracle JDBC driver. So we have to add the JDBC driver manually to our local Maven 2 repository.

First, download the latest Oracle JDBC driver here: http://www.oracle.com/technetwork/indexes/downloads/index.html. Do a search on "JDBC" to find the correct download link.

If you're using Maven inside Eclipse like I am, the "mvn" command will not work from the command prompt. We have to install Maven 2 on our system. You can download Maven 2 here: http://maven.apache.org/download.html. Simply unzip, and make sure the "bin" directory is in your command path.

Now, we can install the JAR file with the following command:

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6
  -Dversion=11.2.0.3 -Dpackaging=jar -Dfile=ojdbc6.jar -DgeneratePom=true

Be sure to check the parameters, like the version number.

Finally, we can use the Oracle JDBC driver in our Maven 2 project by adding the following dependency to our "pom.xml" file:

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>

Converting a database CLOB into a String

I've seen code that converts database CLOBs to String by using CharacterStreams and BufferedReaders to read CLOBs in a loop until the CLOB is fully read. A simpler way is using the getSubString() method of the Clob object. While knowing the length or size of the CLOB, you can read the whole CLOB into a String like this:

DataSource ds = ...
Connection c = null;
PreparedStatement s = null;
ResultSet r = null;

try {
  c = ds.getConnection();
  s = c.prepareStatement("SELECT clobColumn FROM someTable");
  r = s.executeQuery();

  while (r.next()) {
    Clob clob = r.getClob(1);

    if (clob != null) {

      if ((int) clob.length() > 0) {
        String s = clob.getSubString(1, (int) clob.length());
        // Do something with string.
      }
    }
   }
} catch (SQLException e) {
  // Handle exception.
} finally {
  // Close Connection, PreparedStatement, and ResultSet.
}

Beware if the data in the CLOB is really large (larger than Integer.MAX_VALUE). In that case, you'll still need a streaming method.

Calling PL/SQL code in Java

In this blogpost, I will show you how to interact with Oracle PL/SQL code in Java. Before we continue with the Java code, let's create a simple Oracle package we can use to test our code.

Compile the following package specification and body in the Oracle database.

Package specification:

create or replace
package test_package as

  procedure test_procedure(v varchar2);

  function test_function(v varchar2) return varchar2;

end test_package;

Package body:

create or replace
package body package test_package as

procedure test_procedure(v varchar2) is
begin
    null;
end;

function test_function(v varchar2) return varchar2 is
begin
    return v;
end;

end test_package;

The package contains a procedure and a function that returns a VARCHAR2 type. We can call the procedure with the following Java code:

DataSource ds = ...
Connection con = ds.getConnection();
CallableStatement cs =
    con.prepareCall("{call test_package.test_procedure(?)}");
cs.setString(1, "Calling test_procedure!");
cs.executeUpdate();
cs.close();

The function is slightly different, because we have to register the type the function returns.

DataSource ds = ...
Connection con = ds.getConnection();
CallableStatement cs =
    con.prepareCall("{? = call test_package.test_function(?)}");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(2, "Calling test_function!");
cs.execute();
String s = cs.getString(1);
cs.close();

The function can also be called in another way. Like this:

DataSource ds = ...
Connection con = ds.getConnection();
PreparedStatement ps =
    con.prepareStatement(
        "SELECT test_package.test_function(?) S FROM dual"
    );
ps.setString(1, "Calling test_function!");
ResultSet r = ps.executeQuery();
String result = null;

if (r.next()) {
    result = r.getString("S");
}

con.close();

This way is similar to executing a standard SQL-statement.

Saving large XMLTypes in Oracle databases

If you try to save a large XMLType that is over 4k characters long, you'll get this error:

ORA-01461:
  can bind a LONG value only for insert into a LONG column

Oracle database has troubles binding large strings. One solution is to use a temporary clob to store the XML data, and bind that to the SQL-statement.

Connection con = ...
String xml = "<test>test</test>";
oracle.sql.CLOB clob = null;

clob = CLOB.createTemporary(con, true,
    CLOB.DURATION_SESSION);
clob.open(CLOB.MODE_READWRITE);
clob.setString(1, xml);
clob.close();
clob.freeTemporary();

PrepareStatement s = con.prepareStatement(
  "INSERT INTO table (xml) VALUES(XMLType(?))"
);

s.setObject(1, clob);

Using this method, you can store large XML-documents as XMLType in the Oracle database. But there is still a limitation with the XMLType. The XMLType cannot store any text-nodes and attributes values larger than 64k. Oracle suggests to use a XMLType CLOB-storage column to store really large XML-documents. But then, we cannot use native XML-parsing functions, which defeats the purpose of the XMLType column...

In newer versions of Oracle XML DB, this limit has been lifted.

References:

• 64k size limit for XML node
  
• Store large XML in Oracle XML DB
  
• What's new in Oracle XML DB?
  

Using Oracle WebLogic deployment plans

As a developer, I often develop applications that have to be deployed and run in different execution environments. Initially, I start out developing for a development environment. Application tests are executed on a testing environment. The end-users can test and accept the system in the acceptance environment. The final environment is the production environment, where the application is actually used by end-users. This software development cycle is called a DTAP-street.

This has consequences on the Java development, configuration, and deployment process. On every execution environment, there are: different systems we have to connect to, different database/JDBC names we have to use, different IP-addresses/ports we can use, etcetera. This affects the way we package our application (WAR, JAR, and EAR). We want to avoid changing code or annotations for every execution environment. This way we don't need a specific tailor made package for every execution environment.

This blogpost is about how to deal with environment dependent parameters like: IP-addresses and JDBC-names. when you use Oracle WebLogic as your application server. We put these parameters in the deployment plan. For every execution environment, we create a specific deployment plan for it. The parameters in the deployment plan will override the default parameters in the application when deployed. This enables us to use the same application package (WAR, JAR, and EAR) for all the different execution environments.

An example application EAR file is created in this blogpost to illustrate the use of a deployment plan. It consists of the following steps:

1. Create an EJB project, a web project, and an EAR project that contains the first two projects
   
2. Use JNDI lookup and resource injection to read out or inject environment parameters from deployment descriptors
   
3. Create an Oracle WebLogic deployment plan to override the parameters in the standard deployment descriptors
   

First, we create an EJB project with the following stateless session bean:

package com.javaeenotes;

import javax.annotation.Resource;
import javax.ejb.Stateless;

@Stateless(mappedName = "ejb/ejbEnv")
public class EjbEnv implements EjbEnvRemote, EjbEnvLocal {
  @Resource
  private String var1;
  
  @Resource
  private int var2;

  public String getVar1() {
    return var1;
  }

  public int getVar2() {
    return var2;
  }
}

Make sure you also implement the local and remote interfaces to expose the "getter" methods. We have two @Resource annotations to mark the variables we are going to inject with parameters in the ejb-jar.xml deployment descriptor.

Now, create the ejb-jar.xml deployment descriptor:

<?xml version="1.0" encoding="UTF-8"?>
<ejb-jar xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns="http://java.sun.com/xml/ns/javaee"
 xmlns:ejb="http://java.sun.com/xml/ns/javaee/ejb-jar_3_0.xsd"
 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
 http://java.sun.com/xml/ns/javaee/ejb-jar_3_0.xsd"
 version="3.0">

  <display-name>env_ejb</display-name>
  <enterprise-beans>
    <session>
      <ejb-name>EjbEnv</ejb-name>

      <env-entry>
        <env-entry-name>
          com.javaeenotes.EjbEnv/var1
        </env-entry-name>
        <env-entry-type>
          java.lang.String
        </env-entry-type>
        <env-entry-value>
          Environment variables from ejb-jar.xml
        </env-entry-value>
      </env-entry>

      <env-entry>
        <env-entry-name>
          com.javaeenotes.EjbEnv/var2
        </env-entry-name>
        <env-entry-type>
          java.lang.Integer
        </env-entry-type>
        <env-entry-value>
          999
        </env-entry-value>
      </env-entry>

    </session>
  </enterprise-beans>
</ejb-jar>

When the stateless session bean is loaded, both parameters will be injected into the attributes of the bean. Notice that we don't need "setter" methods to do this.

Next, create a web project with a simple servlet:

package com.javaeenotes;

import java.io.IOException;

import javax.ejb.EJB;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class WebEnv extends HttpServlet {
  @EJB(name="ejb/ejbEnv")
  private EjbEnvRemote ejbEnv;

  protected void doGet(HttpServletRequest request,
                       HttpServletResponse response)
      throws ServletException, IOException {

    try {
      Context env = (Context) new InitialContext()
        .lookup("java:comp/env");

      String s = (String) env.lookup("webVar1");
      int i = ((Integer) env.lookup("webVar2")).intValue();

      response.getWriter().write(
        "webVar1: " + s + "\n");
      response.getWriter().write(
        "webVar2: " + i + "\n");

      response.getWriter().write(
        "ejbVar1: " + ejbEnv.getVar1() + "\n");
      response.getWriter().write(
        "ejbVar2: " + ejbEnv.getVar2() + "\n");
    } catch (NamingException e) {
      response.getWriter().write("NamingException");
    }
  }
}

This servlet uses JNDI-lookup to read parameters defined in the web.xml deployment descriptor. You can also see that the stateless session bean we created earlier is injected as attribute when this class is instantiated. When this servlet is called, it will print the environment parameters defined in both the ejb-jar.xml and web.xml.

The web.xml deployment descriptor looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns="http://java.sun.com/xml/ns/javaee"
 xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
 http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
 id="env_web" version="2.5">

  <display-name>env_web</display-name>

  <servlet>
    <description></description>
    <display-name>WebEnv</display-name>
    <servlet-name>WebEnv</servlet-name>
    <servlet-class>com.javaeenotes.WebEnv</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>WebEnv</servlet-name>
    <url-pattern>/WebEnv</url-pattern>
  </servlet-mapping>

  <env-entry>
    <env-entry-name>webVar1</env-entry-name>
    <env-entry-type>java.lang.String</env-entry-type>
    <env-entry-value>
      Environment variables from web.xml
    </env-entry-value>
  </env-entry>

  <env-entry>
    <env-entry-name>webVar2</env-entry-name>
    <env-entry-type>java.lang.Integer</env-entry-type>
    <env-entry-value>888</env-entry-value>
  </env-entry>
</web-app>

Finally, package both projects in an EAR file, and deploy it on the Oracle WebLogic application server. Use the browser to view the output of the servlet. It will print:

webVar1: Environment variables from web.xml
webVar2: 888
ejbVar1: Environment variables from ejb-jar.xml
ejbVar2: 999

Viewing the output, we can verify that it works!

The last step is to create a deployment plan "Plan.xml", which we use to override the parameters defined in both deployment descriptors. The example contents of the deployment plan:

<?xml version='1.0' encoding='UTF-8'?>
<deployment-plan xmlns="http://xmlns.oracle.com/weblogic/deployment-plan"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation=
 "http://xmlns.oracle.com/weblogic/deployment-plan
 http://xmlns.oracle.com/weblogic/deployment-plan/1.0/deployment-plan.xsd"
 global-variables="false">
 <application-name>env_ear</application-name>

 <variable-definition>
  <variable>
   <name>WebEnv_Var1</name>
   <value>NEW environment variables from web.xml</value>
  </variable>
  <variable>
   <name>WebEnv_Var2</name>
   <value>800</value>
  </variable>
  <variable>
   <name>EjbEnv_Var1</name>
   <value>NEW environment variables from ejb-jar.xml</value>
  </variable>
  <variable>
   <name>EjbEnv_Var2</name>
   <value>900</value>
  </variable>
 </variable-definition>

 <module-override>
  <module-name>env_ear.ear</module-name>
  <module-type>ear</module-type>
  <module-descriptor external="false">
   <root-element>weblogic-application</root-element>
   <uri>META-INF/weblogic-application.xml</uri>
  </module-descriptor>
  <module-descriptor external="false">
   <root-element>application</root-element>
   <uri>META-INF/application.xml</uri>
  </module-descriptor>
  <module-descriptor external="true">
   <root-element>wldf-resource</root-element>
   <uri>META-INF/weblogic-diagnostics.xml</uri>
  </module-descriptor>
 </module-override>
 <module-override>
  <module-name>env_ejb.jar</module-name>
  <module-type>ejb</module-type>
  <module-descriptor external="false">
   <root-element>weblogic-ejb-jar</root-element>
   <uri>META-INF/weblogic-ejb-jar.xml</uri>
  </module-descriptor>
  <module-descriptor external="false">
   <root-element>ejb-jar</root-element>
   <uri>META-INF/ejb-jar.xml</uri>

   <variable-assignment>
    <name>EjbEnv_Var1</name>
    <xpath>/ejb-jar/enterprise-beans/session/
    [ejb-name="EjbEnv"]/env-entry/
    [env-entry-name="com.javaeenotes.EjbEnv/var1"]/
    env-entry-value</xpath>
    <operation>replace</operation>
   </variable-assignment>

   <variable-assignment>
    <name>EjbEnv_Var2</name>
    <xpath>/ejb-jar/enterprise-beans/session/
    [ejb-name="EjbEnv"]/env-entry/
    [env-entry-name="com.javaeenotes.EjbEnv/var2"]/
    env-entry-value</xpath>
    <operation>replace</operation>
   </variable-assignment>

  </module-descriptor>
 </module-override>
 <module-override>
  <module-name>env_web.war</module-name>
  <module-type>war</module-type>
  <module-descriptor external="false">
   <root-element>weblogic-web-app</root-element>
   <uri>WEB-INF/weblogic.xml</uri>
  </module-descriptor>
  <module-descriptor external="false">
   <root-element>web-app</root-element>
   <uri>WEB-INF/web.xml</uri>

   <variable-assignment>
    <name>WebEnv_Var1</name>
    <xpath>/web-app/env-entry/
    [env-entry-name="webVar1"]/
    env-entry-value</xpath>
    <operation>replace</operation>
   </variable-assignment>

   <variable-assignment>
    <name>WebEnv_Var2</name>
    <xpath>/web-app/env-entry/
    [env-entry-name="webVar2"]/
    env-entry-value</xpath>
    <operation>replace</operation>
   </variable-assignment>

  </module-descriptor>
 </module-override>
 <config-root></config-root>
</deployment-plan>

At the top we define the variables we want to use to override parameters:

<variable-definition>
 <variable>
  <name>WebEnv_Var1</name>
  <value>NEW environment variables from web.xml</value>
 </variable>
 <variable>
  <name>WebEnv_Var2</name>
  <value>800</value>
 </variable>
 <variable>
  <name>EjbEnv_Var1</name>
  <value>NEW environment variables from ejb-jar.xml</value>
 </variable>
 <variable>
  <name>EjbEnv_Var2</name>
  <value>900</value>
 </variable>
</variable-definition>

Then we use the <variable-assignment>-element to specify what we want to override with XPath. If you're not familiar with XPath, you should find a tutorial for explanation. Simply said, XPath makes it possible to "walk" to the element you want to override. The value string between the <xpath>-elements is actually one line. But for this blogpost, I need to split up the string, because otherwise it won't fit the blog.

<variable-assignment>
 <name>WebEnv_Var2</name>
 <xpath>/web-app/env-entry/
 [env-entry-name="webVar2"]/
 env-entry-value</xpath>
 <operation>replace</operation>
</variable-assignment>

This actually means: replace the content of the <env-entry-value>-element where the <env-entry-name> equals "WebVar2", with the value specified as "WebEnv_Var2" in the variable definitions.

If we deploy the same EAR-file, but this time with the deployment plan, the servlet will output:

webVar1: NEW environment variables from web.xml
webVar2: 800
ejbVar1: NEW environment variables from ejb-jar.xml
ejbVar2: 900

The environment parameters defined in the deployment plan successfully override the parameters defined in the deployment descriptors.

References:

• Understanding Oracle WebLogic deployment plans
  
• Deploying with a deployment plan
  

SUN certificates rebranded to Oracle

Oracle has rebranded SUN certificates to Oracle certificates.

Click this link to see how the SUN certification titles have been rebranded under the Oracle certification program.

What this means:

• SUN certificates you're holding will still be valid, and will not expire.
  
• Exam objectives remain unchanged for current certification exams.
  
• Candidates who pass the exam after 1-3 september 2010 will get the Oracle branded certificate.
  

Getting Oracle SQL result set pages

When you want to display the result of a query that returns a large number of rows, it's common to divide the complete result in pages, and display them one page at a time. To offer the user a way of navigating through the pages, there are clickable page numbers or "next"/"previous" buttons at the bottom of the page. This technique is called result set paging.

To implement this efficiently, the paging should also be built into the query. Because it's not efficient when we use a query to return the complete result from the database, and discard rows we don't want to display afterwards.

Let's say that you want to page the following query:

SELECT col1, col2
  FROM example_table
  WHERE col2 = 'some_condition'
  ORDER BY col1 DESC

Before we can divide the result set, we need to number the rows of the result set. The pseudo column "rownum", provided by Oracle databases, numbers the rows of the result. The first row of the result is always 1.

Using this knowledge, we can retrieve the "page" we want using the WHERE clause with the corresponding start row and end row numbers. In the example below, we want to fetch rows from number 201 to 300.

SELECT *
FROM (SELECT r.*, ROWNUM AS row_number
      FROM (SELECT col1, col2
            FROM example_table
            WHERE col2 = 'some_condition'
            ORDER BY col1 DESC) r)
WHERE row_number >= 201 AND row_number <= 300;

The reason why we wrap the original query in another SELECT query, is that the database assigns ROWNUMs before the ordering, which makes our ordering ineffective for paging. To make sure the ROWNUMs are ordered, we retrieve the ROWNUM column in the outer SELECT query. The database will then assign the ROWNUM in the already correctly ordered result set.

This query can be optimized for newer Oracle databases by removing the top outer SELECT clause, while leaving the WHERE clause intact.

SUN/Oracle Certification links

Here are some official links about SUN/Oracle certifications:

• Oracle's Certification Website
  
• Oracle Certification Blog
  

Extend Oracle tablespace files

Sometimes you want to enlarge the tablespace file, when the size is not enough to handle transactions. This is the case when you get an error like:

ORA-01650: Unable to extend rollback segment RBS6
by %s in tablespace ROLLBACKSEGS

In this case, the rollback segment is not large enough to handle the database transaction. Now, you have to check if the autoextensible flag is set or if the datafile is too small.

First, find the correct database file of the rollback segment:

SELECT * FROM dba_rollback_segs;

Use the file ID from the result above to find the correct file on the file system:

SELECT * FROM dba_data_files;

Now enlarge the file:

ALTER DATABASE DATAFILE 'D:\ORACLE\ROLLBACKSEGS01.DBF'
RESIZE 1024m;

or set the autoextend to ON:

ALTER DATABASE DATAFILE 'C:\ORACLE\ROLLBACKSEGS01.DBF'
AUTOEXTEND ON NEXT 256m MAXSIZE 2048m;

MySQLs auto_increment in Oracle

MySQL has a reserved keyword "auto_increment" that can be used in the ID/primary key column of a new table. Whenever a new record is added to the table, this field will be automatically set to an unique incremented integer value.

In the Oracle database, this feature is missing. But we can simulate this feature using the SEQUENCE and TRIGGER functionality provided by Oracle.

A sequence is a sequential value stored in the database. It starts with an initial user supplied value, and can be incremented by an arbitrary value to a given maximum value. The statement to create a sequence for table ATABLE:

create sequence ATABLE_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

Now, we want to hook up a trigger on an INSERT event of the table. Whenever a record is inserted into the table, the current value of the sequence is incremented and set in the primary key field of the inserted record.

create or replace trigger ATABLE_TRIG
before insert on ATABLE
for each row
begin
select atable_seq.nextval into :new.id from dual;
end;

Importing data with SQL*Loader

Oracle created a tool for loading large amounts of data into an Oracle database. The tool is called SQL*Loader and can be run by using the command sqlldr.

In this example, we load data from a Microsoft Excel sheet. Before we can do that, we have to save the sheet as CSV (Comma Separated Values). Next, we define the structure of the data in a CTL-file, which also contains configuration data for the import process.

An example CTL-file (import.ctl):

options (errors=100, discard='import.discards')
load data
infile 'import.csv'
badfile 'import.errors'
insert
into table import_table
fields terminated by ','
optionally enclosed by '"'
(
    COL1,
    COL2
)

In the configuration above, we load the data in the table "import_table". The table has two columns: COL1 and COL2. The CSV-file has two values on every line, which are separated by a comma.

This configuration has two options defined:

• errors=100
  
• discard=import.discards
  

This means that the load is aborted after 100 errors, and the discarded lines are saved in the import.discards.

Other configuration settings:

• infile: the file to load
  
• badfile: the file to save errors when they occur
  
• insert: insert data in empty table. Other modes: append, replace, truncate
  

We can start the load process by executing the following command:

sqlldr <user>/<password>@<host>
       control=import.ctl log=import.log

References:

• http://www.orafaq.com/wiki/SQL*Loader_FAQ
  
• http://psoug.org/reference/sqlloader.html
  

Daily remote sync Oracle refresh group

One way to integrate Oracle systems is to share data between databases. In this example we will create a group of materialized views, which is then daily synced with a remote database.

In this scenario we have a couple of remote tables on an external Oracle database. The tables can be accessed using a database link we create.

create public database link <link_name>  connect to <schema>  using '<tns_service_name>';

The <link_name> variable is the name you want to use for the database link. The <schema> variable is the schema name of the remote database. The <tns_service_name> variable is the TNS-name of the remote database.

We fill in these variables with example data.

create public database link MY_DB_LINK;  connect to REMOTE_SCHEMA  using 'remote_database';

Now we create two materialized views of the two remote tables using the database link we just created.

CREATE MATERIALIZED VIEW m_table1 AS
SELECT col1, col2 FROM table1@remote_database;
CREATE MATERIALIZED VIEW m_table2 AS
SELECT col1, col2 FROM table2@remote_database;

Finally, we create the refresh group using the PL/SQL code below. The materialized views are refreshed every day at 6:00 in the morning.

begin
  dbms_refresh.make(
    name => 'mviews',
    list => 'm_table1,
    m_table2',
    next_date => to_date('06:00:00', 'hh24:mi:ss'),
    interval => 'sysdate+1'
  );
end;

To check if everything works, the following internal tables of Oracle can be queried to see if everything we created registered successfully by Oracle.

• dba_mviews
  
• dba_refresh
  
• dba_refresh_children
  
• dba_jobs
  

Sometimes the job just doesn't want to start. Maybe, the database is configured not allowed to start database jobs. Use the following query to check the number of jobs allowed to run on the database.

select t.value from v$parameter t
where name='job_queue_processes';

The number must be greater than 0. The standard value is 4. You can alter the value using:

alter system set job_queue_processes = 4;

Migrating Oracle database content

There are many times that I have to migrate data from one Oracle database to another.

Depending on the situation, I can choose between two different methods. The first method is to use SQL*Plus to copy data from one remote table to a local table.

copy from <schema>/<password>@<host>INSERT <new_table>Using select * from <old_table>

Instead of INSERT, you can also use: APPEND, CREATE or REPLACE. For more information about the differences, please follow the link at the end of this post.

When multiple tables and/or triggers are involved, I use the second method. The second method uses the exp/imp tools provided by Oracle. First, an export file is created using exp. In this example, I migrate only two tables (table1 and tabl2). You can add more tables if you want.

exp <schema>/<password>@<host>  file=export.dmp log=export.log tables=(table1, table2) rows=yes indexes=no

Finally, we load the export file into the new database. All the new tables and triggers are created automatically. We use imp to do the import.

imp <schema>/<password>@<host> file=export.dmp full=yes

Make sure the exp/imp tools are the same version as the database, or it won't work.

More info can be found here:

• SQL*Plus Copy from command
  
• Oracle Import/Export FAQ
  

Output in MS Excel-format for browser in PL/SQL

This example will show how to output Tab-Separated-Values with Oracle PL/SQL for Microsoft Excel in a browser. This is not exactly JEE, but the main idea can also be applied in Java.

Create a public accessible procedure that outputs Tab-Separated-Values. Make sure the execute rights are granted on user 'PUBLIC' for the procedure, otherwise the procedure is not allowed to be called by public users.

procedure example is
    cursor c_cursor is
        select col1, col2
        from table;

    r_row c_cursor%rowtype;
begin
    -- Print TSV header.
    owa_util.mime_header('application/vnd.ms-excel');

    htp.print('COL1' || CHR(9) ||
              'COL2' || CHR(9) ||
              CHR(13));

    open c_cursor;
    loop
      fetch c_cursor into r_row;
      exit when c_cursor%NOTFOUND;

      -- Print TSV row.
      htp.print(r_row.col1 || CHR(9) ||
                r_row.col2 || CHR(9) ||
                CHR(13));
    end loop;
    close c_cursor;
end example;

The main point in this procedure is to let the browser know we are dealing with output intended for Microsoft Excel. We use this procedure to set the MIME-type in the HTTP-header of the response:

owa_util.mime_header('application/vnd.ms-excel');

The browser will then forward the output to MS Excel for processing.

In the procedure we output the values separated by tabs, which are generated with the PL/SQL function CHR(9). The line is then closed with a new-line using CHR(13).

The output is accessible using the URL structure:

http://<hostname>/pls/portal/<schema>.<package>.<procedure>