deserialize java object from a blob

Question

first of all, i apologize, i'm about to ask a set of dumb questions. i don't know java AT ALL and i don't know if we are allowed to ask questions like these.

if not - delete my topic.

there's a table in oracle that stores a blob. it's binary and i'm able to decode it, the output looks like this

¬í sr /com.epam.insure.credentialing.forms.StorageBeanÀÓ ¯w/§ L     variablest Ljava/util/Map;xpsr java.util.HashMapÚÁÃ`Ñ F 
loadFactorI     thresholdxp?@     w      t $_hasCompletedt  t 
$_wf_progresssr java.lang.Integerâ ¤÷‡8 I valuexr java.lang.Number†¬•”à‹  xp   t $_wf_statussq ~    t $_form_instance_idsr java.lang.Long;‹äÌ#ß J valuexq ~          ‹©t $_isVisitedt truet 1sq ~  sq ~ ?@     `w   €   _t  confidential readable infot 1t confidential readable infot $_errorssr java.util.ArrayListxÒ™Ça I sizexp    w   
xt regionIdsq ~       ët 
confidential readable infot t  t $_subbean_errorssq ~     w   
xt regiont  SOUTHWESTt idt  t codet  t reqTypeNamet 
confidential readable infot t confidential readable infot tint  t $_hasCompletedt falset comRequiredt  t 
lineImpactq ~ t prChiropractorsq ~ t fromTypeReqt not zipt 342t changeToTypeReq6t confidential readable infot t 
prPodiatristsq ~ t 
$_isValidatedt truet $_hasErrorsq ~ -t EVPapprovalsq ~  sq ~ ?@     w   Approvedq ~ Ct 
NEGOTIATORq ~ Et 
Negotiatort datet 
03/31/2006q ~ It confidential readable infot q ~ \xt updateRequiredt noq ~ t truet  approverssr .forms.StorageBeanList«WtúœG  xq ~    w   
q ~ Rsq ~  sq ~ ?@     w      t commentst  t decisiont Approvedq ~ Ct RVPq ~ Et RVPt datet 
04/04/2006q ~ It t commentst  t decisiont Approvedq ~ Ct COOq ~ Et COOt datet 
04/14/2006q ~ It ~ †xsq ~  sq ~ ?@     w      t commentsq ~ Pt decisiont Approvedq ~ Ct CEOq ~ Et CEOt d

so here are my questions

1. for some reason, when i try to insert the decoded blob value (what i posted above) into a table (i was going to move it to MS Access and parse it there. this would be a horrible solution but i'm desperate) - the only thing that inserts is "’" without the quotes. also, i can't select all and copy it from the DBMS output window, again, the only thing that pastes is "’" without the quotes. it seems like this text is not really there. does anyone have an idea on how to insert it into a table?

2. if i was to do it the right way and use java, where do i start? excuse this dumbness but i don't even know how to run java code. i found a few sample codes on the net but i don't know where to paste it :)

i did google it and saw that i have to create a .java file in a text editor and then compile it, is that true for my case? i thought maybe that's some different java code, i thought maybe in my case i'd have to run it from oracle because that's where the tables are.

i also have the table structure, i attached a piece of it. this blob stores a table.

anyhow, i'm sure it's obvious by now that i'm clueless. if anyone can point me somewhere i'd really appreciate it.

thank you

Solution 1

Here is an example of oracle 11g java stored function that deserializes java object from blob. As a free bonus added an example of oracle java stored procedure to update blob with serialized java object.

If object's class isn't java built-in (as in my case), you would also need to publish it's source (with all dependencies) in oracle database.

CREATE OR REPLACE JAVA SOURCE NAMED "ServiceParamsBLOBHandler" AS
import java.io.*;
import java.util.*;
public class ServiceParamsBLOBHandler {

    private static Object deserialize(InputStream stream) throws Exception {

        ObjectInputStream ois = new ObjectInputStream(stream);
        try {
            return ois.readObject();
        } finally {
            ois.close();
        }
    }

    private static byte[] serialize(Object object) throws IOException {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ObjectOutputStream oos = new ObjectOutputStream(baos);
        oos.writeObject(object);
        oos.close();
        return baos.toByteArray();
    }

    //@SuppressWarnings("unchecked")
    private static List<Map<String, String>> getParams(oracle.sql.BLOB blob) throws Exception {
        return (List<Map<String, String>>) deserialize(blob.getBinaryStream());
    }

    public static oracle.sql.BLOB updatedParamField(oracle.sql.BLOB blob, String paramName, String fieldName, String value)
            throws Exception {

        List<Map<String, String>> params = getParams(blob);
        Map<String, String> param = getParam(params, paramName);
        param.put(fieldName, value);
        oracle.sql.BLOB res = oracle.sql.BLOB.createTemporary(blob.getOracleConnection(), true, oracle.sql.BLOB.DURATION_CALL);
        res.setBytes(1, serialize(params));
        return res;
    }

    public static void updateParamField(oracle.sql.BLOB[] blobs, String paramName, String fieldName, String value)
            throws Exception {

        oracle.sql.BLOB blob = blobs[0];
        List<Map<String, String>> params = getParams(blob);
        Map<String, String> param = getParam(params, paramName);
        param.put(fieldName, value);
        blob.truncate(0);
        blob.setBytes(1, serialize(params));
    }

    private static Map<String, String> getParam(List<Map<String, String>> params, String name) {
        for (Map<String, String> param : params) {
            if (name.equals(param.get("name"))) {
                return param;
            }
        }
        return null;
    }

    public static String getParamField(oracle.sql.BLOB blob, String paramName, String fieldName) throws Exception {
        Map<String, String> param = getParam(getParams(blob), paramName);
        return param == null ? null : param.get(fieldName);
    }

}
/

alter java source "ServiceParamsBLOBHandler" compile
--select * from SYS.USER_ERRORS
/

CREATE OR REPLACE function getServiceParamField(b IN BLOB, paramName IN VARCHAR2, fieldName IN VARCHAR2) RETURN VARCHAR2
as LANGUAGE JAVA NAME 'ServiceParamsBLOBHandler.getParamField(oracle.sql.BLOB, java.lang.String, java.lang.String) return String';
/

CREATE OR REPLACE function updatedServiceParamField(b IN BLOB, paramName IN VARCHAR2, fieldName IN VARCHAR2, value IN VARCHAR2) RETURN BLOB
as LANGUAGE JAVA NAME 'ServiceParamsBLOBHandler.updatedParamField(oracle.sql.BLOB, java.lang.String, java.lang.String, java.lang.String) return oracle.sql.BLOB';
/

CREATE OR REPLACE PROCEDURE updateServiceParamField(b IN OUT BLOB, paramName IN VARCHAR2, fieldName IN VARCHAR2, value IN VARCHAR2)
AS LANGUAGE JAVA NAME 'ServiceParamsBLOBHandler.updateParamField(oracle.sql.BLOB[], java.lang.String, java.lang.String, java.lang.String)';
/

-- oracle blob read usage example:
select getServiceParamField(byte_value, 'account', 'format') from entity_property where name='params';

-- oracle blob update with java stored function usage example:
update entity_property set byte_value=updatedServiceParamField(byte_value, 'account', 'format', '15')
where name='params' and entity_id = 123

-- oracle blob update with java stored procedure usage example:
BEGIN
   FOR c IN (select byte_value from entity_property where name='params' and entity_id = 123 for update) LOOP
       updateServiceParamField(c.byte_value, 'account', 'format', '13');
   END LOOP;
END;
/

Update

Concrete snippets for the case in question.

1) Full object load

    private static String getVariable(oracle.sql.BLOB blob, String name) throws Exception {
        ObjectInputStream ois = new ObjectInputStream(blob.getBinaryStream());
        try {
            //noinspection unchecked
            return ((HashMap<String, String>) ((StorageBean) ois.readObject()).variables).get(name);
        } finally {
            ois.close();
        }
    }

2) Partial field load

    private static String getVariable(oracle.sql.BLOB blob, String name) throws Exception {
        ObjectInputStream ois = new ObjectInputStream(blob.getBinaryStream());
        try {
            ois.skipBytes(variablesOffset);
            //noinspection unchecked
            return ((HashMap<String, String>) ois.readObject()).get(name);
        } finally {
            ois.close();
        }
    }

Solution 2

i will learn to do this in java at some point but since this is a rush - I decided to use SQL to extract fields from the blob. i'm putting this here in case someone else is ever as desperate to do this.

it's a very ugly and slow solution but so far i'm able to get some fields. i will update once i'm done, to say whether i was able to get everything or not.

here's the code i'm using (this is just for 1 field but it will give you an idea)

    DECLARE
     CURSOR c_dts IS
       SELECT Form_ID
         FROM NR_DTS_FORMTABLE
        WHERE   1 = 1
           --AND ROWNUM BETWEEN 501 AND 4500
           AND form_ID > 204815
           --AND ROWNUM < 5000
           AND ROWNUM < 3
           --AND form_id IN (SELECT form_id FROM NR_DTS_BLOB)
           AND Form_Type_ID = 102;
     DTS c_dts%ROWTYPE;
BEGIN
     OPEN c_dts;
     LOOP
       FETCH c_dts INTO DTS;
       EXIT WHEN c_dts%NOTFOUND;
       DECLARE
         v_hold_blob BLOB;
         v_len NUMBER;
         v_raw_chunk RAW(10000);
         v_chr_string VARCHAR2(32767);
         -- v_chr_string CLOB;
         v_position NUMBER;
         c_chunk_len NUMBER := 1;
         Form_ID NUMBER;
       BEGIN
         SELECT form_content
           INTO v_hold_blob
           FROM NR_DTS_FORMTABLE
          WHERE Form_ID = DTS.Form_ID;
         v_len := DBMS_LOB.getlength(v_hold_blob);
         v_position := 1;
         WHILE (v_position <= LEAST(v_len, 32767)) LOOP
              v_raw_chunk := DBMS_LOB.SUBSTR(v_hold_blob, c_chunk_len, v_position);
              v_chr_string := v_chr_string || CHR(hex_to_decimal(RAWTOHEX(v_raw_chunk)));
              v_position := v_position + c_chunk_len;
         END LOOP;

         --insert into table
         INSERT INTO NR_DTS_BLOBFIELDS_VARCHAR(formid
                               ,regionId)
              SELECT DTS.Form_ID
                 ,SUBSTR(v_chr_string
                     ,INSTR(v_chr_string, 'regionIdt') + LENGTH('regionIdt') + 2
                     ,INSTR((SUBSTR(v_chr_string, INSTR(v_chr_string, 'regionIdt') + LENGTH('regionIdt') + 2))
                        ,CHR(116) || CHR(0)))
                    regionId
             FROM DUAL;
       END;
     --  DBMS_OUTPUT.put_line(DTS.Form_ID);
     END LOOP;
     CLOSE c_dts;
END;

Comments

[lalachka]

Thank you so much!!!! I will try it out this week. I might have some more dumb questions. Stay tuned :)

[lalachka]

hey, i don't know ANYTHING about java so please bear with me. what class do i need to publish and why? also, i have a list of fields stored in that blob (it's a table, i have a picture of the structure at the beginning of my post) so where do i enter the name of the fields that i'm trying to get the value for?

[Vadzim]

If you want to deserialize java object, you have to know something about java. ;) StorageBean's class source needs to be loaded in oracle with CREATE OR REPLACE JAVA SOURCE as in my example or with loadjava utility (and all of it's dependencies, if any). But if you manage to somehow manually find the offset and size of variables field from the blob then this field could be separately loaded by offset as built-in java.util.HashMap without the need in enclosing object's class source.

[lalachka]

Lolol exactly. I figured i'm better off doing it in SQL even though it's a bad solution and there's a lot of room for error. So I'm going to accept your answer as the answer even though I can't check it )))))) thank you)))))

[lalachka]

I just saw you added some more stuff. I will try figuring it out, if you don't mind I will ask some more questions later on.

[lalachka]

this is not as clean as i was hoping it would be. some fields are showing up blank though they should be populated. rare but still, doesn't make the data reliable.

[JGlass]

I admire your perseverance ;-) I know Java and can "feel you", lol. Glad you got something working!

[lalachka]

lol, that's one thing i have, perseverance. if something is stuck in my head, i cannot relax until i try all i can. i want to learn java though, where do i start?