Release 8.1.5
A68001-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Supplied Packages Reference Release 8.1.5 A68001-01 |
|
The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs.
DBMS_LOB can read and modify BLOBs, CLOBs, and NCLOBs; it provides read-only operations for BFILEs. The bulk of the LOB operations are provided by this package.
This package must be created under SYS (connect internal). Operations provided by this package are performed under the current calling user, not under the package owner SYS.
All DBMS_LOB subprograms work based on LOB locators. For the successful completion of DBMS_LOB subprograms, you must provide an input locator that represents a LOB that already exists in the database tablespaces or external filesystem.
For internal LOBs, you must first use SQL data definition language (DDL) to define tables that contain LOB columns, and, subsequently, use SQL data manipulation language (DML) to initialize or populate the locators in these LOB columns.
For external LOBs, you must ensure that a DIRECTORY object representing a valid, existing physical directory has been defined, and that physical files exist with read permission for Oracle. If your operating system uses case-sensitive pathnames, then be sure you specify the directory in the correct format.
After the LOBs are defined and created, you may then SELECT a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for access to the LOB value.
For temporary LOBs, you must use the OCI, PL/SQL, or another programmatic interface to create or manipulate them. Temporary LOBs can be either BLOBs, CLOBs, or NCLOBs.
Parameters for the DBMS_LOB subprograms use these datatypes:
The DBMS_LOB package defines no special types. NCLOB is a special case of CLOBs for fixed-width and varying-width, multi-byte national character sets. The clause ANY_CS in the specification of DBMS_LOB subprograms for CLOBs enables them to accept a CLOB or NCLOB locator variable as input.
DBMS_LOB defines the following constants:
file_readonly CONSTANT BINARY_INTEGER := 0; lob_readonly CONSTANT BINARY_INTEGER := 0; lob_readwrite CONSTANT BINARY_INTEGER := 1; lobmaxsize CONSTANT INTEGER := 4294967295; call CONSTANT PLS_INTEGER := 12; session CONSTANT PLS_INTEGER := 10;
Oracle supports a maximum LOB size of 4 gigabytes (232). However, the amount and offset parameters of the package can have values between 1 and 4294967295 (232-1).
The PL/SQL 3.0 language specifies that the maximum size of a RAW or VARCHAR2 variable is 32767 bytes.
Any DBMS_LOB subprogram called from an anonymous PL/SQL block is executed using the privileges of the current user. Any DBMS_LOB subprogram called from a stored procedure is executed using the privileges of the owner of the stored procedure.
With Oracle8i, when creating the procedure, users can set the AUTHID to indicate whether they want definer's rights or invoker's rights. For example:
CREATE PROCEDURE proc1 authid definer ...
or
CREATE PROCEDURE proc1 authid current_user ....
You can provide secure access to BFILEs using the DIRECTORY feature discussed in BFILENAME function in the Oracle8i Application Developer's Guide - Large Objects (LOBs) and the Oracle8i SQL Reference.
length and offset parameters for subprograms operating on BLOBs and BFILEs must be specified in terms of bytes.
length and offset parameters for subprograms operating on CLOBs must be specified in terms of characters.
offset and amount parameters are always in characters for CLOBs/NCLOBs and in bytes for BLOBs/BFILEs.
INVALID_ARGVAL exception if the following restrictions are not followed in specifying values for parameters (unless otherwise specified):
LOB data are permitted: Negative offsets from the tail of the LOB are not permitted.
amount, offset, newlen, nth, etc. Negative offsets and ranges observed in Oracle SQL string functions and operators are not permitted.
offset, amount, newlen, nth must not exceed the value lobmaxsize (4GB-1) in any DBMS_LOB subprogram.
CLOBs consisting of fixed-width multi-byte characters, the maximum value for these parameters must not exceed (lobmaxsize/character_width_in_bytes) characters.
For example, if the CLOB consists of 2-byte characters, such as:
JA16SJISFIXED
Then, the maximum amount value should not exceed:
4294967295/2=2147483647characters.
RAW and VARCHAR2 parameters used in DBMS_LOB subprograms. For example, if you declare a variable to be:
charbufVARCHAR2(3000)
Then, charbuf can hold 3000 single byte characters or 1500 2-byte fixed width characters. This has an important consequence for DBMS_LOB subprograms for CLOBs and NCLOBs.
%CHARSET clause indicates that the form of the parameter with %CHARSET must match the form of the ANY_CS parameter to which it refers.
For example, in DBMS_LOB subprograms that take a VARCHAR2 buffer parameter, the form of the VARCHAR2 buffer must match the form of the CLOB parameter. If the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.
For DBMS_LOB subprograms that take two CLOB parameters, both CLOB parameters must have the same form; i.e., they must both be NCLOBs, or they must both be CLOBs.
amount plus the offset exceeds 4 GB (i.e., lobmaxsize+1) for BLOBs and BFILEs, and (lobmaxsize/character_width_in_bytes)+1 for CLOBs in calls to update subprograms (i.e., APPEND, COPY, TRIM, WRITE and WRITEAPPEND subprograms), then access exceptions are raised.
Under these input conditions, read subprograms, such as READ, COMPARE, INSTR, and SUBSTR, read until End of Lob/File is reached. For example, for a READ operation on a BLOB or BFILE, if the user specifies offset value of 3 GB and an amount value of 2 GB, then READ reads only ((4GB-1)-3GB) bytes.
NULL or invalid input values for parameters return a NULL. Procedures with NULL values for destination LOB parameters raise exceptions.
COMPARE, INSTR, and SUBSTR do not support regular expressions or special matching characters (such as % in the LIKE operator in SQL) in the pattern parameter or substrings.
End Of LOB condition is indicated by the READ procedure using a NO_DATA_FOUND exception. This exception is raised only upon an attempt by the user to read beyond the end of the LOB/FILE. The READ buffer for the last read contains 0 bytes.
LOB updates, you must lock the row containing the destination LOB before making a call to any of the procedures (mutators) that modify LOB data.
offset parameter is 1, which indicates the first byte in the BLOB or BFILE data, and the first character in the CLOB or NCLOB value. No default values are specified for the amount parameter -- you must input the values explicitly.
LOB before calling any subprograms that modify the LOB, such as APPEND, COPY, ERASE, TRIM, or WRITE. These subprograms do not implicitly lock the row containing the LOB.
COMPARE, INSTR, READ, SUBSTR, FILECLOSE, FILECLOSEALL and LOADFROMFILE operate only on an opened BFILE locator; i.e., a successful FILEOPEN call must precede a call to any of these subprograms.
FILEEXISTS, FILEGETNAME and GETLENGTH, a file's open/close status is unimportant; however, the file must exist physically, and you must have adequate privileges on the DIRECTORY object and the file.
DBMS_LOB does not support any concurrency control mechanism for BFILE operations.
FILECLOSEALL subprogram to close all files opened in the session and resume file operations from the beginning.
DIRECTORY, or if you have system privileges, then use the CREATE OR REPLACE, DROP, and REVOKE statements in SQL with extreme caution.
If you, or other grantees of a particular directory object, have several open files in a session, then any of the above commands can adversely affect file operations. In the event of such abnormal termination, your only choice is to invoke a program or anonymous block that calls FILECLOSEALL, reopen your files, and restart your file operations.
BFILE.
In the event of normal program termination, proper file closure ensures that the number of files that are open simultaneously in the session remains less than SESSION_MAX_OPEN_FILES.
In the event of abnormal program termination from a PL/SQL program, it is imperative that you provide an exception handler that ensures closure of all files opened in that PL/SQL program. This is necessary because after an exception occurs, only the exception handler has access to the BFILE variable in its most current state.
After the exception transfers program control outside the PL/SQL program block, all references to the open BFILEs are lost. The result is a larger open file count which may or may not exceed the SESSION_MAX_OPEN_FILES value.
For example, consider a READ operation past the end of the BFILE value, which generates a NO_DATA_FOUND exception:
DECLARE fil BFILE; pos INTEGER; amt BINARY_INTEGER; buf RAW(40); BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21; dbms_lob.open(fil, dbms_lob.lob_readonly); amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := ''; dbms_lob.read(fil, amt, pos, buf); dbms_output.put_line('Read F1 past EOF: '|| utl_raw.cast_to_varchar2(buf)); dbms_lob.close(fil); END; ORA-01403: no data found ORA-06512: at "SYS.DBMS_LOB", line 373 ORA-06512: at line 10
After the exception has occurred, the BFILE locator variable file goes out of scope, and no further operations on the file can be done using that variable. Therefore, the solution is to use an exception handler:
DECLARE fil BFILE; pos INTEGER; amt BINARY_INTEGER; buf RAW(40); BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21; dbms_lob.open(fil, dbms_lob.lob_readonly); amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := ''; dbms_lob.read(fil, amt, pos, buf); dbms_output.put_line('Read F1 past EOF: '|| utl_raw.cast_to_varchar2(buf)); dbms_lob.close(fil); exception WHEN no_data_found THEN BEGIN dbms_output.put_line('End of File reached. Closing file'); dbms_lob.fileclose(fil); -- or dbms_lob.filecloseall if appropriate END; END; / Statement processed. End of File reached. Closing file
In general, you should ensure that files opened in a PL/SQL block using DBMS_LOB are closed before normal or abnormal termination of the block.
Oracle8i supports the definition, creation, deletion, access, and update of temporary LOBs. Your temporary tablespace stores the temporary LOB data. Temporary LOBs are not permanently stored in the database. Their purpose is mainly to perform transformations on LOB data.
A temporary LOB is empty when it is created. By default, all temporary LOBs are deleted at the end of the session in which they were created. If a process dies unexpectedly or if the database crashes, then temporary LOBs are deleted, and the space for temporary LOBs is freed.
In Oracle8i, there is also an interface to let you group temporary LOBs together into a logical bucket. The duration represents this logical store for temporary LOBs. Each temporary LOB can have separate storage characteristics, such as CACHE/ NOCACHE. There is a default store for every session into which temporary LOBs are placed if you don't specify a specific duration. Additionally, you are able to perform a free operation on durations, which causes all contents in a duration to be freed.
There is no support for consistent read (CR), undo, backup, parallel processing, or transaction management for temporary LOBs. Because CR and rollbacks are not supported for temporary LOBs, you must free the temporary LOB and start over again if you encounter an error.
Because CR, undo, and versions are not generated for temporary LOBs, there is potentially a performance impact if you assign multiple locators to the same temporary LOB. Semantically, each locator should have its own copy of the temporary LOB.
A copy of a temporary LOB is created if the user modifies the temporary LOB while another locator is also pointing to it. The locator on which a modification was performed now points to a new copy of the temporary LOB. Other locators no longer see the same data as the locator through which the modification was made. A deep copy was not incurred by permanent LOBs in these types of situations, because CR snapshots and version pages enable users to see their own versions of the LOB cheaply.
You can gain pseudo-REF semantics by using pointers to locators in OCI and by having multiple pointers to locators point to the same temporary LOB locator, if necessary. In PL/SQL, you must avoid using more than one locator per temporary LOB. The temporary LOB locator can be passed "by ref" to other procedures.
Because temporary LOBs are not associated with any table schema, there are no meanings to the terms in-row and out-of-row temporary LOBs. Creation of a temporary LOB instance by a user causes the engine to create and return a 'locator' to the LOB data. The PL/SQL DBMS_LOB package, PRO*C, OCI, and other programmatic interfaces operate on temporary LOBs through these locators just as they do for permanent LOBs.
There is no support for client side temporary LOBs. All temporary LOBs reside in the server.
Temporary LOBs do not support the EMPTY_BLOB or EMPTY_CLOB functions that are supported for permanent LOBs. The EMPTY_BLOB function specifies the fact that the LOB is initialized, but not populated with any data.
A temporary LOB instance can only be destroyed by using OCI or the DBMS_LOB package by using the appropriate FREETEMPORARY or OCIDurationEnd statement.
A temporary LOB instance can be accessed and modified using appropriate OCI and DBMS_LOB statements, just as for regular permanent internal LOBs. To make a temporary LOB permanent, you must explicitly use the OCI or DBMS_LOB COPY command, and copy the temporary LOB into a permanent one.
Security is provided through the LOB locator. Only the user who created the temporary LOB is able to see it. Locators are not expected to be able to pass from one user's session to another. Even if someone did pass a locator from one session to another, they would not access the temporary LOBs from the original session. Temporary LOB lookup is localized to each user's own session. Someone using a locator from somewhere else is only able to access LOBs within his own session that have the same LOB ID. Users should not try to do this, but if they do, they are not able to affect anyone else's data.
Oracle keeps track of temporary LOBs per session in a v$ view called V$TEMPORARY_LOBS, which contains information about how many temporary LOBs exist per session. V$ views are for DBA use. From the session, Oracle can determine which user owns the temporary LOBs. By using V$TEMPORARY_LOBS in conjunction with DBA_SEGMENTS, a DBA can see how much space is being used by a session for temporary LOBs. These tables can be used by DBAs to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.
DBMS_LOB return NULL if any of the input parameters are NULL. All procedures in DBMS_LOB raise an exception if the LOB locator is input as NULL.
CLOBs do not verify if the character set IDs of the parameters (CLOB parameters, VARCHAR2 buffers and patterns, etc.) match. It is the user's responsibility to ensure this.
LOBs still adhere to value semantics in order to be consistent with permanent LOBs and to try to conform to the ANSI standard for LOBs. As a result, each time a user does an OCILobLocatatorAssign, or the equivalent assignment in PL/SQL, the database makes a copy of the temporary LOB.
Each locator points to its own LOB value. If one locator is used to create a temporary LOB, and if it is assigned to another LOB locator using OCILobLOcatorAssign in OCI or through an assignment operation in PL/SQL, then the database copies the original temporary LOB and causes the second locator to point to the copy.
In order for users to modify the same LOB, they must go through the same locator. In OCI, this can be accomplished fairly easily by using pointers to locators and assigning the pointers to point to the same locator. In PL/SQL, the same LOB variable must be used to update the LOB to get this effect.
The following example shows a place where a user incurs a copy, or at least an extra roundtrip to the server.
DECLARE a blob; b blob; BEGIN dbms_lob.createtemporary(b, TRUE, dbms_lob.session); -- the following assignment results in a deep copy a := b; END;
The PL/SQL compiler makes temporary copies of actual arguments bound to OUT or IN OUT parameters. If the actual parameter is a temporary LOB, then the temporary copy is a deep (value) copy.
The following PL/SQL block illustrates the case where the user incurs a deep copy by passing a temporary LOB as an IN OUT parameter.
DECLARE a blob; procedure foo(parm IN OUT blob) is BEGIN ... END; BEGIN dbms_lob.createtemporary(a, TRUE, dbms_lob.session); -- the following call results in a deep copy of the blob a foo(a); END;
To minimize deep copies on PL/SQL parameter passing, use the NOCOPY compiler hint where possible.
This procedure appends the contents of a source internal LOB to a destination LOB. It appends the complete source LOB.
There are two overloaded APPEND procedures.
DBMS_LOB.APPEND ( dest_lob IN OUT NOCOPY BLOB, src_lob IN BLOB); DBMS_LOB.APPEND ( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET dest_lob%CHARSET);
None.
| Parameter | Description |
|---|---|
dest_lob |
Locator for the internal |
src_lob |
Locator for the internal |
| Exception | Description |
|---|---|
VALUE_ERROR |
Either the source or the destination |
CREATE OR REPLACE PROCEDURE Example_1a IS dest_lob, src_lob BLOB; BEGIN -- get the LOB locators -- note that the FOR UPDATE clause locks the row SELECT b_lob INTO dest_lob FROM lob_table WHERE key_value = 12 FOR UPDATE; SELECT b_lob INTO src_lob FROM lob_table WHERE key_value = 21; DBMS_LOB.APPEND(dest_lob, src_lob); COMMIT; EXCEPTION WHEN some_exception THEN handle_exception; END; CREATE OR REPLACE PROCEDURE Example_1b IS dest_lob, src_lob BLOB; BEGIN -- get the LOB locators -- note that the FOR UPDATE clause locks the row SELECT b_lob INTO dest_lob FROM lob_table WHERE key_value = 12 FOR UPDATE; SELECT b_lob INTO src_lob FROM lob_table WHERE key_value = 12; DBMS_LOB.APPEND(dest_lob, src_lob); COMMIT; EXCEPTION WHEN some_exception THEN handle_exception; END;
This procedure closes a previously opened internal or external LOB.
DBMS_LOB.CLOSE ( lob_loc IN OUT NOCOPY BLOB); DBMS_LOB.CLOSE ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS); DBMS_LOB.CLOSE ( file_loc IN OUT NOCOPY BFILE);
None.
No error is returned if the BFILE exists but is not opened. An error is returned if the LOB is not open.
CLOSE requires a round-trip to the server for both internal and external LOBs. For internal LOBs, CLOSE triggers other code that relies on the close call, and for external LOBs (BFILEs), CLOSE actually closes the server-side operating system file.
This function compares two entire LOBs or parts of two LOBs. You can only compare LOBs of the same datatype (LOBs of BLOB type with other BLOBs, and CLOBs with CLOBs, and BFILEs with BFILEs). For BFILEs, the file must be already opened using a successful FILEOPEN operation for this operation to succeed.
COMPARE returns zero if the data exactly matches over the range specified by the offset and amount parameters. Otherwise, a non-zero INTEGER is returned.
For fixed-width n-byte CLOBs, if the input amount for COMPARE is specified to be greater than (4294967295/n), then COMPARE matches characters in a range of size (4294967295/n), or Max(length(clob1), length(clob2)), whichever is lesser.
DBMS_LOB.COMPARE ( lob_1 IN BLOB, lob_2 IN BLOB, amount IN INTEGER := 4294967295, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.COMPARE ( lob_1 IN CLOB CHARACTER SET ANY_CS, lob_2 IN CLOB CHARACTER SET lob_1%CHARSET, amount IN INTEGER := 4294967295, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.COMPARE ( lob_1 IN BFILE, lob_2 IN BFILE, amount IN INTEGER, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER;
pragma restrict_references(COMPARE, WNDS, WNPS, RNDS, RNPS);
CREATE OR REPLACE PROCEDURE Example2a IS lob_1, lob_2 BLOB; retval INTEGER; BEGIN SELECT b_col INTO lob_1 FROM lob_table WHERE key_value = 45; SELECT b_col INTO lob_2 FROM lob_table WHERE key_value = 54; retval := dbms_lob.compare(lob_1, lob_2, 5600, 33482, 128); IF retval = 0 THEN ; -- process compared code ELSE ; -- process not compared code END IF; END; CREATE OR REPLACE PROCEDURE Example_2b IS fil_1, fil_2 BFILE; retval INTEGER; BEGIN SELECT f_lob INTO fil_1 FROM lob_table WHERE key_value = 45; SELECT f_lob INTO fil_2 FROM lob_table WHERE key_value = 54; dbms_lob.fileopen(fil_1, dbms_lob.file_readonly); dbms_lob.fileopen(fil_2, dbms_lob.file_readonly); retval := dbms_lob.compare(fil_1, fil_2, 5600, 3348276, 2765612); IF (retval = 0) THEN ; -- process compared code ELSE ; -- process not compared code END IF; dbms_lob.fileclose(fil_1); dbms_lob.fileclose(fil_2); END;
This procedure copies all, or a part of, a source internal LOB to a destination internal LOB. You can specify the offsets for both the source and destination LOBs, and the number of bytes or characters to copy.
If the offset you specify in the destination LOB is beyond the end of the data currently in this LOB, then zero-byte fillers or spaces are inserted in the destination BLOB or CLOB respectively. If the offset is less than the current length of the destination LOB, then existing data is overwritten.
It is not an error to specify an amount that exceeds the length of the data in the source LOB. Thus, you can specify a large amount to copy from the source LOB, which copies data from the src_offset to the end of the source LOB.
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 dest_lob%CHARSET, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1);
None.
None.
| Exception | Description |
|---|---|
VALUE_ERROR |
Any of the input parameters are |
INVALID_ARGVAL |
-
-
- |
CREATE OR REPLACE PROCEDURE Example_3a IS lobd, lobs BLOB; dest_offset INTEGER := 1 src_offset INTEGER := 1 amt INTEGER := 3000; BEGIN SELECT b_col INTO lobd FROM lob_table WHERE key_value = 12 FOR UPDATE; SELECT b_col INTO lobs FROM lob_table WHERE key_value = 21; DBMS_LOB.COPY(lobd, lobs, amt, dest_offset, src_offset); COMMIT; EXCEPTION WHEN some_exception THEN handle_exception; END; CREATE OR REPLACE PROCEDURE Example_3b IS lobd, lobs BLOB; dest_offset INTEGER := 1 src_offset INTEGER := 1 amt INTEGER := 3000; BEGIN SELECT b_col INTO lobd FROM lob_table WHERE key_value = 12 FOR UPDATE; SELECT b_col INTO lobs FROM lob_table WHERE key_value = 12; DBMS_LOB.COPY(lobd, lobs, amt, dest_offset, src_offset); COMMIT; EXCEPTION WHEN some_exception THEN handle_exception; END;
This procedure creates a temporary BLOB or CLOB and its corresponding index in your default temporary tablespace.
DBMS_LOB.CREATETEMPORARY ( lob_loc IN OUT NOCOPY BLOB, cache IN BOOLEAN, dur IN PLS_INTEGER := 10); DBMS_LOB.CREATETEMPORARY ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, cache IN BOOLEAN, dur IN PLS_INTEGER := 10);
None.
None.
None.
DBMS_LOB.CREATETEMPORARY(Dest_Loc, TRUE, DBMS_LOB.SESSION)
This procedure erases an entire internal LOB or part of an internal LOB.
|
Note:
The length of the |
When data is erased from the middle of a LOB, zero-byte fillers or spaces are written for BLOBs or CLOBs respectively.
The actual number of bytes or characters erased can differ from the number you specified in the amount parameter if the end of the LOB value is reached before erasing the specified number. The actual number of characters or bytes erased is returned in the amount parameter.
DBMS_LOB.ERASE ( lob_loc IN OUT NOCOPY BLOB, amount IN OUT NOCOPY INTEGER, offset IN INTEGER := 1); DBMS_LOB.ERASE ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN OUT NOCOPY INTEGER, offset IN INTEGER := 1);
None.
None.
| Exception | Description |
|---|---|
VALUE_ERROR |
Any input parameter is |
INVALID_ARGVAL |
-
- |
CREATE OR REPLACE PROCEDURE Example_4 IS lobd BLOB; amt INTEGER := 3000; BEGIN SELECT b_col INTO lobd FROM lob_table WHERE key_value = 12 FOR UPDATE; dbms_lob.erase(dest_lob, amt, 2000); COMMIT; END;
This procedure closes a BFILE that has already been opened via the input locator.
DBMS_LOB.FILECLOSE ( file_loc IN OUT NOCOPY BFILE);
None.
| Parameter | Description |
|---|---|
file_loc |
Locator for the |
None.
CREATE OR REPLACE PROCEDURE Example_5 IS fil BFILE; BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99; dbms_lob.fileopen(fil); -- file operations dbms_lob.fileclose(fil); EXCEPTION WHEN some_exception THEN handle_exception; END;
This procedure closes all BFILEs opened in the session.
DBMS_LOB.FILECLOSEALL;
None.
None.
| Exception | Description |
|---|---|
UNOPENED_FILE |
No file has been opened in the session. |
CREATE OR REPLACE PROCEDURE Example_6 IS
fil BFILE;
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99;
dbms_lob.fileopen(fil);
-- file operations
dbms_lob.filecloseall;
EXCEPTION
WHEN some_exception
THEN handle_exception;
END;
This function finds out if a given BFILE locator points to a file that actually exists on the server's filesystem.
DBMS_LOB.FILEEXISTS ( file_loc IN BFILE) RETURN INTEGER;
pragma restrict_references(FILEEXISTS, WNDS, RNDS, WNPS, RNPS);
| Parameter | Description |
|---|---|
file_loc |
Locator for the |
| Return | Description |
|---|---|
0 |
Physical file does not exist. |
1 |
Physical file exists. |
CREATE OR REPLACE PROCEDURE Example_7 IS fil BFILE; BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12; IF (dbms_lob.fileexists(fil)) THEN ; -- file exists code ELSE ; -- file does not exist code END IF; EXCEPTION WHEN some_exception THEN handle_exception; END;
This procedure determines the directory alias and filename, given a BFILE locator. This function only indicates the directory alias name and filename assigned to the locator, not if the physical file or directory actually exists.
The maximum constraint values for the dir_alias buffer is 30, and for the entire pathname is 2000.
DBMS_LOB.FILEGETNAME ( file_loc IN BFILE, dir_alias OUT VARCHAR2, filename OUT VARCHAR2);
None.
| Parameter | Description |
|---|---|
file_loc |
Locator for the |
dir_alias |
Directory alias. |
filename |
Name of the |
None.
| Exception | Description |
|---|---|
VALUE_ERROR |
Any of the input parameters are |
INVALID_ARGVAL |
|
CREATE OR REPLACE PROCEDURE Example_8 IS fil BFILE; dir_alias VARCHAR2(30); name VARCHAR2(2000); BEGIN IF (dbms_lob.fileexists(fil)) THEN dbms_lob.filegetname(fil, dir_alias, name); dbms_output.put_line("Opening " || dir_alias || name); dbms_lob.fileopen(fil, dbms_lob.file_readonly); -- file operations dbms_output.fileclose(fil); END IF; END;
This function finds out whether a BFILE was opened with the given FILE locator.
If the input FILE locator was never passed to the FILEOPEN procedure, then the file is considered not to be opened by this locator. However, a different locator may have this file open. In other words, openness is associated with a specific locator.
DBMS_LOB.FILEISOPEN ( file_loc IN BFILE) RETURN INTEGER;
pragmarestrict_references(FILEISOPEN,WNDS,RNDS,WNPS,RNPS);
| Parameter | Description |
|---|---|
file_loc |
Locator for the |
INTEGER: 0 = file is not open, 1 = file is open
CREATE OR REPLACE PROCEDURE Example_9 IS DECLARE fil BFILE; pos INTEGER; pattern VARCHAR2(20); BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12; -- open the file IF (fileisopen(fil)) THEN pos := dbms_lob.instr(fil, pattern, 1025, 6); -- more file operations dbms_lob.fileclose(fil); ELSE ; -- return error END IF; END;
This procedure opens a BFILE for read-only access. BFILEs may not be written through Oracle.
DBMS_LOB.FILEOPEN ( file_loc IN OUT NOCOPY BFILE, open_mode IN BINARY_INTEGER := file_readonly);
None.
| Parameter | Description |
|---|---|
file_loc |
Locator for the |
open_mode |
File access is read-only. |
None.
CREATE OR REPLACE PROCEDURE Example_10 IS fil BFILE; BEGIN -- open BFILE SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99; IF (dbms_lob.fileexists(fil)) THEN dbms_lob.fileopen(fil, dbms_lob.file_readonly); -- file operation dbms_lob.fileclose(fil); END IF; EXCEPTION WHEN some_exception THEN handle_exception; END;
This procedure frees the temporary BLOB or CLOB in your default temporary tablespace. After the call to FREETEMPORARY, the LOB locator that was freed is marked as invalid.
If an invalid LOB locator is assigned to another LOB locator using OCILobLocatorAssign in OCI or through an assignment operation in PL/SQL, then the target of the assignment is also freed and marked as invalid.
DBMS_LOB.FREETEMPORARY ( lob_loc IN OUT NOCOPY BLOB); DBMS_LOB.FREETEMPORARY ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
None.
| Parameter | Description |
|---|---|
lob_loc |
|
None.
None.
DECLARE a blob; b blob; BEGIN dbms_lob.createtemporary(a, TRUE, dbms_lob.session); dbms_lob.createtemporary(b, TRUE, dbms_lob.session); ... -- the following call frees lob a dbms_lob.freetemporary(a); -- at this point lob locator a is marked as invalid -- the following assignment frees the lob b and marks it as invalid also b := a; END;
When creating the table, you can specify the chunking factor, which can be a multiple of Oracle blocks. This corresponds to the chunk size used by the LOB data layer when accessing or modifying the LOB value. Part of the chunk is used to store system-related information, and the rest stores the LOB value.
This function returns the amount of space used in the LOB chunk to store the LOB value.
DBMS_LOB.GETCHUNKSIZE ( lob_loc IN BLOB) RETURN INTEGER; DBMS_LOB.GETCHUNKSIZE ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER;
pragma restrict_references(GETCHUNKSIZE, WNDS, RNDS, WNPS, RNPS);
| Parameter | Description |
|---|---|
lob_loc |
|
The value returned for BLOBs is in terms of bytes. The value returned for CLOBs is in terms of characters.
None.
Performance is improved if you enter read/write requests using a multiple of this chunk size. For writes, there is an added benefit, because LOB chunks are versioned, and if all writes are done on a chunk basis, then no extra or excess versioning is done or duplicated. You could batch up the WRITE until you have enough for a chunk, instead of issuing several WRITE calls for the same chunk.
This function gets the length of the specified LOB. The length in bytes or characters is returned.
The length returned for a BFILE includes the EOF, if it exists. Any 0-byte or space filler in the LOB caused by previous ERASE or WRITE operations is also included in the length count. The length of an empty internal LOB is 0.
DBMS_LOB.GETLENGTH ( lob_loc IN BLOB) RETURN INTEGER; DBMS_LOB.GETLENGTH ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; DBMS_LOB.GETLENGTH ( lob_loc IN BFILE) RETURN INTEGER;
pragma restrict_references(GETLENGTH, WNDS, WNPS, RNDS, RNPS);
| Parameter | Description |
|---|---|
lob_loc |
The locator for the |
The length of the LOB in bytes or characters as an INTEGER. NULL is returned if the input LOB is NULL or if the input lob_loc is NULL. An error is returned in the following cases for BFILEs:
lob_loc does not have the necessary directory and OS privileges
lob_loc cannot be read because of an OS read error
None.
CREATE OR REPLACE PROCEDURE Example_11a IS lobd BLOB; length INTEGER; BEGIN -- get the LOB locator SELECT b_lob INTO lobd FROM lob_table WHERE key_value = 42; length := dbms_lob.getlength(lob_loc); IF length IS NULL THEN dbms_output.put_line('LOB is null.'); ELSE dbms_output.put_line('The length is ' || length); END IF; END; CREATE OR REPLACE PROCEDURE Example_11b IS DECLARE len INTEGER; fil BFILE; BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12; len := dbms_lob.length(fil); END;
This function returns the matching position of the nth occurrence of the pattern in the LOB, starting from the offset you specify.
The form of the VARCHAR2 buffer (the pattern parameter) must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.
For BFILEs, the file must be already opened using a successful FILEOPEN operation for this operation to succeed.
Operations that accept RAW or VARCHAR2 parameters for pattern matching, such as INSTR, do not support regular expressions or special matching characters (as in the case of SQL LIKE) in the pattern parameter or substrings.
DBMS_LOB.INSTR ( lob_loc IN BLOB, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.INSTR ( lob_loc IN CLOB CHARACTER SET ANY_CS, pattern IN VARCHAR2 CHARACTER SET lob_loc%CHARSET, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.INSTR ( lob_loc IN BFILE, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER;
pragma restrict_references(INSTR, WNDS, WNPS, RNDS, RNPS);
CREATE OR REPLACE PROCEDURE Example_12a IS lobd CLOB; pattern VARCHAR2 := 'abcde'; position INTEGER := 10000; BEGIN -- get the LOB locator SELECT b_col INTO lobd FROM lob_table WHERE key_value = 21; position := DBMS_LOB.INSTR(lobd, pattern, 1025, 6); IF position = 0 THEN dbms_output.put_line('Pattern not found'); ELSE dbms_output.put_line('The pattern occurs at ' || position); END IF; END; CREATE OR REPLACE PROCEDURE Example_12b IS DECLARE fil BFILE; pattern VARCHAR2; pos INTEGER; BEGIN -- initialize pattern -- check for the 6th occurrence starting from 1025th byte SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12; dbms_lob.fileopen(fil, dbms_lob.file_readonly); pos := dbms_lob.instr(fil, pattern, 1025, 6); dbms_lob.fileclose(fil); END;
This function checks to see if the LOB was already opened using the input locator. This subprogram is for internal and external LOBs.
DBMS_LOB.ISOPEN ( lob_loc IN BLOB) RETURN INTEGER; DBMS_LOB.ISOPEN ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; DBMS_LOB.ISOPEN ( file_loc IN BFILE) RETURN INTEGER;
pragma restrict_references(ISOPEN, WNDS, RNDS, WNPS, RNPS);
| Parameter | Description |
|---|---|
lob_loc |
|
file_loc |
File locator. |
None.
For BFILES, openness is associated with the locator. If the input locator was never passed to OPEN, then the BFILE is not considered to be opened by this locator. However, a different locator may have opened the BFILE. More than one OPEN can be performed on the same BFILE using different locators.
For internal LOBs, openness is associated with the LOB, not with the locator. If locator1 opened the LOB, then locator2 also sees the LOB as open. For internal LOBs, ISOPEN requires a round-trip, because it checks the state on the server to see if the LOB is indeed open.
For external LOBs (BFILEs), ISOPEN also requires a round-trip, because that's where the state is kept.
DBMS_LOB.ISTEMPORARY ( lob_loc IN BLOB) RETURN INTEGER; DBMS_LOB.ISTEMPORARY ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER;
PRAGMA RESTRICT_REFERENCES(istemporary, WNDS, RNDS, WNPS, RNPS);
| Parameter | Description |
|---|---|
lob_loc |
|
temporary |
Boolean, which indicates whether the LOB is temporary or not. |
This function returns TRUE in temporary if the locator is pointing to a temporary LOB. It returns FALSE otherwise.
None.
This procedure copies all, or a part of, a source external LOB (BFILE) to a destination internal LOB.
You can specify the offsets for both the source and destination LOBs, and the number of bytes to copy from the source BFILE. The amount and src_offset, because they refer to the BFILE, are in terms of bytes, and the dest_offset is either in bytes or characters for BLOBs and CLOBs respectively.
If the offset you specify in the destination LOB is beyond the end of the data currently in this LOB, then zero-byte fillers or spaces are inserted in the destination BLOB or CLOB respectively. If the offset is less than the current length of the destination LOB, then existing data is overwritten.
There is an error if the input amount plus offset exceeds the length of the data in the BFILE.
DBMS_LOB.LOADFROMFILE ( dest_lob IN OUT NOCOPY BLOB, src_file IN BFILE, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); DBMS_LOB.LOADFROMFILE( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_file IN BFILE, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1);
None.
None.
| Exception | Description |
|---|---|
VALUE_ERROR |
Any of the input parameters are |
INVALID_ARGVAL |
-
-
- |
CREATE OR REPLACE PROCEDURE Example_l2f IS lobd BLOB; fils BFILE := BFILENAME('SOME_DIR_OBJ','some_file'); amt INTEGER := 4000; BEGIN SELECT b_lob INTO lobd FROM lob_table WHERE key_value = 42 FOR UPDATE; dbms_lob.fileopen(fils, dbms_lob.file_readonly); dbms_lob.loadfromfile(lobd, fils, amt); COMMIT; dbms_lob.fileclose(fils); END;
This procedure opens a LOB, internal or external, in the indicated mode. Valid modes include read-only, and read-write. It is an error to open the same LOB twice.
In Oracle8.0, the constant file_readonly was the only valid mode in which to open a BFILE. For Oracle 8i, two new constants have been added to the DBMS_LOB package: lob_readonly and lob_readwrite.
DBMS_LOB.OPEN ( lob_loc IN OUT NOCOPY BLOB, open_mode IN BINARY_INTEGER); DBMS_LOB.OPEN ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, open_mode IN BINARY_INTEGER); DBMS_LOB.OPEN ( file_loc IN OUT NOCOPY BFILE, open_mode IN BINARY_INTEGER := file_readonly);
None.
| Parameter | Description |
|---|---|
lob_loc |
|
open_mode |
Mode in which to open. |
OPEN requires a round-trip to the server for both internal and external LOBs. For internal LOBs, OPEN triggers other code that relies on the OPEN call. For external LOBs (BFILEs), OPEN requires a round-trip because the actual operating system file on the server side is being opened.
This procedure reads a piece of a LOB, and returns the specified amount into the buffer parameter, starting from an absolute offset from the beginning of the LOB.
The number of bytes or characters actually read is returned in the amount parameter. If the input offset points past the End of LOB, then amount is set to 0, and a NO_DATA_FOUND exception is raised.
DBMS_LOB.READ ( lob_loc IN BLOB, amount IN OUT NOCOPY BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW); DBMS_LOB.READ ( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN OUT NOCOPY BINARY_INTEGER, offset IN INTEGER, buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET); DBMS_LOB.READ ( lob_loc IN BFILE, amount IN OUT NOCOPY BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW);
None.
None.
The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.
When calling DBMS_LOB.READ from the client (for example, in a BEGIN/END block from within SQL*Plus), the returned buffer contains data in the client's character set. Oracle converts the LOB value from the server's character set to the client's character set before it returns the buffer to the user.
CREATE OR REPLACE PROCEDURE Example_13a IS src_lob BLOB; buffer RAW(32767); amt BINARY_INTEGER := 32767; pos INTEGER := 2147483647; BEGIN SELECT b_col INTO src_lob FROM lob_table WHERE key_value = 21; LOOP dbms_lob.read (src_lob, amt, pos, buffer); -- process the buffer pos := pos + amt; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('End of data'); END; CREATE OR REPLACE PROCEDURE Example_13b IS fil BFILE; buf RAW(32767); amt BINARY_INTEGER := 32767; pos INTEGER := 2147483647; BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21; dbms_lob.fileopen(fil, dbms_lob.file_readonly); LOOP dbms_lob.read(fil, amt, pos, buf); -- process contents of buf pos := pos + amt; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN dbms_output.putline ('End of LOB value reached'); dbms_lob.fileclose(fil); END; END;
Example for efficient I/O on OS that performs better with block I/O rather than stream I/O:
CREATE OR REPLACE PROCEDURE Example_13c IS fil BFILE; amt BINARY_INTEGER := 1024; -- or n x 1024 for reading n buf RAW(1024); -- blocks at a time tmpamt BINARY_INTEGER; BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99; dbms_lob.fileopen(fil, dbms_lob.file_readonly); LOOP dbms_lob.read(fil, amt, pos, buf); -- process contents of buf pos := pos + amt; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN dbms_output.putline ('End of data reached'); dbms_lob.fileclose(fil); END; END;
This function returns amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB.
For fixed-width n-byte CLOBs, if the input amount for SUBSTR is specified to be greater than (32767/n), then SUBSTR returns a character buffer of length (32767/n), or the length of the CLOB, whichever is lesser.
DBMS_LOB.SUBSTR ( lob_loc IN BLOB, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW; DBMS_LOB.SUBSTR ( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET; DBMS_LOB.SUBSTR ( lob_loc IN BFILE, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW;
pragma restrict_references(SUBSTR, WNDS, WNPS, RNDS, RNPS);
| Return | Description |
|---|---|
RAW |
Function overloading that has a |
VARCHAR2 |
|
NULL |
- |
The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.
When calling DBMS_LOB.SUBSTR from the client (for example, in a BEGIN/END block from within SQL*Plus), the returned buffer contains data in the client's character set. Oracle converts the LOB value from the server's character set to the client's character set before it returns the buffer to the user.
CREATE OR REPLACE PROCEDURE Example_14a IS src_lob CLOB; pos INTEGER := 2147483647; buf VARCHAR2(32000); BEGIN SELECT c_lob INTO src_lob FROM lob_table WHERE key_value = 21; buf := DBMS_LOB.SUBSTR(src_lob, 32767, pos); -- process the data END; CREATE OR REPLACE PROCEDURE Example_14b IS fil BFILE; pos INTEGER := 2147483647; pattern RAW; BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21; dbms_lob.fileopen(fil, dbms_lob.file_readonly); pattern := dbms_lob.substr(fil, 255, pos); dbms_lob.fileclose(fil); END;
This procedure trims the value of the internal LOB to the length you specify in the newlen parameter. Specify the length in bytes for BLOBs, and specify the length in characters for CLOBs.
If you attempt to TRIM an empty LOB, then nothing occurs, and TRIM returns no error. If the new length that you specify in newlen is greater than the size of the LOB, then an exception is raised.
DBMS_LOB.TRIM ( lob_loc IN OUT NOCOPY BLOB, newlen IN INTEGER); DBMS_LOB.TRIM ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, newlen IN INTEGER);
None.
| Parameter | Description |
|---|---|
lob_loc |
Locator for the internal |
newlen |
New, trimmed length of the |
None.
| Exception | Description |
|---|---|
VALUE_ERROR |
|
INVALID_ARGVAL |
- |
CREATE OR REPLACE PROCEDURE Example_15 IS lob_loc BLOB; BEGIN -- get the LOB locator SELECT b_col INTO lob_loc FROM lob_table WHERE key_value = 42 FOR UPDATE; dbms_lob.trim(lob_loc, 4000); COMMIT; END;
This procedure writes a specified amount of data into an internal LOB, starting from an absolute offset from the beginning of the LOB. The data is written from the buffer parameter.
WRITE replaces (overwrites) any data that already exists in the LOB at the offset, for the length you specify.
There is an error if the input amount is more than the data in the buffer. If the input amount is less than the data in the buffer, then only amount bytes or characters from the buffer is written to the LOB. If the offset you specify is beyond the end of the data currently in the LOB, then zero-byte fillers or spaces are inserted in the BLOB or CLOB respectively.
DBMS_LOB.WRITE ( lob_loc IN OUT NOCOPY BLOB, amount IN BINARY_INTEGER, offset IN INTEGER, buffer IN RAW); DBMS_LOB.WRITE ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN BINARY_INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
None.
None.
| Exception | Description |
|---|---|
VALUE_ERROR |
Any of |
INVALID_ARGVAL |
- |
The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.
When calling DBMS_LOB.WRITE from the client (for example, in a BEGIN/END block from within SQL*Plus), the buffer must contain data in the client's character set. Oracle converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.
CREATE OR REPLACE PROCEDURE Example_16 IS lob_loc BLOB; buffer RAW; amt BINARY_INTEGER := 32767; pos INTEGER := 2147483647; i INTEGER; BEGIN SELECT b_col INTO lob_loc FROM lob_table WHERE key_value = 12 FOR UPDATE; FOR i IN 1..3 LOOP dbms_lob.write (lob_loc, amt, pos, buffer); -- fill in more data pos := pos + amt; END LOOP; EXCEPTION4 WHEN some_exception THEN handle_exception; END;
This procedure writes a specified amount of data to the end of an internal LOB. The data is written from the buffer parameter.
There is an error if the input amount is more than the data in the buffer. If the input amount is less than the data in the buffer, then only amount bytes or characters from the buffer are written to the end of the LOB.
DBMS_LOB.WRITEAPPEND ( lob_loc IN OUT NOCOPY BLOB, amount IN BINARY_INTEGER, buffer IN RAW); DBMS_LOB.WRITEAPPEND ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN BINARY_INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
None.
| Exception | Description |
|---|---|
VALUE_ERROR |
Any of |
INVALID_ARGVAL |
- |
The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.
When calling DBMS_LOB.WRITEAPPEND from the client (for example, in a BEGIN/END block from within SQL*Plus), the buffer must contain data in the client's character set. Oracle converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.
CREATE OR REPLACE PROCEDURE Example_17 IS lob_loc BLOB; buffer RAW; amt BINARY_INTEGER := 32767; i INTEGER; BEGIN SELECT b_col INTO lob_loc FROM lob_table WHERE key_value = 12 FOR UPDATE; FOR i IN 1..3 LOOP -- fill the buffer with data to be written to the lob dbms_lob.writeappend (lob_loc, amt, buffer); END LOOP; END;
