Oracle Application Express Packages APEX_APPLICATION Through APEX

2 Oracle Application Express Packages APEX_APPLICATION Through APEX_ZIP

The Oracle Application Express supplies PL/SQL packages for application developers who are building database-centric web applications using Oracle Application Express.

For a complete description of all the packages that ship with Oracle Application Express, see the Oracle Application Express API Reference.

2.1 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 2-1 COPY Procedure Parameters

Parameter	Description
`dest_lob`	LOB locator of the copy target.
`src_lob`	LOB locator of source for the copy.
`amount`	Number of bytes (for `BLOBs`) or characters (for `CLOBs`) to copy.
`dest_offset`	Offset in bytes or characters in the destination LOB (origin: 1) for the start of the copy.
`src_offset`	Offset in bytes or characters in the source LOB (origin: 1) for the start of the copy.

Exceptions

Table 2-2 COPY Procedure Exceptions

Exception Description

Exception	Description
`VALUE_ERROR`	Any of the input parameters are `NULL` or invalid.
`INVALID_ARGVAL`	Either: - `src_offset` or `dest_offset` < 1 - `src_offset` or `dest_offset` > `LOBMAXSIZE` - `amount` < 1 - `amount` > `LOBMAXSIZE`
`QUERY_WRITE`	Cannot perform a LOB write inside a query or PDML parallel execution server

VALUE_ERROR

Any of the input parameters are NULL or invalid.

INVALID_ARGVAL

Either:

- src_offset or dest_offset < 1

- src_offset or dest_offset > LOBMAXSIZE

- amount < 1

- amount > LOBMAXSIZE

QUERY_WRITE

Cannot perform a LOB write inside a query or PDML parallel execution server

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 or CLOB respectively. If the offset is less than the current length of the destination LOB, then existing data is overwritten.
It is not an error to specify an amount that exceeds the length of the data in the source LOB. Thus, you can specify a large amount to copy from the source LOB, which copies data from the src_offset to the end of the source LOB.
It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. If a LOB is under IS JSON FORMAT JSON constraints, and you do not wrap the LOB operations inside the Open/Close API, it checks that the LOB content is a valid JSON for every write operation on the LOB.
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 AI Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure

2.2 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 AI Database does not support constraints on columns or attributes whose type is a LOB, with the following exceptions:
- NOT NULL constraints are supported for an LOB column or attribute IS JSON FORMAT JSON constraints trigger a JSON validation when you close a LOB.
- Oracle does not recommend that you use DBMS_LOB to modify LOBs under IS JSON FORMAT JSON constraints. Because LOBs under IS JSON FORMAT JSON constraints are not validated, you can only check that the LOB content is a valid JSON. However, if required, Oracle recommends that you wrap a LOB operation inside the Open/Close API to perform write operations on LOBs under the IS JSON FORMAT JSON constraints. This validates that the LOB content has valid JSON when you close the LOB. If the LOB is not open, every write operation validates that the LOB content is a valid JSON, and hence, you will not able to write the JSON in pieces.
The following rules apply in the specification of subprograms in this package:
- newlen, offset, and amount parameters for subprograms operating on BLOBs and BFILEs must be specified in terms of bytes.
- newlen, offset, and amount parameters for subprograms operating on CLOBs must be specified in terms of characters.
In multi-byte character sets, it is not possible to interpret these offsets correctly.
A subprogram raises an INVALID_ARGVAL exception if the following restrictions are not followed in specifying values for parameters (unless otherwise specified):
1. Only positive, absolute offsets from the beginning of LOB data are permitted: Negative offsets from the tail of the LOB are not permitted.
2. 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.
3. The value of offset, amount, newlen, nth must not exceed the value lobmaxsize 18446744073709551615 (2⁶⁴-1) in any DBMS_LOB subprogram.
4. For CLOBs in a database with a multibyte database character set and for NCLOBs, the maximum value for these parameters must not exceed trunc(lobmaxsize/2)=9223372036854775807 characters.
PL/SQL language specifications stipulate an upper limit of 32767 bytes (not characters) for RAW and VARCHAR2 parameters used in DBMS_LOB subprograms. For example, if you declare a variable to be:
```
charbuf VARCHAR2(3000)
```
Then, charbuf can hold 3000 single byte characters or 1500 2-byte fixed width characters. This has an important consequence for DBMS_LOB subprograms for CLOBs and NCLOBs.
The %CHARSET clause indicates that the form of the parameter with %CHARSET must match the form of the ANY_CS parameter to which it refers.

For example, in DBMS_LOB subprograms that take a VARCHAR2 buffer parameter, the form of the VARCHAR2 buffer must match the form of the CLOB parameter. If the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.

For DBMS_LOB subprograms that take two CLOB parameters, both CLOB parameters must have the same form; that is, they must both be NCLOBs, or they must both be CLOBs.
If the value of amount plus the offset exceeds the maximum LOB size allowed by the database, then access exceptions are raised.

Under these input conditions, read subprograms, such as READ, COMPARE, INSTR, and SUBSTR, read until End of Lob/File is reached. For example, for a READ operation on a BLOB or BFILE, if the user specifies offset value of 3 GB and an amount value of 2 GB on a LOB that is 4GB in size, then READ returns only 1GB (4GB-3GB) bytes.
Functions with NULL or invalid input values for parameters return a NULL. Procedures with NULL values for destination LOB parameters raise exceptions.
Operations involving patterns as parameters, such as INSTR do not support regular expressions or special matching characters (such as % in the LIKE operator in SQL) in the pattern parameter or substrings.
The End Of LOB condition is indicated by the READ procedure using a NO_DATA_FOUND exception. This exception is raised only upon an attempt by the user to read beyond the end of the LOB. The READ buffer for the last read contains 0 bytes.
For consistent LOB updates, you must lock the row containing the destination LOB before making a call to any of the procedures (mutators) that modify LOB data.
Unless otherwise stated, the default value for an offset parameter is 1, which indicates the first byte in the BLOB or BFILE data, and the first character in the CLOB or NCLOB value. No default values are specified for the amount parameter — you must input the values explicitly.
You must lock the row containing the destination internal LOB before calling any subprograms that modify the LOB, such as APPEND, COPY, ERASE, TRIM, or WRITE. These subprograms do not implicitly lock the row containing the LOB.

Rules and Limits Specific to External Files (BFILEs)

The subprograms COMPARE, INSTR, READ, SUBSTR, FILECLOSE, FILECLOSEALL and LOADFROMFILE operate only on an opened BFILE locator; that is, a successful FILEOPEN call must precede a call to any of these subprograms.
For the functions FILEEXISTS, FILEGETNAME and GETLENGTH, a file's open/close status is unimportant; however, the file must exist physically, and you must have adequate privileges on the DIRECTORY object and the file.
DBMS_LOB does not support any concurrency control mechanism for BFILE operations.
In the event of several open files in the session whose closure has not been handled properly, you can use the FILECLOSEALL subprogram to close all files opened in the session and resume file operations from the beginning.
If you are the creator of a DIRECTORY, or if you have system privileges, then use the CREATE OR REPLACE, DROP, and REVOKE statements in SQL with extreme caution.

If you, or other grantees of a particular directory object, have several open files in a session, then any of the preceding commands can adversely affect file operations. In the event of such abnormal termination, your only choice is to invoke a program or anonymous block that calls FILECLOSEALL, reopen your files, and restart your file operations.

All files opened during a user session are implicitly closed at the end of the session. However, Oracle strongly recommends that you close the files after both normal and abnormal termination of operations on the BFILE.

In the event of normal program termination, proper file closure ensures that the number of files that are open simultaneously in the session remains less than SESSION_MAX_OPEN_FILES.

In the event of abnormal program termination from a PL/SQL program, it is imperative that you provide an exception handler that ensures closure of all files opened in that PL/SQL program. This is necessary because after an exception occurs, only the exception handler has access to the BFILE variable in its most current state.

After the exception transfers program control outside the PL/SQL program block, all references to the open BFILEs are lost. The result is a larger open file count which may or may not exceed the SESSION_MAX_OPEN_FILES value.

For example, consider a READ operation past the end of the BFILE value, which generates a NO_DATA_FOUND exception:

-- This assumes a directory 'DDD' whose path is already known
DECLARE 
       fil BFILE:= bfilename('DDD', 'filename.foo'); 
       pos INTEGER; 
       amt BINARY_INTEGER; 
       buf RAW(40); 
BEGIN 
       SELECT ad_graphic INTO fil FROM print_media WHERE product_id = 3106; 
       dbms_lob.open(fil, dbms_lob.lob_readonly);   
       amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := ''; 
       dbms_lob.read(fil, amt, pos, buf); 
       dbms_output.put_line('Read F1 past EOF: '|| 
           utl_raw.cast_to_varchar2(buf));
       dbms_lob.close(fil); 
END;
      
ORA-01403: no data found 
ORA-06512: at "SYS.DBMS_LOB", line 373 
ORA-06512: at line 10

After the exception has occurred, the BFILE locator variable file goes out of scope, and no further operations on the file can be done using that variable. Therefore, the solution is to use an exception handler:

DECLARE
     fil BFILE; 
     pos INTEGER;
     amt BINARY_INTEGER; 
     buf RAW(40); 
BEGIN 
     SELECT ad_graphic INTO fil FROM print_media WHERE product_id = 3106;
     dbms_lob.open(fil, dbms_lob.lob_readonly);   
     amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := ''; 
     dbms_lob.read(fil, amt, pos, buf); 
     dbms_output.put_line('Read F1 past EOF: '|| 
          utl_raw.cast_to_varchar2(buf)); 
     dbms_lob.close(fil); 
     exception 
     WHEN no_data_found 
     THEN 
       BEGIN 
         dbms_output.put_line('End of File reached. Closing file'); 
         dbms_lob.fileclose(fil); 
         -- or dbms_lob.filecloseall if appropriate 
       END; 
END; 
     / 

Statement processed. 
End of File reached. Closing file

In general, you should ensure that files opened in a PL/SQL block using DBMS_LOB are closed before normal or abnormal termination of the block.

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 2³²-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).

2.3 DESTROY_ORACLE_FS Procedure

This procedure destroys an Oracle file system and then frees the resources that were associated with it. Run the dbms_fs.destroy_oracle_fs() procedure to destroy file systems that are no longer in use.

Syntax

DBMS_FS.DESTROY_ORACLE_FS (
   fstype      IN VARCHAR2,
   fsname      IN VARCHAR2);

Parameters

Table 2-3 DBMS_FS Parameters

Parameter	Description
`fstype`	File system type. Oracle File System (OFS) and Database File System (DBFS) are supported.
`fsname`	Name of the file system

Usage Notes

You can find information about the currently mounted file systems by querying the V$OFSMOUNT dynamic view.
For more information about the file system types, see the fstype description in MAKE_ORACLE_FS Procedure.
Before you run the DBMS_FS.DESTROY_ORACLE_FS procedure, you must unmount the file system by using the DBMS_FS.UNMOUNT_ORACLE_FS procedure. If you run this procedure on a file system that is still in use, it results in an error.
After you run DBMS_FS.DESTROY_ORACLE_FS, Oracle AI Database destroys the file system and frees the associated resources.

Example

The following sample code shows how to destroy a DBFS file system, dbfs_fs1.

BEGIN
 DBMS_FS.DESTROY_ORACLE_FS (
  fstype           => 'dbfs',
  fsname           => 'dbfs_fs1');
END;
/

2.4 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 2-4 ERASE Procedure Parameters

Parameter	Description
`lob_loc`	Locator for the LOB to be erased.For more information, see Operational Notes.
`amount`	Number of bytes (for `BLOBs` or `BFILES`) or characters (for `CLOBs` or `NCLOBs`) to be erased.
`offset`	Absolute offset (origin: 1) from the beginning of the LOB in bytes (for `BLOBs`) or characters (`CLOBs`).

Usage Notes

When data is erased from the middle of a LOB, zero-byte fillers or spaces are written for BLOBs or CLOBs respectively.
The actual number of bytes or characters erased can differ from the number you specified in the amount parameter if the end of the LOB value is reached before erasing the specified number. The actual number of characters or bytes erased is returned in the amount parameter.
ERASE gets the LOB if it is archived, unless the erase covers the entire LOB.
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 2-5 ERASE Procedure Exceptions

Exception Description

Exception	Description
`VALUE_ERROR`	Any input parameter is `NULL`.
`INVALID_ARGVAL`	Either: - `amount` < 1 or `amount` > `LOBMAXSIZE` - `offset` < 1 or `offset` > `LOBMAXSIZE`
`QUERY_WRITE`	Cannot perform a LOB write inside a query or PDML parallel execution server

VALUE_ERROR

Any input parameter is NULL.

INVALID_ARGVAL

Either:

- amount < 1 or amount > LOBMAXSIZE

- offset < 1 or offset > LOBMAXSIZE

QUERY_WRITE

Cannot perform a LOB write inside a query or PDML parallel execution server

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. If a LOB is under IS JSON FORMAT JSON constraints, and you do not wrap the LOB operations inside the Open/Close API, it checks that the LOB content is a valid JSON for every write operation on the LOB.

See Also:

TRIM Procedures
Oracle AI Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure

2.5 GET_LOB_DEDUPLICATION_RATIO Function

Advanced LOB deduplication enables Oracle AI Database to automatically detect duplicate LOB data, within a LOB column or partition, and conserves space by storing only one copy of the data. Use the GET_LOB_DEDUPLICATION_RATIO function to estimate the potential storage savings by enabling deduplication on SecureFile LOBs.

The GET_LOB_DEDUPLICATION_RATIO function returns the deduplication ratio, which allows you to make an informed decision about enabling advanced LOB deduplication or about deduplicating the resultant SecureFiles LOB, before migrating BasicFiles LOB to SecureFiles LOB. For information about advanced LOB deduplication, see ALTER TABLE with Advanced LOB Deduplication in Oracle AI Database SecureFiles and Large Objects Developer's Guide.

The deduplication ratio is estimated for the specified number of rows in a LOB column. For example, let's consider that the deduplication ratio is 2.33. It indicates that after enabling deduplication, around half of the storage space is saved for the sampled rows in the LOB column.

Disclaimer: The deduplication ratio is an approximate value, which is calculated based on the sampled rows in the LOB column. The actual space saved may be different when deduplication is enabled for the entire table.

Required Role

The SECUREFILE_MONITOR role is required to use the GET_LOB_DEDUPLICATION_RATIO function.

Syntax

DBMS_SECUREFILES.GET_LOB_DEDUPLICATION_RATIO (
   tablespacename        IN     VARCHAR2,
   tabowner              IN     VARCHAR2,
   tabname               IN     VARCHAR2,
   lobcolumnname         IN     VARCHAR2,
   partname              IN     VARCHAR2,
   dedup_ratio           OUT    NUMBER,
   subset_numrows        IN     NUMBER DEFAULT DEDUP_RATIO_LOB_MAXROWS
)

Parameters

Table 2-6 GET_LOB_DEDUPLICATION_RATIO Function Parameters

Parameter	Description
`tablespacename`	Name of the tablespace
`tabowner`	Owner of the table
`tabname`	Name of the table
`lobcolumnname`	Name of the LOB column for which the deduplication ratio is calculated
`partname`	In case of partitioned tables, enter the related partition name
`subset_numrows`	Number of rows sampled to estimate the deduplication ratio. By default, the deduplication ratio is calculated for all the rows.

Return Values

Returns the deduplication ratio, dedup_ratio, which indicates storage space saved for SecureFile LOB column after enabling deduplication.

Example

The following sample code calculates and returns the deduplication ratio for C, a LOB column, in ACME_TABLE, which is owned by JOHN.

DECLARE
 dedup_ratio    number;
 l_table_name   varchar2(128) = "ACME_TABLE";
 l_column_name  varchar2(3000) = "C";
 l_tablespace_name varchar2(128) := "TBS";
 l_owner varchar2(128) := "JOHN";
BEGIN
dedup_ratio := dbms_lob.GET_LOB_DEDUPLICATION_RATIO(
    l_tablespace_name,
    l_owner,
    l_table_name,
    l_column_name,
    '',
    dedup_ratio,
    -1);
dbms_output.put_line('Deduplication ratio: ' || dedup_ratio);
END;

Usage Notes

This function can process a maximum of 100000 LOBs or 1% of the total number of rows in the table, whichever is lesser.

2.6 GET_LOB_DEDUPLICATION_RATIO Function

The DBMS_LOB.GET_LOB_DEDUPLICATION_RATIO function is deprecated, and can be desupported in a future release. Instead, use the DBMS_SECUREFILES.GET_LOB_DEDUPLICATION_RATIO function.

See GET_LOB_DEDUPLICATION_RATIO Function.

The GET_LOB_DEDUPLICATION_RATIO function estimates the storage space that you can save by enabling the deduplication feature for an existing SecureFile LOB and returns the deduplication ratio. The deduplication ratio is estimated for the number of rows in the LOB column that you specify. For example, let's consider that the deduplication ratio is 2.33. It indicates that after you enable the deduplication feature, you can save around half of the space for the sampled rows in the LOB column.

Disclaimer: The deduplication ratio is an approximate value, which is calculated based on the sampled rows in the LOB column. The actual space that you save when you enable deduplication for the complete table may be different.

Syntax

DBMS_LOB.GET_LOB_DEDUPLICATION_RATIO (
   tablespacename        IN     VARCHAR2,
   tabowner              IN     VARCHAR2,
   tabname               IN     VARCHAR2,
   lobcolumnname         IN     VARCHAR2,
   partname              IN     VARCHAR2,
   dedup_ratio           OUT    NUMBER,
   subset_numrows        IN     NUMBER DEFAULT DEDUP_RATIO_LOB_MAXROWS
)

Parameters

The following table describes the parameters of the GET_LOB_DEDUPLICATION_RATIO function. When you run this function, provide details of the table that contains the LOB column for which you want to calculate the deduplication ratio.

Table 2-7 GET_LOB_DEDUPLICATION_RATIO Function Parameters

Parameter	Description
`tablespacename`	Name of the tablespace
`tabowner`	Owner of the table
`tabname`	Name of the table
`lobcolumnname`	Name of the LOB column for which you want to calculate the deduplication ratio
`partname`	In case of partitioned tables, enter the related partition name
`subset_numrows`	Number of rows sampled to estimate the deduplication ratio. By default, the deduplication ratio is calculated for all the rows.

Return Values

Returns the deduplication ratio, dedup_ratio, which indicates the space that you can save by enabling the deduplication feature.

Example

The following sample code calculates and returns the deduplication ratio for C, a LOB column, in ACME_TABLE, which is owned by JOHN.

DECLARE
 dedup_ratio    number;
 l_table_name   varchar2(128) = "ACME_TABLE";
 l_column_name  varchar2(3000) = "C";
 l_tablespace_name varchar2(128) := "TBS";
 l_owner varchar2(128) := "JOHN";
BEGIN
dedup_ratio := dbms_lob.GET_LOB_DEDUPLICATION_RATIO(
    l_tablespace_name,
    l_owner,
    l_table_name,
    l_column_name,
    '',
    dedup_ratio,
    -1);
dbms_output.put_line('Deduplication ratio: ' || dedup_ratio);
END;

Usage Notes

The maximum number of LOBs that this function can process is 100000 or 1% of the total number of rows in the table, whichever is lesser.

2.7 MOUNT_ORACLE_FS Procedure

Use the dbms_fs.mount_oracle_fs() procedure to mount an Oracle file system or OFS managed file system on the specified mount point.

Before you begin, complete the following checks to ensure that:

You have created the file system using the dbms_fs.make_oracle_fs() procedure.
The mount point that you specify exists in the local node.
The Oracle user has access permissions.
The mount path is empty, which means that the directory specified by the mount point does not have any files.

Oracle supports an extensive list of mount options that you can use to have better control on resources and achieve good performance. When you use the persist mount option, file systems are automatically remounted every time instance restarts. Since OFSD is a non-fatal background process, it gets automatically restarted after the death of a process. File systems that are mounted at the time of the death of the OFSD process are automatically remounted after starting a new OFSD process. This ensures continuous availability of the mounted file systems even in the case of an error.

To improve the throughput, OFSD has its own local cache for writes and reads. The write cache uses 8 (1 MB) buffers per file connection, and it can use up to 256 MB per file system. You can modify this value through the mount option, wcache_size. A read-ahead algorithm is implemented for read operation and it uses 2 (1 MB) per file connection and it can use up to 256 MB per file system. Use the mount option, rcache_size, to modify this value.

The read-write cache is maintained per node, so this provides local cache consistency. In an RAC environment, consistency is guaranteed only after the flush() or close() operation is performed on the file. When two different processes on two different RAC nodes modify a single file and write to the same offset, then the first process that performs the close() operation on the file will have its data written into the file.

Syntax

DBMS_FS.MOUNT_ORACLE_FS (
  fstype           IN VARCHAR2,                              
  fsname           IN VARCHAR2,                              
  mount_point      IN VARCHAR2,                              
  mount_options    IN VARCHAR2);

Parameters

Table 2-8 MOUNT_ORACLE_FS Procedure Parameters

Parameter	Description
`fstype`	File system type. Oracle File System (OFS) and Database File System (DBFS) are supported.
`fsname`	Name of the file system. Enter a string no longer than 256 characters, using alpha numeric characters.
`mount_point`	Local directory where the file system should be mounted. This directory must already exist. Enter an absolute path. The maximum number of mount points that you can create is 5 mount points per PDB and 1000 mount points per instance.
`mount_options`	Comma-separated mount options, listed in Table 96-7.

Usage Notes

Table 2-9 Supported Mount Options for the MOUNT_ORACLE_FS procedure

Mount Option	Usage Description
`db_access`	Optimizes the read and write operations performed by database process when you access files managed through OFS or DBFS.
`default_permissions`	Enables permission check and restrict access based on file mode. This option is useful with the allow_other mount option.
`allow_other`	Allows other users apart from the operating system user that did the mount can access the files. This will be used in conjunction with permission checks in determining the file access. This option requires setting the user_allow_other parameter in the `/etc/fuse.conf` configuration file on Linux.
`max_read`	Maximum size of the read operation. No maximum size is set by default.
`max_write`	Maximum write size in a single request. The default is 128K.
`direct_io`	Indicates to the operating system kernel not use file system cache.
`nopersist`	Does not store the mount options for use in next instance start up.
`persist`	Stores the mount entry persistently so that on subsequent instance start up it will be automatically mounted again. This option is supported for both `ofs` and `dbfs`.
`ro`	Mounts the file system in read-only mode. Files cannot be modified.
`rw`	Mounts the file system as read-write. This is the default.
`nosuid`	Specifies that the file system cannot contain set userid files.
`suid`	Specifies that the file system can contain set userid files. This is the default.
`ofs_cache_attr_time`	Specifies the OFS cache attribute timeout in seconds. Default value is 5 seconds. The permissible range is `0` to `UB4MAXVAL`. Oracle recommends that you specify low values when you use RAC. If you specify a larger value, such as 1000, the attributes from the files may not match if the same file system is mounted in another path. This is due to the aggressive caching that is done in the OFS layer.
`exec`	Allows executing a file under the mount path. This option is enabled by default.
`noexec`	Prohibits executing a file under the mount path. Use this option to disallow any file to be executed in the file system.
`atime`	Maintains information about the time when the file was accessed. This option is enabled by default.
`noatime`	Does not maintain information about the time when the was file accessed. When you enable this option, it increases throughput as the access time is not updated for every read operation.
`max_readahead`	Sets the maximum size, in bytes, for the read-ahead operations. The default value depends on the versions of the kernel and FUSE in your operating system.
`sync_read`	Permits only synchronous reads. If you use this option, it disables read-ahead caching for OFS.
`async_read`	Permits asynchronous reads. All read and write operations in the file system as performed asynchronously. This is the default option for FUSE versions 7.6 or later.
`dirsync`	Makes all directory operations synchronous. FUSE utilizes this option.
`big_writes`	Permits writes larger than 4KB. This is the default option for FUSE versions 7.6 or later.
`no_big_writes`	Does not allow writes larger than 4KB. This is not a recommended option for OFS.
`xattr_enabled`	Enables the use of extended attributes. Specify this option only if you want to use extended attributes.
`kernel_cache_mode=writeback`	Makes the Linux kernel cache behave like a writeback cache when you enter `writeback` as the value for this option. A writeback cache provides better performance when there are many small input-output requirements. FUSE utilizes this option. Permitted values are: `writeback` and `write-through`. The default value is `write-through`.
`kernel_cache_attr_time=N`	Sets the Linux kernel attribute cache timeout to N seconds. The default value is infinity. Oracle recommends that you set low values for RAC setup.
`wcache_size=N`	Sets the OFS write cache size to N bytes. The default value is 256 MB.
`rcache_size`	Sets the OFS read cache size to N bytes. The default value is 256 MB.
`statfs_ctime`	Sets the time, in seconds, for which `statfs()` gets cached in OFS. The default value is 300 seconds or 5 minutes. Oracle recommends that you use the default value to avoid repeated `statfs()` calls from FUSE.
`no_rbt_cache`	Disallows the use of a red-black tree for caching directory entries. By default, a red-black tree is used to ensure accuracy when there are concurrent updates while listing directories.
`posix_locks`	Implements the POSIX file locks in OFS. This is not supported by OFS. If you want to use the file-locking mechanism, use the `noposix_locks` option.
`noposix_locks`	Implements POSIX file locks in Linux kernel. This is the default option.

Note:

The following options are exclusive options and cannot be used together:

nopersist/persist
ro/rw
nosuid/suid
exec/noexec
atime/noatime
big_writes/no_big_writes
sync_read/async_read
posix_locks/noposix_locks

Usage Notes

This procedure makes the files system visible in the local database instance.
For more information about the file system types, see the fstype description in MAKE_ORACLE_FS Procedure.
You can find information about currently mounted file systems by querying the V$OFSMOUNT dynamic view.
Run the DBMS_FS.MOUNT_ORACLE_FS procedure on a file system that has already been created with DBMS_FS.MAKE_ORACLE_FS in the local computer node where the Oracle AI Database instance is running. You cannot run this procedure on file systems that were created outside of Oracle AI Database.
You cannot update the mount options after mounting the file system. If you want to change the mount options later, you'll have to unmount the file system, and then remount it.

Example 1: Mounts a DBFS file system

Mounts a DBFS file system at /oracle/dbfs/testfs.

BEGIN
 DBMS_FS.MOUNT_ORACLE_FS (
  fstype           => 'dbfs',                              
  fsname           => 'dbfs_fs1',                              
  mount_point      => '/oracle/dbfs/testfs',                              
  mount_options    => 'default_permissions, allow_other, db_access'); 
END;

Example 2: Persist mount a DBFS file system

Persist mounts a DBFS file system at /oracle/dbfs/testfs.

BEGIN
 DBMS_FS.MOUNT_ORACLE_FS (
  fstype           => 'dbfs',                              
  fsname           => 'dbfs_fs1',                              
  mount_point      => '/oracle/dbfs/testfs',                              
  mount_options    => 'default_permissions, allow_other, persist, db_access'); 
END;

2.8 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 2-10 TRIM Procedure Parameters

Parameter	Description
`lob_loc`	Locator for the internal LOB whose length is to be trimmed. For more information, see Operational Notes.
`newlen`	New, trimmed length of the LOB value in bytes for `BLOBs` or characters for `CLOBs`.

Exceptions

Table 2-11 TRIM Procedure Exceptions

Exception Description

Exception	Description
`VALUE_ERROR`	`lob_loc` is `NULL`.
`INVALID_ARGVAL`	Either: - `new_len` < 0 - `new_len` > `LOBMAXSIZE`
`QUERY_WRITE`	Cannot perform a LOB write inside a query or PDML parallel execution server

VALUE_ERROR

lob_loc is NULL.

INVALID_ARGVAL

Either:

- new_len < 0

- new_len > LOBMAXSIZE

QUERY_WRITE

Cannot perform a LOB write inside a query or PDML parallel execution server

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. If a LOB is under IS JSON FORMAT JSON constraints, and you do not wrap the LOB operations inside the Open/Close API, it checks that the LOB content is a valid JSON for every write operation on the LOB.
TRIM gets the LOB, if necessary, before altering the length of the LOB, unless the new length specified is '0'

See Also:

"ERASE Procedures"
"WRITEAPPEND Procedures"
Oracle AI Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure

2.9 UNMOUNT_ORACLE_FS Procedure

This procedure unmounts an Oracle file system on the specified mount point.

File systems that are mounted in a PDB are automatically unmounted when the PDB is closed. You can also use dbms_fs.unmount_oracle_fs() to explicitly unmount an Oracle file system that you have mounted through OFS.

Syntax

DBMS_FS.UNMOUNT_ORACLE_FS (
  fsname             IN VARCHAR2,                              
  mount_point        IN VARCHAR2,                              
  unmount_options    IN VARCHAR2);

Table 2-12 UNMOUNT_ORACLE_FS Procedure Parameters

Parameter Description

Parameter	Description
`fsname`	Name of the file system.
`mount_point`	Local directory where the file system had been mounted. Enter an absolute path.
`unmount_options`	Optionally, enter `force` or `clean`. Enter `force` to unmount the file system forcibly. This setting prevents new requests from being sent to the file system. All pending requests on the file system are either completed or canceled. If you omit this setting, then attempts to unmount a busy file system cause an `EBUSY` error. Enter `clean` if you don't want the file system to be remounted when the OFSD restarts.

fsname

Name of the file system.

mount_point

Local directory where the file system had been mounted. Enter an absolute path.

unmount_options

Optionally, enter force or clean.

Enter force to unmount the file system forcibly. This setting prevents new requests from being sent to the file system. All pending requests on the file system are either completed or canceled. If you omit this setting, then attempts to unmount a busy file system cause an EBUSY error.

Enter clean if you don't want the file system to be remounted when the OFSD restarts.

Usage Notes

Before you unmount the file system, ensure that all applications that use this file system are shut down. Also ensure that no processes reference the mounting file system.
You can find information about the currently mounted file systems by querying the V$OFSMOUNT dynamic view.
For more information about the file system types, see the fstype description in MAKE_ORACLE_FS Procedure.
After unmounting the file system, the write permissions are removed from the mount point to prevent applications from writing to the underlying file system instead of the OFS supported file systems.
When an Oracle instance is shut down in normal immediate mode, then all the mounted file systems are automatically unmounted.
If a file system is mounted with the MOUNT_ORACLE_FS procedure with the persist option, it will be automatically mounted again when the database instance starts or the PDB is plugged. If this file system is unmounted by executing DBMS_FS.UNMOUNT_ORACLE_FS, it will remain unmounted even if the persist option was used to mount it.
If you perform a SHUTDOWN ABORT, then the file system may still show as mounted but it may not be accessible. In this case, you can unmount the system manually by calling the unmount command at the operating system level or the fusermount procedure on Linux systems.
Do not use fusermount -u to unmount a running file system as it causes inconsistency in Oracle views, such as v$ofsmount.
You can export the local mount point of an Oracle file system to point to the remote system, and then NFS mount the file system from the remote system by using the operating system mount command. The DBMS_FS.MOUNT_ORACLE_FS procedure is similar to mount commands that are used for other local file systems.
For better security, Oracle recommends that you use access control lists (ACLs) and Kerberos to control access to sensitive data.
Do not attempt to unmount the file system from the operating system level. Doing so can leave the file system internal tables created by Oracle AI Database in an inconsistent state.

Example

The following sample code unmounts a DBFS mounted file system at /oracle/dbfs/testfs.

BEGIN
 DBMS_FS.UNMOUNT_ORACLE_FS (
  fsname           => 'dbfs_fs1',                              
  mount_point      => '/oracle/dbfs/testfs',
  mount_options    => 'force'); 
END;

2.10 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 2-13 WRITE Procedure Parameters

Parameter	Description
`lob_loc`	Locator for the internal LOB to be written to. For more information, see Operational Notes
`amount`	Number of bytes (for `BLOBs`) or characters (for `CLOBs`) to write
`offset`	Offset in bytes (for `BLOBs`) or characters (for `CLOBs`) from the start of the LOB (origin: 1) for the write operation.
`buffer`	Input buffer for the write

Exceptions

Table 2-14 WRITE Procedure Exceptions

Exception	Description
`VALUE_ERROR`	Any of `lob_loc`, `amount`, or `offset` parameters are `NULL`, out of range, or `INVALID`.
`INVALID_ARGVAL`	Either: - `amount` < 1 - `amount` > `32767 bytes (or the character equivalent)` - `offset` < 1 - `offset` > `LOBMAXSIZE`
`QUERY_WRITE`	Cannot perform a LOB write inside a query or PDML parallel execution server
`SECUREFILE_OUTOFBOUNDS`	Attempted to perform a write operation past the end of a LOB having `FRAGMENT_*` on it

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 or CLOB respectively.
The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.
When calling DBMS_LOB.WRITE from the client (for example, in a BEGIN/END block from within SQL*Plus), the buffer must contain data in the client's character set. The database converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.
It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. If a LOB is under IS JSON FORMAT JSON constraints, and you do not wrap the LOB operations inside the Open/Close API, it checks that the LOB content is a valid JSON for every write operation on the LOB.
WRITE gets the LOB, if necessary, before writing the LOB, unless the write is specified to overwrite the entire LOB.

See Also:

"APPEND Procedures"
"COPY Procedures"
Oracle AI Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure

2.11 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 2-15 WRITEAPPEND Procedure Parameters

Parameter	Description
`lob_loc`	Locator for the internal LOB to be written to. For more information, see Operational Notes
`amount`	Number of bytes (for `BLOBs`) or characters (for `CLOBs`) to write
`buffer`	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 2-16 WRITEAPPEND Procedure Exceptions

Exception Description

VALUE_ERROR

Any of lob_loc, amount, or offset parameters are NULL, out of range, or INVALID.

INVALID_ARGVAL

Either:

- amount < 1

- amount > 32767 bytes (or the character equivalent)

QUERY_WRITE

Cannot perform a LOB write inside a query or PDML parallel execution server

Usage Notes

The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.
When calling DBMS_LOB.WRITEAPPEND from the client (for example, in a BEGIN/END block from within SQL*Plus), the buffer must contain data in the client's character set. The database converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.
It is not mandatory that you wrap the LOB operation inside the Open/Close interfaces. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.
If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. If a LOB is under IS JSON FORMAT JSON constraints, and you do not wrap the LOB operations inside the Open/Close API, it checks that the LOB content is a valid JSON for every write operation on the LOB.
WRITEAPPEND gets the LOB, if necessary, before appending to the LOB.

See Also:

"APPEND Procedures"
"COPY Procedures"
"WRITE Procedures"
Oracle AI Database SecureFiles and Large Objects Developer's Guide for additional details on usage of this procedure