The WRITE stored procedure writes a specified amount of data into an internal LOB, starting from an absolute offset position from the beginning of the LOB.
The data to be written comes from the buffer parameter. WRITE replaces (overwrites) any data that already exists in the LOB at the offset based on the length you specify.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
DBMS_LOB.WRITE (
lob_loc IN OUT NOCOPY BLOB,
amount IN INTEGER,
offset IN INTEGER,
buffer IN RAW);
DBMS_LOB.WRITE (
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount IN INTEGER,
offset IN INTEGER,
buffer IN VARCHAR2 CHARACTER SET ANY_CS);
Parameters
| Parameter | Description |
|---|---|
| lob_loc | The locator for the internal LOB that the data is written to. For more information, see Considerations. |
| amount | The amount of data to write. The value is in bytes for BLOBs, and in characters for CLOBs. |
| offset | The offset from the beginning of the LOB, with 1 as the origin, for the read operation. The value is in bytes for BLOBs and in characters for CLOBs. |
| buffer | The buffer for the write operation. |
Exceptions
| Exception | Description |
|---|---|
| VALUE_ERROR | Any of the lob_loc, amount, and offset parameters is NULL, invalid, or out of range. |
| INVALID_ARGVAL | Several possibilities:
|
| QUERY_WRITE | Cannot perform a LOB write inside a query or PDML parallel execution server. |
| BUFFERING_ENABLED | The operation cannot be performed because LOB buffering is enabled. |
| SECUREFILE_OUTOFBOUNDS | A write operation past the end of a LOB having FRAGMENT_* was attempted. |
Considerations
If the amount specified is larger than the size of data in the buffer, an exception will be thrown. If the amount specified is smaller than the size of data in the buffer, only the data in the buffer will be written to the
LOB. If the offset you specify is beyond the end of the data in theLOB, zero-byte fillers will be inserted into theBLOB, and spaces will be inserted into theCLOB.The form of the
VARCHAR2buffer must match the form of theCLOBparameter. That is, if the type of the inputLOBparameter isCLOB, the data in the buffer must be of theCHARtype.When you call
DBMS_LOB.WRITEfrom the client, data in the buffer must use the client's character set. Before the data is written from the buffer to theLOB, the database converts the character set of the client's buffer to the character set of the server.You can choose not to wrap the
LOBoperation inside the Open/Close API. If you did not open theLOBbefore the operation, the functional and domain indexes on theLOBcolumn are updated during the call. However, if you opened theLOBbefore the operation, you must close it before you commit the transaction. When an internalLOBis closed, the functional and domain indexes on theLOBcolumn are updated.If you do not wrap the
LOBoperation in the Open/Close API, the functional and domain indexes are updated each time you write to theLOB. To avoid adversely affecting performance, we recommend that you wrap write operations on theLOBin theOPENorCLOSEstatement.When necessary,
WRITEobtains theLOBbefore writing data to it, unless the write operation is specified to overwrite the entireLOB.