Monday, 15 October 2007

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();
}
}
}



3 comments:

Nigel said...

A pedant writes: External tables were available in 9i - see http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch03.htm#1004919

Regards Nigel

Pas Apicella said...

Yeah correct I always thought it first came into 10g but as you pointed out it was 9i.

Thanks.

OnlinePharmacy said...

5PmD1V Your blog is great. Articles is interesting!