java - Insert CLOB into Oracle database -


my question is: how around ora-01704: string literal long error when inserting (or doing in queries) clobs?

i want have query this:

insert    mytable values ('clob1')    mytable values ('clob2') --some of these clobs more 4000 characters...    mytable values ('clob3') select * dual; 

when try actual values though ora-01704: string literal long back. pretty obvious, how insert clobs (or execute statement @ clob)?

i've tried looking @ question, don't think has i'm looking for. clobs have in list<string> , iterate through them make statement. code follows:

private void insertqueries(string temptablename) throws filenotfoundexception, dataexception, sqlexception, ioexception { string prequery = "  " + temptablename + " values ('"; string postquery = "')" + stringhelper.newline; stringbuilder inserts = new stringbuilder("insert all" + stringhelper.newline); list<string> readqueries = getdomoqueries(); (string query : readqueries) {   inserts.append(prequery).append(query).append(postquery); } inserts.append("select * dual;");  databasecontroller.getinstance().executequery(databaseconnectionurl, inserts.tostring()); 

}

public resultset executequery(string connection, string query) throws dataexception, sqlexception {   connection conn = connectionpool.getinstance().get(connection);   statement stmt = conn.createstatement();   resultset rs = stmt.executequery(query);   conn.commit();   connectionpool.getinstance().release(conn);   return rs; } 

you making way complicated.

use preparedstatement , addbatch() each clob in list:

string sql = "insert  " + temptablename + " values (?)"; preparedstatement stmt = connection.preparestatement(sql); (string query : readqueries) {   stmt.setcharacterstream(1, new stringreader(query), query.lenght());   stmt.addbatch(); } stmt.exececutebatch(); 

no messing around escaping strings, no problem length of literals, no need create temporary clobs. , fast using single insert statement.

if using current driver (> 10.2) think setcharacterstream() call , creation of reader not necessary either. simple setstring(1, query) work well.


Comments

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

php - How can I echo out this array? -