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;
/