PRO*C可以用三种方式对LOB字段处理。 1、The DBMS_LOB package inside PL/SQL blocks. 2、OCI (Oracle Call Interface) function calls. 3、Embedded SQL statements. Embedded SQL statements.的方式简单而且比较灵活。OTN上提供一个例子： In this example we will be reading data from a BLOB with an unknown arbitrary length into a buffer and then writing the data from the buffer into an external file. Our buffer is small, so depending on the size of the BLOB we are reading, we may be able to read the BLOB value into the buffer in a single READ statement or we may be required to utilize a standard polling method instead. First we start off with oci.h and some simple local variable declarations example 5. #include <oci.h> OCIBlobLocator *blob ; FILE *fp ; unsigned int amt, offset = 1 ; Now we need a buffer to store the BLOB value and then write to the file from: #define MAXBUFLEN 5000 unsigned char buffer[MAXBUFLEN] ; EXEC SQL VAR buffer IS RAW(MAXBUFLEN) ; Allocate the BLOB host variable and select a BLOB which we will READ: EXEC SQL ALLOCATE :blob ; EXEC SQL SELECT a_blob INTO :blob FROM lob_table WHERE ... ; We can then open the external file to which we will write the BLOB value: fp = fopen((const char *)"image.gif", (const char *)"w") ; If the buffer can hold the entire LOB value in a single READ we need to catch the NOT FOUND condition to signal LOB READ termination: EXEC SQL WHENEVER NOT FOUND GOTO end_of_lob ; Now do our first READ.We set the amount to the maximum value of 4 Gigabytes. It is larger than our buffer so if the LOB doesn't fit we will READ using a polling mode: amt = 4294967295 ; EXEC SQL LOB READ :amt FROM :blob AT ffset INTO :buffer ; If we get here then it means that the buffer was not large enough to hold the entire LOB value, so we must write what we have using binary I/O and continue reading: (void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ; We use a standard polling method to continue reading with the LOB READ inside of an infinite loop. We can set up the NOT FOUND condition to terminate the loop: EXEC SQL WHENEVER NOT FOUND DO break ; while (TRUE) { During polling, the offset is not used so we can omit it in subsequent LOB READs. We need the amount, however, because it will tell us how much was READ in the last READ invocation EXEC SQL LOB READ :amt FROM :blob INTO :buffer ; (void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ; } Here, we have reached the end of the LOB value. The amount holds the amount of the last piece that was READ. During polling, the amount for each interim piece was set to MAXBUFLEN, or the maximum size of our buffer: end_of_lob: (void) fwrite((void *)buffer, (size_t)amt, (size_t)1, fp) ;
1、The DBMS_LOB package inside PL/SQL blocks.
2、OCI (Oracle Call Interface) function calls.
3、Embedded SQL statements.
Embedded SQL statements.的方式简单而且比较灵活。OTN上提供一个例子：
In this example we will be reading data from a BLOB with an unknown arbitrary length into a buffer and then writing the data from the buffer into an external file.
Our buffer is small, so depending on the size of the BLOB we are reading, we may
be able to read the BLOB value into the buffer in a single READ statement or we
may be required to utilize a standard polling method instead.
First we start off with oci.h and some simple local variable declarations
example 5.
#include <oci.h>
OCIBlobLocator *blob ;
FILE *fp ;
unsigned int amt, offset = 1 ;
Now we need a buffer to store the BLOB value and then write to the file from:
#define MAXBUFLEN 5000
unsigned char buffer[MAXBUFLEN] ;
EXEC SQL VAR buffer IS RAW(MAXBUFLEN) ;
Allocate the BLOB host variable and select a BLOB which we will READ:
EXEC SQL ALLOCATE :blob ;
EXEC SQL SELECT a_blob INTO :blob FROM lob_table WHERE ... ;
We can then open the external file to which we will write the BLOB value:
fp = fopen((const char *)"image.gif", (const char *)"w") ;
If the buffer can hold the entire LOB value in a single READ we need to catch the
NOT FOUND condition to signal LOB READ termination:
EXEC SQL WHENEVER NOT FOUND GOTO end_of_lob ;
Now do our first READ.We set the amount to the maximum value of 4 Gigabytes. It
is larger than our buffer so if the LOB doesn't fit we will READ using a polling
mode:
amt = 4294967295 ;
EXEC SQL LOB READ :amt FROM :blob AT ffset INTO :buffer ;
If we get here then it means that the buffer was not large enough to hold the entire
LOB value, so we must write what we have using binary I/O and continue reading:
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
We use a standard polling method to continue reading with the LOB READ inside
of an infinite loop. We can set up the NOT FOUND condition to terminate the loop:
EXEC SQL WHENEVER NOT FOUND DO break ;
while (TRUE)
{
During polling, the offset is not used so we can omit it in subsequent LOB READs.
We need the amount, however, because it will tell us how much was READ in the
last READ invocation
EXEC SQL LOB READ :amt FROM :blob INTO :buffer ;
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
}
Here, we have reached the end of the LOB value. The amount holds the amount of
the last piece that was READ. During polling, the amount for each interim piece
was set to MAXBUFLEN, or the maximum size of our buffer:
end_of_lob:
(void) fwrite((void *)buffer, (size_t)amt, (size_t)1, fp) ;
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_16lob.htm#997954