Tag: APEX

SSL Enabled Hosting for Oracle ORDS on Oracle EBS Server

On By windows7bugsIn APEX, Linux, Oracle Applications(R12), Oracle relatedLeave a comment

One of the major concerns that we had while upgrading Oracle EBS R12 version from 12.2.10 to 12.2.14 was how to bring the Oracle ORDS instance under the same SSL hood as we didn’t have a plan to have another server dedicated for ORDS. Our current setup is like below, single instance of Oracle EBS R12 12.2.14 SSL enabled and the ORDS instance hosted from the same server using Apache-Tomcat, that listens to default 8080 port.

The business requirement is pretty straight forward. Develop using APEX, access it from the EBS environment, doesn’t mean we are not going to have standalone APEX applications as extensions for EBS.

Now, comes the real issue. How to bring both instances to the same SSL hood? For example, EBS instance is accessed from the URL https://hostname & we must ensure that the ORDS instance also should be accessible from the same hostname like https://hostname/ords

Please note, none of the below hacks are advisable for a PRODUCT environment. While the hack perfectly fits the TESTING environments, the resources for EBS could be severely compromised when the APEX instance becomes hungry for more resources in the form of processing and memory. Follow the Oracle recommendations always for hosting ORDS from a different server. I will post comprehensive suggestions compiled by AI by the end of this post. This hack was tested against an instance of Oracle EBS R12 12.2.14 & should work with R12 12.2.x versions. Point to interest, version 12.2.14 is the initial version of R12 that tightly integrates APEX, hence do not expect your applications start enjoying the same fruits, if you are on a previous release. Regardless, the reverse proxy method should work.

Let us hack

As application manager user, shutdown the application instance and run the below command

grep "ssl.conf" $FND_TOP/admin/driver/fndtmpl.drv

This should provide you an output like the below

      fnd admin/template oracle_apache_ssl_conf_FMW.tmp INSTE8 <s_ohs_instance_loc>/config/OHS/<s_ohs_component> oracle_apache_ssl.conf 600

Once we have the template name, for this case, “oracle_apache_ssl_conf_FMW.tmp”, we should copy this template to $FND_TOP/admin/template/custom folder. If there is no custom folder, create one. Please note, “custom” is the only allowed name and case sensitive. Always read the header area of the template, that will help you to understand whether the template will be merged during the next autoconfig run.

cp $FND_TOP/admin/template/oracle_apache_ssl_conf_FMW.tmp $FND_TOP/admin/template/custom/oracle_apache_ssl_conf_FMW.tmp

Using your favorite text editor, open up the template file from custom folder and add the following entries by the dead end of the file

<IfModule mod_proxy.c>
    ProxyRequests Off
    ProxyPreserveHost On
    ProxyPass /ords http://fullyqualifieddomainname:8080/ords
    ProxyPassReverse /ords http://fullyqualifieddomainname:8080/ords
    ProxyPass /i http://fullyqualifieddomainname:8080/i
    ProxyPassReverse /i http://fullyqualifieddomainname:8080/i
    <Location /ords>
        RequestHeader set X-Forwarded-Proto "https"
        RequestHeader set X-Forwarded-Port "443"
    </Location>
</IfModule>

Here the fully qualified domain name should be replaced with your hostname, for example “apps.example.com”

Now run the below commands

$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start
perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

It’s time to run “autoconfig“. Please make sure that all the above exercises were performed on the run file edition environment.

Once the autoconfig completes “successfully”, quickly ensure the OHS instance has the “oracle_apache_ssl.conf” file amended with custom template entries.

grep "ProxyPass /ords" $FMW_HOME/webtier/instances/*/config/OHS/*/oracle_apache_ssl.conf

If you see a result like the below

ProxyPass /ords http://fullyqualifieddomainname:8080/ords

That means your custom template was merged and you can proceed to next configuration modification. This time we will be making a change to Apache-Tomcat web server. Switch to your Apache-tomcat installation folder, for example ‘/u01/apache-tomcat/conf’, make a backup for the file server.xml. Now, edit the server.xml file and replace the block that looks like this

<Connector port="8080" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="8443" />

with the below

<Connector port="8080" protocol="HTTP/1.1"
           connectionTimeout="20000"
           proxyName="fullyqualifieddomainname"
           proxyPort="443"
           scheme="https"
           secure="true"
           SSLEnabled="false"
           URIEncoding="UTF-8" />

Save the configuration file. Start EBS application tier and Apache-Tomcat server sequentially. You should able to access your ORDS instance by calling https://hostname/ords now. In addition you may able to make these entries using enterprise manager OHS plug-ins.

By bringing the ORDS instance under the same SSL hood, you are going to avoid multiple access issues, one of them being the “mixed content” error when the EBS tries to access the APEX applications using ORDS that is HTTP. Although this hack is to survive future autoconfig and patching cycles, NOT AT ALL recommended.

Please consider the suggestions compiled by one of the major AI agents, as listed below.

Prompt

Currently we are hosting both ORACLE EBS R12 and APEX under the same hostname. Can you find Oracle’s recommendations for independent hosts for each product? Further help me to understand the implication incase if we continue with the current setup, achieved by modifying ssl.conf using template for reverse proxy. Maximize your explanations, recommendations and assessment.

AI response

Oracle’s official guidance on deploying E-Business Suite (EBS R12) and Oracle Application Express (APEX) emphasizes separation of concerns in architecture. Let’s break this down into recommendations, implications, and strategic assessment.

🔹 Oracle’s Recommendations

  • Independent Middle Tiers: Oracle recommends deploying APEX and EBS on separate middle-tier hosts.
    • EBS R12 relies on Oracle WebLogic + Forms/Servlets stack.
    • APEX runs on Oracle REST Data Services (ORDS), typically deployed on a standalone WebLogic, Tomcat, or Oracle HTTP Server.
  • Reverse Proxy Usage: Oracle supports reverse proxying (via Oracle HTTP Server or Apache) but recommends distinct hostnames or subdomains (e.g., ebs.company.com vs. apex.company.com) for clarity, isolation, and SSL management.
  • Security & Maintainability: Oracle’s technical papers highlight that mixing workloads under one hostname complicates SSL termination, cookie/session management, and audit compliance.

🔹 Implications of Current Setup (Same Hostname via ssl.conf Reverse Proxy)

AreaRisk/Implication
SecurityShared hostname increases risk of cross-application cookie leakage, session hijacking, and difficulty in applying distinct SSL/TLS policies.
Audit & ComplianceRegulatory audits (SOX, GDPR, VAT compliance) often require clear segregation of application boundaries. Mixing under one hostname weakens traceability.
Operational ComplexityReverse proxy rules in ssl.conf must be carefully maintained. Any misconfiguration can expose APEX apps through EBS paths or vice versa.
PerformanceBoth stacks compete for network and SSL termination resources. Troubleshooting latency or bottlenecks becomes harder.
Patching & UpgradesEBS patches (WebLogic, Forms) and APEX/ORDS upgrades have different cycles. Sharing a hostname risks downtime overlap and dependency conflicts.
Disaster RecoveryHostname-level coupling complicates failover. Independent hosts allow selective DR strategies (e.g., APEX-only failover without impacting EBS).

🔹 Strategic Assessment & Recommendations

  1. Move Toward Independent Hosts
    • Assign separate DNS names and SSL certificates.
    • Example: ebs.company.com → EBS R12 stack; apex.company.com → ORDS/APEX stack.
    • This aligns with Oracle’s recommended middle-tier deployment architecture.
  2. If You Continue Current Setup
    • Harden reverse proxy rules: ensure strict path-based routing (/apex/* vs /OA_HTML/*).
    • Isolate cookies: configure SameSite, Secure, and HttpOnly flags to prevent leakage.
    • Maintain separate SSL contexts within ssl.conf for each app.
    • Document routing logic for audit purposes.
  3. Future-Proofing
  • Independent hosts simplify cloud migration (OCI, AWS).
  • Easier to scale APEX independently (APEX often grows faster with custom apps).
  • Supports hybrid DR strategies and microservices-style extension of EBS.

🔹 My Assessment

Continuing with the same hostname setup is technically feasible but audit-unfriendly, harder to maintain, and risk-prone. Oracle’s stance is clear: separate middle tiers and hostnames provide cleaner architecture, stronger compliance, and operational resilience.

How to Properly Deregister Custom Tops in Oracle EBS manually

On By windows7bugsIn APEX, NOT a DBALeave a comment

I’ve created a custom top for APEX integration with Oracle EBS R12 12.2.10 recently & by mistake choose 5001 instead of 50000+ for application id. Then I wanted to do it correct and proceeded with the documented procedures.

perl adDeregisterCustomProd.pl

This script errored out as the application id was less than 20000 (unfortunately, I didn’t copy the exact error messages). So, started a session with gemini3, that I recently subscribed. Well, although the AI helped to a great extend, finally I had to refer a blog that helped me to resolve the related issues & finally come up with a clean context file.

Lets’ see how it works. Shutdown the application instance & start deleting the custom top specific entries from related tables. As user “apps” 

DELETE FROM fnd_product_installations WHERE application_id = 5001
/
DELETE FROM fnd_application_tl WHERE application_id = 5001
/
DELETE FROM fnd_application WHERE application_id = 5001
/
DELETE FROM fnd_oracle_userid WHERE oracle_id = 5001
/
commit
/

Now, delete the entry for your custom application from fnd_oam_context_custom, a step that shouldn’t be missed. Use the select statement to find out specific information about your custom application

DELETE FROM fnd_oam_context_custom WHERE upper(oa_var) LIKE '%XXAPEX%'
/
COMMIT
/

Now, as Oracle user “system” you need to drop the custom schema. I wanted to drop the custom schema “XXAPEX”

SELECT editions_enabled FROM dba_users WHERE username = 'XXAPEX'
/
DROP USER XXAPEX CASCADE
/

Now, remove the physical path for the custom application

rm -rf $XXAPEX_TOP

Usually, one should proceed with auto config and do an adop fs_clone to replicate the changes while using dual file systems. For me, after auto config, I was still seeing the custom top entry within the context file & no questions will help the AI to pinpoint my issue. Suggestions after suggestion auto config recreated the entry for the custom top inside the context file.

I gave the AI some rest time & started scavenging through blogs & came across the below one

How to Deregister Custom Top in EBS?

Here the blogger is dealing with a situation that was raised while using “adDeregisterCustomProd.pl”. Fortunately, the blogger has copied the entire log of the activities and I found a very interesting entry

XXAR entry in topfile.txt is removed succesfully ..!

Well, that was it! I hurried to AI once again and asked for the physical location for the topfile.txt & removed the custom top entry from the list. Manually deleted the line from context file & ran auto config once again on “run” environment. Once the config completed, there were no more entries for the custom application to be found.

As we are using SSL for the EBS instance, restarted the application and proceeded with fs_clone. I am noting the fs_clone issues while using SSL to avoid another post. If you initiate the fs_clone without the application server being up using SSL, you are prone to hit the error

[user: applmgr] [VirtualHost: main] (13)Permission denied:  make_sock: could not bind to address [::]:443

The solution is simple, start the application server if it was shutdown & try fs_clone once again.

APEX Integration with Oracle EBS R12 12.2

On By windows7bugsIn APEX, Oracle Database 19cLeave a comment

Updated on 3rd February 2025 (Added additional information about running apex_ebs_setup.sql)

Updated on 29th January 2025

If you are using Oracle EBS R12 12.2.14 already, you may refer this document. It has much straight forward approaches documented.

Today we will see how to integrate Oracle APEX with an existing Oracle EBS R12 12.2.10 instance that is on top of Oracle database 19c. Recently I’ve posted a thread about how to install Apex on Linux and specified that it was done against EBS database. You can use the same thread for installing APEX, ORDS and Apache Tomcat for hosting.

Oracle provides a document that was last reviewed in 2019 with cryptic instructions to setup a custom schema for the APEX integration & gravely misses environments like ours where multitenant database is used! As usual I am not making any claims that this is the “certificated*” way of setting up APEX for EBS. This is just another attempt to connect the dots and do stuffs the right way after multiple attempts.

Oracle documentation instructs to create a new schema XX_APEX as part of setting up new workplace. This process creates a separate tablespace for the schema and this instruction contradicts with the instruction to setup the same as a custom application. EBS R12 12.2 “AD Splice” doesn’t respect custom tablespace definitions and defaults the tablespaces to APPS_TS_TX_DATA and APPS_TS_TX_IDX for custom schemas. So how do we circumvent this issue? Simple answer. Create a custom application using “AD Splice” bearing the name XX_APEX to meet the documentation preferences & use it with APEX workplace later. One of the best detailed instructions for creating a custom application using “AD Splice” could be found here

For additional references you may refer the below also.

I followed RishOraDev’s instructions and the custom application “XX_APEX” was created without any issues.

Once the custom application creation is over, we can proceed with creating a new workspace for APEX applications.

Logon to your APEX portal as Admin and proceed with create workspace.

Next step, choose Yes for “Re-use existing schema” and pick XX_APEX that you have created earlier.

Provide a meaningful username for Administrator. I am using Admin & this shouldn’t be confused with the APEX admin account. Select an existing Schema will grant additional rights to the schema that are essential for APEX.

You will have to change the password during the initial login. So use a temporary password and reserve your highly secret password for your permanent password :)

That’s it. A new workspace for APEX has been created!

You can execute apex_ebs_setup.sql and other supplementary scripts now to setup the example scenarios described in the white paper. If you try to execute apex_ebs_setup.sql prior setting up the schema for the workspace, the last statement will fail as the custom schema will not have CREATE VIEW rights. Grant the right to schema for such cases.

Now you can proceed with login as XX_APEX workspace admin and continue with defining developers accounts so that your team could start building applications. Please refer the whitepaper for understanding what you should and shouldn’t do with APEX while it is integrated with EBS.

There are few scripts offered by Oracle for APEX environment and one might feel tempted to try them, I cannot blame that urge. However, please note the whitepaper was never meant for a multitenant database and the instructions were clearly for 11G. If your EBS is already migrated to a multitenant setup, please make sure that you will be running the scripts as APPS user. Switch to APPLMGR, source the environment. Sample as below

[applmgr@erp-test ebs_apex_scripts]$ sqlplus apps/apps @apex_ebs_setup.sql

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Feb 3 18:22:19 2025

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

.  ____   ____           ____        ____
. /    \ |    \   /\    /     |     /
.|      ||    /  /  \  |      |    |
.|      ||---    ----  |      |    |--
.|      ||   \  /    \ |      |    |
. \____/ |    \/      \ \____ |____ \____
.
.
. Application Express E*Business Suite Integration Example Setup.
.................................................................
.

FOO3
--------------------------------------
apex_ebs_setup_2025-02-03_18-22-19.log

.
.  << Enter Criteria >>
.
.   Version of EBS [12.2]:
.   EBS APPS Username [APPS]:
.   Password for EBS APPS Username:
.   APEX Schema Name [XX_APEX]:
.   Password for APEX Schema:
.
.
... Ebnable editions in APEX Schema
.

PL/SQL procedure successfully completed.

.
... Conect to the APPS Schema
.
Connected.
.
... Create views for use in APEX Applications
.

View created.


Grant succeeded.


Grant succeeded.

.
... Create Sample APIs to be called from APEX applications
.

Package created.


Package body created.

No errors.

Grant succeeded.

.
... Create APEX Global package for use with EBS Responsibilities
.

Package created.


Package body created.

No errors.

Grant succeeded.

.
... Conect to the XX_APEX Schema
.
Connected.
.
... Create APEX View referencing the APPS View
.

View created.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Forget about the hacks to connect to PDB as sys in order to run the scripts :)

Have questions? let me know through comments.

*Certificated is a term that I made to name and shame people who always defend their ignorance by claiming whatever they cannot do or answer as not certified.

Oracle APEX Summit sample application

On By windows7bugsIn APEX, Oracle Database 19c, Oracle FormsLeave a comment

Recently, after setting up APEX for the nth time, I wanted to give the stack a try. I’ve been developing around Oracle technologies for more than 20 years and I felt it was the time. After some serious searches, I came across an Oracle blog that was discussing about migrating from Oracle Forms & APEX, which I was looking for.

[Forms to APEX] Creating a Migration Project (oracle.com)

By the end of the article, author had given a link to the sample application “Summit” that was directly migrated from Oracle Forms. Currently, APEX doesn’t support direct migrations from anymore. Regardless, the example project was truly informative. I suggest you to create a document using the explanations provide with the blog, which could help you with many details later.

As on 28th November 2024, against the feedback, Oracle APEX team has updated the download section with a latest export & it works without any issues once after imported. Hence, majority of the bashing has been removed from this post ;)

Although you can import the application database objects to an existing schema, it is better to be on a schema dedicated for the application so that you don’t have to scavenge through dozens of objects to identify the app specific ones.

CREATE USER SUMMIT IDENTIFIED BY SUMMIT
DEFAULT TABLESPACE EXAMPLE
TEMPORARY TABLESPACE TEMP
/
GRANT DBA TO SUMMIT
/
GRANT CONNECT TO SUMMIT
/
GRANT RESOURCE TO SUMMIT
/

Newly created “Summit” schema prior the demo application installation.

Once you created the schema, add it to your APEX workspace and import the APEX sample application that you downloaded. It is a pretty straight forward thing and when the installer asks you to install the additional components, proceed.

Do not forget to change the Parsing Schema in case if your workspace is associated with multiple schemas.

Proceed with the installation. It takes just few seconds and must install the supporting Objects (that are tables, procedures, sequences, functions etc).

Run the application and you should be welcomed with a beautiful dashboard!