oracle 选择 * 返回 CLOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/722113/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select * returns CLOB
提问by Quassnoi
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 * fromtable shows CLOBand I cannot see the data. How can I see the data and how to I Update?
我之前没有使用过oracle，我要修改clob数据，因为它有我的家庭电话号码。但是当我做select * from表格显示时CLOB，我看不到数据。如何查看数据以及如何更新？
I need the query.
我需要查询。
回答by MichaelN
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.
您使用什么工具来执行查询？sqlplus 会将来自 clob 列的 select 截断为参数 long 的值。如果您使用的是 sqlplus，则将 long 设置为足够大的值以保存 clob，然后执行简单的选择应该返回数据。Clob 只是文本，因此可以像表中的任何其他列一样查询它。如果您正在使用 sqlplus 并且它不返回任何内容而不是部分，那么请确保表中的列已填充。
回答by Quassnoi
In PL/SQL Developer, select ROWIDalong with table columns:
在 中PL/SQL Developer，ROWID与表列一起选择：
SELECT t.*, t.rowid
FROM mytable t
This will allow you to edit the table's data.
这将允许您编辑表的数据。
Then just check a ...button near the CLOBfield and edit it.
然后只需检查...该CLOB字段附近的按钮并对其进行编辑。
You can load it from file or just type into the edit field.
您可以从文件中加载它，也可以在编辑字段中输入。
回答by JeeBee
In code?
在代码中？
Java (updating a CLOB field held as a field in an object called dbo):
Java（更新在名为 dbo 的对象中作为字段保存的 CLOB 字段）：
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:
其中 writeClob() 是：
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...
有时我想知道为什么该死的 Oracle 驱动程序本身无法检测到字段是 CLOB 并且只是透明地处理 ps.setString("very long value"); 本身......这就是有人回答说“为什么不使用 Oracle LOB Java 实用程序方法？！”的地方 我从来不知道...
回答by tuinstoel
Are you using an old version of sqlplus?
您使用的是旧版本的 sqlplus 吗？
If you are using sql developer, just make the column of the grid wider.
如果您使用的是 sql developer，只需将网格的列加宽即可。
回答by tuinstoel
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.
我正在使用 dbVisualizer 并且我做了一个定期更新并且它起作用了。感谢 MichaelN 告知 Clob 只是文本，因此它可以作为表中的任何其他列进行查询。这帮助我在几分钟内解决了我的问题。
回答by Esneyder
I present my approach for you situation: This is an example in pl/sql. You can create a procedure for read from clob.
我为您介绍了我的方法：这是 pl/sql 中的一个示例。您可以创建一个从 clob 读取的过程。
Declare
Variableclob Clob;
Temp_Save Varchar2(32767);
index_pass number;
Begin
For I In (select id_reporte from reporte where id_reporte between 201 and 218)
Loop
Index_Pass:=To_Number(I.Id_Reporte);
Select Consulta Into Temp_Save From Reporte Where Id_Reporte=Index_Pass;
Variableclob:=To_Clob(Temp_Save);
Dbms_Output.Put_Line(Variableclob);
End Loop ;
End;
Remember to use:
set serveroutput on;
begin DBMS_OUTPUT.ENABLE(9999999999999); end;
回答by BB-on-StackOverflow
In SQL developer, "Open" table. Select the "Data" tab (this will retrieve all data). Then in the Filter bar (top side), provide your criteria to get to the single row that you want to update. Update the required column(s) and then Commit the changes.
在 SQL 开发人员中，“打开”表。选择“数据”选项卡（这将检索所有数据）。然后在过滤器栏（顶部）中，提供到达要更新的单行的条件。更新所需的列，然后提交更改。
