CleanAttachments
Mon 01 January 2018Syntax
CleanAttachments(([PreserveCaseHint])
Description
Use the CleanAttachments function to delete all unreferenced (orphaned) files from database tables serving as file storage locations.
Note: CleanAttachments operates only on database tables that have been used as file attachment storage locations, and not on FTP sites or HTTP repositories.
Warning! There is no way to roll back changes made by the CleanAttachments function. Oracle recommends that you perform a database backup before invoking this function.
It is important that you understand how the system determines that a file is unreferenced, and how it determines which tables contain file attachments.
CleanAttachments compiles two lists:
-
List 1: A list of file references that is constructed by finding all the distinct values in the ATTACHSYSFILENAME column in each table with a record definition that contains the FILE_ATTACH_SBR subrecord (at any level). Any file not in this list is considered not referenced (orphaned).
-
List 2: A list of actual stored files that is constructed by finding the distinct values in the ATTACHSYSFILENAME column in each table with a record definition that contains the FILE_ATTDET_SBR subrecord at the top level.
The system deletes any file that appears in the second list, but not in the first, after having determined the effect of the optional PreserveCaseHint parameter.
Note: A table is only considered to contain file references if its associated record contains the FILE_ATTACH_SBR subrecord (at any level). If an application has stored file references in tables that do not contain the FILE_ATTACH_SBR subrecord, and you invoke the CleanAttachments function, then all the files uploaded to the database through that application will be deleted because the files will not be found in list 1 and the system therefore regards them as unreferenced.
Similarly, the FILE_ATTDET_SBR subrecord must be at the top level of the table that contains the actual attachments or the table will be ignored by CleanAttachments. In this case, CleanAttachments does not find any files to delete and does nothing at all.
To schedule a regular job to clean up orphaned file attachments, you can use the CLEANATT84 Application Engine program, which can be executed from the Delete Orphan Files (Batch) page. See for more information.
Alternatively, you can use the Manage Attachment Repositories page to execute CleanAttachments directly in a synchronous manner. See âDeleting Orphan Attachmentsâ in for more information.
Important! Executing CleanAttachments directly in a synchronous manner may result in a timeout if the number of file attachments to be processed is large. Therefore, Oracle recommends that you use the batch processing interface instead.
Parameters
|
Field or Control |
Definition |
|---|---|
| PreserveCaseHint |
An optional integer parameter that provides the CleanAttachments function with a hint about how the PreserveCase parameter was used when the files were originally uploadedâthat is, whether the PreserveCase parameter was True, False, or a mix of the two. The default value is %CleanAttach_Default (a mix of the two). |
For PreserveCaseHint , specify one of the following constant values:
|
Numeric Value |
Constant Value |
Description |
|---|---|---|
|
0 |
%CleanAttach_Default |
Indicates that the comparison is to be performed as if PreserveCase were True when some of the files were uploaded to this database and False for others. Therefore, a file in list 2 (actual stored files) is retained if it would have been retained had PreserveCaseHint been specified as either %CleanAttach_PreserveCase or %CleanAttach_NoPreserveCase. Otherwise, the file is considered an orphan and is deleted. |
|
1 |
%CleanAttach_PreserveCase |
Indicates that the comparison is to be performed as if PreserveCase were True when all the files were uploaded to this database. Therefore, the comparison between list 1 and list 2 requires an exact match of the file name including its file extension. Any files in list 2 (actual stored files) that do not have an exact match in list 1 (names of referenced files) are deleted. |
|
2 |
%CleanAttach_NoPreserveCase |
Indicates that the comparison is to be performed as if PreserveCase were False when all the files were uploaded to this database. Therefore, the comparison between list 1 and list 2 will be performed only after the file extension of each file in list 1 is lowercased. Any files in list 2 (actual stored files) that do not have an exact match in list 1 (names of referenced files) after lowercasing the file extension in list 1 are deleted. |
The following table summarizes the action of CleanAttachments on five different stored files depending on the values found in the file reference table and depending on the value of the optional PreserveCaseHint parameter. CleanAttachments will either retain or delete the file from the file storage tables.
|
System File Name in File Storage Tables (List 2) |
System File Name in File Reference Tables (List 1) |
PreserveCaseHint = %CleanAttach_ Default |
PreserveCaseHint = %CleanAttach_ PreserveCase |
PreserveCaseHint = %CleanAttach_ NoPreserveCase |
|---|---|---|---|---|
|
file1.txt |
file1.txt |
Retain |
Retain |
Retain |
|
file2.txt |
file2.TXT |
Retain |
Delete |
Retain |
|
file3.TXT |
file3.TXT |
Retain |
Retain |
Delete |
|
file4.TxT |
file4.TXT or file4.txt |
Delete |
Delete |
Delete |
|
file5.txt |
none found |
Delete |
Delete |
Delete |
Returns
An integer value. You can check for either an integer or a constant value:
Note: Because CleanAttachments is designed to work with multiple files, to track errors when using CleanAttachments set your PeopleCode trace to 2112 and your SQL trace to 15 so that errors will be written to the appropriate trace files.
|
Numeric Value |
Constant Value |
Description |
|---|---|---|
|
0 |
%Attachment_Success |
Files were deleted successfully. |
|
1 |
%Attachment_Failed |
Files were not deleted successfully. The following are some possible situations where %Attachment_Failed could be returned:
|