About BLOB type operation in Oracle9i
Because JDBC2.0 does not fully implement the operation of BLOB, you must use the Oracle expansion package (Classes12.jar) when reading and writing of the BLOB type of Oracle.
1. BLOB write operation
1.1 Write blob Java operation
..........
// Call the stored procedure of writing BLOB
String SQL = "{CALL INSERT_BLOB (?)}"
// obtain database connection
Connection conn = Dao.createcoon ();
CallableStatement Proc = Conn.PrepareCall (SQL);
// Set the byte array
Proc.setBytes (1, blob);
// Perform a stored procedure
proc.execute ();
// Release connection
proc.close ();
CONN.CLOSE ();
..........
1.2 Write the Oracle stored procedure of blob
Procedure insert_blob
(Blobdata in blob_table_xx% type) --blob
AS
LOBD BLOB;
Begin
Set Transaction Name 'INSERTBLOB';
- Insert the blab_row_xx column in the blob_table_xx table inserting an empty BLOB
INSERT INTO BLOB_TABLE_XX (blob_row_xx) VALUES (EMPTY_BLOB ());
- Positioning the original blob inserted
SELECT service_IMG INTO LOBD From BLOB_TABLE_XX WHERE XXX = XXX for Update;
- Write the content into the mixed blob
DBMS_LOB.WRITE (LOBD, UTL_RAW.LENGTH (blobdata), 1, blobdata;
- transaction submission
COMMIT;
EXCEPTION
When Others Then
Begin
Rollback; - After the error, the transaction rollback
END;
END;
2. BLOB read operation
2.1 Read BLOB Oracle Store
Create Or Replace Package Body BLOB_PKG_XXX IS
Type Cursortype is Ref cursor;
Function query_blob
Return blob_pkg_xxx.cursortype
AS
L_cursor blob_pkg_xxx.cursortype
Begin
Open L_Cursor for SELECT BLOB_ROW_XX
From blob_table_xx
WHERE XXX = XXX;
RETURN L_CURSOR;
END;
END BLOB_PKG_XXX;
2.2 Reading BLOB Java Operation
/ **
* Method getBlobbytes. Read the contents of the BLOB and store in a byte array
* @Param Blob
* @Return Byte []
* @Throws SQLException
* @Throws ioException
* /
Public Byte [] getBlobbytes (blob blob) throws sqlexception, oException {
/ / Get blob IO stream
BufferedInputStream INS =
New bufferedInputStream (blob.getbinarystream ());
/ / Get the length of BLOB
Int buffersize = (int )blob.length ();
// Set up byte cache
Byte [] bt = new byte [buffersize];
// write the BLOB content to the cache
INS.READ (BT, 0, buffersize);
Return BT;
}
..........
Try {
String SQL = "{? = CALL BLOB_PKG_XXX.QUERY_BLOB ()}";
// obtain database connection
Connection conn = Dao.createcoon ();
CallableStatement Proc = Conn.PrepareCall (SQL);
// Set the return object type as result set
Proc.registerOutparameter (1, ORACletYpes.cursor);
// Perform a stored procedure
proc.execute ();
// Get the result set
ResultSet Rset = (ResultSet) proc.getObject (1);
While (RSET.NEXT ()) {
/ / Get the BLOB object in the result set
BLOB BLOB = (blob) Rset.getblob (blob_row_xx);
Byte [] bt = DAO.GETBLOBBYTES (BLOB);
}
// Release connection
proc.close ();
CONN.CLOSE ();
} catch (namingexception e) {
E.PrintStackTrace ();
} catch (sqlexception e) {
E.PrintStackTrace ();
} catch (exception e) {
E.PrintStackTrace ();
}
..........
