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.
Comments (6)
where can i get source code for lobs.exe
Thans and regards,
saven
Posted by juniorDev | November 6, 2008 2:06 PM
I just upload the source code.
http://www.dbatools.net/software/lobs.c
Posted by anysql | November 7, 2008 8:05 AM
When I tried to use an EXTERNAL TNS that has no Password i.e.
user=EXTERNAL/@PRED.WORLD
I get a null password given: logon denied.
or
user=EXTERNAL/''@PRED.WORLD
I get invalid username/password; logon denied
How do I use an EXTERNAL accout?
Thanks!
John
Posted by John | March 17, 2009 9:05 AM
If you are using OS authorized login locally, just type run it as following:
lobs user=/ .....
I am not sure of the external account? Are you using Oracle advanced security?
Posted by anysql | March 17, 2009 3:28 PM
Hello :
I have a problem ....
C:\Cbrown\Oficina\download\blob>lobs_win32.exe user=admdba/XXXXX@prod-se8i_calidad query="select arqelet from isosystem.DOCREVIS DR where nmarqelet='00008597.doc'"
ERROR: query must return two columns.
any idea ???
thank
Posted by Carlos Brown | August 18, 2009 6:08 AM
In the query, first column as the file name column, second column as the LOB data column.
Posted by anysql | August 18, 2009 11:48 PM