Jonathan Lalou's Blog

Case

I have had to access a BLOB and read its content. By principle, I dislike using binary objects, which do not suit easy tracing and auditing. Anyway, in my case, floats are stored in a BLOB, and I need read them in order to validate my current development.

You have many ways to read the content of the BLOB. I used two: SQL and Java

SQL

Start your TOAD for Oracle ; you can launch queries similar to this:

[sql]SELECT UTL_RAW.cast_to_binary_float
(DBMS_LOB.submyrecord (myrecord.myrecordess,
4,
1 + (myrecordessnameid * 4)
)
) AS myrecordessvalue
FROM mytable myrecord
WHERE myrecordessid = 123456; [/sql]
You can also run a stored procedure, similar to this:
[sql]
DECLARE
blobAsVariable BLOB;
my_vr RAW (4);
blobValue FLOAT;
bytelen NUMBER := 4;
v_index NUMBER := 5;
jonathan RAW (4);
loopLength INT;
BEGIN
SELECT myField
INTO blobAsVariable
FROM myTable
WHERE tableid = (5646546846);

DBMS_LOB.READ (blobAsVariable, bytelen, 1, jonathan);
loopLength := UTL_RAW.cast_to_binary_integer (jonathan);

FOR rec IN 1 .. loopLength
LOOP
DBMS_LOB.READ (blobAsVariable, bytelen, v_index, my_vr);
blobValue := UTL_RAW.cast_to_binary_float (my_vr);
v_index := v_index + 4;
DBMS_OUTPUT.put_line (TO_CHAR (blobValue));
END LOOP;
END;[/sql]

Java

I am still not sure to be DBA expert. Indeed I am convinced I am more fluent in Java than in PL/SQL 😉

Create a Spring configuration file, let’s say BlobRuntimeTest-applicationContext.xml:
[xml]<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

<!– $Id: BlobRuntimeTest-applicationContext.xml $ –>
<bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@myDBserver:1234:MY_SCHEMA"/>
<property name="username" value="jonathan"/>
<property name="password" value="lalou"/>
<property name="initialSize" value="2"/>
<property name="minIdle" value="2"/>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>

</beans>[/xml]

Now create a runtime test:
[java]/**
* User: Jonathan Lalou
* Date: Aug 7, 2011
* Time: 5:22:33 PM
* $Id: BlobRuntimeTest.java $
*/
public class BlobRuntimeTest extends TestCase {
private static final Logger LOGGER = Logger.getLogger(BlobRuntimeTest.class);

private static final String TABLE = "jonathanTable";
private static final String PK_FIELD = "jonathanTablePK";
private static final String BLOB_FIELD = "myBlobField";
private static final int[] PK_VALUES = {123, 456, 789};

private ApplicationContext applicationContext;
private JdbcTemplate jdbcTemplate;

@Before
public void setUp() throws Exception {
applicationContext = new ClassPathXmlApplicationContext(
"lalou/jonathan/the/cownboy/BlobRuntimeTest-applicationContext.xml");
assertNotNull(applicationContext);
jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
assertNotNull(jdbcTemplate);
}

@After
public void tearDown() throws Exception {
}

@Test
public void testGetArray() throws Exception {
for (int pk_value : PK_VALUES) {
final Blob blob;
final byte[] bytes;
final float[] floats;

blob = (Blob) jdbcTemplate.queryForObject("select " + BLOB_FIELD + " from " + TABLE + " where " + PK_FIELD + " = " + pk_value, Blob.class);
assertNotNull(blob);
bytes = blob.getBytes(1, (int) blob.length());
// process your blob: unzip, read, concat, add, etc..
// floats = ….

LOGGER.info("Blob size: " + floats.length);
LOGGER.info(ToStringBuilder.reflectionToString(floats));
}
}
}
[/java]