How to Read a BLOB for a Human Being?
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:
SELECT UTL_RAW.cast_to_binary_float (DBMS_LOB.submyrecord (myrecord.myrecordess, 4, 1 + (myrecordessnameid * 4) ) ) AS myrecordessvalue FROM mytable myrecord WHERE myrecordessid = 123456;
You can also run a stored procedure, similar to this:
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;
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 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>
Now create a runtime test:
/** * 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)); } } }