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开发人员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:数字或值错误:原始变量长度太长
The BLOB contains text in XML format.
BLOB包含XML格式的文本。
5 个解决方案
#1
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
#2
Cause it over the size of the display field. It needs to set the size You add 1500
for the substr
, it should be work.
导致它超过显示字段的大小。它需要设置你为substr添加1500的大小,它应该是有效的。
select utl_raw.cast_to_varchar2(dbms_lob.substr(colname,1500))
from user_settings where <row_id>=...
#3
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:你需要坐下来弄清楚你正在处理什么,然后找出你需要做什么。
#4
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)中
#5
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写入String或文件中。
#1
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
#2
Cause it over the size of the display field. It needs to set the size You add 1500
for the substr
, it should be work.
导致它超过显示字段的大小。它需要设置你为substr添加1500的大小,它应该是有效的。
select utl_raw.cast_to_varchar2(dbms_lob.substr(colname,1500))
from user_settings where <row_id>=...
#3
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:你需要坐下来弄清楚你正在处理什么,然后找出你需要做什么。
#4
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)中
#5
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写入String或文件中。