oracle 查看包含 XML 格式文本的 BLOB 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/979322/
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
View BLOB data which contains text in XML format
提问by wonea
How to view a BLOB data, can i export it to text file? I am using Oracle SQL developer 5.1. When i tried
如何查看 BLOB 数据，我可以将其导出到文本文件吗？我正在使用 Oracle SQL developer 5.1。当我尝试
select utl_raw.cast_to_varchar2(dbms_lob.substr(COLNAME))
from user_settings where <fieldname>=...
It returns the following error: ORA-06502 PL/SQL : numeric or value error : raw variable length too long
它返回以下错误： ORA-06502 PL/SQL : numeric or value error : raw variable length too long
The BLOB contains text in XML format.
BLOB 包含 XML 格式的文本。
回答by wonea
To view xml data stored as a BLOB, do the following;
要查看存储为 BLOB 的 xml 数据，请执行以下操作；
- Open the table view, with the Data tab selected
- Double click on the column field value, and a pencil button should appear in the field. Click the pencil button.
- The Edit Value window should open, click checkbox for 'View As: Text'. From this window you can also save out any particular file data you require.
- 打开表格视图，选择数据选项卡
- 双击列字段值，该字段中应出现一个铅笔按钮。单击铅笔按钮。
- 应打开“编辑值”窗口，单击“查看为：文本”复选框。在此窗口中，您还可以保存所需的任何特定文件数据。
PS: I'm running Oracle SQL Developer version 3.1.05
PS：我正在运行 Oracle SQL Developer 版本 3.1.05
回答by steps
Cause it over the size of the display field. It needs to set the size
You add 1500for the substr, it should be work.
导致它超过显示字段的大小。它需要设置大小您添加1500的substr，它应该是工作。
select utl_raw.cast_to_varchar2(dbms_lob.substr(colname,1500))
from user_settings where <row_id>=...
回答by Arafangion
BLOB data is typically just... a binary blob of data.
BLOB 数据通常只是……一个二进制数据块。
Sure, you can export it to a text file by converting it to some kind of text representation... But what if it is an image?
当然，您可以通过将其转换为某种文本表示形式将其导出为文本文件……但如果它是图像呢？
jaganath: You need to sit down and figure out what it is you're dealing with, and then find out what it is you need to do.
jaganath：你需要坐下来弄清楚你在处理什么，然后找出你需要做什么。
回答by Gary Myers
You could look at DBMS_LOB.CONVERTTOCLOB
你可以看看 DBMS_LOB.CONVERTTOCLOB
But if it is XML, why store it in a BLOB rather than an XMLType (or CLOB)
但如果是 XML，为什么要将其存储在 BLOB 中而不是 XMLType（或 CLOB）中
回答by Rahul
From the error message it seems that the blob length is too long to fit into a varchar. You could do the conversion in your application code and write the XML into a String or file.
从错误消息看来，blob 长度太长而无法放入 varchar。您可以在应用程序代码中进行转换并将 XML 写入字符串或文件。
