Java How to insert CLOB into oracle database

Question

I need to write an XML file content into oracle database where the column is of CLOB datatype  How will I do that

User · Answer

You can very well do it with below code  i am giving you just the code to insert xml hope u are done with rest of other things    import oracle xdb XMLType     now inside the class          this will be to convert xml into string  File file   new File your file path   FileReader fileR   new FileReader file   fileR read data   String str   new String data       now to enter it into db  conn   DriverManager getConnection serverName  userId  password    XMLType objXml   XMLType createXML conn  str       inside the query statement put this code  objPreparedstatmnt setObject your value index  objXml     I have done like this and it is working fine

User · Answer

This code worked for me  I use ojdbc6-11 2 0 2 jar   java sql Connection con  javax xml bind Marshaller marshaller   Clob xmlClob   con createClob    try     try  Writer xmlClobWriter   xmlClob setCharacterStream 1         m marshal jaxbObject  xmlClobWriter          xmlClobWriter close      try  PreparedStatement stmt   con prepareStatement  INSERT INTO table  xml  values             stmt setClob 1  xmlClob       stmt executeUpdate          finally     xmlClob free

User · Answer

OUTDATED See Lukas Eder s answer below    With about 100 lines of code  -  Here is an example   The main point  Unlike with other JDBC drivers  the one from Oracle doesn t support using Reader and InputStream as parameters of an INSERT  Instead  you must SELECT the CLOB column FOR UPDATE and then write into the ResultSet  I suggest that you move this code into a helper method class  Otherwise  it will pollute the rest of your code

User · Answer

Converting clob to string   Clob clob rs getClob 2         String str  String clob getSubString 1  int clob length           System out println  Clob Data is     str

User · Answer

The easiest way is to simply use the  stmt setString position  xml     methods  for  small  strings which can be easily kept in Java memory   or  try     java sql Clob clob        oracle sql CLOB createTemporary        connection  false  oracle sql CLOB DURATION SESSION      clob setString 1  xml     stmt setClob position  clob     stmt execute          Important  finally     clob free

User · Answer

passing the xml content as string   table1   ID   int XML  CLOB    import oracle jdbc OraclePreparedStatement     Your Code     void insert int id  String xml       try           String sql    INSERT INTO table1 ID XML  VALUES                      id                                     PreparedStatement ps   conn prepareStatement sql             OraclePreparedStatement  ps  setStringForClob 1  xml           ps execute            result   true            catch  Exception e            e printStackTrace

User · Answer

I had similar issue  Changed one of my table column from varchar2 to CLOB  I didn t needed to change any java code  I kept it as setString     only so no need to change set method as setClob   etch if you are using following versions ATLEAST of Oracle and jdbc driver   I tried in In Oracle 11g and driver ojdbc6-11 2 0 4 jar

User · Answer

Try this   there is no need to set its a CLOB    public static void main String   args                    try                           System out println  Opening db                         Class forName  oracle jdbc driver OracleDriver                         if con  null                       con DriverManager getConnection  jdbc oracle thin  192 9 200 103 1521  orcl   sas   sas                          if stmt  null                      stmt con createStatement                            int res 9                       String usersSql     call Esme Insertsmscdata                                   CallableStatement stmt   con prepareCall usersSql                  THIS THE CLOB DATA               stmt setString 1  SS  5268771  00058711  04192018  SS  5268771  00058712  04192018  SS  5268772  00058713  04192018  SS  5268772  00058714  04192018  SS  5268773  00058715  04192018  SS  5268773  00058716  04192018  SS  5268774  00058717  04192018  SS  5268774  00058718  04192018  SS  5268775  00058719  04192018  SS  5268775  00058720  04192018                               stmt setString 2   bcvbcvb                        stmt setString 3  String valueOf  4522                         stmt setString 4   42 25 632 25                        stmt registerOutParameter 5 OracleTypes NUMBER                           stmt execute                        res stmt getInt 5                       stmt close                         System out println res                                                 catch Exception e                                                   try                                                         con close                              catch  SQLException e1

User · Answer

For this purpose you need to make the connection result set      ResultSet TYPE SCROLL SENSITIVE ResultSet CONCUR UPDATABLE   Connection con null    initialize connection variable to connect to your database    Statement stmt   con createStatement ResultSet TYPE SCROLL SENSITIVE ResultSet CONCUR UPDATABLE   String query  Select MYCLOB from TABLE NAME for update   con setAutoCommit false   ResultSet resultset stmt executeQuery query     if resultset next     oracle sql CLOB    clobnew     OracleResultSet  rss  getCLOB  MYCLOB    PrintWriter pw   new PrintWriter clobnew getCharacterOutputStream      BufferedReader br   new BufferedReader  new FileReader  new File  filename xml        String  lineIn   null  while    lineIn   br readLine        null         pw println  lineIn          pw close          br close       con setAutoCommit true   con commit        Note  its important that you add the phrase for update at the end of the query that is written to select the row     Follow the above code to insert the XML file

User · Answer

Take a look at the LobBasicSample for an example to use CLOB  BLOB  NLOB datatypes

[java] Java: How to insert CLOB into oracle database

Examples related to java

Examples related to oracle

Examples related to insert

Examples related to clob