An example of how to quickly and easily import data from a delimited text file into a table Oracle, using Java.
1. Create java source with name CSVReader.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CSVReader" AS
import java.lang.*;
import java.io.*;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.CallableStatement;
import oracle.sql.driver.*;
// Read file line by line and write data values into table
public class CSVReader {
// Procedure set encoding for Java = cp1251 (for situations where database character set is AL32UTF8),
// otherwise there not will be read Russian characters
public static void encod()
{
System.setProperty("file.encoding","cp1251");
}
// Function connection
public static Connection getConnection()
throws SQLException {
return DriverManager.getConnection("jdbc:default:connection:");
}
// Procedure imports data values from text file with separator into table
public static void read2table(String dirname, String fileName, String sep, Integer skiplines, String ownername, String tablename)
throws IOException, SQLException {
Connection con = null;
BufferedReader br = null;
ResultSet rs = null;
String line = null;
Statement statement = null;
String stmt = "";
String sqlstr = "";
Integer counter = 1;
Integer colcount = 0;
String deldblquoted = "";
encod();
File csvFile = new File(dirname + "\\" + fileName);
try{
con = getConnection();
br = new BufferedReader(new FileReader(csvFile));
sqlstr = "select count(1) from all_tab_columns where owner = '" + ownername + "' and table_name = '" + tablename + "'";
statement = con.createStatement();
rs = statement.executeQuery(sqlstr);
while (rs.next()) {
// get number of columns in table
colcount = rs.getInt(1);
}
rs.close();
sqlstr = "";
// read line by line
while ((line = br.readLine()) != null) {
// skip lines when set to skiplines
if (counter > skiplines) {
String[] val = line.split(sep);
for (Integer i=0; i<colcount; i++) {
// trim beggining and ending double qoutes from a string
deldblquoted = val[i].replaceAll("^\"|\"$", "");
stmt = stmt + ",'" + deldblquoted + "'";
}
// generating insert-command
sqlstr = "insert into " + ownername + "." + tablename + " values(" + stmt.substring(1, stmt.length()) + ")";
statement = con.createStatement();
// execute insert SQL stetement
statement.executeUpdate(sqlstr);
stmt = "";
statement.close();
}
counter++;
}
} finally {
if (con != null) {
con.close();
if (br != null) {
try {
br.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
};
2. Then create a procedure of “csvreader” for publication the Java call specification, so we can access it via PL/SQL.
CREATE OR REPLACE PROCEDURE csvreader (p_dir_name VARCHAR2, p_file_name VARCHAR2, p_sep VARCHAR2, p_skip_lines NUMBER, p_owner_name VARCHAR2, p_table_name VARCHAR2) AS LANGUAGE JAVA NAME 'CSVReader.read2table( java.lang.String, java.lang.String, java.lang.String, java.lang.Integer, java.lang.String, java.lang.String)';
3. Grant the Oracle JVM the relevant filesystem permissions.
DECLARE l_user VARCHAR2(30) := '<user_name>'; BEGIN DBMS_JAVA.grant_permission(l_user, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete'); DBMS_JAVA.grant_permission(l_user, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', ''); DBMS_JAVA.grant_permission(l_user, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', ''); DBMS_JAVA.grant_permission(l_user, 'SYS:java.util.PropertyPermission', 'file.encoding', 'write' ); END; /
4. Create test table.
CREATE TABLE CSVTEST ( IP1 VARCHAR2(18 BYTE), IP2 VARCHAR2(18 BYTE), UN1 VARCHAR2(10 BYTE), UN2 VARCHAR2(10 BYTE), CN VARCHAR2(3 BYTE), COUNTRY VARCHAR2(50 BYTE) );
5. Create file «d:\newfile.txt» with data.
"1.0.0.0","1.0.0.255","16777216","16777471","AU","Australia" "1.0.1.0","1.0.3.255","16777472","16778239","CN","China" "1.0.4.0","1.0.7.255","16778240","16779263","AU","Australia" "1.0.8.0","1.0.15.255","16779264","16781311","CN","China" "1.0.16.0","1.0.31.255","16781312","16785407","JP","Japan" "1.0.32.0","1.0.63.255","16785408","16793599","CN","China" "1.0.64.0","1.0.127.255","16793600","16809983","JP","Japan" "1.0.128.0","1.0.255.255","16809984","16842751","TH","Thailand" OR 1.0.0.0,1.0.0.255,16777216,16777471,AU,Australia 1.0.1.0,1.0.3.255,16777472,16778239,CN,China 1.0.4.0,1.0.7.255,16778240,16779263,AU,Australia 1.0.8.0,1.0.15.255,16779264,16781311,CN,China 1.0.16.0,1.0.31.255,16781312,16785407,JP,Japan 1.0.32.0,1.0.63.255,16785408,16793599,CN,China 1.0.64.0,1.0.127.255,16793600,16809983,JP,Japan 1.0.128.0,1.0.255.255,16809984,16842751,TH,Thailand
6. Execute our procedure and import data values from newfile.txt into table CVSTEST.
begin csvreader( p_dir_name => 'd:', p_file_name => 'newfile.txt', p_sep => ',', p_skip_lines => 0, p_owner_name => '<owner_name>', p_table_name => 'CSVTEST'); end; /
7. Now check that we imported in the table CSVTEST.
select * from CSVTEST;
SoulUran 