Re: How to read BLOB data from Oracle table

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Tue, 30 Dec 2008 17:53:16 -0800 (PST)
Message-ID: <434e016c-5f41-46c7-abca-2c4da0670c0e@z27g2000prd.googlegroups.com>

On Dec 30, 7:05 pm, shweta.kapar..._at_googlemail.com wrote:
> On Dec 27, 3:37 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
> > On Dec 26, 4:11 pm, shweta.kapar..._at_googlemail.com wrote:
>
> > > Hi All
> > > How to read BLOB column using simple method.
> > > I can do using TOAD ( export option).
> > > Regards
>
> > Hi,
>
> > What application platform (java, .Net, etc.) environment are you
> > using?
>
> > Regards,
>
> > Steve
>
> 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

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/rep_at_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_at_83ad50 Received on Tue Dec 30 2008 - 19:53:16 CST