Use an Oracle clob in a predicate created from a String > 4k

Stream it. You can't just paste a huge value into a SQL statement.

You'll need to:

• Insert an empty BLOB in the INSERT statement (using EMPTY_BLOB()? ...don't quite remember).
• Get the output stream for the empty blob.
• Then get an input stream from the file. Please don't load the entire file in memory.
• Then transfer blocks from the input stream into the output stream using buffering. A 16 KB buffer should do.
• Close both streams.

This is the standard way of dealing with massive data in Oracle. Plenty of examples out there.

Retrieving massive data (BLOB and CLOB types) works the same way. Just use InputStreams in that case.

Reading the documentation of the "BIOVIA Direct" API, there's an interesting example on page 27, excerpt shown below:

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

It uses an already loaded CLOB. Therefore I guess a decent-enough solution would be to load the CLOB into a table of yours (or pre-load them all in advance), and then just use them.

Step #1 - Load your CLOB into a table:

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

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

And use your Java code to perform the CLOB insertion (probably using streams) as shown in another answer (plenty of examples in the Internet). For example, insert your mol data content with ID = 123.

Step #2 - Run your query, using the already loaded mol file:

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

You can set the :id parameter to 123 to use the file you loaded before (or any other one).

You can call your old fashion function like this. create a Class to handle the ResultSet

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

and call your query using the JdbcTemplate in your method

 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());

I had to revert to using a PreparedStatement, but i've improved the normal implementation a bit by getting the connection from Spring and using apache commons BeanListHandler to map the ResultSet to an object List

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

You can declare fileDataStr as CLOB using con which is the connection

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

and then use it like below

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

Also if you are using SID instead of Service name in your connection string try changing your property file like below

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