External Tables with 11g JDBC driver

External tables arrived in the Oracle 10g database today I had to give it a test drive on a 11g database with the 11g JDBC driver. In the old days I would use sql*loader to load a flat file into the database and then access the data but with external tables you can avoid that as shown in this very simple demo.

External Data file: mydata.txt

1, 'Pas Apicella'
2, 'Adrian Campanaro'
3, 'Fred Blog'
4, 'Dave Hughes'
5, 'Joe Blow'

1. Create a directory on the server where the file exists

create directory scott_dir as '/home/oracle/pas';

2. Create the external table as shown below

drop table my_emps;

create table my_emps
(empno number,
ename varchar2(20))
organization external
(type oracle_loader
default directory scott_dir
access parameters
(
  fields terminated by ','
  optionally enclosed by "'"
  missing field values are null
)
location ('mydata.txt')
);

3. Here is the simple JDBC code which sees it as an ordinary table

 public void run() throws SQLException
 {
   Connection conn = getConnection();
   conn.setAutoCommit(false);
   Statement stmt = null;
   ResultSet rset = null;
   String sql = "select empno, ename from my_emps";
   SQLWarning x = null;
 
   try
   {
     stmt = conn.createStatement();
     rset = stmt.executeQuery(sql);
     
     while (rset.next())
     {
       System.out.println
         (rset.getInt(1) + " : " +
          rset.getString(2));
     }
 

     System.out.println("Successfully read external table");
     conn.commit();
   }
   catch (Exception e)
   {
     e.printStackTrace();
   }
   finally
   {
     if (rset != null)
     {
       rset.close();
     }

     if (stmt != null)
     {
       stmt.close();
     }

     if (conn != null)
     {
       conn.close();
     }
   }
 }