How to get all the dependencies Oracle object

1. You want to define all used objects, a change which may affect your object:

select 
    level as hlevel, -- dependencies level
    referenced_owner as ref_owner, -- referenced owner name
    lpad(' ', 2*(level-1)) || referenced_name as ref_name,  -- refernced object name
    referenced_type as ref_type, -- referenced object type
    owner parent_owner, -- parent owner name
    name parent_name, - parent object name
    type parent_type -- parent object type
from dba_dependencies
where referenced_owner not in ('SYS', 'SYSTEM', 'PUBLIC')
start with
    owner = :owner -- owner name of your object
    and name = :object -- your object name
connect by owner = prior referenced_owner
    and name = prior referenced_name
    and type = prior referenced_type
ORDER SIBLINGS BY referenced_name; 

2. You want to define all the objects that are affected by changing of your object:

select 
    level as hlevel, -- dependencies level
    owner as parent_owner, -- parent owner name
    lpad(' ', 2*(level-1)) || name as parent_name, -- parent object name
    type as parent_type, -- parent object type
    referenced_owner, -- referenced owner name
    referenced_name, -- referenced object name
    referenced_type -- referenced object type
from dba_dependencies
where referenced_owner not in ('SYS', 'SYSTEM', 'PUBLIC')
start with
    referenced_owner = :owner -- owner name of your object
    and referenced_name = :object -- your object name
connect by prior owner = referenced_owner
    and prior name = referenced_name
    and prior type = referenced_type
ORDER SIBLINGS BY name
; 

 

Реклама

Download HTML and File using PL\SQL

  • Oracle 11 and above

Загрузка HTML-страницы и файла по URL-адресу с помощью PL\SQL
Чтобы все работало Вам необходимо:
1. Иметь привилегии:

GRANT EXECUTE ON UTL_HTTP TO USERNAME;
GRANT EXECUTE ON DBMS_LOB TO USERNAME;

2. Должен быть создан файл ACL:

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'all-network-PUBLIC.xml',
description => 'A test of the ACL functionality',
principal => 'USERNAME',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/

3. Выдать права на файл пользователю:

BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'all-network-PUBLIC.xml',
principal => 'USERNAME',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);

COMMIT;
END;
/

4. Настроить доступ к доменному имени сайта, например, http://www.nalog.gov.by:

BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'all-network-PUBLIC.xml',
host => 'www.nalog.gov.by',
lower_port => 80,
upper_port => NULL);
COMMIT;
END;
/

5. Если у вас используется прокси, то разрешите и к нему доступ, например:

BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'all-network-PUBLIC.xml',
host => 'myproxy.it.by',
lower_port => 8080,
upper_port => NULL);
COMMIT;
END;
/

6. Создадим временную таблицу для сохранения HTML-страницы и файлов:

CREATE GLOBAL TEMPORARY TABLE USERNAME.HTTP_CLOB (
url        VARCHAR2(2000),
page_data  CLOB)
ON COMMIT PRESERVE ROWS
RESULT_CACHE (MODE DEFAULT)
NOCACHE;

CREATE GLOBAL TEMPORARY TABLE USERNAME.HTTP_BLOB (
url        VARCHAR2(2000),
file_data  BLOB)
ON COMMIT PRESERVE ROWS
RESULT_CACHE (MODE DEFAULT)
NOCACHE;

7. Создадим процедуру для загрузки HTML-страницы по URL-адресу:

CREATE OR REPLACE PROCEDURE USERNAME.LOAD_HTML_BY_URL
(p_url  IN  VARCHAR2) AS
l_http_request   UTL_HTTP.req;
l_http_response  UTL_HTTP.resp;
l_clob           CLOB;
l_text           VARCHAR2(32767);

BEGIN
-- Initialize the CLOB.
DBMS_LOB.createtemporary(l_clob, FALSE);

-- Set proxy (if you need)
UTL_HTTP.SET_PROXY('http://username:password@myproxy.it.by:8080');

-- Make a HTTP request and get the response.
l_http_request  := UTL_HTTP.begin_request(p_url, 'POST', 'HTTP/1.1');
utl_http.set_header(l_http_request, 'Content-Type',
'text/xml; charset=utf-8');
l_http_response := UTL_HTTP.get_response(l_http_request);

-- Copy the response into the CLOB.
BEGIN
LOOP
UTL_HTTP.read_text(l_http_response, l_text, 32766);
DBMS_LOB.writeappend (l_clob, LENGTH(l_text), l_text);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;

-- Insert the data into the table.
INSERT INTO USERNAME.HTTP_CLOB(url, page_data)
VALUES (p_url, l_clob);
COMMIT;

-- Relase the resources associated with the temporary LOB.
DBMS_LOB.freetemporary(l_clob);

EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response(l_http_response);
DBMS_LOB.freetemporary(l_clob);
RAISE;

END LOAD_HTML_BY_URL;
/

8. Создадим процедурe для загрузки файла по URL-адресу:

CREATE OR REPLACE PROCEDURE USERNAME.LOAD_FILE_BY_URL
(p_url  IN  VARCHAR2) AS
l_http_request   UTL_HTTP.req;
l_http_response  UTL_HTTP.resp;
l_blob           BLOB;
l_raw            RAW(32767);

BEGIN
-- Initialize the BLOB
DBMS_LOB.createtemporary(l_blob, FALSE);

-- Set proxy (if you need)
UTL_HTTP.SET_PROXY('http://username:password@myproxy.it.by:8080');

-- Make a HTTP request and get the response
l_http_request  := UTL_HTTP.begin_request(p_url);
l_http_response := UTL_HTTP.get_response(l_http_request);

-- Copy the response into the BLOB
BEGIN
LOOP
UTL_HTTP.read_raw(l_http_response, l_raw, 32767);
DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;

-- Insert the data into the table
INSERT INTO USERNAME.HTTP_BLOB(url, file_data)
VALUES (p_url, l_blob);
COMMIT;

-- Relase the resources associated with the temporary LOB
DBMS_LOB.freetemporary(l_blob);

EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response(l_http_response);
DBMS_LOB.freetemporary(l_blob);
RAISE;

END LOAD_FILE_BY_URL;
/

9. Пример вызова процедур загрузки:

exec USERNAME.LOAD_HTML_BY_URL('http://www.nalog.gov.by/ru/reestr_ru/');
exec USERNAME.LOAD_FILE_BY_URL('http://www.nalog.gov.by/uploads/documents/10-02-2016.xls/');

10. Результаты можно увидеть по запросу:

select * from USERNAME.HTTP_CLOB;
select * from USERNAME.HTTP_BLOB;

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

What encoding string UTF8 or WIN1251

Пример, как определить кодировку строки UTF8 или WIN1251?

with t as
(select 'Текст в кодировке UTF8' filename from dual union
select 'Текст в кодировке WIN1251' filename from dual union
select 'Text does not contain cyrilic charcters' filename from dual)
select filename,
case
    when not regexp_like(asciistr(convert(filename, 'cl8mswin1251')), '\FFFD') then
        case
            when regexp_like(convert(filename, 'cl8mswin1251', 'utf8'),'[а-яА-Я]') then 'UTF8'
            else 'WIN1251'
        end
        else 'WIN1251'
end ENCODE from t
order by 2;

FILENAME                                  ENCODE
----------------------------------------- -------
Текст РІ РєРѕРґРёСЂРѕРІРєРµ UTF8     UTF8   
Текст в кодировке WIN1251                 WIN1251
Text does not contain cyrilic charcters   WIN1251

Теперь дополним пример конвертацией текста UTF8 в WIN1251:

with t as
(select 'Текст в кодировке UTF8' filename from dual union
select 'Текст в кодировке WIN1251' from dual union
select 'Text does not contain cyrilic charcters' from dual)
select
encode,
case
    when ENCODE = 'WIN1251' then filename
    else convert(filename, 'cl8mswin1251', 'utf8')
end decode_filename
from(
select filename,
case
    when not regexp_like(asciistr(convert(filename, 'cl8mswin1251')), '\FFFD') then
        case
            when regexp_like(convert(filename, 'cl8mswin1251', 'utf8'),'[а-яА-Я]') then 'UTF8'
            else 'WIN1251'
        end
        else 'WIN1251'
end ENCODE from t)
order by 2;
                                      
ENCODE    DECODE_FILENAME
--------  -----------------------------------------
UTF8      Текст в кодировке UTF8
WIN1251   Текст в кодировке WIN1251
WIN1251   Text does not contain cyrilic charcters                                                      

 

 

Convert mail message from utf8 to windows-1251

Если вы с помощью PL\SQL делаете разбор почтовых сообщений (стандарта MIME) и получаете атрибуты (тема, тело сообщения, имена вложенных файлов и т.п.), Вы можете столкнуться с ситуацией, когда значение некоторых атрибутов дает подобный результат:

=?utf-8?B?cGxhbV/QutCw0Lot0YLQviDRgtCw0LpfMi5qcGc=?=

В моем случае я получал имя файла вложенного в письмо. Данное письмо я считывал из почтового ящика при помощи пакета MAIL_CLIENT.
Как оказалось когда имя файла во вложении было на латинице, то я его получал без проблем, но когда в имени файла содержались символы кириллицы, он кодировался в формат заголовка MIME с кодировкой UTF8.У Oracle для этих случаев есть пакет UTL_ENCODE, который позволяет кодировать и декодировать данные для почтовых сообщений.
В итоге написав следующую конструкцию, я решил проблему с кириллицей в имени файла:

file_name := case
when filename like '=?utf-8?%' then utl_encode.mimeheader_decode(filename)
else filename
end;

где, filename - это значение атрибута - имя вложенного файла в письме.
А условие case нужно для того, чтобы выполнять декодирование строк формата заголовка MIME в UTF8,
и не обрабатывать их, если имя содержит только латиницу.

В итоге я получил вместо:
=?utf-8?B?cGxhbV/QutCw0Lot0YLQviDRgtCw0LpfMi5qcGc=?=
нужное мне значение имени файла:
plam_как-то так_2.jpg

Для проверки:

select utl_encode.mimeheader_decode('=?utf-8?B?cGxhbV/QutCw0Lot0YLQviDRgtCw0LpfMi5qcGc=?=')
VAL from dual;

VAL                                                                             
----------------------
plam_как-то так_2.jpg  

Как получить инициалы (INITIALS) из ФИО (NAME)

  • Oracle 10, 11

Данная конструкция позволяет из ФИО (NAME) сотрудника получить фамилию (LASTNAME), имя (FIRSTNAME), отчество (FATHERNAME) и иницалы (INITIALS).

with t as
(select 'Иванов Петр Васильевич' as name from dual)
select t.name,
regexp_replace(t.name, ' (.*)') LASTNAME,
regexp_replace(t.name, ' (.*)|^[^ ]* ') FIRSTNAME,
regexp_replace(t.name, '(.*) ') FATHERNAME,
regexp_replace(regexp_replace(t.name, ' (.*)|^[^ ]* '),'.*','.',2,1)||regexp_replace(regexp_replace(t.name, '(.*) '),'.*','.',2,1) INITIALS
from t;

NAME                    LASTNAME  FIRSTNAME  FATHERNAME  INITIALS
----------------------  ------    ----       ----------  ----                                                            
Иванов Петр Васильевич  Иванов    Петр       Васильевич  П.В.