Wednesday, September 4, 2013

More on saving BLOB with Oracle DBAdapter

This will NOT work:
CREATE TABLE BLATABLE (BLAFIELD BLOB);

INSERT INTO BLATABLE (BLAFIELD) VALUES ('SOMEVALUE');

you get a ORA-01465 - "invalid hex number"

When I try to save a String to a BLOB field in a stored procedure, I need to convert a String to a bytearray. Using UTL_RAW.CAST_TO_RAW works for strings of < 2K, for longer strings you hit the limit of the internal implementation of this function:
UTL_RAW.CAST_TO_RAW(theString)
ORA-06502: PL/SQL: numeric or value error: raw variable length too long


Using UTL_RAW.TO_RAW entails that you use a hex representation of a string; if you pass any non-hex string you get:

UTL_RAW.TO_RAW(theString) ORA-01465: invalid hex number

You can test that actually UTL_RAW.TO_RAW converts hex to RAW format:
UTL_RAW.TO_RAW('3435') = '45'

http://docs.oracle.com/html/A90373_03/ap_urpkg.htm
In XQuery there is no built-in function to transform String or XML in Hex. Sad.
You can either write yourself such XQuery (good luck), or use Apache Common Hex class in a custom Xpath A good way is this:

public static String fromXMLObjectToHexString(org.apache.xmlbeans.XmlObject theXMLObject) {
 return Hex.encodeHexString(theXMLObject.toString().getBytes());
}





No comments: