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.
This chapter contains the following topics:
Overview
Security Model
Constants
Datatypes
Operational Notes
Rules and Limits
Exceptions
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. Operations provided by this package are performed under the current calling user, not under the package owner SYS.
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.
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 ...
See Also:
For more information onAUTHID and privileges, see Oracle Database PL/SQL Language ReferenceYou can provide secure access to BFILEs using the DIRECTORY feature discussed in BFILENAME function in the Oracle Database SecureFiles and Large Objects Developer's Guide and the Oracle Database SQL Language Reference.
For information about the security model pertaining to temporary LOBs, see Operational Notes.
The DBMS_LOB package uses the constants shown in following tables:
Table 82-1 DBMS_LOB Constants - Basic
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
|
Create the |
|
|
|
|
Open the specified |
|
|
|
|
Open the specified |
|
|
|
|
Open the specified |
|
|
|
18446744073709551615 |
Maximum size of a |
|
|
|
|
Create the |
Table 82-2 DBMS_LOB Constants - Option Types
| Constant | Definition | Value | Description |
|---|---|---|---|
|
|
|
|
Set/Get the |
|
|
|
|
Set/Get the |
|
|
|
|
Get the |
Table 82-3 DBMS_LOB Constants - Option Values
| Constant | Definition | Value | Description |
|---|---|---|---|
|
|
|
|
For SETOPTIONS Procedures, set compress off; for GETOPTIONS Functions, compress is off |
|
|
|
|
For SETOPTIONS Procedures, set compress on; for GETOPTIONS Functions, compress is on |
|
|
|
|
For SETOPTIONS Procedures, set deduplicate is off; for GETOPTIONS Functions, deduplicate is off |
|
|
|
|
For SETOPTIONS Procedures, set deduplicate is on; for GETOPTIONS Functions, deduplicate is on |
|
|
|
|
For GETOPTIONS Functions, encrypt is off |
|
|
|
|
For GETOPTIONS Functions, encrypt is on |
Table 82-4 DBMS_LOB Constants - DBFS State Value Types
| Constant | Definition | Value | Description |
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table 82-5 DBMS_LOB Constants - DBFS Cache Flags
| Constant | Definition | Value | Description |
|---|---|---|---|
|
|
|
1 |
Put the |
|
|
|
|
Put the |
The DBMS_LOB package uses the datatypes shown in Table 82-7.
Table 82-7 Datatypes Used by DBMS_LOB
| Type | Description |
|---|---|
|
|
Source or destination binary |
|
|
Source or destination |
|
|
Source or destination character |
|
|
Source or destination character buffer (used with |
|
|
Specifies the size of a buffer or |
|
|
Large, binary object stored outside the database. |
The DBMS_LOB package defines no special types.
An NCLOB is a CLOB for holding fixed-width and varying-width, multibyte national character sets.
The clause ANY_CS in the specification of DBMS_LOB subprograms for CLOBs enables the CLOB type to accept a CLOB or NCLOB locator variable as input.
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 file system. See also Chapter 1 of Oracle Database SecureFiles and Large Objects Developer's Guide.
To use LOBs in your database, you must first use SQL data definition language (DDL) to define the tables that contain LOB columns.
To populate your table with internal LOBs after LOB columns are defined in a table, you use the SQL data manipulation language (DML) to initialize or populate the locators in the LOB columns.
For an external LOB (BFILE) to be represented by a LOB locator, you must:
Ensure that a DIRECTORY object representing a valid, existing physical directory has been defined, and that physical files (the LOBs you plan to add) exist with read permission for the database. If your operating system uses case-sensitive path names, then be sure you specify the directory in the correct format.
Pass the DIRECTORY object and the filename of the external LOB you are adding to the BFILENAME function to create a LOB locator for your external LOB.
Once you have completed these tasks, you can insert or update a row containing a LOB column using the specified LOB locator.
After the LOBs are defined and created, you can then SELECT from 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 details on the different ways to do this, you must refer to the section of the Oracle Database SecureFiles and Large Objects Developer's Guide that describes "Accessing External LOBs (BFILEs)."
The database 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.
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.
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.
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 for each temporary LOB. The temporary LOB locator can be passed by reference 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/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.
The database keeps track of temporary LOBs for each session in a v$ view called V$TEMPORARY_LOBS, which contains information about how many temporary LOBs exist for each session. V$ views are for DBA use. From the session, the database 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.
The following notes are specific to temporary LOBs:
All functions in 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.
Operations based on CLOBs do not verify if the character set IDs of the parameters (CLOB parameters, VARCHAR2 buffers and patterns, and so on) match. It is the user's responsibility to ensure this.
Data storage resources are controlled by the DBA by creating different temporary tablespaces. DBAs can define separate temporary tablespaces for different users, if necessary.
Oracle Database does not support constraints on columns or attributes whose type is a LOB, with the following exception: NOT NULL constraints are supported for a LOB column or attribute.
The following rules apply in the specification of subprograms in this package:
length, offset, and amount parameters for subprograms operating on BLOBs and BFILEs must be specified in terms of bytes.
length, offset, and amount parameters for subprograms operating on CLOBs must be specified in terms of characters.
In multi-byte character sets, it is not possible to interpret these offsets correctly. As a result, SUBSTR raises the following error: ORA-22998: CLOB or NCLOB in multibyte character set not supported.
A subprogram raises an INVALID_ARGVAL exception if the following restrictions are not followed in specifying values for parameters (unless otherwise specified):
Only positive, absolute offsets from the beginning of LOB data are permitted: Negative offsets from the tail of the LOB are not permitted.
Only positive, nonzero values are permitted for the parameters that represent size and positional quantities, such as amount, offset, newlen, nth, and so on. Negative offsets and ranges observed in SQL string functions and operators are not permitted.
The value of offset, amount, newlen, nth must not exceed the value lobmaxsize 18446744073709551615 (264) in any DBMS_LOB subprogram.
For CLOBs consisting of fixed-width multibyte 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:
18446744073709551615/2 = 9223372036854775807
PL/SQL language specifications stipulate an upper limit of 32767 bytes (not characters) for RAW and VARCHAR2 parameters used in DBMS_LOB subprograms. For example, if you declare a variable to be:
charbuf VARCHAR2(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.
The %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; that is, they must both be NCLOBs, or they must both be CLOBs.
If the value of amount plus the offset exceeds the maximum LOB size allowed by the database, 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 on a LOB that is 4GB in size, then READ returns only 1GB (4GB-3GB) bytes.
Functions with NULL or invalid input values for parameters return a NULL. Procedures with NULL values for destination LOB parameters raise exceptions.
Operations involving patterns as parameters, such as 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.
The 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. The READ buffer for the last read contains 0 bytes.
For consistent 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.
Unless otherwise stated, the default value for an 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.
You must lock the row containing the destination internal 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.
The subprograms COMPARE, INSTR, READ, SUBSTR, FILECLOSE, FILECLOSEALL and LOADFROMFILE operate only on an opened BFILE locator; that is, a successful FILEOPEN call must precede a call to any of these subprograms.
For the functions 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.
In the event of several open files in the session whose closure has not been handled properly, you can use the FILECLOSEALL subprogram to close all files opened in the session and resume file operations from the beginning.
If you are the creator of a 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 preceding 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.
All files opened during a user session are implicitly closed at the end of the session. However, Oracle strongly recommends that you close the files after both normal and abnormal termination of operations on the 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:
-- This assumes a directory 'DDD' whose path is already known
DECLARE
fil BFILE:= bfilename('DDD', 'filename.foo');
pos INTEGER;
amt BINARY_INTEGER;
buf RAW(40);
BEGIN
SELECT ad_graphic INTO fil FROM print_media WHERE product_id = 3106;
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 ad_graphic INTO fil FROM print_media WHERE product_id = 3106;
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.
Table 82-8 DBMS_LOB Exceptions
| Exception | Code | Description |
|---|---|---|
|
|
|
You are trying to write too much data to the LOB: LOB size is limited to 4 gigabytes. |
|
|
|
Cannot perform operation with LOB buffering enabled |
|
|
|
The length of the |
|
|
|
The length of the |
|
|
|
The argument is expecting a non |
|
|
|
The directory used for the current operation is not valid if being accessed for the first time, or if it has been modified by the DBA since the last access. |
|
|
|
|
|
|
|
The directory leading to the file does not exist. |
|
|
|
The user does not have the necessary access privileges on the directory or the file for the operation. |
|
|
|
The number of open files has reached the maximum limit. |
|
|
|
The operation attempted on the file failed. |
|
|
|
Cannot perform a LOB write inside a query or PDML slave |
|
|
|
A non- |
|
|
|
An invalid argument was passed to a |
|
|
|
The mark provided to a |
|
|
|
Attempted to perform a |
|
|
|
The file is not open for the required operation to be performed. |
|
|
|
PL/SQL error for invalid values to subprogram's parameters. |
Table 82-9 DBMS_LOB Package Subprograms
| Subprogram | Description |
|---|---|
|
Appends the contents of the source |
|
|
Closes a previously opened internal or external |
|
|
Compares two entire |
|
|
Reads character data from a source |
|
|
Takes a source |
|
|
Copies all, or part, of the source |
|
|
Copies the DBFS link in the source |
|
|
Retrieves the data for the LOB from the DBFS store |
|
|
Creates a temporary |
|
|
Returns a unique file path name for use in creating a DBFS Link |
|
|
Erases all or part of a |
|
|
Closes the file |
|
|
Closes all previously opened files |
|
|
Checks if the file exists on the server |
|
|
Gets the directory object name and file name |
|
|
Checks if the file was opened using the input |
|
|
Opens a file |
|
|
Deletes the data at the specified offset for the specified length from the |
|
|
Inserts the specified data (limited to 32K) into the |
|
|
Moves the amount of bytes ( |
|
|
Replaces the data at the specified offset with the specified data (not to exceed 32k) |
|
|
Frees the temporary |
|
|
Returns the DBFS Link path associated with the specified SecureFile |
|
|
Retrieves the current DBFS Link state of the specified SecureFile |
|
|
Returns the amount of space used in the |
|
|
Returns the content ID string previously set by means of the SETCONTENTTYPE Procedure |
|
|
Gets the length of the LOB value |
|
|
Obtains settings corresponding to the |
|
|
Returns the storage limit for LOBs in your database configuration |
|
|
Returns the matching position of the nth occurrence of the pattern in the |
|
|
Checks to see if the |
|
|
Checks if the locator is pointing to a temporary |
|
|
Loads |
|
|
Loads |
|
|
Loads |
|
|
Writes the specified SecureFile data to the DBFS store |
|
|
Opens a |
|
|
Reads data from the |
|
|
Links the specified SecureFile to the specified path name. It does not copy the data to the path |
|
|
Sets the content type string for the data in the LOB |
|
|
Enables CSCE features on a per-LOB basis, overriding the default LOB column settings |
|
|
Returns part of the |
|
|
Trims the |
|
|
Writes data to the |
|
|
Writes a buffer to the end of a |
This procedure appends the contents of a source internal LOB to a destination LOB. It appends the complete source LOB.
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);
It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.
If APPEND is called on a LOB that has been archived, it implicitly gets the LOB before the first byte is written
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis 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);
Table 82-12 CLOSE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
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.
It is not mandatory that you wrap all LOB operations inside the Open/Close interfaces. However, if you open a LOB, you must close it before you commit the transaction; an error is produced if you do not. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
It is an error to commit the transaction before closing all opened LOBs that were opened by the transaction. When the error is returned, the openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed, but the domain and function-based indexes are not updated. If this happens, you should rebuild the functional and domain indexes on the LOB column.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis function compares two entire LOBs or parts of two LOBs.
DBMS_LOB.COMPARE ( lob_1 IN BLOB, lob_2 IN BLOB, amount IN INTEGER := DBMS_LOB.LOBMAXSIZE, 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 := DBMS_LOB.LOBMAXSIZE, 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;
Table 82-13 COMPARE Function Parameters
| Parameter | Description |
|---|---|
|
|
LOB locator of first target for comparison. |
|
|
LOB locator of second target for comparison. |
|
|
Number of bytes (for |
|
|
Offset in bytes or characters on the first |
|
|
Offset in bytes or characters on the second |
INTEGER: 0 if the comparison succeeds, nonzero if not.
NULL, if any of amount, offset_1 or offset_2 is not a valid LOB offset value. A valid offset is within the range of 1 to LOBMAXSIZE inclusive.
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 0 if the data exactly matches over the range specified by the offset and amount parameters. COMPARE returns -1 if the first CLOB is less than the second, and 1 if it is greater.
For fixed-width n-byte CLOBs, if the input amount for COMPARE is specified to be greater than (DBMS_LOB.LOBMAXSIZE/n), then COMPARE matches characters in a range of size (DBMS_LOB.LOBMAXSIZE/n), or Max(length(clob1), length(clob2)), whichever is lesser.
If COMPARE is called on any LOB that has been archived, it implicitly gets the LOB before the compare begins.
Table 82-14 COMPARE Function Exceptions for BFILE operations
| Exception | Description |
|---|---|
|
|
File was not opened using the input locator. |
|
|
Directory does not exist. |
|
|
You do not have privileges for the directory. |
|
|
Directory has been invalidated after the file was opened. |
|
|
File does not exist, or you do not have access privileges on the file. |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on either LOB |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis procedure reads character data from a source CLOB or NCLOB instance, converts the character data to the character set you specify, writes the converted data to a destination BLOB instance in binary format, and returns the new offsets. You can use this interface with any combination of persistent or temporary LOB instances as the source or destination.
DBMS_LOB.CONVERTTOBLOB( dest_lob IN OUT NOCOPY BLOB, src_clob IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, blob_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER);
Table 82-15 CONVERTTOBLOB Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
|
|
|
Number of characters to convert from the source If you want to copy the entire |
|
|
|
|
|
|
|
|
Desired character set ID of the converted data. |
|
|
This information is returned so you can use it for subsequent conversions without losing or misinterpreting any source data. For the very first conversion, or if do not care, use the default value of zero. |
|
|
Currently, the only possible warning is — inconvertible character. This occurs when the character in the source cannot be properly converted to a character in destination. The default replacement character (for example, '?') is used in place of the inconvertible character. The return value of this error message is defined as the constant |
Before calling the CONVERTTOBLOB procedure, the following preconditions must be met:
Both the source and destination LOB instances must exist.
If the destination LOB is a persistent LOB, the row must be locked. To lock the row, select the LOB using the FOR UPDATE clause of the SELECT statement.
All parameters are required. You must pass a variable for each OUT or IN OUT parameter. You must pass either a variable or a value for each IN parameter.
Table 82-16 gives a summary of typical values for each parameter. The first column lists the parameter, the second column lists the typical value, and the last column describes the result of passing the value. Note that constants are used for some values. These constants are defined in the dbmslob.sql package specification file.
Table 82-16 DBMS_LOB.CONVERTTOBLOB Typical Values
| Parameter | Value | Description |
|---|---|---|
|
|
|
convert the entire file |
|
|
|
start from the beginning |
|
|
|
start from the beginning |
|
|
|
default |
|
|
|
default language context |
|
|
|
no warning message, success character in source cannot be properly converted |
You must specify the desired character set for the destination LOB in the blob_csid parameter. You can pass a zero value for blob_csid. When you do so, the database assumes that the desired character set is the same as the source LOB character set.
You must specify the offsets for both the source and destination LOBs, and the number of characters to copy from the source LOB. The amount and src_offset values are in characters and the dest_offset is in bytes. To convert the entire LOB, you can specify LOBMAXSIZE for the amount parameter.
CONVERTTOBLOB gets the source and/or destination LOBs as necessary prior to conversion and write of the data.
Table 82-17 gives possible exceptions this procedure can throw. The first column lists the exception string and the second column describes the error conditions that can cause the exception.
Table 82-17 CONVERTTOBLOB Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of the input parameters are |
|
|
One or more of the following: - - - - |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for more information on using LOBs in application developmentThis procedure takes a source BLOB instance, converts the binary data in the source instance to character data using the character set you specify, writes the character data to a destination CLOB or NCLOB instance, and returns the new offsets. You can use this interface with any combination of persistent or temporary LOB instances as the source or destination.
DBMS_LOB.CONVERTTOCLOB( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_blob IN BLOB, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, blob_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER);
Table 82-18 CONVERTTOCLOB Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
|
|
|
Number of bytes to convert from the source If you want to copy the entire BLOB, pass the constant |
|
|
|
|
|
|
|
|
The character set ID of the source data |
|
|
This information is returned so you can use it for subsequent conversions without losing or misinterpreting any source data. For the very first conversion, or if do not care, use the default value of zero. |
|
|
Warning message. This parameter indicates when something abnormal happened during the conversion. You are responsible for checking the warning message. Currently, the only possible warning is — inconvertible character. This occurs when the character in the source cannot be properly converted to a character in destination. The default replacement character (for example, '?') is used in place of the inconvertible character. The return value of this error message is defined as the constant |
Before calling the CONVERTTOCLOB procedure, the following preconditions must be met:
Both the source and destination LOB instances must exist.
If the destination LOB is a persistent LOB, the row must be locked before calling the CONVERTTOCLOB procedure. To lock the row, select the LOB using the FOR UPDATE clause of the SELECT statement.
All parameters are required. You must pass a variable for each OUT or IN OUT parameter. You must pass either a variable or a value for each IN parameter.
Table 82-19 gives a summary of typical values for each parameter. The first column lists the parameter, the second column lists the typical value, and the last column describes the result of passing the value. Note that constants are used for some values. These constants are defined in the dbmslob.sql package specification file.
Table 82-19 DBMS_LOB.CONVERTTOCLOB Typical Values
| Parameter | Value | Description |
|---|---|---|
|
|
|
convert the entire file |
|
|
|
start from the beginning |
|
|
|
start from the beginning |
|
|
|
default |
|
|
|
default language context |
|
|
|
no warning message, success character in source cannot be properly converted |
You must specify the desired character set for the destination LOB in the blob_csid parameter. You can pass a zero value for blob_csid. When you do so, the database assumes that the desired character set is the same as the source LOB character set.
You must specify the offsets for both the source and destination LOBs, and the number of characters to copy from the source LOB. The amount and src_offset values are in characters and the dest_offset is in bytes. To convert the entire LOB, you can specify LOBMAXSIZE for the amount parameter.
CONVERTTOCLOB gets the source and/or destination LOBs as necessary prior to conversion and write of the data.
Table 82-20 CONVERTTOCLOB Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of the input parameters are |
|
|
One or more of the following: - - - - |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for more information on using LOBs in application developmentThis 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.
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);
Table 82-21 COPY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
|
|
|
Number of bytes (for |
|
|
Offset in bytes or characters in the destination |
|
|
Offset in bytes or characters in the source |
Table 82-22 COPY Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of the input parameters are |
|
|
Either: - - - - |
|
|
Cannot perform a LOB write inside a query or PDML slave |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on either LOB |
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.
It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.
Prior to copy, the source and destination LOBs are gotten, if they are currently archived. For a complete over-write, the destination LOB is not be retrieved.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis procedure copies the DBFS link in the source LOB to the destination LOB.
DBMS_LOB.COPY_DBFS_LINK ( lob_loc_dst IN OUT BLOB, lob_loc_src IN BLOB, flags IN PLS_INTEGER DEFAULT DBFS_LINK_NOCACHE); DBMS_LOB.COPY_DBFS_LINK ( lob_loc_dst IN OUT CLOB CHARACTER SET ANY_CS, lob_loc_src IN CLOB CHARACTER SET ANY_CS, flags IN PLS_INTEGER DEFAULT DBFS_LINK_NOCACHE);
Table 82-23 COPY_DBFS_LINK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
|
|
|
Options to
|
Table 82-24 COPY_DBFS_LINK Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Either |
|
|
|
|
|
If the source |
This procedure reads the archived data from the DBFS store and writes it back into the LOB.
DBMS_LOB.COPY_FROM_DBFS_LINK ( lob_loc IN OUT BLOB); DBMS_LOB.COPY_FROM_DBFS_LINK ( lob_loc IN OUT CLOB CHARACTER SET ANY_CS);
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 := DBMS_LOB.SESSION); DBMS_LOB.CREATETEMPORARY ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, cache IN BOOLEAN, dur IN PLS_INTEGER := 10);
Table 82-27 CREATETEMPORARY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
Specifies if |
|
|
1 of 2 predefined duration values ( If |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
Oracle Database PL/SQL Language Reference for more information about NOCOPY and passing temporary lobs as parameters
This subprogram returns a unique file path name for use in creating a DBFS Link.
DBMS_LOB.DBFS_LINK_GENERATE_PATH ( lob_loc IN BLOB, storage_dir IN VARCHAR2) RETURN VARCHAR2; DBMS_LOB.DBFS_LINK_GENERATE_PATH ( lob_loc IN CLOB CHARACTER SET ANY_CS, storage_dir IN VARCHAR2) RETURN VARCHAR2;
This procedure erases an entire internal LOB or part of an internal LOB.
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);
Table 82-30 ERASE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Locator for the |
|
|
Number of bytes (for |
|
|
Absolute offset (origin: 1) from the beginning 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.
ERASE gets the LOB if it is archived, unless the erase covers the entire LOB.
Note:
The length of theLOB is not decreased when a section of the LOB is erased. To decrease the length of the LOB value, see the "TRIM Procedures".Table 82-31 ERASE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any input parameter is |
|
|
Either: - - |
|
|
Cannot perform a LOB write inside a query or PDML slave |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
This procedure closes a BFILE that has already been opened through the input locator.
Note:
The database has only read-only access toBFILEs. This means that BFILEs cannot be written through the database.Table 82-33 FILECLOSE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
|
|
|
File was not opened with the input locator. |
|
|
Directory does not exist. |
|
|
You do not have privileges for the directory. |
|
|
Directory has been invalidated after the file was opened. |
|
|
File does not exist, or you do not have access privileges on the file. |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
This procedure closes all BFILEs opened in the session.
Table 82-34 FILECLOSEALL Procedure Exception
| Exception | Description |
|---|---|
|
|
No file has been opened in the session. |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
This function finds out if a specified BFILE locator points to a file that actually exists on the server's file system.
Table 82-37 FILEEXISTS Function Exceptions
| Exception | Description |
|---|---|
|
|
Directory does not exist. |
|
|
You do not have privileges for the directory. |
|
|
Directory has been invalidated after the file was opened. |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
This procedure determines the directory object and filename, given a BFILE locator. This function only indicates the directory object 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 path name, it is 2000.
Table 82-39 FILEGETNAME Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of the input parameters are |
|
|
|
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis function finds out whether a BFILE was opened with the specified 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.
Table 82-41 FILEISOPEN Function Exceptions
| Exception | Description |
|---|---|
|
|
Directory does not exist. |
|
|
You do not have privileges for the directory. |
|
|
Directory has been invalidated after the file was opened. |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
This procedure opens a BFILE for read-only access. BFILE data may not be written through the database.
DBMS_LOB.FILEOPEN ( file_loc IN OUT NOCOPY BFILE, open_mode IN BINARY_INTEGER := file_readonly);
Table 82-43 FILEOPEN Procedure Exceptions
| Exception | Description |
|---|---|
|
|
|
|
|
|
|
|
Number of open files in the session exceeds |
|
|
Directory associated with |
|
|
Directory has been invalidated after the file was opened. |
|
|
File does not exist, or you do not have access privileges on the file. |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
This procedure deletes the data at the specified offset for the specified length from the LOB without having to rewrite all the data in the LOB following the specified offset.
DBMS_LOB.FRAGMENT_DELETE ( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, offset IN INTEGER); DBMS_LOB.FRAGMENT_DELETE ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, offset IN INTEGER);
Table 82-44 FRAGMENT_DELETE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
LOB locator. For more information, see Operational Notes. |
|
|
Number of bytes ( |
|
|
Offset into the LOB in bytes ( |
Table 82-45 FRAGMENT_DELETE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
A parameter value was invalid |
|
|
Cannot perform operation during a query |
|
|
Cannot perform operation with LOB buffering enabled |
|
|
A non- |
|
|
Attempted to perform a |
This procedure inserts the specified data (limited to 32K) into the LOB at the specified offset.
DBMS_LOB.FRAGMENT_INSERT ( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, offset IN INTEGER, buffer IN RAW); DBMS_LOB.FRAGMENT_INSERT ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
Table 82-46 FRAGMENT_INSERT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
LOB locator.For more information, see Operational Notes. |
|
|
Number of bytes (BLOB) or characters (CLOB/NCLOB) to be inserted into the LOB |
|
|
Offset into the LOB in bytes (BLOB) or characters (CLOB/NCLOB) to begin the insertion |
|
|
Data to insert into the LOB |
Table 82-47 FRAGMENT_INSERT Procedure Exceptions
| Exception | Description |
|---|---|
|
|
A parameter value was invalid |
|
|
Cannot perform operation during a query |
|
|
Cannot perform operation with LOB buffering enabled |
|
|
A non- |
|
|
Attempted to perform a |
This procedure moves the amount of bytes (BLOB) or characters (CLOB/NCLOB) from the specified offset to the new offset specified.
DBMS_LOB.FRAGMENT_MOVE ( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, src_offset IN INTEGER, dest_offset IN INTEGER); DBMS_LOB.FRAGMENT_MOVE ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, src_offset IN INTEGER, dest_offset IN INTEGER);
Table 82-48 FRAGMENT_MOVE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
LOB locator. For more information, see Operational Notes. |
|
|
Number of bytes ( |
|
|
Beginning offset into the |
|
|
Beginning offset into the |
Table 82-49 FRAGMENT_MOVE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
A parameter value was invalid |
|
|
Cannot perform operation during a query |
|
|
Cannot perform operation with LOB buffering enabled |
|
|
A non- |
|
|
Attempted to perform a |
This procedure replaces the data at the specified offset with the specified data (not to exceed 32k).
DBMS_LOB.FRAGMENT_REPLACE ( lob_loc IN OUT NOCOPY BLOB, old_amount IN INTEGER, new_amount IN INTEGER, offset IN INTEGER, buffer IN RAW); DBMS_LOB.FRAGMENT_REPLACE ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, old_amount IN INTEGER, new_amount IN INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
Table 82-50 FRAGMENT_REPLACE Function Parameters
| Parameter | Description |
|---|---|
|
|
LOB locator. For more information, see Operational Notes. |
|
|
Number of bytes ( |
|
|
Number of bytes ( |
|
|
Beginning offset into the LOB in bytes (BLOB) or characters ( |
|
|
Data to insert into the |
Table 82-51 FRAGMENT_REPLACE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
A parameter value was invalid |
|
|
Cannot perform operation during a query |
|
|
Cannot perform operation with LOB buffering enabled |
|
|
A non- |
|
|
Attempted to perform a |
This procedure frees the temporary BLOB or CLOB in the default temporary tablespace.
DBMS_LOB.FREETEMPORARY ( lob_loc IN OUT NOCOPY BLOB); DBMS_LOB.FREETEMPORARY ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
Table 82-52 FREETEMPORARY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
When a new temporary LOB is created, and there is currently no temporary LOB in use with the same duration (session, call), a new temporary LOB segment is created. When the temporary LOB is freed, the space it consumed is released to the temporary segment. If there are no other temporary LOBs for the same duration, the temporary segment is also freed.
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.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis function returns the DBFS Link path associated with the specified SecureFile.
DBMS_LOB.GET_DBFS_LINK ( lob_loc IN BLOB, storage_path OUT VARCHAR2(DBFS_LINK_PATH_MAX_SIZE), lob_length OUT NUMBER); DBMS_LOB.GET_DBFS_LINK ( lob_loc IN CLOB CHARACTER SET ANY_CS, storage_path OUT VARCHAR2(DBFS_LINK_PATH_MAX_SIZE), lob_length OUT NUMBER);
This procedure retrieves the current DBFS Link state of the specified SecureFile.
DBMS_LOB.GET_DBFS_LINK_STATE ( lob_loc IN BLOB, storage_path OUT VARCHAR2(DBFS_LINK_PATH_MAX_SIZE), state OUT NUMBER, cached OUT BOOLEAN); DBMS_LOB.GET_DBFS_LINK_STATE ( lob_loc IN CLOB CHARACTER SET ANY_CS, storage_path OUT VARCHAR2(DBFS_LINK_PATH_MAX_SIZE), state OUT NUMBER, cached OUT BOOLEAN);
Table 82-55 GET_DBFS_LINK_STATE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
Path where the LOB is stored in DBFS |
|
|
One of |
|
|
If the |
If the LOB has never been archived, state is set to DBMS_LOB.DBFS_LINK_NEVER. If the LOB has been archived, state is set to DBMS_LOB.DBFS_LINK_YES. If the LOB has been previously retrieved from the archive, state is set to DBFS_LINK_NO.
If the LOB was archived, but the data was left in the RDBMS, cached is set to TRUE. Cached is set to FALSE if the data was removed after the put, and NULL if state is DBMS_LOB.DBFS_LINK_NEVER.
This procedure returns the content type string previously set by means of the SETCONTENTTYPE Procedure.
DBMS_LOB.GETCONTENTTYPE ( lob_loc IN BLOB) RETURN VARCHAR2; DBMS_LOB.GETCONTENTTYPE ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN VARCHAR2;
This function returns the LOB storage limit for the specified LOB.
DBMS_LOB.GET_STORAGE_LIMIT ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; DBMS_LOB.GET_STORAGE_LIMIT ( lob_loc IN BLOB) RETURN INTEGER;
Table 82-59 GET_STORAGE_LIMIT Function Parameters
| Parameter | Description |
|---|---|
|
|
|
The value returned from this function is the maximum allowable size for specified LOB locator. For BLOBs, the return value depends on the block size of the tablespace the LOB resides in and is calculated as (232)-1 (4294967295) times the block size of the tablespace. For CLOBs/NCLOBs, the value returned is the(232)-1 (4294967295) times the block size of the tablespace divided by the character width of the CLOB/NCLOB.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for details on LOB storage limitsWhen creating the table, you can specify the chunking factor, a multiple of tablespace blocks in bytes. 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;
Table 82-60 GETCHUNKSIZE Function Parameters
| Parameter | Description |
|---|---|
|
|
|
With regard to basic LOB files, 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.
These tactics of performance improvement do not apply to SecureFiles.
Note that chunk size is independent of LOB type (BLOB, CLOB, NCLOB, Unicode or other character set).
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis 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 ( file_loc IN BFILE) RETURN INTEGER;
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 operating system privileges
lob_loc cannot be read because of an operating system read error
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis function obtains settings corresponding to the option_type field for a particular LOB.
DBMS_LOB.GETOPTIONS ( lob_loc IN BLOB, option_types IN PLS_INTEGER) RETURN PLS_INTEGER; DBMS_LOB.GETOPTIONS ( lob_loc IN CLOB HARACTER SET ANY_CS, option_types IN PLS_INTEGER) RETURN PLS_INTEGER;
Table 82-64 GETOPTIONS Function Parameter
| Parameter | Description |
|---|---|
|
|
Locator for the |
|
|
The return values are a combination of COMPRESS_ON, ENCRYPT_ON and DEDUPLICATE_ON (see DBMS_LOB Constants - Option Values) depending on which option types (see DBMS_LOB Constants - Option Types) are passed in.
Table 82-65 GETOPTIONS Procedure Exceptions
| Exception | Description |
|---|---|
|
|
A parameter value was invalid |
|
|
Cannot perform operation during a query |
|
|
Cannot perform operation with LOB buffering enabled |
|
|
A non- |
You cannot turn compression or deduplication on or off for a SecureFile column that does not have those features on. The GetOptions Functions and SETOPTIONS Procedures work on individual SecureFiles. You can turn off a feature on a particular SecureFile and turn on a feature that has already been turned off by SetOptions, but you cannot turn on an option that has not been given to the SecureFile when the table was created.
This function returns the matching position of the nth occurrence of the pattern in the LOB, starting from the offset you specify.
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 ( file_loc IN BFILE, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER;
Table 82-66 INSTR Function Parameters
| Parameter | Description |
|---|---|
|
|
Locator for the |
|
|
The file locator for the LOB to be examined. |
|
|
Pattern to be tested for. The pattern is a group of |
|
|
Absolute offset in bytes ( |
|
|
Occurrence number, starting at 1. |
Table 82-67 INSTR Function Return Values
| Return | Description |
|---|---|
|
INTEGER |
Offset of the start of the matched pattern, in bytes or characters. It returns 0 if the pattern is not found. |
|
NULL |
Either: -any one or more of the - - - |
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.
Table 82-68 INSTR Function Exceptions for BFILES
| Exception | Description |
|---|---|
|
|
File was not opened using the input locator. |
|
|
Directory does not exist. |
|
|
You do not have privileges for the directory. |
|
|
Directory has been invalidated after the file was opened. |
|
|
File does not exist, or you do not have access privileges on the file. |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
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;
Table 82-69 ISOPEN Function Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
File locator. |
For BFILES, openness is associated with the locator. If the input locator was never passed to OPEN, 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.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis function determines whether a LOB instance is temporary.
DBMS_LOB.ISTEMPORARY ( lob_loc IN BLOB) RETURN INTEGER; DBMS_LOB.ISTEMPORARY ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER;
Table 82-70 ISTEMPORARY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
The return value is 1 if the LOB is temporary and exists; 0 if the LOB is not temporary or does not exist; NULL if the given locator is NULL.
When you free a Temporary LOB with FREETEMPORARY, the LOB locator is not set to NULL. Consequently, ISTEMPORARY will return 0 for a locator that has been freed but not explicitly reset to NULL.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis procedure loads data from BFILE to internal BLOB. This achieves the same outcome as LOADFROMFILE, and returns the new offsets.
DBMS_LOB.LOADBLOBFROMFILE ( dest_lob IN OUT NOCOPY BLOB, src_bfile IN BFILE, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER);
Table 82-71 LOADBLOBFROMFILE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
|
|
|
Number of bytes to load from the |
|
|
( |
|
|
|
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 in bytes for BLOBs.
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. 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 (unless the amount specified is LOBMAXSIZE which you can specify to continue loading until the end of the BFILE is reached).
It is not mandatory that you wrap the LOB operation inside the OPEN/CLOSE operations. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the OPEN/CLOSE, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.
LOADFROMFILE gets the destination LOB prior to the load unless the load covers the entire LOB.
There is no easy way to omit parameters. You must either declare a variable for IN/OUT parameter or provide a default value for the IN parameter. Here is a summary of the constants and the defaults that can be used.
Table 82-72 Suggested Values of the Parameter
| Parameter | Default Value | Description |
|---|---|---|
|
|
|
Load the entire file |
|
|
|
start from the beginning |
|
|
|
start from the beginning |
Constants defined in DBMSLOB.SQL
lobmaxsize CONSTANT INTEGER := DBMS_LOB.LOBMAXSIZE;
Table 82-73 LOADBLOBFROMFILE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of the input parameters are |
|
|
Either: - - - - |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the BLOB |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis procedure loads data from a BFILE to an internal CLOB/NCLOB with necessary character set conversion and returns the new offsets.
DBMS_LOB.LOADCLOBFROMFILE ( dest_lob IN OUT NOCOPY NOCOPY CLOB CHARACTER SET ANY_CS, src_bfile IN BFILE, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, bfile_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER);
Table 82-74 LOADCLOBFROMFILE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
|
|
|
Number of bytes to load from the |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
(OUT) Warning message. This indicates something abnormal happened during the loading. It may or may not be caused by the user's mistake. The loading is completed as required, and it's up to the user to check the warning message. Currently, the only possible warning is the inconvertible character. This happens when the character in the source cannot be properly converted to a character in destination, and the default replacement character (for example, '?') is used in place. The message is defined the constant value |
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 in characters for CLOBs.
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 CLOB. 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 (unless the amount specified is LOBMAXSIZE which you can specify to continue loading until the end of the BFILE is reached).
Note the following requirements:
The destination character set is always the same as the database character set in the case of CLOB and national character set in the case of NCLOB.
csid=0 indicates the default behavior that uses database csid for CLOB and national csid for NCLOB in the place of source csid. Conversion is still necessary if it is of varying width
It is not mandatory that you wrap the LOB operation inside the OPEN/CLOSE operations. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the OPEN/CLOSE, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.
The source BFILE can contain data in the Unicode character set. The Unicode standard defines many encoding schemes that provide mappings from Unicode characters to sequences of bytes. Table 82-75, "Supported Unicode Encoding Schemes" lists Unicode encodings schemes supported by this subprogram.
Table 82-75 Supported Unicode Encoding Schemes
| Encoding Scheme | Oracle Name | bfile_csid Value |
|---|---|---|
|
|
|
873 |
|
|
|
2000 |
|
|
|
2002 |
|
|
|
871 |
|
|
|
872 |
|
|
|
1000 |
All three UTF-16 encoding schemes encode Unicode characters as 2-byte unsigned integers. Integers can be stored in big-endian or in little-endian byte order. The UTF-16BE encoding scheme defines big-endian data. The UTF-16LE scheme defines little-endian data. The UTF-16 scheme requires that the source BFILE contains the Byte Order Mark (BOM) character in the first two bytes to define the byte order. The BOM code is 0xFEFF. If the code is stored as {0xFE,0xFF}, the data is interpreted as big-endian. If it is stored as {0xFF,0xFE}, the data is interpreted as little-endian.
In UTF-8 and in CESU-8 encodings the Byte Order Mark is stored as {0xEF,0xBB, 0xBF}. With any of the Unicode encodings, the corresponding BOM sequence at the beginning of the file is recognized and not loaded into the destination LOB.
Table 82-76 Suggested Values of the Parameter
| Parameter | Suggested Value | Description |
|---|---|---|
|
|
|
Load the entire file |
|
|
|
start from the beginning |
|
|
|
start from the beginning |
|
|
|
default csid, use destination csid |
|
|
|
default language context |
|
|
|
no warning message, everything is ok |
Constants defined in DBMSLOB.SQL
lobmaxsize CONSTANT INTEGER := 18446744073709551615; warn_inconvertible_char CONSTANT INTEGER := 1; default_csid CONSTANT INTEGER := 0; default_lang_ctx CONSTANT INTEGER := 0; no_warning CONSTANT INTEGER := 0;
Table 82-77 LOADCLOBFROMFILE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of the input parameters are |
|
|
Either: - - - - |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the CLOB |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis procedure copies all, or a part of, a source external LOB (BFILE) to a destination internal LOB.
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);
Table 82-78 LOADFROMFILE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
|
|
|
Number of bytes to load from the |
|
|
Offset in bytes or characters in the destination |
|
|
Offset in bytes in the source |
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.
Note:
The inputBFILE must have been opened prior to using this procedure. No character set conversions are performed implicitly when binary BFILE data is loaded into a CLOB. The BFILE data must already be in the same character set as the CLOB in the database. No error checking is performed to verify this.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.
Note:
If the character set is varying width, UTF-8 for example, theLOB value is stored in the fixed-width UCS2 format. Therefore, if you are using DBMS_LOB.LOADFROMFILE, the data in the BFILE should be in the UCS2 character set instead of the UTF-8 character set. However, you should use sql*loader instead of LOADFROMFILE to load data into a CLOB or NCLOB because sql*loader provides the necessary character set conversions.It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.
Table 82-79 LOADFROMFILE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of the input parameters are |
|
|
Either: - - - - |
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis procedure writes the specified SecureFile data to the DBFS store.
DBMS_LOB.MOVE_TO_DBFS_LINK ( lob_loc IN OUT BLOB, storage_path IN VARCHAR2(dbfs_link_path_max_size), flags IN BINARY INTEGER DEFAULT DBFS_LINK_NOCACHE); DBMS_LOB.MOVE_TO_DBFS_LINK ( lob_loc IN OUT CLOB CHARACTER SET ANY_CS, storage_path IN VARCHAR2(dbfs_link_path_max_size), flags IN BINARY INTEGER DEFAULT DBFS_LINK_NOCACHE);
Table 82-80 MOVE_TO_DBFS_LINK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
storage_path |
Path where the LOB will be be stored |
|
|
Either |
If the LOB is already archived, the procedure silently returns as if the put was successful. In that case, if DBFS_LINK_NOCACHE is specified, or flags is defaulted, the LOB data is removed from the RDBMS.
Calling this procedure multiple times on the same LOB with the same flags has no effect.
Calling the procedure on a LOB that is already archived causes the LOB to be cached (DBFS_LINK_CACHE) or removed (DBFS_LINK_NOCACHE) according to the flag setting.
This procedure opens a LOB, internal or external, in the indicated mode. Valid modes include read-only, and read/write.
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);
Table 82-82 OPEN Procedure Parameters
| Parameter | Description |
|---|---|
|
|
LOB locator. For more information, see Operational Notes. |
|
|
Mode in which to open. For For |
Note:
If theLOB was opened in read-only mode, and if you try to write to the LOB, then an error is returned. BFILE can only be opened with read-only mode.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.
It is not mandatory that you wrap all LOB operations inside the Open/Close interfaces. However, if you open a LOB, you must close it before you commit the transaction; an error is produced if you do not. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
It is an error to commit the transaction before closing all opened LOBs that were opened by the transaction. When the error is returned, the openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed, but the domain and function-based indexes are not updated. If this happens, you should rebuild the functional and domain indexes on the LOB column.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis 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 INTEGER, offset IN INTEGER, buffer OUT RAW); DBMS_LOB.READ ( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN OUT NOCOPY INTEGER, offset IN INTEGER, buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET); DBMS_LOB.READ ( file_loc IN BFILE, amount IN OUT NOCOPY INTEGER, offset IN INTEGER, buffer OUT RAW);
Table 82-83 READ Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Locator for the |
|
|
The file locator for the |
|
|
Number of bytes (for |
|
|
Offset in bytes (for |
|
|
Output buffer for the read operation. |
Table 82-84 lists exceptions that apply to any LOB instance. Table 82-85 lists exceptions that apply only to BFILEs.
Table 82-84 READ Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of |
|
|
Either: - - - - - |
|
|
End of the |
Table 82-85 READ Procedure Exceptions for BFILEs
| Exception | Description |
|---|---|
|
|
File is not opened using the input locator. |
|
|
Directory does not exist. |
|
|
You do not have privileges for the directory. |
|
|
Directory has been invalidated after the file was opened. |
|
|
File does not exist, or you do not have access privileges on the file. |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
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. The database converts the LOB value from the server's character set to the client's character set before it returns the buffer to the user.
READ get s the LOB, if necessary, before the read.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedureThis function links the specified SecureFile to the specified path name. It does not copy the data to the path.
DBMS_LOB.SET_DBFS_LINK ( lob_loc IN OUT BLOB, archive_id IN RAW(1024)); DBMS_LOB.SET_DBFS_LINK( lob_loc_dst IN OUT CLOB CHARACTER SET ANY_CS, archive_id IN RAW(1024));
Table 82-86 SET_DBFS_LINK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
Archive ID as returned by calling either of the GET_DBFS_LINK Functions Functions |
This procedure sets the content type string for the data in the LOB.
DBMS_LOB.SETCONTENTTYPE ( lob_loc IN OUT NOCOPY BLOB, contenttype IN VARCHAR2); DBMS_LOB.SETCONTENTTYPE ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, contenttype IN VARCHAR2);
This procedure enables/disables CSCE features on a per-LOB basis, overriding the default LOB column settings.
DBMS_LOB.SETOPTIONS ( lob_loc IN BLOB, option_types IN PLS_INTEGER, options IN PLS_INTEGER); DBMS_LOB.SETOPTIONS ( lob_loc IN CLOB CHARACTER SET ANY_CS, option_types IN PLS_INTEGER, options IN PLS_INTEGER);
Table 82-90 SETOPTIONS Procedure Parameter
| Parameter | Description |
|---|---|
|
|
Locator for the |
|
|
|
|
|
DBMS_LOB.SETOPTIONS cannot be used to enable or disable encryption on individual LOBs.
You cannot turn compression or deduplication on or off for a SecureFile column that does not have those features on. The GETOPTIONS Functions and SetOptions Procedures work on individual SecureFiles. You can turn off a feature on a particular SecureFile and turn on a feature that has already been turned off by SetOptions, but you cannot turn on an option that has not been given to the SecureFile when the table was created.
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 greater than (32767/n), then SUBSTR returns a character buffer of length (32767/n), or the length of the CLOB, whichever is lesser. For CLOBs in a varying-width character set, n is the maximum byte-width used for characters in the CLOB.
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 ( file_loc IN BFILE, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW;
Table 82-92 SUBSTR Function Parameters
| Parameter | Description |
|---|---|
|
|
Locator for the |
|
|
The file locator for the |
|
|
Number of bytes (for |
|
|
Offset in bytes (for |
Table 82-94 SUBSTR Function Exceptions for BFILE operations
| Exception | Description |
|---|---|
|
|
File is not opened using the input locator. |
|
|
Directory does not exist. |
|
|
You do not have privileges for the directory. |
|
|
Directory has been invalidated after the file was opened. |
|
|
File does not exist, or you do not have access privileges on the file. |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
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. The database converts the LOB value from the server's character set to the client's character set before it returns the buffer to the user.
DBMS_LOB.SUBSTR will return 8191 or more characters based on the characters stored in the LOBs. If all characters are not returned as a consequence of the character byte size exceeding the available buffer, the user should either call DBMS_LOB.SUBSTR with a new offset to read the remaining characters, or call the subprogram on loop until all the data is extracted.
SUBSTR get s the LOB, if necessary, before read.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
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.
Note:
TheTRIM procedure decreases the length of the LOB to the value specified in the newlen parameter.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);
Table 82-95 TRIM Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Locator for the internal |
|
|
New, trimmed length of the |
Table 82-96 TRIM Procedure Exceptions
| Exception | Description |
|---|---|
|
|
|
|
|
Either: - - |
|
|
Cannot perform a LOB write inside a query or PDML slave |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.
TRIM gets the LOB, if necessary, before altering the length of the LOB, unless the new length specified is '0'
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
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.
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 lob_loc%CHARSET);
Table 82-97 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Locator for the internal |
|
|
Number of bytes (for |
|
|
Offset in bytes (for |
|
|
Input buffer for the write |
Table 82-98 WRITE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of |
|
|
Either: - - - - |
|
|
Cannot perform a LOB write inside a query or PDML slave |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
|
|
Attempted to perform a write operation past the end of a LOB having |
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.
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. The database converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.
It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.
WRITE gets the LOB, if necessary, before writing the LOB, unless the write is specified to overwrite the entire LOB.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
This procedure writes a specified amount of data to the end of an internal LOB. The data is written from the buffer parameter.
DBMS_LOB.WRITEAPPEND ( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, buffer IN RAW); DBMS_LOB.WRITEAPPEND ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
Table 82-99 WRITEAPPEND Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Locator for the internal |
|
|
Number of bytes (for |
|
|
Input buffer for the write |
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.
Table 82-100 WRITEAPPEND Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Any of |
|
|
Either: - - |
|
|
Cannot perform a LOB write inside a query or PDML slave |
|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
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. The database converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.
It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.
WRITEAPPEND gets the LOB, if necessary, before appending to the LOB.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure