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;

How get check number for EAN-13 in Oracle

Штриховой код EAN-13 необходимо отличать от международного товарного номера EAN-13. Штриховой код EAN-13 представляет собой графическое изображение уникального международного номера товара EAN/UCC-13 в виде, пригодном для автоматического считывания. Значение номера EAN-13 продублировано арабскими цифрами в нижней части штрихового кода (13 знаков).
Номер товара EAN-13 имеет следующую структуру:
— 3 цифры – это региональный код (префикс) страны (например, 460-469 – Россия 482 — Украина, 540-549 —  Бельгия-Люксембург и т.д.);
— 6 цифр – это регистрационный номер предприятия
— 3 цифры – это порядковый номер продукции внутри предприятия;
— последняя 13-я цифра – контрольное число, вычисляется из предыдущих двенадцати.

Для расчета контрольного номера EAN-13 в Oracle можно воспользоваться следующей функцией:

CREATE OR REPLACE FUNCTION EAN13_CHECKNUMBER(STRIHCODE NUMBER)
RETURN NUMBER
IS
CHECKNUMBER NUMBER;

BEGIN

CHECKNUMBER :=
case
when mod(substr(STRIHCODE,1,1) +
substr(STRIHCODE,2,1)*3 +
substr(STRIHCODE,3,1) +
substr(STRIHCODE,4,1)*3 +
substr(STRIHCODE,5,1) +
substr(STRIHCODE,6,1)*3 +
substr(STRIHCODE,7,1) +
substr(STRIHCODE,8,1)*3 +
substr(STRIHCODE,9,1) +
substr(STRIHCODE,10,1)*3 +
substr(STRIHCODE,11,1) +
substr(STRIHCODE,12,1)*3, 10
) != 0
then concat(STRIHCODE,
(10 - mod(substr(STRIHCODE,1,1) +
substr(STRIHCODE,2,1)*3 +
substr(STRIHCODE,3,1)+
substr(STRIHCODE,4,1)*3 +
substr(STRIHCODE,5,1) +
substr(STRIHCODE,6,1)*3 +
substr(STRIHCODE,7,1) +
substr(STRIHCODE,8,1)*3 +
substr(STRIHCODE,9,1) +
substr(STRIHCODE,10,1)*3 +
substr(STRIHCODE,11,1) +
substr(STRIHCODE,12,1)*3, 10))
)
else concat(STRIHCODE,
(mod(substr(STRIHCODE,1,1) +
substr(STRIHCODE,2,1)*3 +
substr(STRIHCODE,3,1)+
substr(STRIHCODE,4,1)*3 +
substr(STRIHCODE,5,1) +
substr(STRIHCODE,6,1)*3 +
substr(STRIHCODE,7,1) +
substr(STRIHCODE,8,1)*3 +
substr(STRIHCODE,9,1) +
substr(STRIHCODE,10,1)*3 +
substr(STRIHCODE,11,1) +
substr(STRIHCODE,12,1)*3, 10))
)
END;

RETURN CHECKNUMBER;

END;
/

Давайте, получим номер EAN-13 с контрольным числом для кода 299309600001:

select EAN13_CHECKNUMBER(299309600001) EAN13 from dual;

EAN13
------------
2993096000017

В итоге получили номер EAN-13 = 2993096000017, где контрольное число = 7.

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;