문자열 > 4k에서 만든 술어에 Oracle clob 사용

문자열 > 4k에서 만든 술어에 Oracle clob 사용

아래 Oracle SELECT 술어에 사용할 4000자 이상의 문자열(file_data bind 변수에 제공됨)로 클로브를 생성하려고 합니다.

myQuery=
select *
from dcr_mols
WHERE flexmatch(ctab,:file_data,'MATCH=ALL')=1;

TO_CLOB() round file_data를 추가하면 악명 높은 varchar의 Oracle 4k 제한에 실패합니다(4k 문자열도 괜찮습니다).SQL 개발자의 오류는 다음과 같습니다.

ORA-01460: unimplemented or unreasonable conversion requested
01460. 00000 -  "unimplemented or unreasonable conversion requested"

FYI flexmatch 함수는 분자 검색에 사용되며, 여기에 설명되어 있습니다: http://help.accelrysonline.com/ulm/onelab/1.0/content/ulm_pdfs/direct/developers/direct_2016_developersguide.pdf

기능 자체는 좀 복잡하지만 본질은 두 번째 파라미터가 클롭이어야 합니다.그래서 제 질문은 4000자 이상의 Java String bind_variable을 sql(또는 Java)의 clob으로 변환하는 방법입니다.

아래 방법(클랍 삽입 시 작동)을 자바(Spring boot 2)에서 사용해 보았습니다.

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("file_data", fileDataStr,Types.CLOB);
jdbcNamedParameterTemplate.query(myQuery,parameters,…

이 메서드는 작동해야 하지만 FYI의 변환된 Flexmatch 오류로 인해 실패합니다.

SQL state [99999]; error code [29902]; ORA-29902: error in executing ODCIIndexStart() routine\nORA-20100: 
MDL-0203: Unable to read from CLOB (csfrm=1, csid=873): 
ORA-22922: nonexistent LOB value\nMDL-0021: Unable to copy LOB to string\nMDL-1051: Molstructure search query is not a valid molecule\nMDL-0976: 
Molecule index search initialization failed\nORA-06512: at \"C$MDLICHEM80.MDL_MXIXMDL\", line 329\nORA-06512: at \"C$MDLICHEM80.MDL_MXIXMDL\", line 309\n; nested exception is java.sql.SQLException: 
ORA-29902: error in executing ODCIIndexStart() routine\nORA-20100: MDL-0203: Unable to read from CLOB (csfrm=1, csid=873): 
ORA-22922: nonexistent LOB value\nMDL-0021: Unable to copy LOB to string\nMDL-1051: Molstructure search query is not a valid molecule\nMDL-0976: 
Molecule index search initialization failed\nORA-06512: at \"C$MDLICHEM80.MDL_MXIXMDL\", line 329\nORA-06512: at \"C$MDLICHEM80.MDL_MXIXMDL\", line 309\n"

참고로 저는 SpringBoot 2를 사용하고 있지만 작동하기 위해 Oracle Connection(Spring NamedParametersJdbcTemplate 객체에서 획득)을 사용하는 어떤 방법도 얻을 수 없기 때문에(심지어 클로브 <4k>에서도) 바보 같은 짓을 한 것으로 의심됩니다.시도해 봤습니다.

 @Autowired
 NamedParameterJdbcTemplate  jdbcNamedParameterTemplate;
OracleConnection conn =  this.jdbcNamedParameterTemplate.getJdbcTemplate().getDataSource().getConnection().unwrap(OracleConnection.class);
Clob myClob =  conn.createClob();
myClob.setString( 1, fileDataStr);
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("file_data", myClob,Types.CLOB);

application.properties:

spring.datasource.url=jdbc:oracle:thin:@//${ORA_HOST}:${ORA_PORT}/${ORA_SID}
spring.datasource.username=${ORA_USER}
spring.datasource.password=${ORA_PASS}

참고로 오래된 학교에 가서 스프링이 아닌 연결과 Clob() 메서드가 설정된 Prepared Statement를 사용하면 잘 작동합니다.

OracleDataSource ods = new OracleDataSource();
String url ="jdbc:oracle:thin:@//" + ORA_HOST +":"+ORA_PORT +"/"+ORA_SID;
ods.setURL(url);
ods.setUser(user);
ods.setPassword(passwd);
Connection conn = ods.getConnection();
Clob myClob=conn.createClob();
PreparedStatement ps = conn.prepareStatement("select dcr_number from dcr_mols WHERE flexmatch(ctab,?,'MATCH=ALL')=1");
myClob.setString(1,myMol);
ps.setClob(1,myClob);
ResultSet rs =ps.executeQuery();

하지만 저는 자바나 SQL의 스프링 2 솔루션을 선호합니다. 어떤 도움이든, 제안해 주시면 감사하겠습니다.

스트리밍.SQL 문에 큰 값을 붙여넣을 수는 없습니다.

다음 작업이 필요합니다.

• 빈 BLOB 삽입INSERT문장(EMENT_BLOB(?...기억이 잘 나지 않음)을 사용합니다.
• 빈 블롭에 대한 출력 스트림을 가져옵니다.
• 그런 다음 파일에서 입력 스트림을 가져옵니다.전체 파일을 메모리에 로드하지 말아 주세요.
• 그런 다음 버퍼링을 사용하여 입력 스트림에서 출력 스트림으로 블록을 전송합니다.16KB 버퍼면 됩니다.
• 양쪽 스트림을 닫습니다.

이것이 Oracle에서 대규모 데이터를 처리하는 표준 방식입니다.많은 예시들이 있습니다.

방대한 데이터 검색 중(BLOB그리고.CLOBtypes)도 같은 방식으로 작동합니다.그런 경우에는 입력 스트림만 사용하면 됩니다.

"BIOVIA Direct" API의 설명서를 읽다 보면 27페이지에 흥미로운 예가 있는데, 아래에 발췌되어 있습니다.

select ...
from ...
where flexmatch(
ctab,
(select ctab from nostruct_table),
'all'
)=1

이미 로드된 CLOB를 사용합니다.따라서 CLOB를 테이블에 로드(또는 모두 미리 로드)한 후 사용하는 것이 적절한 해결책이 될 것으로 생각입니다.

1단계 - CLOB를 테이블에 로드합니다.

create table mol_file (
  id number(12) primary key not null,
  content clob
);

insert into mol_file (id, content) values (:id, :content);

그리고 자바 코드를 사용하여 다른 답변(인터넷의 수많은 예)과 같이 CLOB 삽입(아마도 스트림 사용)을 수행합니다.를 들어 =어 ID=로 mol123.

2단계 - 이미 로드된 mol 파일을 사용하여 쿼리를 실행합니다.

select *
from dcr_mols
WHERE flexmatch(
        ctab,
        (select content from mol_file where id = :id),
        'MATCH=ALL'
      ) = 1;

를 할 수 .:id에 매개 변수를 지정합니다.123이전에 로드한 파일(또는 다른 파일)을 사용합니다.

예전 패션 기능을 이렇게 부를 수 있습니다. 결과 집합을 처리할 클래스를 만듭니다.

 class MyPreparedStatementCallback implements PreparedStatementCallback {
    public Object doInPreparedStatement(PreparedStatement preparedStatement)
            throws SQLException, DataAccessException {
        ResultSet rs = preparedStatement.executeQuery();
        List result = new LinkedList();
        rs.close();
        return result;
    }
}

그리고 당신의 메소드에서 JdbcTemplate를 사용하여 당신의 쿼리를 호출합니다.

 jdbcTemplate.execute(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection)

                throws SQLException, DataAccessException {

            PreparedStatement ps = connection.prepareStatement("select dcr_number from dcr_mols WHERE flexmatch(ctab,?,'MATCH=ALL')=1");
            Clob myClob =  connection.createClob();
            myClob.setString( 1, fileDataStr);
            MapSqlParameterSource parameters = new MapSqlParameterSource();
            parameters.addValue("file_data", myClob, Types.CLOB);
            ps.setClob(1,myClob);
            return ps;

        };
    }, new MyPreparedStatementCallback());

PreparedStatement를 사용하는 것으로 되돌아가야 했지만 Spring에서 연결을 가져오고 Apache commons BeanListHandler를 사용하여 ResultSet을 개체 목록에 매핑하여 일반 구현을 조금 개선했습니다.

import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

@Autowired
DataSource dataSource;

List<MyDao> myMethod(String fileData){
    String myQuery="select * from dcr_mols WHERE flexmatch(ctab,?,'MATCH=ALL')=1";

try {
    Connection conn = this.dataSource.getConnection()   // Get connection from spring

    Clob myClob =  conn.createClob();   // Open a dB clob 
    myClob.setString( 1, fileData);     // add data to clob
    PreparedStatement ps = conn.prepareStatement(myQuery);
    ps.setClob(1,myClob);              // Add a clob into the PreparedStatement
    ResultSet rs =ps.executeQuery();   // Execute the prepared statement

    //ResultSetHandler<List<MyDao>> handler = new BeanListHandler<MyDao>(MyDao.class);   // Define the ResultSet handler
    ResultSetHandler<List<MyDao>> handler = new BeanListHandler<MyDao>(MyDao.class, new BasicRowProcessor(new GenerousBeanProcessor()));  // This is better than the above handler , because GenerousBeanProcessor removes the requirement for the column names to exactly match the java variables

    List<MyDao> myDaoList = handler.handle(rs);   // Map ResultSet to List of MyDao objects
    }catch (Exception e) {
        e.printStackTrace();
    }

return myDaoList;
}

연결인 con을 사용하여 fileDataStr을 CLOB로 선언할 수 있습니다.

java.sql.Clob fileDataStr = oracle.sql.CLOB.createTemporary
(con, false, oracle.sql.CLOB.DURATION_SESSION);

그다음에 아래와 같이 사용합니다.

 parameters.addValue("file_data", fileDataStr,Types.CLOB);

또한 연결 문자열에서 서비스 이름 대신 SID를 사용하는 경우 아래와 같이 속성 파일을 변경해 보십시오.

spring.datasource.url=jdbc:oracle:thin:@//${ORA_HOST}:${ORA_PORT}:${ORA_SID}

언급URL : https://stackoverflow.com/questions/60974278/use-an-oracle-clob-in-a-predicate-created-from-a-string-4k