Oracle CLOB and BLOB data types can be used to store long text or large images, provide better performance compare to the LONG and LONG RAW data types for row accessing. However it's not easy to manipulate them with DBMS_LOB package in SQL*Plus. It's not easy to coding and cannot perform unloading or uploading from the client machine.
I wrote a free utility named "lobs" (Download) to process these two data types. With this utility, we can easily unload LOB values from database to OS files or upload OS files to LOB values. The following is the command line help.
Usage: lobs.exe -u user=... query=...
Notes:
-u = Upload file to LOB field.
user = username/password@tnsname
sql = SQL file name
query = select statement
The "-U" option is used to upload OS files to the database, the default is to unload LOB values to OS files. The "query=" option is used to specify the SQL query in command line, and the "sql=" option is used to specify a SQL file contains complex SQL query which is difficult to be put in the command line.
Let's take a quick example of unloading operation. The key point is the SQL query, first column should be the OS file's name, and the second column should be the CLOB or BLOB columns.
lobs user=test/test@test1 query="SELECT 'img_'||ROWNUM||'.jpg, picture from images where ..."
And for the uploading operation, you must set the NULL lobs to empty lob value first by the following SQLs.
UPDATE IMAGES SET PICTURE = EMPTY_BLOB() WHERE ...;
UPDATE DOCUMENTS SET CONTENT = EMPTY_CLOB() WHERE ...;
commit;
Then for the SQL query, the first column is the OS files, and the second column is the BLOB or CLOB column, and you must add "FOR UPDATE" to lock the LOB locators. Finally add the "-U" option to the command line.
lobs -U user=test/test@test1 query="SELECT 'img_'||ROWNUM||'.jpg, picture from images where ... FOR UPDATE"
This utility is OCI based program, so you must install the Oracle client before you start the job.