Николай Бараненко

нашел такое решение, вопрос с условностью проверки наличия атрибута read

for row in cursor.fetchall():
    count_rows+=1
    result_inside={}
    row_content=[]
    for col, val in zip(col_names, row):
        # проверяем наличие атрибута у объекта, если CLOB то приеняем read
        if hasattr(val, 'read'):
            result_inside[col] = val.read()
        else:
            result_inside[col] = val
        row_content.append(result_inside[col])
    json_file.write(json.dumps(result_inside, default = myconverter))

у решения выше есть проблема

Internally, Oracle uses LOB locators which are allocated based on the
cursor array size. Thus, it is important that the data in the LOB
object be manipulated before another internal fetch takes place. The
safest way to do this is to use the cursor as an iterator. In
particular, do not use the fetchall() method.

поэтому

for row in cursor:
    count_rows+=1
    result_inside={}
    row_content=[]
    for col, val in zip(col_names, row):
        # проверяем наличие атрибута у объекта, если CLOB то приеняем read
        if hasattr(val, 'read'):
            result_inside[col] = val.read()
        else:
            result_inside[col] = val
        row_content.append(result_inside[col])
    json_file.write(json.dumps(result_inside, default = myconverter))

у объекта csv_file нет действитльно атрибута csv :-)

writer= csv.writer(csv_file, delimiter=';')
    for line in data:
        writer.writerow(result_inside)

CREATE TABLE UM_TASKS_PARTITION_BY_ID (
  id         INTEGER not null,
  id_task    INTEGER,
  status     VARCHAR2(50),
  result     CLOB,
  start_date DATE,
  end_date   DATE,
  duration   FLOAT
) 
PARTITION BY RANGE(ID) INTERVAL (5) (
  partition UM_TASKS_PARTITION_BY_ID_1 values less than (5)    
);

проблема была в том, что в БД service_name был в нижнем регистре

SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string dboracle

установка пакетов помогла решить эту проблему

yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install make -y
yum install nfs-utils -y
yum install net-tools -y
yum install smartmontools -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y

В общем в одной подсети инстансы oracle с одинаковыми service name и портами жить могут.

в listener.ora добавляем информацию о новом подключении по IP и меняем порт

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 2001))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2001))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 2001))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2001))
    )	
  )

В tnsnames.ora заносим информацию о новых экземплярах

SERVER01_DBORACLE_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 2001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dboracle)
    )
  )  
  
SERVER01_DBORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 2001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dboracle)
    )
  )    
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2001))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

LISTENER_DBORACLE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 2001))

После перезапускаем службы листенера и самой БД.

Проверяем командой
lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 13-APR-2017 16:27:57

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=2001)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date                13-APR-2017 15:28:54
Uptime                    0 days 0 hr. 59 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\DataBases\Oracle\product\12.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\DataBases\Oracle\diag\tnslsnr\SERVER01\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=2001)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC2001ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.22)(PORT=2001)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC2001ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=SERVER01)(PORT=5500))(Security=(my_wallet_directory=D:\DATABASES\ORACLE\admin\dboracle\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dboracle" has 1 instance(s).
  Instance "dboracle", status READY, has 1 handler(s) for this service...
Service "dboracleXDB" has 1 instance(s).
  Instance "dboracle", status READY, has 1 handler(s) for this service...

The command completed successfully

обязательно должна быть информация о

Service "dboracle" has 1 instance(s).
  Instance "dboracle", status READY, has 1 handler(s) for this service...
Service "dboracleXDB" has 1 instance(s).
  Instance "dboracle", status READY, has 1 handler(s) for this service...

lsnrctl reload – переинициализировать листенер иногда помагает, когда инстанс не инициализовался листенером

удалось решить проблему таким образом

в рамках одной подсети было исключено пересечение в наименованиях ключа листенера KEY = EXTPROC2002

решил проблему так

conn = jaydebeapi.connect('oracle.jdbc.driver.OracleDriver',
                          connection_string,[USERNAME,PASSWORD])

Рабочий вариант будет выглядеть так

Connection conn = null;
CallableStatement stmt = null;
ResultSet rset = null;

String SOME_NAME = "{call REPORTS.mi_inwork(?,?)}";

	try {
        conn = DriverManager.getConnection(Connection_URL,UserName, Password);

		        stmt = conn.prepareCall(SOME_NAME);//We have declared this at the very top
        stmt.setString(1, "Итого");//Passing CompanyID here
        stmt.registerOutParameter(2, OracleTypes.CURSOR);//Refcursor selects the row based upon query results provided in Package.
        stmt.execute();
        rset = (ResultSet) stmt.getObject(2);

        while (rset.next()) {
            out.println(rset.getString(1)+" | "+ rset.getString(2)+" | "+ rset.getString(3));
}
} catch (Exception e) {
     //   LOGGER.error("Error extracting ", e);
        out.println( "<h1>exception: "+e.getMessage()+"</h1>" );
} finally {
     //   DBUtils.cleanUp(conn, stmt, rset);
}

проблему удалось решить так

declare
  l_statement varchar2(32767);
begin

  l_statement := 'begin
                     EXAMPLE1_POINTS(''Да'',''средний"'');
                  end;';
  begin
     execute immediate l_statement;
  exception
     when others 
     then 
       null;
        /*log_error(sqlerrm);*/
  end;


  l_statement := 'begin
                     EXAMPLE2_POINTS(''Да'',''средний"'');
                  end;';
  begin
     execute immediate l_statement;
  exception
     when others 
     then 
       null;
        /*log_error(sqlerrm);*/
  end;

  l_statement := 'begin
                     EXAMPLE3_POINTS(''Да'',''старый"'');
                  end;';
  begin
     execute immediate l_statement;
  exception
     when others 
     then 
       null;
        /*log_error(sqlerrm);*/
  end;

end;