How to read BLOB data from Oracle table

[shweta....@googlemail.com]

Hi All
How to read BLOB column using simple method.
I can do using TOAD ( export option).
Regards

[DA Morgan]

Define "READ."

Anyone can "read" a blob with the DBMS_LOB package.
http://www.psoug.org/reference/dbms_lob.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Steve Howard]

Hi,

What application platform (java, .Net, etc.) environment are you
using?

Regards,

Steve

[Viktor Zacek]

<shweta....@googlemail.com> wrote:

I do not know toad, but I would expect a way to export it.

If you can use Hora (from Keeptool, rival of toad ;-) ):
Table
-> Tab: Content
-> select desired row
-> click on "..." beside the blob-field, then "save to file"
-> select folder and filename

here you go ;-)

Best regards,
Viktor Zacek

[Mladen Gogala]

You can kiss the TOAD and will turn into a BLOB.

--
Mladen Gogala
http://mgogala.freehostia.com

[shweta....@googlemail.com]

Hi Steve,
Sorry for the late response.
We are using Java. And we keep JMS database for message store. It has
tables with BLOB cols. those i wanted to read.
Regards

[Steve Howard]

Try this...

SQL> select dbms_lob.substr(b,2000,1) from blobtest;

DBMS_LOB.SUBSTR(B,2000,1)
--------------------------------------------------------------------------------
7F454C460101010000000000000000000200030001000000208C040834000000582E000000000000
34002000080028002300200006000000340000003480040834800408000100000001000005000000
04000000030000003401000034810408348104081300000013000000040000000100000001000000
000000000080040

SQL> desc blobtest;
Name Null? Type
----------------------------------------- --------
----------------------------
C NUMBER
B BLOB

SQL> exit

linux2:oracle:tst10g1:/home/oracle>cat readBlob.java
import java.sql.*;
import oracle.jdbc.driver.*;

public class readBlob {
public static void main (String args[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:rep/r...@192.168.1.54:1521:tst10g1");
PreparedStatement psGetBlob = conn.prepareStatement ("select b "
+
"from
blobtest " +
"where c
= ?");
psGetBlob.setInt(1, Integer.parseInt(args[0]));
ResultSet rst = psGetBlob.executeQuery();
if (rst.next()) {
oracle.sql.BLOB blob = ((OracleResultSet)rst).getBLOB(1);
System.out.println(blob);
}
rst.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
linux2:oracle:tst10g1:/home/oracle>java readBlob 1
oracle.sql.BLOB@83ad50