Import data values from file with separator using java in Oracle PL/SQL

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;

 

Реклама

Импорт из ActiveDirectory в Oracle

Подключиться из Oracle к AD возможно с помощью пакета DBMS_LDAP (доступ по Lightweight Directory Access Protocol).

Создадим таблицу для регистрации данных из AD.

CREATE TABLE USERS_AD
( CN          VARCHAR2(256 BYTE),
JOB_TITLE   VARCHAR2(256 BYTE),
DEPARTMENT  VARCHAR2(256 BYTE),
LOGIN       VARCHAR2(256 BYTE),
STATUS      VARCHAR2(256 BYTE),
DN_SHORT    VARCHAR2(128 BYTE),
DN          VARCHAR2(512 BYTE)
);

Создадим процедуру извлечения данных из AD и регистрации их в таблице USERS_AD.

CREATE OR REPLACE procedure AD_TO_DB
AS

ldap_server Varchar2 (256);
ldap_port Varchar2 (256);
ldap_dn Varchar2 (256);
ldap_user Varchar2 (256);
ldap_password Varchar2 (256);
ldap_base Varchar2 (256);
ldap_session dbms_ldap.session;
retval PLS_INTEGER;
ldap_message dbms_ldap.message;
ldap_attrs dbms_ldap.string_collection;
ldap_entry dbms_ldap.message;
ldap_attr_name VARCHAR2(256);
ldap_ber_elmt dbms_ldap.ber_element;
ldap_attr_index PLS_INTEGER;
entry_index PLS_INTEGER;
attr_index PLS_INTEGER;
i PLS_INTEGER;
ldap_vals dbms_ldap.string_collection;
ldap_log Varchar2 (256);
ldap_cn Varchar2 (256);
ldap_dep Varchar2 (256);
ldap_title Varchar2 (256);
ldap_stat Varchar2 (128);
ldap_dn_short Varchar2 (128);

begin

execute immediate 'truncate table USER_FROM_AD';
commit;

retval := -1;

--Подключение к серверу AD
ldap_server := 'ldap.terra.int'; --указать ваш dns или ip сервера AD
ldap_port := '389'; --by default TCP port 389
ldap_session := dbms_ldap.init(hostname => ldap_server,
portnum => ldap_port);
dbms_output.put_line(RPAD('LDAP session ',25,' ') || ': ' || RAWTOHEX(SUBSTR(ldap_session,1,8)) || '(returned from init)');

--Авторизация в AD
ldap_user := 'cn=droid,ou=specials,dc=terra,dc=int'; --указать пользователя домена, который имеет доступ к AD
ldap_password := 'xxxxxxx';
--запускаем простую аутентификацию к AD по имени пользователя и ввода пароля
retval := dbms_ldap.simple_bind_s(ld => ldap_session,
dn => ldap_user,
passwd => ldap_password);
dbms_output.put_line(RPAD('LDAP User ',25,' ') || ': ' || ldap_user);
dbms_output.put_line(RPAD('LDAP Pass ',25,' ') || ': ' || ldap_password);

dbms_ldap.use_exception := TRUE;

--Просмотр аттрибутов по ветке AD
ldap_base := 'ou=regions,ou=company users,dc=terra,dc=int'; --интересующая ветка AD
ldap_attrs(1) := 'cn';  --ФИО
ldap_attrs(2) := 'title'; --должность
ldap_attrs(3) := 'department';  --подразделение
ldap_attrs(4) := 'sAMAccountName';  --логин
ldap_attrs(5) := 'userAccountControl'; --статус
--filter => 'objectclass = *'
--запускаем функцию синхронного поиска в каталоге с фильтром по объектному классу - пользователи
retval := DBMS_LDAP.search_s(ld       => ldap_session,
base     => ldap_base,
scope    => DBMS_LDAP.SCOPE_SUBTREE,
filter   => 'objectclass=User',
attrs    => ldap_attrs,
attronly => 0,
res      => ldap_message);
dbms_output.put_line(RPAD('search_s Returns ',25,' ') || ': '|| TO_CHAR(retval));
dbms_output.put_line(RPAD('LDAP message ',25,' ') || ': ' || RAWTOHEX(SUBSTR(ldap_message,1,8)) || '(returned from search_s)');

--Подсчет вернувшихся значений
retval := dbms_ldap.count_entries(ld => ldap_session,
msg => ldap_message);
dbms_output.put_line(RPAD('Number of Entries ',25,' ') || ': '|| TO_CHAR(retval));

--Получение первой записи
ldap_entry := dbms_ldap.first_entry(ld => ldap_session,
msg => ldap_message);
entry_index := 0;

<< entry_loop >>
WHILE ldap_entry IS NOT NULL
LOOP

--Вывести текущую запись
ldap_dn := DBMS_LDAP.get_dn(ld => ldap_session,
ldapentry => ldap_entry);

ldap_dn_short := trim(replace(substr(replace(ldap_dn,substr(ldap_dn,-35,100),''),instr(replace(ldap_dn,substr(ldap_dn,-35,100),''),'OU=',-1)),'OU=',''));
--Получить первый атрибут
ldap_attr_name := dbms_ldap.first_attribute(ld => ldap_session,
ldapentry => ldap_entry,
ber_elem => ldap_ber_elmt);
ldap_cn := NULL;
ldap_title := NULL;
ldap_dep := NULL;
ldap_log := NULL;
ldap_stat := NULL;

attr_index := 1;
<< attributes_loop >>
WHILE ldap_attr_name IS NOT NULL
LOOP

--Получить значение, связанное с атрибутом
ldap_vals := DBMS_LDAP.get_values (ld => ldap_session,
ldapentry => ldap_entry,
attr => ldap_attr_name);

<< values_loop >>
FOR i in ldap_vals.FIRST..ldap_vals.LAST
LOOP

CASE ldap_attr_name
WHEN 'cn' THEN ldap_cn := ldap_vals(i);
WHEN 'title' THEN ldap_title := ldap_vals(i);
WHEN 'department' THEN ldap_dep := ldap_vals(i);
WHEN 'sAMAccountName' THEN ldap_log := ldap_vals(i);
WHEN 'userAccountControl' THEN ldap_stat := ldap_vals(i);
END CASE;

END LOOP values_loop;
--Следующий аттрибут
ldap_attr_name := dbms_ldap.next_attribute(ld => ldap_session,
ldapentry => ldap_entry,
ber_elem => ldap_ber_elmt);

attr_index := attr_index + 1;

END LOOP attributes_loop;

--Освобождаем ber element
IF (ldap_ber_elmt IS NOT NULL) THEN
dbms_ldap.ber_free(ber => ldap_ber_elmt, freebuf => 0);
END IF;

--Следующее значение для аттрибута
ldap_entry := dbms_ldap.next_entry(ld => ldap_session,
msg => ldap_entry);

entry_index := entry_index + 1;

--Если получены данные, то пишем их в таблицу
IF entry_index > 0 THEN
EXECUTE IMMEDIATE 'INSERT INTO USER_AD values(:1, :2, :3, :4, :5, 6:, :7)'
USING ldap_cn, ldap_title, ldap_dep, ldap_log, ldap_stat, ldap_dn_short, ldap_dn;
END IF;

END LOOP entry_loop;

COMMIT;

--Сообщение LDAP
retval := dbms_ldap.msgfree(lm => ldap_message);
DBMS_OUTPUT.PUT_LINE('AD operation successful!');

--Завершение сессии в AD
retval := DBMS_LDAP.unbind_s(ld => ldap_session);
dbms_output.put_line(RPAD('unbind_res Returns ',25,' ') || ': ' ||TO_CHAR(retval));

--Исключение ошибок
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(' Error code : ' || TO_CHAR(SQLCODE));
dbms_output.put_line(' Error Message : ' || SQLERRM);
dbms_output.put_line(' Exception encountered .. exiting');

END AD_TO_DB;
/

Теперь можно запустить процедуру и получить пользовательские данные из AD.