100 DBMS_LOB
The DBMS_LOB
package provides subprograms to operate on BLOBs
, CLOBs
, NCLOBs
, BFILEs
, and temporary LOBs
. You can use DBMS_LOB
to access and manipulate specific parts of a LOB or complete LOBs.
This chapter contains the following topics:
100.1 DBMS_LOB Overview
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.
100.2 DBMS_LOB Deprecated Subprograms
The DBMS_LOB.LOADFROMFILE program is deprecated from the DBMS_LOB
package in Oracle Database 12c release 12.2
Note:
Oracle recommends that do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.
Use DBMS_LOB.LoadClobFromFile or DBMS_LOB.LoadBlobFromFile instead.
100.3 DBMS_LOB Security Model
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 on AUTHID
and privileges, see Oracle Database PL/SQL Language Reference
You can provide secure access to BFILEs
using the DIRECTORY
feature discussed in BFILENAME
function in theOracle 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.
100.4 DBMS_LOB Constants
This topic describes the constants used by the DBMS_LOB package
These are shown in following tables:
Table 100-1 DBMS_LOB Constants - Basic
Constant | Type | Value | Description |
---|---|---|---|
|
|
|
Create the |
|
|
|
Open the specified |
|
|
|
Open the specified LOB read-only |
|
|
|
Open the specified LOB read-write |
|
|
18446744073709551615 |
Maximum size of a LOB in bytes |
|
|
|
Create the |
Table 100-2 DBMS_LOB Constants - Option Types
Constant | Definition | Value | Description |
---|---|---|---|
|
|
|
Set/Get the |
|
|
|
Set/Get the |
|
|
|
Get the |
Table 100-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 100-4 DBMS_LOB Constants - DBFS State Value Types
Constant | Definition | Value | Description |
---|---|---|---|
|
|
|
LOB has never been archived |
|
|
|
LOB was archived, but as been read back in to the RDBMS |
|
|
|
LOB is currently archived |
Table 100-5 DBMS_LOB Constants - DBFS Cache Flags
Constant | Definition | Value | Description |
---|---|---|---|
|
|
1 |
Put the LOB data to the archive, but keep the data in the RDBMS as a cached version |
|
|
|
Put the LOB data to the archive, and remove the data from the RDBMS. |
Table 100-6 DBMS_LOB Constants - Miscellaneous
Constant | Definition | Value | Description |
---|---|---|---|
|
|
|
Maximum number of bytes allowed in the content type string |
|
|
|
The maximum length of DBFS pathnames |
100.5 DBMS_LOB Datatypes
The table in this topic describes the datatypes used by DBMS_LOB.
Table 100-7 Datatypes Used by DBMS_LOB
Type | Description |
---|---|
|
Source or destination binary LOB. |
|
Source or destination |
|
Source or destination character LOB (including |
|
Source or destination character buffer (used with |
|
Specifies the size of a buffer or LOB, the offset into a LOB, or the amount to access. |
|
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 CLOB
s enables the CLOB
type to accept a CLOB
or NCLOB
locator variable as input.
100.6 DBMS_LOB Operational Notes
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
Starting from 12.2 release, you can select a persistent LOB locator from a remote table into a local variable. The remote column can be of type BLOB, CLOB, or NCLOB. You cannot select BFILE
from a remote table. The LOB variable that refers to the LOB value in a remote table is called a remote locator.
All the DBMS_LOB
APIs other than the ones that are meant for BFILEs will now accept and support operations on remote LOB locators. All the APIs that take in two locators must have both LOBs collocated at one database.
See Also:
Distributed LOBs chapter in 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.
-
Internal LOBs
-
External LOBs
-
Temporary LOBs
Internal LOBs
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.
External LOBs
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 theBFILENAME
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, See Oracle Database SecureFiles and Large Objects Developer's Guide
Temporary LOBs
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 roll backs 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
returnNULL
if any of the input parameters areNULL
. All procedures inDBMS_LOB
raise an exception if the LOB locator is input asNULL
. -
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.
See Also:
Oracle Database PL/SQL Language Reference for more information on NOCOPY
syntax
100.7 DBMS_LOB Rules and Limits
This topic describes general DBMS_LOB rules and limits, rules and limits specific to external files (BFILEs), and maximum LOB and buffer sizes.
General Rules and Limits
-
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:
-
newlen
,offset
, andamount
parameters for subprograms operating onBLOBs
andBFILEs
must be specified in terms of bytes. -
newlen
,offset
, andamount
parameters for subprograms operating onCLOBs
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 valuelobmaxsize
18446744073709551615 (264) in anyDBMS_LOB
subprogram. -
For
CLOB
s 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
andVARCHAR2
parameters used inDBMS_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 forDBMS_LOB
subprograms forCLOBs
andNCLOBs
. -
The
%CHARSET
clause indicates that the form of the parameter with%CHARSET
must match the form of theANY_CS
parameter to which it refers.For example, in
DBMS_LOB
subprograms that take aVARCHAR2
buffer parameter, the form of theVARCHAR2
buffer must match the form of theCLOB
parameter. If the input LOB parameter is of typeNCLOB
, then the buffer must containNCHAR
data. Conversely, if the input LOB parameter is of typeCLOB
, then the buffer must containCHAR
data.For
DBMS_LOB
subprograms that take twoCLOB
parameters, bothCLOB
parameters must have the same form; that is, they must both beNCLOBs
, or they must both beCLOBs
. -
If the value of
amount
plus theoffset
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
, andSUBSTR
, read untilEnd
of
Lob/File
is reached. For example, for aREAD
operation on aBLOB
orBFILE
, if the user specifies offset value of 3 GB and an amount value of 2 GB on a LOB that is 4GB in size, thenREAD
returns only 1GB (4GB-3GB) bytes. -
Functions with
NULL
or invalid input values for parameters return aNULL
. Procedures withNULL
values for destination LOB parameters raise exceptions. -
Operations involving patterns as parameters, such as
COMPARE
,INSTR
, andSUBSTR
do not support regular expressions or special matching characters (such as%
in theLIKE
operator in SQL) in thepattern
parameter or substrings. -
The
End
Of
LOB condition is indicated by theREAD
procedure using aNO_DATA_FOUND
exception. This exception is raised only upon an attempt by the user to read beyond the end of the LOB. TheREAD
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 theBLOB
orBFILE
data, and the first character in theCLOB
orNCLOB
value. No default values are specified for theamount
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
, orWRITE
. These subprograms do not implicitly lock the row containing the LOB.
Rules and Limits Specific to External Files (BFILEs)
-
The subprograms
COMPARE
,INSTR
,READ
,SUBSTR
,FILECLOSE
,FILECLOSEALL
andLOADFROMFILE
operate only on an openedBFILE
locator; that is, a successfulFILEOPEN
call must precede a call to any of these subprograms. -
For the functions
FILEEXISTS
,FILEGETNAME
andGETLENGTH
, a file's open/close status is unimportant; however, the file must exist physically, and you must have adequate privileges on theDIRECTORY
object and the file. -
DBMS_LOB
does not support any concurrency control mechanism forBFILE
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 theCREATE
OR
REPLACE
,DROP
, andREVOKE
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 theSESSION_MAX_OPEN_FILES
value.For example, consider a
READ
operation past the end of theBFILE
value, which generates aNO_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.
Maximum LOB Size
The maximum size for LOBs supported by the database is equal to the value of the blocksize of the tablespace the LOB column resides in times the value 232-1 (4294967295). This allows for a maximum LOB size ranging from 8 terabytes to 128 terabytes.
Maximum Buffer Size
The maximum buffer size, 32767 bytes.
For BLOBs
, where buffer size is expressed in bytes, the number of bytes cannot exceed 32767.
For CLOBs
or NCLOBs
, where buffer size is expressed in characters, the number of characters cannot result in a buffer larger than 32767 bytes. For example, if you are using fixed-width, two-byte characters, then specifying 20000 characters is an error (20000*2 = 40000, which is greater than 32767).
100.8 DBMS_LOB Exceptions
The table in the topic describes the exceptions for DBMS_LOB.
Table 100-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 parallel execution server |
|
|
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. |
100.9 Summary of DBMS_LOB Subprograms
This table lists the DBMS_LOB
subprograms and briefly describes them.
Table 100-9 DBMS_LOB Package Subprograms
Subprogram | Description |
---|---|
Appends the contents of the source LOB to the destination LOB |
|
Writes the content of a |
|
Closes a previously opened internal or external LOB |
|
Compares two entire |
|
Reads character data from a source |
|
Takes a source |
|
Copies all, or part, of the source LOB to the destination LOB |
|
Copies the DBFS Link in the source LOB to the destination LOB |
|
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 LOB |
|
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 LOB |
|
Inserts the specified data (limited to 32K) into the LOB at the specified offset |
|
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 LOB chunk to store the LOB value |
|
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 LOB |
|
Checks to see if the LOB was already opened using the input locator |
|
Checks to see if the LOB is local to the database or if it belongs to a remote database. |
|
Returns |
|
Checks if the locator is pointing to a temporary LOB |
|
Loads |
|
Loads |
|
Loads This procedure has been deprecated starting with Oracle Database 12c release 12.2. |
|
Writes the specified SecureFile data to the DBFS store |
|
Opens a LOB (internal, external, or temporary) in the indicated mode |
|
Reads data from the LOB starting at the specified offset |
|
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 LOB value starting at the specified offset |
|
Trims the LOB value to the specified shorter length |
|
Writes data to the LOB from a specified offset |
|
Writes a buffer to the end of a LOB |
100.9.1 APPEND Procedures
This procedure appends the contents of a source internal LOB to a destination LOB. It appends the complete source LOB.
Syntax
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);
Parameters
Table 100-10 APPEND Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the internal LOB to which the data is to be appended. |
|
Locator for the internal LOB from which the data is to be read. |
Exceptions
Table 100-11 APPEND Procedure Exceptions
Exception | Description |
---|---|
|
Either the source or the destination LOB is |
|
Cannot perform a LOB write inside a query or PDML parallel execution server |
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on either LOB |
Usage Notes
-
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
orCLOSE
statement. -
If
APPEND
is called on a LOB that has been archived, it implicitly gets the LOB before the first byte is written -
If
APPEND
is called on a SecureFiles LOB that is a DBFS Link, an exception is thrown.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
100.9.2 CLOB2FILE Procedure
This procedure writes the content of a CLOB
into a bfile
. This procedure gets called from the deprecated dbms_xslprocessor.clob2file
internally.
Syntax
DBMS_LOB.CLOB2FILE( src_cl IN CLOB, file_loc IN VARCHAR2, file_name IN VARCHAR2, csid IN NUMBER := 0, open_mode IN VARCHAR2 :='wb');
Parameters
Table 100-12 CLOB2FILE Procedure Parameters
Parameter | Description |
---|---|
|
Source CLOB locator to write into a file |
|
Directory object name where the file is located |
|
File name |
|
Character set id of the CLOB locator
|
|
The mode to open the output file in.
wb. |
100.9.3 CLOSE Procedure
This procedure closes a previously opened internal or external LOB.
Syntax
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);
Parameters
Table 100-13 CLOSE Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
Exceptions
No error is returned if the BFILE
exists but is not opened. An error is returned if the LOB is not open.
Usage Notes
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 procedure
100.9.4 COMPARE Functions
This function compares two entire LOBs or parts of two LOBs.
Syntax
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;
Pragmas
pragma restrict_references(COMPARE, WNDS, WNPS, RNDS, RNPS);
Parameters
Table 100-14 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 LOB (origin: 1) for the comparison. |
|
Offset in bytes or characters on the second LOB (origin: 1) for the comparison. |
Return Values
-
INTEGER
: 0 if the comparison succeeds, nonzero if not. -
NULL
, if any ofamount,
offset_1
oroffset_2
is not a valid LOB offset value. A valid offset is within the range of 1 toLOBMAXSIZE
inclusive.
Usage Notes
-
You can only compare LOBs of the same datatype (
LOBs
ofBLOB
type with otherBLOBs
, andCLOBs
withCLOBs
, andBFILEs
withBFILEs
). ForBFILEs
, the file must be already opened using a successfulFILEOPEN
operation for this operation to succeed. -
COMPARE
returns 0 if the data exactly matches over the range specified by theoffset
andamount
parameters.COMPARE
returns -1 if the firstCLOB
is less than the second, and 1 if it is greater. -
For fixed-width n-byte
CLOBs
, if the input amount forCOMPARE
is specified to be greater than (DBMS_LOB
.LOBMAXSIZE
/n), thenCOMPARE
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. -
If
COMPARE()
is called on a SecureFiles LOB that is a DBFS Link, the linked LOB is streamed from DBFS, if possible, otherwise an exception is thrown.
Exceptions
Table 100-15 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 procedure
100.9.5 CONVERTTOBLOB Procedure
This 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.
Syntax
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);
Parameters
Table 100-16 CONVERTTOBLOB Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator of the destination LOB instance. |
|
LOB locator of the source LOB instance. |
|
Number of characters to convert from the source LOB. If you want to copy the entire LOB, pass the constant |
|
|
|
|
|
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 |
Usage Notes
Preconditions
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 theSELECT
statement.
Constants and Defaults
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 100-17 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 100-17 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 |
General Notes
-
You must specify the desired character set for the destination LOB in the
blob_csid
parameter. You can pass a zero value forblob_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
andsrc_offset
values are in characters and thedest_offset
is in bytes. To convert the entire LOB, you can specifyLOBMAXSIZE
for theamount
parameter. -
CONVERTTOBLOB
gets the source and/or destination LOBs as necessary prior to conversion and write of the data.
Exceptions
Table 100-18 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 100-18 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 development
100.9.6 CONVERTTOCLOB Procedure
This 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.
Syntax
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);
Parameters
Table 100-19 CONVERTTOCLOB Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator of the destination LOB instance. |
|
LOB locator of the source LOB instance. |
|
Number of bytes to convert from the source LOB. 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 |
Usage Notes
Preconditions
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 theFOR UPDATE
clause of theSELECT
statement.
Constants and Defaults
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 100-20 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 100-20 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 |
General Notes
-
You must specify the desired character set for the source LOB in the
blob_csid
parameter. You can pass a zero value forblob_csid
. When you do so, the database assumes that the desired character set is the same as the destianation 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
andsrc_offset
values are in bytes and thedest_offset
is in characters. To convert the entire LOB, you can specifyLOBMAXSIZE
for theamount
parameter. -
CONVERTTOCLOB
gets the source and/or destination LOBs as necessary prior to conversion and write of the data.
Exceptions
Table 100-21 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 development
100.9.7 COPY Procedures
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.
Syntax
DBMS_LOB.COPY ( dest_lob IN OUT NOCOPY BLOB, src_lob IN BLOB, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); DBMS_LOB.COPY ( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET dest_lob%CHARSET, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1);
Parameters
Table 100-22 COPY Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator of the copy target. |
|
LOB locator of source for the copy. |
|
Number of bytes (for |
|
Offset in bytes or characters in the destination LOB (origin: 1) for the start of the copy. |
|
Offset in bytes or characters in the source LOB (origin: 1) for the start of the copy. |
Exceptions
Table 100-23 COPY Procedure Exceptions
Exception | Description |
---|---|
|
Any of the input parameters are |
|
Either: - - - - |
|
Cannot perform a LOB write inside a query or PDML parallel execution server |
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on either LOB |
Usage Notes
-
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
orCLOB
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
orCLOSE
statement. -
Prior to copy, the source and destination LOBs are retrieved, if they are currently archived. For a complete over-write, the destination LOB is not retrieved.
-
If the source LOB is a DBFS Link, the data is streamed from DBFS, if possible, otherwise an exception is thrown. If the destination LOB is a DBFS Link, an exception is thrown.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
100.9.8 COPY_DBFS_LINK Procedures
This procedure copies the DBFS Link in the source LOB to the destination LOB.
Syntax
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);
Parameters
Table 100-24 COPY_DBFS_LINK Procedure Parameters
Parameter | Description |
---|---|
|
LOB to be made to reference the same storage data as |
|
LOB from which to copy the reference |
|
Options to
|
Exceptions
Table 100-25 COPY_DBFS_LINK Procedure Exceptions
Exception | Description |
---|---|
|
Either |
|
|
|
If the source LOB has been retrieved, never archived, or if the LOB has been migrated in and out (modified or not) since the locator was gotten. |
100.9.9 COPY_FROM_DBFS_LINK
This procedure retrieves the archived SecureFiles LOB data from the DBFS HSM store and to the database.
Syntax
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);
Parameters
Table 100-26 COPY_FROM_DBFS_LINK Procedure Parameters
Parameter | Description |
---|---|
|
LOB to be retrieved from the archive |
Usage Note
COPY_FROM_DBFS_LINK
does not remove the underlying DBFS file.
If the LOB is successfully retrieved, COPY_FROM_DBFS_LINK
silently returns success.
Exceptions
Table 100-27 COPY_FROM_DBFS_LINK Procedure Exceptions
Exception | Description |
---|---|
|
|
|
If the LOB has already been retrieved and has been modified since retrieval, if the LOB has been migrated in and out (modified or not) since the locator was retrieved |
100.9.10 CREATETEMPORARY Procedures
This procedure creates a temporary BLOB
or CLOB
and its corresponding index in your default temporary tablespace.
Syntax
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);
Parameters
Table 100-28 CREATETEMPORARY Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
|
Specifies if LOB should be read into buffer cache or not. |
|
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
100.9.11 DBFS_LINK_GENERATE_PATH Functions
This subprogram returns a unique file path name for use in creating a DBFS Link.
Syntax
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;
Pragmas
PRAGMA RESTRICT_REFERENCES(dbfs_link_generate_path, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 100-29 DBFS_LINK_GENERATE_PATH Function Parameters
Parameter | Description |
---|---|
|
LOB to be retrieved from DBFS |
|
DBFS directory that will be the parent directory of the file |
Exceptions
Table 100-30 DBFS_LINK_GENERATE_PATH Function Exceptions
Exception | Description |
---|---|
|
|
Usage Notes
Returns a globally unique file pathname that can be used for archiving. This is guaranteed to be globally unique across all calls to this function for different LOBs and versions of that LOB. It is always the same for the same LOB and version.
100.9.12 ERASE Procedures
This procedure erases an entire internal LOB or part of an internal LOB.
Syntax
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);
Parameters
Table 100-31 ERASE Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB to be erased.For more information, see Operational Notes. |
|
Number of bytes (for |
|
Absolute offset (origin: 1) from the beginning of the LOB in bytes (for |
Usage Notes
-
When data is erased from the middle of a LOB, zero-byte fillers or spaces are written for
BLOBs
orCLOBs
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 theamount
parameter. -
ERASE
gets the LOB if it is archived, unless the erase covers the entire LOB. -
If the LOB to be erased is a DBFS Link, an exception is thrown.
Note:
The length of the LOB is not decreased when a section of the LOB is erased. To decrease the length of the LOB value, see the "TRIM Procedures".
Exceptions
Table 100-32 ERASE Procedure Exceptions
Exception | Description |
---|---|
|
Any input parameter is |
|
Either: - - |
|
Cannot perform a LOB write inside a query or PDML parallel execution server |
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
Usage Notes
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
100.9.13 FILECLOSE Procedure
This procedure closes a BFILE
that has already been opened through the input locator.
Note:
The database has only read-only access to BFILEs
. This means that BFILEs
cannot be written through the database.
Syntax
DBMS_LOB.FILECLOSE ( file_loc IN OUT NOCOPY BFILE);
Parameters
Table 100-33 FILECLOSE Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the |
Exceptions
Table 100-34 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
100.9.14 FILECLOSEALL Procedure
This procedure closes all BFILEs
opened in the session.
Syntax
DBMS_LOB.FILECLOSEALL;
Exceptions
Table 100-35 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
100.9.15 FILEEXISTS Function
This function finds out if a specified BFILE
locator points to a file that actually exists on the server's file system.
Syntax
DBMS_LOB.FILEEXISTS ( file_loc IN BFILE) RETURN INTEGER;
Pragmas
pragma restrict_references(FILEEXISTS, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 100-36 FILEEXISTS Function Parameter
Parameter | Description |
---|---|
|
Locator for the |
Return Values
Table 100-37 FILEEXISTS Function Return Values
Return | Description |
---|---|
|
Physical file does not exist. |
|
Physical file exists. |
Exceptions
Table 100-38 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
100.9.16 FILEGETNAME 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.
Syntax
DBMS_LOB.FILEGETNAME ( file_loc IN BFILE, dir_alias OUT VARCHAR2, filename OUT VARCHAR2);
Parameters
Table 100-39 FILEGETNAME Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the |
|
Directory object name |
|
Name of the |
Exceptions
Table 100-40 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 procedure
100.9.17 FILEISOPEN Function
This function finds out whether a BFILE
was opened with the specified FILE
locator.
Syntax
DBMS_LOB.FILEISOPEN ( file_loc IN BFILE) RETURN INTEGER;
Pragmas
PRAGMA RESTRICT_REFERENCES(fileisopen, WNDS, RNDS, WNPS, RNPS
);
Parameters
Table 100-41 FILEISOPEN Function Parameter
Parameter | Description |
---|---|
|
Locator for the |
Return Values
INTEGER
: 0 = file is not open, 1 = file is open
Usage Notes
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.
Exceptions
Table 100-42 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
100.9.18 FILEOPEN Procedure
This procedure opens a BFILE
for read-only access. BFILE
data may not be written through the database.
Syntax
DBMS_LOB.FILEOPEN ( file_loc IN OUT NOCOPY BFILE, open_mode IN BINARY_INTEGER := file_readonly);
Parameters
Table 100-43 FILEOPEN Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the |
|
File access is read-only. |
Exceptions
Table 100-44 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
100.9.19 FRAGMENT_DELETE 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.
Syntax
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);
Parameters
Table 100-45 FRAGMENT_DELETE Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
|
Number of bytes ( |
|
Offset into the LOB in bytes ( |
Exceptions
Table 100-46 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 |
100.9.20 FRAGMENT_INSERT Procedures
This procedure inserts the specified data (limited to 32K) into the LOB at the specified offset.
Syntax
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);
Parameters
Table 100-47 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 |
Exceptions
Table 100-48 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 |
Usage Notes
FRAGMENT_INSERT
gets the LOB, if necessary, before performing operations on the LOB.
100.9.21 FRAGMENT_MOVE Procedure
This procedure moves the amount of bytes (BLOB) or characters (CLOB/NCLOB) from the specified offset to the new offset specified.
Syntax
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);
Parameters
Table 100-49 FRAGMENT_MOVE Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
|
Number of bytes ( |
|
Beginning offset into the LOB in bytes ( |
|
Beginning offset into the LOB in bytes ( |
Exceptions
Table 100-50 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 |
Usage Notes
-
All offsets are pre-move offsets.
-
Offsets of more than 1 past the end of the LOB are not permitted.
-
FRAGMENT_MOVE
gets the LOB, if necessary, before performing operations on the LOB.
100.9.22 FRAGMENT_REPLACE Procedures
This procedure replaces the data at the specified offset with the specified data (not to exceed 32k).
Syntax
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);
Parameters
Table 100-51 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 LOB |
Exceptions
Table 100-52 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 |
Usage Notes
-
Invoking this procedure is equivalent to deleting the old amount of bytes/characters at offset and then inserting the new amount of bytes/characters at offset.
-
FRAGMENT_REPLACE
gets the LOB, if necessary, before performing operations on the LOB.
100.9.23 FREETEMPORARY Procedures
This procedure frees the temporary BLOB
or CLOB
in the default temporary tablespace.
Syntax
DBMS_LOB.FREETEMPORARY ( lob_loc IN OUT NOCOPY BLOB); DBMS_LOB.FREETEMPORARY ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
Parameters
Table 100-53 FREETEMPORARY Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator.For more information, see Operational Notes. |
Usage Notes
-
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 procedure
100.9.24 GET_DBFS_LINK Functions
This function returns the DBFS path name for the specified SecureFile LOB.
Syntax
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);
Parameters
Table 100-54 GET_DBFS_LINK Function Parameters
Parameter | Description |
---|---|
|
LOB to be retrieved from DBFS |
|
Path where the LOB is stored in DBFS |
|
LOB length at the time of write to DBFS |
Return Values
The Archive ID
Exceptions
Table 100-55 GET_DBFS_LINK Function Exceptions
Exception | Description |
---|---|
|
|
|
The LOB has already been retrieved and has been modified since retrieval or the LOB has been migrated in and out (modified or not) since the locator was retrieved |
100.9.25 GET_DBFS_LINK_STATE Procedures
GET_DBFS_LINK_STATE
retrieves the current link state of the specified SecureFile.
Syntax
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);
Parameters
Table 100-56 GET_DBFS_LINK_STATE Procedure Parameters
Parameter | Description |
---|---|
|
LOB to be retrieved from the archive |
|
Path where the LOB is stored in the DBFS HSM store |
|
One of |
|
If the LOB is archived and the data was specified to be cashed on put |
Exceptions
Table 100-57 GET_DBFS_LINK_STATE Procedure Exceptions
Exception | Description |
---|---|
|
|
Usage Notes
-
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 toDBMS_LOB
.DBFS_LINK_YES
. If the LOB has been previously retrieved from the archive, state is set toDBFS_LINK_NO
. -
If the LOB was archived, but the data was left in the RDBMS,
cached
is set toTRUE
. If the data was removed after the link was created,cached
is set toFALSE
, andNULL
if state isDBMS_LOB
.DBFS_LINK_NEVER
.
100.9.26 GETCONTENTTYPE Functions
This procedure returns the content type string previously set by means of the SETCONTENTTYPE Procedure.
Syntax
DBMS_LOB.GETCONTENTTYPE ( lob_loc IN BLOB) RETURN VARCHAR2; DBMS_LOB.GETCONTENTTYPE ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN VARCHAR2;
Pragmas
PRAGMA RESTRICT_REFERENCES(getcontenttype, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 100-58 GETCONTENTTYPE Function Parameters
Parameter | Description |
---|---|
|
LOB whose content type is to be retrieved |
Return Values
The returned content type.
If the SecureFiles LOB does not have a contenttype
associated with it, GETCONTENTTYPE()
returns NULL
.
Exceptions
Table 100-59 GETCONTENTTYPE Function Exceptions
Exception | Description |
---|---|
|
|
Related Topics
100.9.27 GET_STORAGE_LIMIT Function
This function returns the LOB storage limit for the specified LOB.
Syntax
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;
Pragmas
PRAGMA RESTRICT_REFERENCES(get_storage_limit, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 100-60 GET_STORAGE_LIMIT Function Parameters
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
Return Value
The value returned from this function is the maximum allowable size for specified LOB locator. For BLOB
s, 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 CLOB
s/NCLOB
s, the value returned is the(232)-1 (4294967295) times the block size of the tablespace divided by the character width of the CLOB
/NCLOB
.
Usage
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for details on LOB storage limits
100.9.28 GETCHUNKSIZE Functions
When 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.
Syntax
DBMS_LOB.GETCHUNKSIZE ( lob_loc IN BLOB) RETURN INTEGER; DBMS_LOB.GETCHUNKSIZE ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER;
Pragmas
PRAGMA RESTRICT_REFERENCES(getchunksize, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 100-61 GETCHUNKSIZE Function Parameters
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
Return Values
The return value is a usable chunk size in bytes.
Usage Notes
-
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 severalWRITE
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 procedure
Exceptions
Table 100-62 GETCHUNKSIZE Procedure Exceptions
Exception | Description |
---|---|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
100.9.29 GETLENGTH Functions
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.
Syntax
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;
Pragmas
pragma restrict_references(GETLENGTH, WNDS, WNPS, RNDS, RNPS);
Parameters
Table 100-63 GETLENGTH Function Parameter
Parameter | Description |
---|---|
|
The file locator for the LOB whose length is to be returned. |
Return Values
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 errorSee Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
Exceptions
Table 100-64 GETLENGHTH Procedure Exceptions
Exception | Description |
---|---|
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
100.9.30 GETOPTIONS Functions
This function obtains compression, deduplication, and encryption settings corresponding to the option_type
field for a particular LOB.
Syntax
DBMS_LOB.GETOPTIONS ( lob_loc IN BLOB, option_types IN PLS_INTEGER) RETURN PLS_INTEGER; DBMS_LOB.GETOPTIONS ( lob_loc IN CLOB CHARACTER SET ANY_CS, option_types IN PLS_INTEGER) RETURN PLS_INTEGER;
Parameters
Table 100-65 GETOPTIONS Function Parameter
Parameter | Description |
---|---|
|
Locator for the LOB to be examined. For more information, see Operational Notes. |
|
See Table 100-2 |
Return Values
The return values are a combination of COMPRESS_ON
, ENCRYPT_ON
and DEDUPLICATE_ON
(see Table 100-3) depending on which option types (see Table 100-2) are passed in.
Exceptions
Table 100-66 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- |
Usage Notes
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.
100.9.31 INSTR Functions
This function returns the matching position of the nth occurrence of the pattern in the LOB, starting from the offset you specify.
Syntax
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;
Pragmas
pragma restrict_references(INSTR, WNDS, WNPS, RNDS, RNPS);
Parameters
Table 100-67 INSTR Function Parameters
Parameter | Description |
---|---|
|
Locator for the LOB to be examined. For more information, see Operational Notes. |
|
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. |
Return Values
Table 100-68 INSTR Function Return Values
Return | Description |
---|---|
|
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 - - - |
Usage Notes
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.
Exceptions
Table 100-69 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
100.9.32 ISOPEN Functions
This function checks to see if the LOB was already opened using the input locator. This subprogram is for internal and external LOBs.
Syntax
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;
Pragmas
PRAGMA RESTRICT_REFERENCES(isopen, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 100-70 ISOPEN Function Parameters
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
|
File locator. |
Return Values
The return value is 1 if the LOB is open, 0 otherwise.
Usage Notes
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 procedure
100.9.33 ISREMOTE Function
This function checks to see if the LOB is local to the database or if it belongs to a remote database.
Syntax
DBMS_LOB.ISREMOTE ( lob_loc IN BLOB) RETURN BOOLEAN;
DBMS_LOB.ISREMOTE ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN BOOLEAN;
Pragmas
PRAGMA RESTRICT_REFERENCES(isremote, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 100-71 ISREMOTE Function Parameter
Parameter | Description |
---|---|
|
Locator for the |
Return Values
BOOLEAN
: TRUE
for remote LOBs obtained over a database link; FALSE
for LOBs obtained from local database
See Also:
-
Distributed LOBs chapter in Database SecureFiles and Large Objects Developer's Guide for more details on the usage of this procedure.
100.9.34 ISSECUREFILE Function
This function returns TRUE
if the LOB locator passed to it is for a SecureFile LOB. It returns FALSE
otherwise.
Syntax
DBMS_LOB ISSECUREFILE( lob_loc IN BLOB) RETURN BOOLEAN;
Pragmas
PRAGMA RESTRICT_REFERENCES(issecurefile, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 100-72 ISSECUREFILE Function Parameter
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
Return Values
This function returns TRUE
if the LOB locator passed to it is for a SecureFile LOB. It returns FALSE
otherwise.
100.9.35 ISTEMPORARY Functions
This function determines whether a LOB instance is temporary.
Syntax
DBMS_LOB.ISTEMPORARY ( lob_loc IN BLOB) RETURN INTEGER; DBMS_LOB.ISTEMPORARY ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER;
Pragmas
PRAGMA RESTRICT_REFERENCES(istemporary, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 100-73 ISTEMPORARY Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
Return Values
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
.
Usage Notes
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 procedure
100.9.36 LOADBLOBFROMFILE Procedure
This procedure loads data from BFILE
to internal BLOB
. This achieves the same outcome as LOADFROMFILE
, and returns the new offsets.
Syntax
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);
Parameters
Table 100-74 LOADBLOBFROMFILE Procedure Parameters
Parameter | Description |
---|---|
|
|
|
|
|
Number of bytes to load from the |
|
( |
|
|
Usage Notes
-
You can specify the offsets for both the source and destination LOBs, and the number of bytes to copy from the source
BFILE
. Theamount
andsrc_offset
, because they refer to theBFILE
, are in terms of bytes, and thedest_offset
is in bytes forBLOBs
. -
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 isLOBMAXSIZE
which you can specify to continue loading until the end of theBFILE
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 theOPEN
orCLOSE
statement. -
LOADFROMFILE gets the destination LOB prior to the load unless the load covers the entire LOB.
Constants and Defaults
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 100-75 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;
Exceptions
Table 100-76 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 procedure
100.9.37 LOADCLOBFROMFILE Procedure
This procedure loads data from a BFILE
to an internal CLOB/NCLOB
with necessary character set conversion and returns the new offsets.
Syntax
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);
Parameters
Table 100-77 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 |
Usage Notes
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 ofNCLOB
. -
csid=0
indicates the default behavior that uses databasecsid
forCLOB
and nationalcsid
forNCLOB
in the place of sourcecsid
. 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 theOPEN
orCLOSE
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 100-78 lists Unicode encodings schemes supported by this subprogram.
Table 100-78 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.
Constants
Here is a summary of the constants and the suggested values that can be used.
Table 100-79 Suggested Values of the LOADCLOBFROMFILE 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;
Exceptions
Table 100-80 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 procedure
100.9.38 LOADFROMFILE Procedure
This deprecated procedure copies all, or a part of, a source external LOB (BFILE
) to a destination internal LOB.
Note:
This procedure has been deprecated starting in Oracle Database 12c release 12.2.
Syntax
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);
Parameters
Table 100-81 LOADFROMFILE Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator of the target for the load. |
|
|
|
Number of bytes to load from the |
|
Offset in bytes or characters in the destination LOB (origin: 1) for the start of the load. |
|
Offset in bytes in the source |
Usage Notes
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 input BFILE
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, the LOB 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.
Exceptions
Table 100-82 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 procedure
100.9.39 MOVE_TO_DBFS_LINK Procedures
This procedure archives the specified LOB data (from the database) into the DBFS HSM Store.
Syntax
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);
Parameters
Table 100-83 MOVE_TO_DBFS_LINK Procedure Parameters
Parameter | Description |
---|---|
|
LOB to be archived |
storage_path |
Path where the LOB will be stored |
|
Either |
Exceptions
Table 100-84 MOVE_TO_DBFS_LINK Procedure Exceptions
Exception | Description |
---|---|
|
|
Usage Notes
-
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, orflags
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.
100.9.40 OPEN Procedures
This procedure opens a LOB, internal or external, in the indicated mode. Valid modes include read-only, and read/write.
Syntax
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);
Parameters
Table 100-85 OPEN Procedure Parameters
Parameter | Description |
---|---|
|
LOB locator. For more information, see Operational Notes. |
|
Mode in which to open. For For |
Usage Notes
Note:
If the LOB 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 procedure
100.9.41 READ Procedures
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.
Syntax
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);
Parameters
Table 100-86 READ Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB to be read. For more information, see Operational Notes. |
|
The file locator for the LOB to be examined. |
|
Number of bytes (for |
|
Offset in bytes (for |
|
Output buffer for the read operation. |
Exceptions
Table 100-87 lists exceptions that apply to any LOB instance. Table 100-88 lists exceptions that apply only to BFILE
s.
Table 100-87 READ Procedure Exceptions
Exception | Description |
---|---|
|
Any of |
|
Either: - - - - - |
|
End of the LOB is reached, and there are no more bytes or characters to read from the LOB: |
Table 100-88 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 |
Usage Notes
-
The form of the
VARCHAR2
buffer must match the form of theCLOB
parameter. In other words, if the input LOB parameter is of typeNCLOB
, then the buffer must containNCHAR
data. Conversely, if the input LOB parameter is of typeCLOB
, then the buffer must containCHAR
data. -
When calling
DBMS_LOB
.READ
from the client (for example, in aBEGIN
/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 gets the LOB, if necessary, before the read.
-
If the LOB is a DBFS LINK, data is streamed from DBFS, if possible, otherwise an exception is thrown.
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
100.9.42 SET_DBFS_LINK Procedures
This function links the specified SecureFile to the specified path name. It does not copy the data to the path.
Syntax
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));
Parameters
Table 100-89 SET_DBFS_LINK Procedure Parameters
Parameter | Description |
---|---|
|
LOB for which to store the reference value |
|
Archive ID as returned by calling either of the GET_DBFS_LINK Functions Functions |
Exceptions
Table 100-90 SET_DBFS_LINK Procedure Exceptions
Exception | Description |
---|---|
|
|
100.9.43 SETCONTENTTYPE Procedure
This procedure sets the content type string for the data in the LOB.
Syntax
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);
Parameters
Table 100-91 SETCONTENTTYPE Procedure Parameters
Parameter | Description |
---|---|
|
LOB to be assigned the content type |
|
String to be assigned |
Exceptions
Table 100-92 SETCONTENTTYPE Procedure Exceptions
Exception | Description |
---|---|
|
|
Usage Notes
To clear an existing content type associated with a SECUREFILE
, invoke SETCONTENTTYPE
with contenttype
set to empty string.
100.9.44 SETOPTIONS Procedures
This procedure enables/disables compression and deduplication on a per-LOB basis, overriding the default LOB column settings.
Syntax
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);
Parameters
Table 100-93 SETOPTIONS Procedure Parameter
Parameter | Description |
---|---|
|
Locator for the LOB to be examined. For more information, see Operational Notes. |
|
See Table 100-2 |
|
See Table 100-3 |
Exceptions
Table 100-94 SETOPTIONS Procedure Exceptions
Exception | Description |
---|---|
|
Unsupported object type for the operation |
|
A parameter value was invalid |
|
Cannot perform operation during a query |
|
Cannot perform operation with LOB buffering enabled |
Usage Notes
-
DBMS_LOB.SETOPTIONS
cannot be used to enable or disable encryption on individual LOBs. -
You cannot turn the compression or deduplication features on or off for a SecureFile column if they were not turned when the table was created.
The GETOPTIONS Functions and
SETOPTIONS
Procedures work on individual SecureFiles. You can turn off compression or deduplication on a particular SecureFiles LOB and turn on them on, if they have already been turned off bySETOPTIONS
. -
This call incurs a round-trip to the server to make the changes persistent.
100.9.45 SUBSTR Functions
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.
Syntax
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;
Pragmas
pragma restrict_references(SUBSTR, WNDS, WNPS, RNDS, RNPS);
Parameters
Table 100-95 SUBSTR Function Parameters
Parameter | Description |
---|---|
|
Locator for the LOB to be read. For more information, see Operational Notes. |
|
The file locator for the LOB to be examined. |
|
Number of bytes (for |
|
Offset in bytes (for |
Return Values
Table 100-96 SUBSTR Function Return Values
Return | Description |
---|---|
|
Function overloading that has a |
|
|
|
Either: - any input parameter is - - - - |
Exceptions
Table 100-97 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 |
Usage Notes
-
The form of the
VARCHAR2
buffer must match the form of theCLOB
parameter. In other words, if the input LOB parameter is of typeNCLOB
, then the buffer must containNCHAR
data. Conversely, if the input LOB parameter is of typeCLOB
, then the buffer must containCHAR
data. -
When calling
DBMS_LOB
.SUBSTR
from the client (for example, in aBEGIN
/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 callDBMS_LOB.SUBSTR
with a new offset to read the remaining characters, or call the subprogram on loop until all the data is extracted. -
SUBSTR
gets the LOB, if necessary, before read. -
If the LOB is a DBFS Link, the data is streamed from DBFS, if possible, otherwise, an exception is thrown.
See Also:
-
Oracle Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure
100.9.46 TRIM Procedures
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:
The TRIM
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.
Syntax
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);
Parameters
Table 100-98 TRIM Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the internal LOB whose length is to be trimmed. For more information, see Operational Notes. |
|
New, trimmed length of the LOB value in bytes for |
Exceptions
Table 100-99 TRIM Procedure Exceptions
Exception | Description |
---|---|
|
|
|
Either: - - |
|
Cannot perform a LOB write inside a query or PDML parallel execution server |
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
Usage Notes
-
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
orCLOSE
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
100.9.47 WRITE Procedures
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.
Syntax
DBMS_LOB.WRITE ( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, offset IN INTEGER, buffer IN RAW); DBMS_LOB.WRITE ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
Parameters
Table 100-100 WRITE Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the internal LOB to be written to. For more information, see Operational Notes |
|
Number of bytes (for |
|
Offset in bytes (for |
|
Input buffer for the write |
Exceptions
Table 100-101 WRITE Procedure Exceptions
Exception | Description |
---|---|
|
Any of |
|
Either: - - - - |
|
Cannot perform a LOB write inside a query or PDML parallel execution server |
|
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 |
Usage Notes
-
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
orCLOB
respectively. -
The form of the
VARCHAR2
buffer must match the form of theCLOB
parameter. In other words, if the input LOB parameter is of typeNCLOB
, then the buffer must containNCHAR
data. Conversely, if the input LOB parameter is of typeCLOB
, then the buffer must containCHAR
data. -
When calling
DBMS_LOB
.WRITE
from the client (for example, in aBEGIN
/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
orCLOSE
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
100.9.48 WRITEAPPEND Procedures
This procedure writes a specified amount of data to the end of an internal LOB. The data is written from the buffer
parameter.
Syntax
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);
Parameters
Table 100-102 WRITEAPPEND Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the internal LOB to be written to. For more information, see Operational Notes |
|
Number of bytes (for |
|
Input buffer for the write |
Usage Notes
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.
Exceptions
Table 100-103 WRITEAPPEND Procedure Exceptions
Exception | Description |
---|---|
|
Any of |
|
Either: - - |
|
Cannot perform a LOB write inside a query or PDML parallel execution server |
|
Cannot perform operation with LOB buffering enabled if buffering is enabled on the LOB |
Usage Notes
-
The form of the
VARCHAR2
buffer must match the form of theCLOB
parameter. In other words, if the input LOB parameter is of typeNCLOB
, then the buffer must containNCHAR
data. Conversely, if the input LOB parameter is of typeCLOB
, then the buffer must containCHAR
data. -
When calling
DBMS_LOB
.WRITEAPPEND
from the client (for example, in aBEGIN
/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
orCLOSE
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