ATTACHMENTS: BLOB/BFILE –PROFILE ATTACHMENTS STORAGE Explained
APPLIES TO
Oracle Hospitality OPERA 5 Property Services - Version 5.4.0 and later
[File Attachments and Email Attachments]
ISSUE
The goal of this document is to explain the differences between BLOB/BFILE configuration type in OPERA regarding Attachments (both profile and email).
DETAILS
There are two different attachment possibilities in OPERA: BLOB and BFILE.
BLOB file attachment storage will store attachments in the database and are subsequently not accessible outside of Opera.
BFILE file attachment storage will store attachments in a file on the database and are available from outside of Opera.
If a property were to choose BLOB, then the attachments are stored as BLOB and would require a BLOB schema to save the attachments into the database. The schema is specified with the General>Attachment Schema Name application setting and the setting value gets updated when the SMTP tool creates the BLOB schema.
If the property were to choose BFILE, then the attachments are stored as BFILE and would require an ORACLE directory to store the physical file. The directory name is specified with the General>Attachment Directory Name application setting.
When using BFILE:
APPLICATION PARAMETERS:
- GENERAL - Function - FILE ATTACHMENTS = Y
- GENERAL - Setting - ATTACHMENT DIRECTORY NAME = ATTACHMENTS
- GENERAL - Setting - ATTACHMENT SCHEMA NAME = BFILE
- GENERAL - Setting - ATTACHMENT STORAGE = BFILE
DIRECTORY:
Setup directory (folder structure) on Single server or DB server [This directory must be shared and accessible for all DB servers]
- D:\oracle\admin\OPERA\attachments
- D:\oracle\admin\OPERA\email_attachments
You can then run in SQLDeveloper the following (as SYS user) to see if the database is looking at the same directory path:
- Query:
SELECT OWNER, DIRECTORY_NAME, DIRECTORY_PATH FROM ALL_DIRECTORIES where directory_name like '%ATTACHMENT%';
- Results:
directory_name: ATTACHMENTS; directory_path: D:\oracle\oradata\attachments
directory_name: EMAIL_ATTACHMENT; directory_path: D:\oracle\oradata\email_attachments
Note: Notice that these directory paths are different than what we have in the physical directory structure, so we have to amend them accordingly either by creating the directories in Windows, or by changing the values in the database to match.
Check if the paths and folders exist on ALL Database Servers and if you have enough privileges on those folders as read/write.
Steps to Follow to Correct This if the Directory Paths are not the same:
- Create two folders ATTACHMENTS and EMAIL_ATTACHMENTS in the DB server D:\oracle\oradata\ (assuming these folders don't exist)
- Login to sqlplus as SYS user:
sqlplus "SYS/password@opera as SYSDBA" - Type the following commands
CREATE OR REPLACE DIRECTORY ATTACHMENTS AS 'D:\ORACLE\ORADATA\ATTACHMENTS';
- Once the above is over we need to grant the rights for the directories
GRANT READ, WRITE ON DIRECTORY EMAIL_ATTACHMENTS TO PUBLIC;
When using BLOB:
CREATING BLOB-ATTACHMENT SCHEMA:
- In the Application Server, access the Opera SMT tool via D:\micros\opera\tools
- Select Schema Management
- Log in as OPERA schema and Connect
- Select the Blob Data Schema
- Schema/User defaults to schema name with '_BLOB' [Password will default to same value as schema name] [For this example we will use OPERA_BLOB]
- Enter SYS Password
- Choose Oracle Alias
- Select Create Schema
- Define Table Space as either OPERA_DATA or have a DBA create a new data file for table space in this schema
- Select Create Schema
- Select Done
- Exit Opera SMT
In case a BLOB Schema already exists and you need to check its correct name, use the following query:
select username from sys.dba_users where username like '%BLOB%';
- Results (for our example):
OPERA_BLOB
APPLICATION PARAMETERS:
- GENERAL - Function - FILE ATTACHMENTS = Y
- GENERAL - Setting - ATTACHMENT DIRECTORY NAME = ATTACHMENTS [Not used when Attachment Storage is BLOB but still displayed. Ignore]
- GENERAL - Setting - ATTACHMENT SCHEMA NAME = Name of schema created [OPERA_BLOB] (can be named anything, this is just for example purposes)
- GENERAL - Setting - ATTACHMENT STORAGE = BLOB
Checking if there are any attachments in the BLOB Schema:
select count(*) from OPERA_BLOB.BLOB_ATTACHMENT;
Once an attachment has been associated with the profile, run the below queries and you will see the attachment within the LINKED_ATTACHMENTS table as well as a Y for hasattachments within the NAME_VIEW.
select attach_id,attachment_type,description,filename,insert_date, insert_user, resort, update_date,update_user,linked_to,link_id,attachment_size,attachment_origin,default_yn,attachment_location from linked_attachments where resort='XXXXX' and
Filename='XXXXX';
Ensure you replace the RESORT and FILENAME with your example(s)
select hasattachments from name_view where name_id='XXXXXX';
Ensure you replace NAME_ID with your example(s) name id.
select count(*) from name_view where hasattachments='Y' and active_yn='Y';
select name_id, hasattachments from name_view where hasattachments='Y' and active_yn='Y';
NOTE: Removing the attachment from a profile also removes the BFILE from the directory location.
MOVING ATTACHMENTS FROM BFILE TO BLOB:
You can migrate from BFILE to BLOB or vice versa
- In Utilities, under Property Config, select Change Attachment Storage
- Enter Password [Password is provided by SE/DEV via Bug, so sending a separate SR to OPERA L2 may be required for this matter]
- This utility checks the Current Storage Option and Attachments to Move
- Select OK [This migrates all attachments from DB to BLOB Schema or vice versa]
Note: Please ensure you have table space available in the data file being used by the [BLOB schema]
Note: Notice that Current Storage Options displayed as BFILE and Attachments to Move as 0