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

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

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).

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.