code_junkie

Oracle single-table constant merge with CLOB using JDBC

Nov 12th, 2011
80
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.98 KB | None | 0 0
  1. create table data (
  2. id VARCHAR2(255),
  3. key VARCHAR2(255),
  4. value CLOB);
  5.  
  6. merge into data
  7. using (
  8. select
  9. ? id,
  10. ? key,
  11. ? value
  12. from
  13. dual
  14. ) val on (
  15. data.id=val.id
  16. and data.key=val.key
  17. )
  18. when matched then
  19. update set data.value = val.value
  20. when not matched then
  21. insert (id, key, value) values (val.id, val.key, val.value);
  22.  
  23. ORA-01461: cannot bind a LONG value for insert into a long column
  24.  
  25. CLOB tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);
  26.  
  27.  
  28. // Open the temporary CLOB in readwrite mode to enable writing
  29. tempClob.open(CLOB.MODE_READWRITE);
  30.  
  31. // Get the output stream to write
  32. Writer tempClobWriter = tempClob.getCharacterOutputStream();
  33.  
  34. // Write the data into the temporary CLOB
  35. tempClobWriter.write(stringData);
  36.  
  37. // Flush and close the stream
  38. tempClobWriter.flush();
  39. tempClobWriter.close();
  40.  
  41. // Close the temporary CLOB
  42. tempClob.close();
  43.  
  44. myStatement.setCLOB(column.order, tempClob);
Add Comment
Please, Sign In to add comment