CSVReader PL/SQL + java

  • support on Oracle 10, 11, 12

1. Create java source with name CSVReader.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CSVReader" AS
import java.lang.*;
import java.io.File;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.*;

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

// Procedure for filling the object type t_line (to display the data from csv file)
static void toLine (int pRowNum, String pValue, int pDelQoutes)
throws Exception {
encod();
#sql {
begin
csvreader.private_add_line (
p_row_nr        => :pRowNum,
p_value         => :pValue,
p_delqoutes     => :pDelQoutes );
end;
};
}

// Function replace all separator between double quotes
public static String replace_between_qoutes(String p_text, String p_sep)
throws Exception {
char sep = p_sep.charAt(0);
String copy = new String();
boolean inQuotes = false;
int code = (int) sep;
char dblq = '"';

for(int i=0; i<p_text.length();++i) {
if (p_text.charAt(i)=='"')
inQuotes = !inQuotes;

if (p_text.charAt(i)==sep && inQuotes)
copy += "chr("+code+")";

else
copy += p_text.charAt(i);
}
return copy;
}

// Procedure reading csv file from disk
public static void read(String p_file, Integer p_skip, Integer p_delqoutes)
throws Exception {
encod();
int             line_counter     = 1;
int             col_counter      = 0;
File            file        = new File(p_file);
BufferedReader  br          = null;
String          line        = "";
String          text        = "";

br = new BufferedReader(new FileReader(file));
// read line by line
while ((line = br.readLine()) != null) {
// skip lines when set to skiplines
if (line_counter > p_skip) {
// filling oracle type for display values
CSVReader.toLine(
line_counter,
line,
p_delqoutes
);
}
line_counter++;
}
}

};
/

2. Then create a package of “csvreader” for publication the Java call specification, so we can access it via PL/SQL.

CREATE OR REPLACE package csvreader as

/* Package for parse CSV file in Oracle */

g_sep       varchar2(10) := ',';

type t_line is record (
row_nr      number,
col_nr      number,
value       varchar2(4000));
type t_lines is table of t_line;

/* pipelined table function to return all cells of an CSV file */
function get_lines (
p_file         in varchar2,
p_sep          in varchar2 := ',',
p_skip         in number   := 0,
p_delqoutes    in number   := 1)
return t_lines
pipelined;

/* for filling type t_line from java code */
procedure private_add_line (
p_row_nr        in number,
p_value         in varchar2,
p_delqoutes     in number);

function replace_between_qoutes(
p_text          in varchar2,
p_sep           in varchar2
)
return Varchar2
is language java
name 'CSVReader.replace_between_qoutes(java.lang.String, java.lang.String) return java.lang.String';

end csvreader;
/

CREATE OR REPLACE package body csvreader as

g_lines t_lines;

procedure read (
    p_file          in varchar2,
    p_skip          in number,
    p_delqoutes     in number)
is language java
name 'CSVReader.read(java.lang.String, java.lang.Integer, java.lang.Integer)';

/* parse line by line of csv file */
function get_lines (
    p_file          in varchar2,
    p_sep           in varchar2 := ',',
    p_skip          in number   := 0,
    p_delqoutes     in number   := 1)
    return t_lines
    pipelined
is
begin
    g_lines := new t_lines();

    if p_sep != ',' then
        g_sep := p_sep;
    end if;

    if p_file is not null then
        read (
            p_file      => p_file,
            p_skip      => p_skip,
            p_delqoutes => p_delqoutes);
    end if;

    for i in 1 .. g_lines.count loop
        pipe row (g_lines(i));
    end loop;

    g_lines := null;
    return;
end get_lines;

/* filling type t_line */
procedure private_add_line (
    p_row_nr        in number,
    p_value         in varchar2,
    p_delqoutes     in number)
is
    l_row t_line;
    val varchar2(4000);
begin

-- divided line on the part depending of the separator
for rec in ( select replace(
                    regexp_substr(newstr,'[^'||g_sep||']+', 1, level),
                        'chr('||ascii(g_sep)||')', g_sep) text, level lvl
             from
                ( select str, listagg(rep,'') within group (order by n) newstr
                 from (
                     select str
                     ,n, case
                            when (mod(n,2)=q)
                                then '"'||replace(sub,g_sep,'chr('||ascii(g_sep)||')')||'"'
                            else sub
                         end rep
                     from (
                         select str,
                         m.column_value n,
                         case when regexp_like(str,'^"') then 1 else 0 end q,
                         regexp_substr(str,'[^"]+',1,m.column_value) sub
                         from  (select p_value str from dual) t,
                                table(cast(multiset(
                                    select level from dual
                                    connect by level <= regexp_count(str,'[^"]+')
                                ) as sys.odciNumberList)) m
                     )
                 )
                 group by str
                )
            connect by regexp_substr(newstr, '[^'||g_sep||']+', 1, level) is not null
           )
loop
    -- remove the double quotation marks at the beginning and end of the line
    val := case
                when p_delqoutes = 0 then rec.text
                else regexp_replace(rec.text, '^([^"]*"(.*)"[^"]*|(.*))$', '\2\3')
           end;

    l_row.row_nr        := p_row_nr;
    l_row.col_nr        := rec.lvl;
    l_row.value         := val;
    g_lines.extend;
    g_lines(g_lines.count) := l_row;
    
end loop;

end private_add_line;

end csvreader;
/

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' );
EXECUTE IMMEDIATE 'GRANT EXECUTE ON sys.odciNumberList to '||l_user;
END;
/

4. How use:

/*
Input parameters:
  p_file        - fullpath to file. The file must be located on the computer
                  where the database Oracle or a network path,
                  where access to the computer issued to Oracle;
  p_sep         - separator, default ",";
  p_skip        - skip n-lines before read, deafult 0;
  p_delqoutes   - remove the double quotation marks at the beginning
                  and end of the line (0 - no, 1 - yes), default 1.

Output:
  row_nr      number,         - number of line in file
  col_nr      number,         - number of column
  value       varchar2(4000)  - text

Example:
create file "c:\test.txt" with this content:
"1.0.0.0","1.0.0.255","16777216","16777471","AU","Australia, "Montenegro LCD", USA"
"1.0.1.0","1.0.3.255","16777472","16778239","CN","China"
"1.0.4.0","1.0.7.255","16778240","16779263","AU","Australia, USA"
"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, Siria"
*/

-- run this select:
select * from table(csvreader.get_lines(
p_file      => 'c:\test.txt',
p_sep       => ',',
p_skip      => 0,
p_delqoutes => 1));
---------------------------------------------
1    1    1.0.0.0
1    2    1.0.0.255
1    3    16777216
1    4    16777471
1    5    AU
1    6    Australia, "Montenegro LCD", USA
2    1    1.0.1.0
2    2    1.0.3.255
............................................
Реклама

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;

 

Managing files and folders using Java in Oracle PL/SQL

Collection of Java utilities for work with files anf folders in Oracle PL/SQL.
For testing I used Java 6 and Oracle 11.2.0.1.

1. Create a number of important facilities for testing and the work of our utilities.

CREATE TABLE BLOB_TAB
( F_ID    INTEGER,
  F_NAME  VARCHAR2(128 BYTE),
  F_BLOB  BLOB,
  F_LEN   VARCHAR2(20 BYTE) );

CREATE SEQUENCE BLOB_TAB_SEQ
  START WITH 1
  MAXVALUE 9999999
  MINVALUE 1
  NOCYCLE
  CACHE 5
  ORDER;

CREATE GLOBAL TEMPORARY TABLE ATTR_FILES
( DIRNAME   VARCHAR2(255 BYTE),
  FILENAME  VARCHAR2(255 BYTE),
  LENGTH    NUMBER,
  MOD_DATE  DATE,
  TYPE      VARCHAR2(1 BYTE) )
ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE TYPE LIST_FILES AS TABLE OF VARCHAR2(255);

2. Create Java source named «Lobster».

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Lobster" as
import java.io.*;
import java.sql.*;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import oracle.jdbc.OracleTypes;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.text.SimpleDateFormat;
import java.util.Date;
import oracle.sql.driver.*;
import oracle.sql.ArrayDescriptor;
import oracle.sql.ARRAY;

// What's: Collection java utilities for work with files, folders, CLOB and BLOB.
// Author: souluran adn Denis Popov (http://www.sql.ru/faq/faq_topic.aspx?fid=469)

public class Lobster {

// 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");
  }

// Procedure get list of file names in directory
  public static ARRAY list_files(String dirname)
  throws SQLException {
    encod();
    Connection con = null;
    ARRAY listed = null;
    con = getConnection();
    File dir = new File(dirname);
    try{
        if (dir.exists()) {
            ArrayDescriptor arrayDescriptor = new ArrayDescriptor("LIST_FILES",con);
            listed = new ARRAY(arrayDescriptor,con,((Object[])dir.list()));
        }
    } finally {
    if (con != null) {con.close();}
      }
  return listed;
  }

// Procedure get list of attributes files in directory
  public static void list_files_attr(String dirname)
  throws SQLException {
     encod();
     File path = new File(dirname);

     if (path.exists()) {

        String[] list = path.list();
        String element;

        File fileObject;
        long length;
        String dateStr, type;
        Date now = new Date();
        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy H:m:s");

        for(int i = 0; i < list.length; i++)
        {
            element = list[i];
            fileObject = new File(dirname + File.separatorChar + list[i]);
            length = fileObject.length();
            if (fileObject.isDirectory()) {
              type = "d";
            } else if (fileObject.isFile()) {
              type = "f";
            } else {
              type = "?";
            }

            java.util.Date d = new java.util.Date(fileObject.lastModified());
            dateStr = format.format(d);

            #sql { INSERT INTO ATTR_FILES (dirname, filename, length, mod_date, type)
                   VALUES (:dirname, :element, :length, to_date(:dateStr, 'dd.mm.yyyy hh24:mi:ss'), :type) };
        }
     }
  }

// Function get modified time file
  public static String getfiletime(String dirname, String fileName)
  throws SQLException {
     encod();
     File path = new File(dirname + "\\" + fileName);
     String dateStr = null;
     if (path.exists()) {
        Date now = new Date();
        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy H:m:s");
        java.util.Date d = new java.util.Date(path.lastModified());
        dateStr = format.format(d);
     }
     return dateStr;
  }

// Function check exixsting file in directory
  public static oracle.sql.NUMBER existsfile(String dirname, String fileName) throws Exception {
    //System.setProperty("file.encoding","cp1251");
    encod();
    int res = 0;
    File theDir = new File(dirname + "\\" +fileName);
    if (!theDir.exists()) {
        res = 0;
     }
    else {
        res = 1;
     }
   return new oracle.sql.NUMBER(res);
  }

// Procedure move file
  public static void movefile(String dirname, String fileName, String newdirname, String newfileName)
  throws IOException {
    encod();
    //String myfile = new String(fileName.getBytes("windows-1251"), "windows-1251");
    File srcFile = new File(dirname + "\\" + fileName);
    File destFile = new File(newdirname + "\\" + newfileName);
    InputStream input = null;
    OutputStream output = null;
    try {
        input = new FileInputStream(srcFile);
        output = new FileOutputStream(destFile);
        byte[] buf = new byte[1024];
        int bytesRead;
        while ((bytesRead = input.read(buf)) > 0) {
        output.write(buf, 0, bytesRead);
        }
    } finally {
        input.close();
        output.close();
        srcFile.delete();
    }
  }

// Procedure copy file
  public static void copyfile(String dirname, String fileName, String newdirname, String newfileName)
  throws Exception, IOException {
    encod();
    File srcFile = new File(dirname + "\\" + fileName);
    File destFile = new File(newdirname + "\\" + newfileName);
    InputStream input = null;
    OutputStream output = null;
    try {
        input = new FileInputStream(srcFile);
        output = new FileOutputStream(destFile);
        byte[] buf = new byte[1024];
        int bytesRead;
        while ((bytesRead = input.read(buf)) > 0) {
        output.write(buf, 0, bytesRead);
        }
    } finally {
        input.close();
        output.close();
    }

  }

// Procedure delete file
  public static void deletefile(String dirname, String fileName)
  throws IOException {
    System.setProperty("file.encoding","cp1251");
    try {
        File srcFile = new File(dirname + "\\" + fileName);
        srcFile.delete();
    } finally {
    }
  }

// Procedure create new directory in Windows
  public static void newdir(String dirname)
  throws Exception {
    encod();
    File theDir = new File(dirname);
    // if the directory does not exist, create it
    if (!theDir.exists()) {
    //System.out.println("creating directory: " + dirname);
    boolean result = false;
        try{
            theDir.mkdir();
            result = true;
            }
        catch(SecurityException se){
            //handle it
            }
    if(result) {
               //System.out.println(dirname + " created");
               }
                          }
  }

// Function check exixsting directory in Windows
  public static oracle.sql.NUMBER existsdir(String dirname) throws Exception {
    encod();
    int res = 0;
    File theDir = new File(dirname);
    if (!theDir.exists()) {
        res = 0;
     }
    else {
        res = 1;
     }
   return new oracle.sql.NUMBER(res);
  }

// Procedure recursive search files in directory
  public static void recursivecopy(File src, File dest)
  throws Exception, IOException  {
    encod();
        if(src.isDirectory()){

            //if directory not exists, create it
            if(!dest.exists()){
               dest.mkdir();
               System.out.println("Directory copied from "
                              + src + "  to " + dest);
            }

            //list all the directory contents
            String files[] = src.list();

            for (String file : files) {
               //construct the src and dest file structure
               File srcFile = new File(src, file);
               File destFile = new File(dest, file);
               //recursive copy
               recursivecopy(srcFile, destFile);
            }

        }else{
            //if file, then copy it
            //Use bytes stream to support all file types
            InputStream in = new FileInputStream(src);
                OutputStream out = new FileOutputStream(dest);

                byte[] buffer = new byte[1024];

                int length;
                //copy the file content in bytes
                while ((length = in.read(buffer)) > 0){
                   out.write(buffer, 0, length);
                }

                in.close();
                out.close();
                System.out.println("File copied from " + src + " to " + dest);
        }
  }

// Procedure copy directory in Windows
  public static void copydir(String srcDir, String destDir) throws Exception, IOException {
        encod();
        File src = new File(srcDir);
        File dest = new File(destDir);
        recursivecopy(src, dest);
  }

// Procedure recursive search in directory
  private static void recursiveDelete(File file) {
      System.setProperty("file.encoding","cp1251");
         if (!file.exists()) return;
         if (file.isDirectory()) {
            for (File f : file.listFiles()) recursiveDelete(f);
            file.delete();
         } else {
            file.delete();
         }
  }

// Procedure delete directory with all contetns in Windows
  public static void deletedir(String dirname) throws IOException  {
         encod();
         File directory = new File(dirname);
         recursiveDelete(directory);
  }

// Function import file into CLOB
  public static CLOB file2clob(String dirname, String fileName)
    throws SQLException, IOException {
    Connection con = null;
    CLOB clob = null;
    Writer writer = null;
    InputStream in = null;
    try {
      encod();
      con = getConnection();
      // For Oracle 9i and above.
      clob = CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);
      // For Oracle 8i.
//      clob = createTemporaryClob(con);
      writer = clob.getCharacterOutputStream();
      in = new BufferedInputStream(new FileInputStream(new File(dirname + "\\" + fileName)));
      byte[] buf = new byte[clob.getChunkSize()];
      int length;
      while ((length = in.read(buf)) != -1) {
        writer.write(new String(buf, 0, length));
      }
    } finally {
      if (in != null) {in.close();}
      if (writer != null) {writer.close();}
      if (con != null) {con.close();}
    }
    return clob;
  }

// Function import file with characterSet into CLOB
  public static CLOB file2clob(String dirname, String fileName, String characterSet)
    throws SQLException, IOException {
    Connection con = null;
    CLOB clob = null;
    Writer writer = null;
    Reader reader = null;
    try {
      encod();
      con = getConnection();
      // For Oracle 9i and above.
      clob = CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);
      // For Oracle 8i.
//      clob = createTemporaryClob(con);
      writer = clob.getCharacterOutputStream();
      reader = new InputStreamReader(
        new BufferedInputStream(new FileInputStream(new File(dirname + "\\" + fileName))), characterSet
      );
      char[] chars = new char[clob.getChunkSize()];
      int iChar;
      while ((iChar = reader.read(chars)) != -1) {
        writer.write(chars, 0, iChar);
      }
    } finally {
      if (reader != null) {reader.close();}
      if (writer != null) {writer.close();}
      if (con != null) {con.close();}
    }
    return clob;
  }

// Procedure export CLOB into file
  public static void clob2file(CLOB clob, String dirname, String fileName)
    throws SQLException, IOException {
    Writer writer = null;
    Reader reader = null;
    try {
      encod();
      writer = new BufferedWriter(new FileWriter(dirname + "\\" + fileName));
      reader = new BufferedReader(clob.getCharacterStream());
      int length;
      char[] buf = new char[clob.getChunkSize()];
      while ((length = reader.read(buf, 0, clob.getChunkSize())) != -1) {
        writer.write(buf, 0, length);
      }
    } finally {
      if (writer != null) {writer.close();}
      if (reader != null) {reader.close();}
    }
  }

// Procedure export CLOB into file with characterSet
  public static void clob2file(CLOB clob, String dirname, String fileName, String characterSet)
    throws SQLException, IOException {
    Writer writer = null;
    Reader reader = null;
    try {
      encod();
      writer = new OutputStreamWriter(
        new BufferedOutputStream(new FileOutputStream(new File(dirname + "\\" + fileName))), characterSet
      );
      reader = new BufferedReader(clob.getCharacterStream());
      int length;
      char[] buf = new char[clob.getChunkSize()];
      while ((length = reader.read(buf, 0, clob.getChunkSize())) != -1) {
        writer.write(buf, 0, length);
      }
    } finally {
      if (writer != null) {writer.close();}
      if (reader != null) {reader.close();}
    }
  }

// Function import file into BLOB
  public static BLOB file2blob(String dirname, String fileName)
    throws SQLException, IOException {
    Connection con = null;
    BLOB blob = null;
    OutputStream out = null;
    InputStream in = null;
    //Locale.setDefault(new Locale("ru", "RUS", "WIN"));
    try {
      encod();
      con = getConnection();
      // For Oracle 9i and above.
      blob = BLOB.createTemporary(con, true, BLOB.DURATION_SESSION);
      // For Oracle 8i.
//      blob = createTemporaryBlob(con);
      out = blob.getBinaryOutputStream();
      String myfile = null;
      in = new BufferedInputStream(new FileInputStream(new File(dirname + "\\" + fileName)));
      int length;
      int chunkSize = blob.getChunkSize();
      byte[] buf = new byte[chunkSize];
      while ((length = in.read(buf)) != -1) {
        out.write(buf, 0, length);
      }
    } finally {
      if (in != null) {in.close();}
      if (out != null) {out.close();}
      if (con != null) {con.close();}
    }
    return blob;
  }

// Procedure export BLOB into file
  public static void blob2file(BLOB blob, String dirname, String fileName)
    throws SQLException, IOException {
    InputStream in = null;
    OutputStream out = null;
    try {
      encod();
      out = new BufferedOutputStream(new FileOutputStream(new File(dirname + "\\" + fileName)));
      in = blob.getBinaryStream();
      int length;
      byte[] buf = new byte[blob.getChunkSize()];
      while ((length = in.read(buf)) != -1) {
        out.write(buf, 0, length);
      }
    } finally {
      if (in != null) {in.close();}
      if (out != null) {out.close();}
    }
  }

// Function connection
  public static Connection getConnection()
    throws SQLException {
    return DriverManager.getConnection("jdbc:default:connection:");
  }

// Function create temporary CLOB
  public static CLOB createTemporaryClob(Connection con)
    throws SQLException {
    CallableStatement cst = null;
    try {
      cst = con.prepareCall("{call dbms_lob.createTemporary(?, false, dbms_lob.SESSION)}");
      cst.registerOutParameter(1, OracleTypes.CLOB);
      cst.execute();
      return (CLOB)cst.getClob(1);
    } finally {
      if (cst != null) { cst.close(); }
    }
  }

// Function create temporary BLOB
  public static BLOB createTemporaryBlob(Connection con)
    throws SQLException {
    CallableStatement cst = null;
    try {
      cst = con.prepareCall("{call dbms_lob.createTemporary(?, false, dbms_lob.SESSION)}");
      cst.registerOutParameter(1, OracleTypes.BLOB);
      cst.execute();
      return (BLOB)cst.getBlob(1);
    } finally {
      if (cst != null) { cst.close();
        }
    }
  }

}
/

3. Then create a package of «Lobster» for publication the Java call specification, so we can access it via PL/SQL.

CREATE OR REPLACE PACKAGE Lobster as

/* Package of utilities for work with files, folders, CLOB and BLOB */

/* get list of files from directory */
function list_files(p_dir_name VARCHAR2) RETURN list_files AS
language java name 'Lobster.list_files(java.lang.String) return oracle.sql.ARRAY';

/* get list of attributes files from directory */
procedure list_files_attr(p_dir_name varchar2) as
language java name 'Lobster.list_files_attr(java.lang.String)';

/* file exists or not */
function existsfile(p_dir_name varchar2, p_file_name varchar2) return number as
language java name 'Lobster.existsfile(java.lang.String, java.lang.String) return oracle.sql.NUMBER';

/* move file to another directory */
procedure movefile(p_dir_name varchar2, p_file_name varchar2, p_newdir_name varchar2, p_newfile_name varchar2) as
language java name 'Lobster.movefile(java.lang.String, java.lang.String, java.lang.String, java.lang.String)';

/* copy file to another directory */
procedure copyfile(p_dir_name varchar2, p_file_name varchar2, p_newdir_name varchar2, p_newfile_name varchar2) as
language java name 'Lobster.copyfile(java.lang.String, java.lang.String, java.lang.String, java.lang.String)';

/* delete file from directory */
procedure deletefile (p_dir_name varchar2, p_file_name varchar2) as
language java name 'Lobster.deletefile(java.lang.String, java.lang.String)';

/* create new directory or subdirectory */
procedure newdir(p_dir_name varchar2) as
language java name 'Lobster.newdir(java.lang.String)';

/* directory exists or not */
function existsdir(p_dir_name varchar2) return number as
language java name 'Lobster.existsdir(java.lang.String) return oracle.sql.NUMBER';

/* delete directory or subdirectory with all contents */
procedure deletedir(p_dir_name varchar2) as
language java name 'Lobster.deletedir(java.lang.String)';

/* copy all contents from directory to another directory */
procedure copydir(p_dir_name varchar2, p_newdir_name varchar2) as
language java name 'Lobster.copydir(java.lang.String, java.lang.String)';

/* import file into CLOB */
function file2clob(p_dir_name varchar2, p_file_name varchar2) return clob as
language java name 'Lobster.file2clob(java.lang.String, java.lang.String) return oracle.sql.CLOB';

/* import file into CLOB with encoding */
function file2clob(p_dir_name varchar2, p_file_name varchar2, p_characterset varchar2) return clob as
language java name 'Lobster.file2clob(java.lang.String, java.lang.String, java.lang.String) return oracle.sql.CLOB';

/* import CLOB into file */
procedure clob2file(p_clob clob, p_dir_name varchar2, p_file_name varchar2) as
language java name 'Lobster.clob2file(oracle.sql.CLOB, java.lang.String, java.lang.String)';

/* import CLOB into file with encoding */
procedure clob2file(p_clob clob, p_dir_name varchar2, p_file_name varchar2, p_characterset varchar2) as
language java name 'Lobster.clob2file(oracle.sql.CLOB, java.lang.String, java.lang.String, java.lang.String)';

/* import file into BLOB */
function file2blob(p_dir_name varchar2, p_file_name varchar2) return blob as
language java name 'Lobster.file2blob(java.lang.String, java.lang.String) return oracle.sql.BLOB';

/* import BLOB into file */
procedure blob2file(p_blob blob, p_dir_name varchar2, p_file_name varchar2) as
language java name 'Lobster.blob2file(oracle.sql.BLOB, java.lang.String, java.lang.String)';

end;
/ 

4. Grant the Oracle JVM the relevant filesystem permissions.

DECLARE
  l_schema VARCHAR2(30) := 'USERNAME';
BEGIN
  DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.util.PropertyPermission', 'file.encoding', 'write' );
END;
/

5. Examples how use package Lobster:

-- import file into BLOB
DECLARE

l_id INT DEFAULT 1;
l_dir VARCHAR2(128);
l_name VARCHAR2(128);
l_blob BLOB;

BEGIN
l_dir := 'd:\mydir\test';
l_name := 'my file.xls';

select BLOB_TAB_SEQ.NEXTVAL into l_id from dual;

INSERT INTO blob_tab
VALUES(l_id, null, EMPTY_BLOB(), 0);

SELECT f_blob INTO l_blob
FROM blob_tab
WHERE f_id = l_id
FOR UPDATE;

l_blob := lobster.file2blob(l_dir, l_name);

UPDATE blob_tab
SET f_name = l_name, f_blob = l_blob, f_len = dbms_lob.getlength(l_blob)
where f_id = l_id;
COMMIT;

END;
/

-- import BLOB into file
DECLARE
l_dir VARCHAR2(128);
l_name VARCHAR2(128);
l_blob BLOB;

BEGIN
l_dir := 'd:\mydir\test';
l_name := 'my file2.xls';

SELECT f_blob INTO l_blob
FROM blob_tab
WHERE f_id = :id;

lobster.blob2file(l_blob, l_dir, l_name);

END;
/

-- list of file name
SELECT column_value FROM TABLE(lobster.list_files('d:\mydir'));

--list of file attributes
begin
execute immediate 'truncate table ATTR_FILES';
lobster.list_files_attr('d:\mydir');
end;
/
select * from ATTR_FILES;

-- manage file and folders
declare
res number;
begin
-- copy file to another folder
lobster.copyfile('d:\mydir', 'test.txt', 'd:\my', 'test2.txt');
-- move file to another folder
lobster.movefile('d:\mydir', 'test.txt', 'd:\my', 'test.txt');
-- delete file
lobster.deletefile('d:\my', 'test.txt');
-- create new folder
lobster.newdir('d:\my5');
-- delete folder
lobster.deletedir('d:\my5');
-- copy all contetnts from folder to anothe folder
lobster.copydir('d:\my', 'd:\mytest');
-- exists file
res := lobster.existsfile('d:\my', 'test.txt');
dbms_output.put_line('file is ' || res);
res := lobster.existsdir('d:\my');
dbms_output.put_line('folder is ' || res);
end;
/ 

ManageEngine ServieDesk : wrong DueBy Date for SLA

Если в созданной заявке неверно проставляется Срок исполнения (DueBy Date), нужно проверить параметры, от которых данный показатель:
1. Admin -> Operation Hours: правильно ли задано рабочее время.
2. Admin -> Service Catalog -> кнопка Manage: правильно ли задано значение времени в SLA.

Если в предыдущих пунктах все верно, то нужно обратить внимание на версию JRE time zone, под которой работает ServiceDesk.
Может быть она устарела и требует немедленного обновления!

1. В командной строке (CMD) получим версию JRE time zone и текущего файла tzupdater.jar (если файла tzupdater.jar нет, то переходим к п.2.):

c:\ManageEngine\ServiceDesk — это директория, куда установлен ServiceDesk

CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -V -v

------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
 java.vendor: Sun Microsystems Inc.
 java.version: 1.6.0_45
 JRE time zone data version: tzdata2014e    //версия загруженная в java
 Embedded time zone data version: tzdata2014e    //версия файла tzupdater.jar
 Validating the time zone data
 Validation complete 

2. Теперь пройдем по этой ссылке http://www.oracle.com/technetwork/java/javase/tzdata-versions-138805.html к списку всхем имеющихся версий tzupdater.jar.

Например, после нашей имеющейся версии tzdata2014e уже было выпущено несколько новых, где добавлены и изменены часовые зоны
(в одном из которых, Since Belarus is not changing its clocks even though Moscow is, the time zone abbreviation in Europe/Minsk is changing from FET to its more-traditional value MSK on 2014-10-26 at 01:00).

3. Здесь можно скачать последнюю версию tzupdater.jar: http://www.oracle.com/technetwork/java/javase/downloads/index.html

4. Скачанный файл нужно скопировать в директорию c:\ManageEngine\ServiceDesk\jre\bin\. Если там уже имеется файл, то переименуйте его, например, tzupdater.jar.old.

5. В командной строке получим разницу версий JRE time zone и скаченного файла tzupdater.jar:

CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -t -v

-----------------------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_45
JRE time zone data version: tzdata2014e    //текущая версия загруженная в java
Embedded time zone data version: tzdata2014i    //версия файла tzupdater.jar недавно скачанного
Validating the time zone data
/data/tzdata2014i.test:4870: test failed: Asia/Kashgar
/data/tzdata2014i.test:4874: test failed: Asia/Urumqi
/data/tzdata2014i.test:5402: test failed: Pacific/Fiji
/data/tzdata2014i.test:5404: test failed: Pacific/Fiji
/data/tzdata2014i.test:5406: test failed: Pacific/Fiji
/data/tzdata2014i.test:5408: test failed: Pacific/Fiji
/data/tzdata2014i.test:5410: test failed: Pacific/Fiji
/data/tzdata2014i.test:5412: test failed: Pacific/Fiji
/data/tzdata2014i.test:5414: test failed: Pacific/Fiji
/data/tzdata2014i.test:5416: test failed: Pacific/Fiji
/data/tzdata2014i.test:5418: test failed: Pacific/Fiji
/data/tzdata2014i.test:5517: time zone not found: Pacific/Bougainville
/data/tzdata2014i.test:5518: time zone not found: Pacific/Bougainville
/data/tzdata2014i.test:5519: time zone not found: Pacific/Bougainville
/data/tzdata2014i.test:5522: test failed: Asia/Yakutsk
/data/tzdata2014i.test:5525: test failed: Europe/Kaliningrad
/data/tzdata2014i.test:5528: test failed: Europe/Volgograd
/data/tzdata2014i.test:5531: test failed: Asia/Magadan
/data/tzdata2014i.test:5532: time zone not found: Asia/Srednekolymsk
/data/tzdata2014i.test:5533: time zone not found: Asia/Srednekolymsk
/data/tzdata2014i.test:5534: time zone not found: Asia/Srednekolymsk
/data/tzdata2014i.test:5537: test failed: Asia/Khandyga
/data/tzdata2014i.test:5540: test failed: Asia/Yekaterinburg
/data/tzdata2014i.test:5543: test failed: Asia/Vladivostok
/data/tzdata2014i.test:5548: test failed: Europe/Simferopol
/data/tzdata2014i.test:5551: test failed: Asia/Ust-Nera
/data/tzdata2014i.test:5554: test failed: Asia/Omsk
/data/tzdata2014i.test:5557: test failed: Asia/Novosibirsk
/data/tzdata2014i.test:5560: test failed: Asia/Sakhalin
/data/tzdata2014i.test:5563: test failed: Asia/Krasnoyarsk
/data/tzdata2014i.test:5564: time zone not found: Asia/Chita
/data/tzdata2014i.test:5565: time zone not found: Asia/Chita
/data/tzdata2014i.test:5566: time zone not found: Asia/Chita
/data/tzdata2014i.test:5569: test failed: Asia/Irkutsk
/data/tzdata2014i.test:5572: test failed: Europe/Moscow
/data/tzdata2014i.test:5577: test failed: America/Grand_Turk
/data/tzdata2014i.test:5580: test failed: W-SU

Validation tests failed. 

6. Теперь обновим JRE Time zone:
CMD>

CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -u -v

----------------------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_45
JRE time zone data version: tzdata2014e
Embedded time zone data version: tzdata2014i
Extracting files... done.
Renaming directories... done.
Validating the new time zone data... done.
Time zone data update is complete. 

7. Проверим версию JRE time zone:
CMD>

</pre>
CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -t -v

----------------------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_45
JRE time zone data version: tzdata2014i        //версия загруженная в java
Embedded time zone data version: tzdata2014i    //версия файла tzupdater.jar
Validating the time zone data
Validation complete 

Значения JRE time zone data version и Embedded time zone data version должны быть равны!

8. Перезапустим машину, на которой установлен сервер ManageEngine SevriceDesk, чтобы все измнения вступили в силу.

9. Если после выполнения п.1-п.8 все равно проблема осталась, то откройте файл c:\ManageEngine\Servicedesk\bin\startout.log и найдите в нем значение параметра user.timezone (временная зона). Если значение не равно временной зоне, что установлена в системе (например, если user.timezone = America/Caracas, а в системе установлено Europe/Minsk), значит Java запускается с какой-то своей временной зоной по умолчанию.

10. Чтобы исправить эту ситуацию, нужно остановить службу SD, открыть файл c:\ManageEngine\Servicedesk\server\default\conf\wrapper.conf на редактирование. Найти в нем раздел # Java Additional Parameters, где перечислены параметры запуска Java, и добавить туда новый параметр (-Duser.timezone):
В моем случае в этом разделе:

# Java Additional Parameters (added new parameter #24)
wrapper.java.additional.1=-server
wrapper.java.additional.2=-Dprogram.name=run.sh
wrapper.java.additional.3=-Djboss.server.type=com.adventnet.j2ee.deployment.system.AdventNetServerImpl
wrapper.java.additional.4=-Dorg.jboss.logging.Log4jService.catchSystemOut=false
wrapper.java.additional.5=-Dorg.jboss.logging.Log4jService.catchSystemErr=false
wrapper.java.additional.6=-Djava.util.logging.manager=com.adventnet.logging.LogManager
wrapper.java.additional.7=-Djava.util.logging.config.file=../server/default/conf/logging.xml
wrapper.java.additional.8=-Djava.util.logging.config.class=com.adventnet.logging.LoggingScanner
wrapper.java.additional.9=-Dlog.dir=../server/default
wrapper.java.additional.10=-Dtier-type=BE
wrapper.java.additional.11=-Dtier-id=BE1
wrapper.java.additional.12=-Ddb.home=../pgsql
wrapper.java.additional.13=-Dproduct.home=../
wrapper.java.additional.14=-Dfile.encoding=UTF-8
wrapper.java.additional.15=-Dsdp.java.gc=false
wrapper.java.additional.16=-Dserver.dir=../
wrapper.java.additional.17=-Duser.language=en
wrapper.java.additional.18=-Duser.region=US
wrapper.java.additional.19=-XX:PermSize=64m
wrapper.java.additional.20=-XX:MaxPermSize=256m
wrapper.java.additional.21=-Djboss.shutdown.forceHalt=false
wrapper.java.additional.100=-DlogDir=../server/default/log
wrapper.java.additional.22=-Dproduct.home=../
wrapper.java.additional.23=-Dorg.tanukisoftware.wrapper.WrapperManager.mbean=false
wrapper.java.additional.24=-Duser.timezone=Europe/Minsk # добавил нужную мне timezone

11. Запустить службу SD. Попробовать создать новую заявку, Срок исполнения должен ставиться верный, исходя из выбранного SLA.