The COPY stored procedure copies all or part of a source internal LOB to a target internal LOB.
You can specify the offset for the source LOB and target LOB and the number of bytes or characters to be copied.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
DBMS_LOB.COPY (
dest_lob IN OUT NOCOPY BLOB,
src_lob IN BLOB,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);
DBMS_LOB.COPY (
dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_lob IN CLOB CHARACTER SET ANY_CS,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);
Parameters
| Parameter | Description |
|---|---|
| dest_lob | The locator of the target LOB. |
| src_lob | The locator of the source LOB. |
| amount | The amount of data to be copied. The value is in bytes for BLOBs and in characters for CLOBs. |
| dest_offset | The offset, with 1 as the origin, for the start of copy in the target LOB. The value is in bytes or characters. |
| src_offset | The offset, with 1 as the origin, for the start of copy in the source LOB. The value is in bytes or characters. |
Exceptions
| Exception | Description |
|---|---|
| VALUE_ERROR | Any input parameters are NULL or invalid. |
| INVALID_ARGVAL | Several possibilities:
|
| QUERY_WRITE | LOB write is not allowed in the query. |
| BUFFERING_ENABLED | The operation cannot be performed because LOB buffering is enabled. |
Considerations
If the offset specified in the target
LOBis beyond the end of the data in theLOB, the targetBLOBwill be filled with zero bytes, and the targetCLOBwill be filled with spaces. If the offset is less than the current length of the targetLOB, the existing data will be overwritten.No error will occur if the specified amount exceeds the data length of the source
LOB. Therefore, you can specify a large amount of data to be copied from the sourceLOB. This way, data starting from the offset position determined bysrc_offsetto the end of the sourceLOBis copied.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.If the source and target
LOBsare archived, the procedure retrieves them before copying data. In case of a complete overwrite, the procedure does not retrieve the targetLOB.