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.

Requirements:
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)

Assumptions:
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.


Questions:
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)

Platform:
Oracle 8i on Solaris.


Thanks a lot.