select * returns CLOB

Question

I have not used oracle before and I was to modify the clob data as it has my home phone number. But when I do select * from table shows CLOB and I cannot see the data. How can I see the data and how to I Update?

I need the query.

Thanks Jen

Answer 1

In PL/SQL Developer, select ROWID along with table columns:

SELECT  t.*, t.rowid
FROM    mytable t

This will allow you to edit the table's data.

Then just check a ... button near the CLOB field and edit it.

You can load it from file or just type into the edit field.

Answer 2

In code?

Java (updating a CLOB field held as a field in an object called dbo):

String sql = "SELECT clobby_wobby FROM table WHERE uuid = ? FOR UPDATE";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, dbo.getUID());
ResultSet rs = ps.executeQuery();
if (rs.next()) {
    oracle.sql.CLOB clob = (oracle.sql.CLOB) ((OracleResultSet) rs).getClob(1);
    failed = writeClob(clob, dbo.getClobbyWobby());
}
rs.close();
ps.close();

where writeClob() is:

protected boolean writeClob(oracle.sql.CLOB clob, String data) throws SQLException {

    if (data == null) { return false; }
    return writeClob(clob, data.toCharArray());
}

protected boolean writeClob(oracle.sql.CLOB clob, char[] data) throws SQLException {

    if (data == null || clob == null) { return false; }
    char[] buffer = new char[clob.getBufferSize()];
    Writer os = clob.getCharacterOutputStream();
    int len = -1;
    CharArrayReader car = new CharArrayReader(data);
    try {
        while ((len = car.read(buffer)) != -1) {
            os.write(buffer, 0, len);
            os.flush();
        }
    } catch (IOException ioe) {
        logger.error("IOException copying clob data into DB", ioe);
        return false;
    } finally {
        try {
            car.close();
            os.close();
        } catch (IOException ioe) {}
    }
    return true;
}

and sometimes I wonder why the darned Oracle driver itself can't detect that a field is a CLOB and just transparently cope with ps.setString("very long value"); itself... and this is where someone replies saying "why not use Oracle LOB Java utility methods?!" that I never knew about...

Answer 3

Are you using an old version of sqlplus?

If you are using sql developer, just make the column of the grid wider.

Answer 4

What tool are you using to perform the query? sqlplus will truncate select from a clob column to the value of the parameter long. If you are using sqlplus then set long to a large enough value to hold the clob and then doing a simple select should return data. Clobs are just text so it can be query as any other column in a table. If you are using sqlplus and it returns nothing instead of partial, then make sure the column in the table is populated.

Answer 5

I am using dbVisualizer and I did a regular update and it worked. Thanks MichaelN for informing that Clobs are just text so it can be query as any other column in a table. This helped me fix my issue in few minutes.