Python script as Windows service + on schedule + logging

  • Testing on Windows 7, 2008, 2012

Note:
In line: timer = schedule.every(10).seconds, you sets schedule time.
In line: timer.do(test1), where test1 — this your main script(program) to execute schedule task.
In line: _svc_name_ = «mytest-service» — this is your service name.
In line: _svc_display_name_ = «mytest-service» — this is your service display name in Windows service list.

Edit by yourself and save this code to file serv.py:

import win32serviceutil  # https://sourceforge.net/projects/pywin32/files/pywin32/Build%20220/pywin32-220.win32-py3.4.exe/download
import win32service
import win32event
import servicemanager
import socket
import time
import logging
import logging.handlers
import schedule  # https://pypi.python.org/pypi/schedule
from mytest import test1

# Managaement of service
# python.exe serv.py install | remove | start | stop | help

# Sets log file path.
log_file ="c:\\output.log";

# Return a logger with the specified name.
mylogger = logging.getLogger("MyLogger")

# Sets the threshold for this logger to lvl. Logging messages which are less severe than lvl will be ignored.
mylogger.setLevel(logging.DEBUG)

# Sets rotation parameters of disk log files
# https://docs.python.org/3.4/library/logging.handlers.html#rotatingfilehandler
handler = logging.handlers.RotatingFileHandler(log_file, maxBytes=10485760, backupCount=2)

# Sets format of record in log file
formatter = logging.Formatter('%(asctime)s - %(module)-10s - %(levelname)-8s %(message)s', '%d-%m-%Y %H:%M:%S')
handler.setFormatter(formatter)

# Adds the specified handler to logger "MyLogger"
mylogger.addHandler(handler)

class WinService(win32serviceutil.ServiceFramework):
    _svc_name_ = "mytest-service"    # service name
    _svc_display_name_ = "mytest service"    # display service name

    def __init__(self, args):
        win32serviceutil.ServiceFramework.__init__(self, args)
        self.stop_event = win32event.CreateEvent(None, 0, 0, None)
        socket.setdefaulttimeout(60)
        self.stop_requested = False

    # stop command service
    def SvcStop(self):
        self.ReportServiceStatus(win32service.SERVICE_STOP_PENDING)
        win32event.SetEvent(self.stop_event)
        mylogger.info("*** STOPPING SERVICE ***\n")
        self.stop_requested = True

    # start command service
    def SvcDoRun(self):
        servicemanager.LogMsg(
            servicemanager.EVENTLOG_INFORMATION_TYPE,
            servicemanager.PYS_SERVICE_STARTED,
            (self._svc_name_, '')
        )
        mylogger.info("*** STARTING SERVICE ***\n")
        # run main process
        self.main()

    # main process
    def main(self):
        mylogger.info("... STARTING SCHEDULE PROCESS ...\n")
        # schedule time for our task(program)
        # For example, I use "test1" procedure from "mytest" own module
        timer = schedule.every(10).seconds
        timer.do(test1)
        # run main loop for schedule process while service runs
        while not self.stop_requested:
            # execute task on schedule
            schedule.run_pending()
            time.sleep(1)
        return

if __name__ == '__main__':
    win32serviceutil.HandleCommandLine(WinService)

For example, I use simple procedure «test1» from file mytest.py:

import logging

# Return a logger with the specified name.
mylogger = logging.getLogger("MyLogger")

def test1():
    mylogger.info("Run!\n")

Create Windows service which will run procedure «test1». For this you should call command line (CMD) and run this command:

python.exe serv.py install

serv_install

Next step, start this service:

python.exe serv.py start

— Or start service from Windows service list
serv_servicelist

After some time try stopping service and see log file:

python.exe serv.py stop

— Or stop service from Windows service list

In log file you can see following info:
19-07-2016 10:34:20 — serv       — INFO     *** STARTING SERVICE ***

19-07-2016 10:34:20 — serv       — INFO     … STARTING SCHEDULE PROCESS …

19-07-2016 10:34:30 — mytest     — INFO     Run!

19-07-2016 10:34:40 — mytest     — INFO     Run!

19-07-2016 10:34:50 — mytest     — INFO     Run!

19-07-2016 10:35:00 — mytest     — INFO     Run!

19-07-2016 10:35:10 — mytest     — INFO     Run!

19-07-2016 10:35:14 — serv       — INFO     *** STOPPING SERVICE ***

P.s.: Have a good day!

How to check port access by Python

from socket import socket, gethostbyname, AF_INET, SOCK_STREAM
import time

'''
Function to check the availability of computer by port.
host - IP-address or computer name
port - port number
count - number of times to repeat the request on port
timeout - amount of seconds between echo requets on port
'''


def is_port(host="127.0.0.1", port=1433, count=1, timeout=1):
checked = False
    for i in range(count):
        ip = gethostbyname(host)
        sock = socket(AF_INET, SOCK_STREAM)
        result = sock.connect_ex((ip, port))
        if result == 0:
            print("*** port is available! ***")
            checked = True
            break
        sock.close()
        time.sleep(timeout)
        if not checked:
            print("*** port is NOT available! ***")
   return checked 

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
; 

 

How ping computer on Python?

This python code will do it!

import subprocess
import re

'''
Function to check the availability of the network computer.
Support computer on Windows
    host - name or ip remote computer (default 127.0.0.1)
    n - number of requests sent to the echo (default 3)
'''


def is_ping(host='127.0.0.1', n='3'):
    # call Windows utility Ping
    ping = subprocess.Popen(["ping", "-n", n, host], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    # get echo answer from remote computer
    out, error = ping.communicate()
    # decode string in utf-8
    msg = out.decode('utf-8')
    # availability status is True
    if re.findall("Reply from", msg):
        l = msg.split('\r\n')
        print(l)
        # get last availability status
        for el in l:
            if re.findall("Reply from", el):
                state = el
                print(el)
        # last availability status is True
        if re.findall("Reply from \d+.\d+.\d+.\d+: bytes=\d+ time<\d+ms TTL=\d+", state):
            print('*** {0} is available ***'.format(host))
            return True
        # last availability status is False
        else:
            print('*** {0} is unavailable ***'.format(host))
            return False
    # availability status is False
    else:
        print('*** {0} is unavailable ***'.format(host))
        return False

For example, save is_ping in file pinger.py and calling in python console:

from pinger import is_ping
is_ping('192.168.1.10', 4)

----------------------------------------
Reply from 192.168.1.10: bytes=32 time<1ms TTL=128
Reply from 192.168.1.10: bytes=32 time<1ms TTL=128
Reply from 192.168.1.10: bytes=32 time<1ms TTL=128
Reply from 192.168.1.10: bytes=32 time<1ms TTL=128
*** 192.168.1.10 is available ***
True

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.

ЭЦП AvCmUt3 в меню файла

Проверка и подпись ЭЦП выполняется утилитой AvCmUt3, входящей в Комплект Avest CSP Personal.
Все команды и параметры утилиты AvCmUt3 можно увидеть при помощи ключа «/?»:
cmd> AvCmUt3 /?

Проверка ЭЦП
Чтобы добавить проверку ЭЦП в контекстном меню файла, нужно выполнить следующие шаги:
1. Зайти в ветку реестра HKEY_CLASSES_ROOT\*\SHELL
2. Создать подраздел с именем EDScheck
3. В данном разделе создать параметр <String Value>:MUIVerb и задать ему значение:Проверка ЭЦП
key_edscheck
4. Создать в ветке EDScheck подраздел command
5. В подразделе command задайте параметру (Default) = c:\Program Files (x86)\Avest\AvPCM_nces\AvCmUt3.exe -v «%1»
eds_check_reg

Подпись ЭЦП
Чтобы добавить подпись ЭЦП в контекстном меню файла, нужно выполнить следующие шаги:
1. Зайти в ветку реестра HKEY_CLASSES_ROOT\*\SHELL
2. Создать подраздел с именем EDSsign
3. В данном разделе создать параметр <String Value>:MUIVerb и задать ему значение:Подпись ЭЦП
key_edssign
4. Создать в ветке EDSsign подраздел command
5. В подразделе command задайте параметру (Default) = c:\Program Files (x86)\Avest\AvPCM_nces\AvCmUt3.exe -s «%1»
eds_sign_reg

Теперь проверьте, что все сделано верно и в контекстном меню файла появились наши функции.
Для этого зайдите в проводник Windows или Total Commander и правой кнопкой мыши вызовите контекстное меню на любом файле:
eds_result

Search string in text file in Windows

  • Works only in Windows!

Create batch-file with name «findrow.bat» and save the following code:

@echo off
rem (c) 18.09.2015
rem infile - file in which you want to search a string
set infile=%1
set searchstring=%2
rem offset - include additional lines to (example, -3) or after (example, 3) search string
set offset=%3
rem get number of search string in file
for /F "delims=:" %%i in ('findstr /N /I /C:%searchstring% %infile%') do (set par=%%i)
set /a pos=%par%+%offset%
goto start
:1
if %offset% LSS 0 set k=1
if %offset% GTR 0 set k=-1
if [%pos%]==[%par%] goto end
set /a pos=%pos%+%k%
rem displays the search string (and additional)
:start
for /f "usebackq delims=" %%x in (`find /n /v "" %infile% ^| find "[%pos%]"`) do (set value=%%x)
echo %value%
goto 1
:end

File creation «findrow.bat» has three input parameters that must always be asked:

findrow.bat %1 %2 %3

Where:
%1 — file in which you want to search a string
%2 — search string
%3 — include additional lines to (example, -3) or after (example, 3) search string

Let’s look at a more complex case, for example, the search string in the binary log of server MySQL. Need to find a command ‘DROP TABLE `mysalers`’ in the binary log mysql-bin.000001:

mysqlbinlog --database=sale "c\:MySQL\data\mysql-bin.000001" > "d:\binlog.txt" | d:\findrow.bat "d:\binlog.txt" "DROP TABLE `mysalers`" -3
output
--------
[325]# at 6558
[326]#150918 10:51:09 server id 1  end_log_pos 6679 CRC32 0x22af3729    Query
thread_id=1     exec_time=0     error_code=0
[327]SET TIMESTAMP=1442562669/*!*/;
[328]DROP TABLE `mysalers` /* generated by server */

As a result, we obtain the desired command to delete a table `mysalers` and 3 lines going to it. These lines are also important because they contain the position of the search string and the future planned for her in the binary log. These positions can then be used when restoring the database of MySQL when you want to skip some commands.

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;