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

Question

I'm trying to create a clob from a string of > 4000 chars (supplied in the file_data bind variable) to be used in a Oracle SELECT predicate below:

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

If I add TO_CLOB() round file_data it fails the infamous Oracle 4k limit for a varchar (it's fine for <4k strings). The error (in SQL Developer) is:

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

FYI The flexmatch function is used for searching molecules , and is a described here : http://help.accelrysonline.com/ulm/onelab/1.0/content/ulm_pdfs/direct/developers/direct_2016_developersguide.pdf

The function itself is a bit complicated but the essence is the 2nd parameter has to be a clob. So my question is how do I convert a Java String bind_variable of over 4000 chars to a clob in sql (or Java).

I tried the method below (which works when inserting clobs) in Java (Spring boot 2) using:

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

This method should work but it fails with a converluted flexmatch error which FYI is:

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"

Note i'm using SpringBoot 2 but I can't get any method using an OracleConnection (obtained from my Spring NamedParametersJdbcTemplate object) to work (even on clobs <4k) , so I suspect i've done something stupid. I've tried:

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

Note it works fine if I go old school and use a non spring connection plus a PreparedStatement,which has a setClob() method:

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

But I would prefer a Spring 2 solution in Java or Sql . Any help, suggestions appreciated.

Answer 1

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.