Skip to Main Content
  • Questions
  • Dynamic query containing Blob, which contains different field.


Question and Answer

Tom Kyte

Thanks for the question, Sathish.

Asked: December 01, 2002 - 10:07 pm UTC

Last updated: May 23, 2006 - 10:08 am UTC

Version: 8i

Viewed 1000+ times

You Asked

I have a table with a blob field, and type among other details. Say,
Create table trx (
trxId Number(18),
trxType Varchar2(20),
objValue Blob,

Blob Fields contains a java serialised object, different objects based on types, though all of them implements same interface.

We access this object always thru' j2ee container, so it works fine so far.

Now users want to use reports using sqlplus, crystall reports etc. So they want a solution to this blob issue.

Assuming users know the fields that in blob, I was thinking of writing a stored procedure, which accepts a (out )ref cursor, and query. Query would contain some predefined prefix, say trx_view.xx so that Stored Proc would know its part of blob field.

for example,
exec ReportUtil.executeReport(:refcursor, "select trxId, trx_view.BillPayee, trx_view.transferedAmount, companyName from trx, company where trx.companyId=company.companyId");

Where BillPayee, transferedAmount are the blob fields( Note that these fields won't be there always, this will be based on trxType)

The Stored procedure executeReport would call a java stored procudure, to parse and give a query like this: (No particular reason to use java stored proc. My developer found it easier to parse using java)

Select trxId, ReportUtil.Blob2Varchar(serialisedBlob,1), ReportUtil.Blob2Varchar(serialisedBlob,2), companyName from trx, company, ( select ReportUtil.getSerialisedBlob(objValue) from trx ) where trx.companyId=company.companyId.

Implementation of ReportUtil.getSerialisedBlob:
getSerialisedBlob(objValue in Blob, fields in Varchar_table) return Varchar2.

This will accept a blob field, a varchar table ,which is {"BillPayee","transferedAmount" in this case} and return a pipe separted values.( For example, "ABC Corp|100.00"). This is implemented in java stored Proc

Implementation of ReportUtil.Blob2Varchar:
ReportUtil.Blob2Varchar(pipeSepartedValue Varchar2, int Number) return varchar

Returns the proper value, based on the pipe field.

Rational Behind:
I want to deserialised only once for each row, as its a costly operation.

I thought of inserting into a temp table, and then fetching it but don't want to perform the operation twice. Also my output would contain a huge result set. Thought it would be inefficient(not sure).

I also thought of returning a table ( instead of ugly pipe separated values ), but it gives end of connection error once in both sqlplus and plsql procedure.

Returns a lot of records.
Avoid quering twice, means,

select blobValue from <query>
then select required fields..

In this way, processing will be easier, but there is over load. ( Is there really matter)

No blob field in where clause
no functions to use with blob field( like trx_view.BillPayeeAmount+trx_view.tra nsferAmount -- This is for ease of parsing. May be can do it later.
Not allowed to do any DDL in runtime.

Looks very messy approach to me, your suggested way? Consider there will be lot of records, need to efficient.
How to cater for complex objects? ( My ObjValue would not jsut contain a flattened structure,but also some dept ). May be we can assume one level depth.
How to cater for other types.( above just assumes Varchar..Atleast I need to support number, date)

Oracle 8i on Solaris.

Thanks a lot.

and Tom said...

I see no way to avoid this "overhead" and do not have a simple clean answer for you. Sorry.

But hey -- this is a perfect example.

A perfect example of why you want to store data in the database in the format that the database natively supports.

A perfect example of why you don't want to use the database as a bit bucket as you have done!

So, you'll take this serialized java object, parse it, return a string which needs yet MORE parsing.....

Sorry -- don't know what to tell you. There is going to be overhead for all of this parsing, calling Java from SQL, etc. If only you had stored the data in a format understandable to the database itself. If it as if you asked me to memorize a book written in French. I could do it -- but I would get nothing from it. I would not be able to answer a single question anyone asked about the book. The best I could do is regurgitate verbaitim the contents of the book -- but provide no meaning, no inflection, nothing.

But this is a great example of what I've been saying for a long time. If you tie the logic all up in the application -- if you make that fatal mistake of assuming "all access via my application only" -- you'll find out the hard way you were wrong. Don't get me wrong, your in great company -- 3278 Green Screen applications on a mainframe, many of them did this. Client Server applications -- famous for it.

Year after year, the latest greatest programming paradigm comes along -- only to be upsurped by the latest greatest next year. and then what? that really cool thing you did last year is the monkey on your back now.

The data is key here, the applications are almost secondary. The data will be repurposed, reused, shared. If you do it all via your application -- you will remove the ability to repurpose, reuse, share the data.

</code> <code>
and search (ctl-f) for disagreements. Very similar thread but talking about identification, authorization, access control, auditing (you know, those things a database normally does). I'll bet you suffer from that issue as well - so even if you accomplish this goal -- how do you secure the data since I'm sure all security is done at the application level?


  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment


You hit the nail so perfectly on the head that there is not even a follow-up on this one.

A reader, October 27, 2004 - 5:54 pm UTC

What about XMLType?

Alexander, May 23, 2006 - 9:54 am UTC


What about XMLType? It seems to be designed exactly for the purpose of being a "bit bucket" or a "node bucket". What would you recommend XMLType for?

Tom Kyte
May 23, 2006 - 10:08 am UTC

Not this, this was a clear case of the developers saying "nope, don't care that there is a database and the end users might actually have the nerve to claim the data is theirs".

I would not considering using the xmltype for anything remotely even close to this.

A reader, September 13, 2006 - 5:03 pm UTC


Rodrigo De León, September 18, 2007 - 9:48 pm UTC

From my experience, the Java/.NET zealots are the worst offenders.

Stunningly, um, near-sighted

Duke Ganote, January 29, 2010 - 12:18 pm UTC

The myopia of some application builders is stunning. I can't think of many applications that lack some reporting/integration requirements -- yet demonstrating the return-on-investment and effectiveness of the app for the company appears, at best, an afterthought. It may be an managerial concern that "Now users want ... reports". Really?

More to Explore


Complete documentation on Securefiles and Large Objects here