This post originated from an RSS feed registered with Java Buzz
by David Rolfe.
Original Post: LOBs 101, Why BFILE and Web Services don't work together.
Feed Title: OrindaBlog - Orinda Software's Corporate Blog
Feed URL: http://www.jroller.com/rss/orindasoft
Feed Description: We're in the business of creating products that make it easier to work with Oracle, Java and JDBC. We want this blog to be something more than a stream of product-related propaganda - our intention is two-thirds of the content will be of general relevance to people who work with Oracle, Java and PL/SQL. Entries to this blog will be made as and when we have information that we think our readers would be interested in.
Oracle users currently have 3 different Large OBject data types to choose from -
BLOB, CLOB and BFILE. We'll look at the basic mechanics of how they work from a Java developers perspective and then explain why oracle.sql.BFILE poses serious challanges for web service developers.
LOB's 101
When you retrieve an oracle.sql.BLOB or oracle.sql.CLOB the single most important thing to understand is that you're dealing with a pointer, not a file. Not only that, but that the oracle.sql.CLOB you just retrieved from the database is totally independent of the SQL statement or PL/SQL procedure
that was used to return it to you. What you've actually got is a pointer to a large object inside the database that will continue to exist until your commit, rollback or lose your connection. Once you understand this life will become bearable.
There are significant API changes for Oracle's JDBC drivers as you move up through the different versions of Oracle. You will need to re-read the JavaDocs and check your LOB code line by line when you upgrade. Of course, if you used OrindaBuild you could just re-generate your code...
oracle.sql.BFILE is different. A BFILE is a pointer to a file that resides outside the database. In fact a BFILE consists of two things:
The name of an Oracle DIRECTORY (as found in the ALL_DIRECTORIES view)
The name of the file within that directory
oracle.sql.BFILE objects are small - often under 100 bytes. Oracle makes no attempt to verify that the file referred to by the oracle.sql.BFILE exists and is usable until you attempt to read from it. The only way to create an oracle.sql.BFILE is to call use the SQL function BFILENAME, which implies a database call. You can quite happily create BFILE objects for files which don't exist. This makes sense when you consider how much work checking every BFILE every time it was referred to would impose on the server, but obviously needs to be understood by the developer.
LOBs and Web Services
If you're going to write a Web Service app and are one of the surprising number of people who
are still on 8i you should upgrade to at least 9i. Creating CLOBS and BLOBS is a lot easier because of the
createTemporary() method that appears in 9.0.1.
Using LOBS in a Web Service environment is not a problem, provided you think carefully about how big the LOBs will actually be and how much memory you'll need - Oracle can support huge CLOBS and BLOBS that simply wouldn't be practical in a Web Service scenario.
Creating BFILES and Web Services
If you are writing a web service application that stores LOBs in Oracle you should avoid the use of the BFILE data type. It may well seem very tempting but the JVM which is running the service will need to be on the same filesystem as the Oracle database server, which will severly limit scalability. If, as is normal, your JVM is on a different machine you could in theory get round this by creating a BFILE
that points to a non-existent directory/file on the database server and then trying to asynchronously move the file to where the BFILE pointer claims it is before anyone actually tries to read the BFILE. For obvious reasons we don't recommend this.
Previous Topic
