I am learning Oracle Architecture for APEX setup. I posted similar post but I believe I did not explain myself well for that and I am trying to post it with different explanation.
Databases accounts setup
Let us say we have HR data residing under HR_DATA schema and ACCOUNTING data residing under ACCOUNTING_DATA schema.
We have HR_USER and ACCOUNTING_USER and it is required to use HR_USER to access HR_DATA and ACCOUNTING_USER to access ACCOUNTING_DATA. All of the above data and accounts are in one oracle database.
APEX setup
We have one APEX workspace called APEX_WORKSPACE and want to build APEX apps for both HR and ACCOUNTING.
APEX is setup with DAD called /DEFAULT to access APEX app. So, the URL to access to APEX app looks like the following.
https://hostname/ords/default/f?p=100
We have some CSS/JS files which are uploaded to Static Application Files and we are referencing them At page level with #APP_FILES#MAIN.CSS. So, far at this level, those CSS/JS files were loaded and work fine.
Our thought
We will create two DADs/Connection pools with based paths such as /HR and /ACCOUNTING where
/HR will use HR_USER as DB_USER and
/ACCOUNTING will use ACCOUNTING_USER as DB_USER.
Remark: we are trying to connect to a single database with multiple DADs/Connection pools.
Problem
We try to access APEX App with new Dads.
/DEFAULT DAD: https://hostname/ords/default/f?p=100 - it works fine, CSS/JS files loaded.
/HR DAD: https://hostname/ords/hr/f?p=100 - CSS/JS files – 404 not found error
/ACCOUNTING DAD: https://hostname/ords/accounting/f?p=100 - CSS/JS files – 404 not found error
When I tried to get the actual URL of exact CSS file for /HR and /ACCOUNTING, here are the followings.
https://hostname/ords/hr/apex_workspace/r/100/files/static/v9/MAIN.CSS https://hostname/ords/accounting/apex_workspace/r/100/files/static/v9/MAIN.CSS
If I replaced /HR or /ACCOUNTING with /DEFAULT for above URLs, it definitely works as followings.
https://hostname/ords/default/apex_workspace/r/100/files/static/v9/MAIN.CSS
Here are some alternatives we found on research:
• Using CDN: our company doesn’t really allow outbound connections.
• Loading working URL for CSS files to APEX APP property/interface. The approach with this is that every time we promote apex app from DEV to TEST or TEST to PROD, we have to change the hostname at URL links. Is there a way we can change CSS/JS file URL dynamically?
• Having Webserver setup internally for these CSS/JS files (might not be cost-effective).
Questions
Is there any fix we can do so new DADs can point to the right URL for the CSS file?
What is the connection between Oracle DAD path and APEX APP Static files?
Is this related to ORDS or APEX?
Is there any alternative solution which can fits to our needs which can be also cost-effective?
Thanks for your help! Any recommendation is appreciated.