How to update the CLOB column with bulk data using sql oracle?

Question

I have the data like (7A+5dfAA7D...) alphabet and numbers. Its length is greater than two lack characters. So I can't update that clob column using update because it throws an error saying the string literal too long.

How to update the values?

please help me.

Thanks in advance!

What sort of update are you doing: overwrsubiting? string replacement? appending? — APC
actually it is empty column simply update using simple update query — Manohar Kulanthai vel
You will need to use a PreparedStatement (or whatever equivalent thing your programming language offers you) — a_horse_with_no_name

Rene Rene · Accepted Answer · 2012-01-11T13:42:32

Here is part of some java code that inserts data from a file into a CLOB column. The trick is to first insert an empty_clob() value and then to update the record.

try {
            /* Register the Oracle driver */
            DriverManager.registerDriver(new OracleDriver());

            /* Establish a connection to the Oracle database. I have used the Oracle Thin driver.
              jdbc:oracle:thin@host:port:sid, "user name", "password" */

            conn = DriverManager.getConnection(connectString, userName, passWord);

            /* Set auto commit to false, it helps to speed up things, by default JDBC's auto commit feature is on.
               This means that each SQL statement is commited as it is executed. */

            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            /* Insert all the data, for the BLOB column we use the function empty_blob(), which creates a locator for the BLOB datatype.
               A locator is an object that ponts to the actual location of the BLOB data in the database. A locator is essential to manipulate BLOB data. */

            query = "INSERT INTO CLOB_TABLE (id,data_clob,bestandsnaam,dt_geplaatst) " +
                    "VALUES(" + ID + ", empty_clob(),\'" + fileName + "\',sysdate)";

            //System.out.println(query);

            stmt.execute(query);

            /* Once the locator has been inserted, we retrieve the locator by executing a SELECT statement 
               with the FOR UPDATE clause to manually lock the row. */

            query = "SELECT DATA_CLOB FROM CLOB_TABLE WHERE ID=\'" + ID + "\' FOR UPDATE";

            //System.out.println(query);
            rs = stmt.executeQuery(query);
            //System.out.println("Select statement uitgevoerd");

            if (rs.next()) {

                /* Once a locator has been retrieved we can use it to insert the binary data into the database. */
                CLOB clob = (CLOB)((OracleResultSet)rs).getClob(1);
                os = clob.getAsciiOutputStream();
                final File f = new File(fileName);
                is = new FileInputStream(f);
                final byte[] buffer = new byte[clob.getBufferSize()];
                int bytesRead = 0;
                while ((bytesRead = is.read(buffer)) != -1) {
                    os.write(buffer, 0, bytesRead);
                }
                clob = null;
                returnValue = 0;
            }


        } catch

How to update the CLOB column with bulk data using sql oracle?

2 Answers