How can I read blob data in Oracle SQL Developer. It is stored in byte[] format. Is there any possibility to read in string format.
Asked
Active
Viewed 9.7k times
17
-
text is not readable form – bharat Feb 11 '15 at 08:41
1 Answers
33
Follow these steps in Oracle SQL Developer -
- Open data window of your table.
- The BLOB cell will be named as (BLOB).
- Right click the cell.
- You will see a pencil icon. Click on it.
- It will open a blob editor window.
- You would find two check boxes against the option View as : Image or Text.
- Select the appropriate check box.
- If above step is still convincing, then use the Download option.
Update
OP says "text is not understandable manner it is showing �� �� like this "
Probably, the locale-specific NLS characterset doesn't support those characters. It might also be that those are Multi-Byte characters. I would suggest, SPOOL the result to an HTML file, open it with your browser, you could view the content as most of the browsers are capable of displaying multiple charactersets.
You could do something like this from SQL*Plus -
SET MARKUP HTML ON SPOOL ON
SPOOL report.html
select substr(clob_column, 1, 32767) from table_name...
SPOOL OFF
Update 2
Regarding SPOOL as HTML, test case -
SET MARKUP HTML ON SPOOL ON
SPOOL D:\report.html
SELECT substr(ename, 1, 5) ename FROM emp where rownum <=5;
SPOOL OFF
Works perfectly for me, html file opens up with my browser, look at the screenshot -
Lalit Kumar B
- 47,486
- 13
- 97
- 124
-
What is readable format for you, please explain. I cannot address such a broad term. – Lalit Kumar B Feb 11 '15 at 08:53
-
What happens when you just do a `SUBSTR` over the column? At least you can view the first 32767 characters. – Lalit Kumar B Feb 11 '15 at 08:54
-
-
I am alredy using that substring query iam getting text like this ��srcom.hexgen.orm.PortfolioAlloc/���ZisActiveL_hexGenEntityVersiont3Lcom/hexgen/core/orm/basetypes/HexGenEntityVersion;L allocationPertLjava/math/BigDecimal;L – bharat Feb 11 '15 at 08:57
-
Because your NLS character set doesn't support those characters. Try to `SPOOL` it to an `HTML` file and see the content. – Lalit Kumar B Feb 11 '15 at 09:02
-
-
SET MARKUP HTML ON SPOOL ON SPOOL report.html select substr(blob_column, 1, 32767) from HEXGEN_HIS SPOOL OFF – bharat Feb 11 '15 at 09:18
-
At step 3, I have to Double-Left click in the cell to see the pencil. (I have version 4.0.2) – Dominique Fortin Aug 05 '20 at 21:47
-
1
-
@java-addict301 choose "Single Record View". Then you will see the pencil button in the popup that appears. – Robin Green Mar 11 '21 at 21:32
-
If the file is a zip file, make sure to save it as zip while using download option. Also, I figured that the substr approach doesn't work on BLOB columns. It can be used for CLOB columns. – little_amb Mar 22 '22 at 03:56
-