How to Enable PL/SQL gateway (MOD_PLSQL) in Oracle EBS R12.2

Oracle disabled mod_plsql support by default in EBS R12.1, and much to my surprise that Oracle still put mod_plsql stuff in EBS 12.2.  Again it is not enabled by default, but making it work is not a difficult task at all.

All the changes mentioned below is in fs1, but it could be fs2 depends on which one is your running instance.

(1) Stop or start the Apache by adapachl.sh script.  No need to stop the Weblogic since this functionality is provided by Apache Mod.

(2) Identify the running oracle_apache.conf file under directory
$IAS_ORACLE_HOME/instances/EBS_web_[SID]_OHS1/config/OHS/EBS_web_[SID]

Add a line at the end of this file:
include ${ORACLE_INSTANCE}/config/${COMPONENT_TYPE}/${COMPONENT_NAME}/plsql.conf
The variables specified in config file will be resolved during runtime.  So no need to put the actual path in there.

(3) The file plsql.conf mentioned in this line is not exist (under the same directory of oracle_apache_conf just modified).  Make a copy of this file from directory
$ORACLE_HOME/Apache/modplsql/conf

Modify the lines similar as follows:

#Window 
LoadModule plsql_module "${ORACLE_HOME}/ohs/modules/mod_plsql.dll"
#Linux/Unix
LoadModule plsql_module "${ORACLE_HOME}/ohs/modules/modplsql.so"

# Turn on logging for debug only!!
PlsqlLogEnable on

PlsqlLogDirectory ${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}

include "${ORACLE_INSTANCE}/config/${COMPONENT_TYPE}/${COMPONENT_NAME}/mod_plsql/dads.conf"

include "${ORACLE_INSTANCE}/config/${COMPONENT_TYPE}/${COMPONENT_NAME}/mod_plsql/cache.conf"

(4) The file dads.conf does exist but the content is empty.  So add the mod_plsql location for your instance

<Location /pls/[SID] >

SetHandler pls_handler

Order deny,allow

Allow from all

AllowOverride None

PlsqlDatabaseUsername apps

PlsqlDatabasePassword apps

PlsqlDatabaseConnectString localhost:1521:[SID] ServiceNameFormat

PlsqlAuthenticationMode Basic

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8

PlsqlRequestValidationFunction XX_MOD_PLSQL_CHECK

PlsqlErrorStyle DebugStyle

<Location >

(5) The rest of the setup will be identical to R12.1
http://symplik.blogspot.com/2013/10/how-to-enable-plsql-gateway-in-r12.html

How to Enable PL/SQL gateway (MOD_PLSQL) in Oracle EBS R12.1

If your company is planning to upgrade you 11i instance to R12.1, then please be aware that the PL/SQL gateway (mod_plsql) is no longer available by default in R12.1.   However, it doesn't mean that it cannot be activated again. Oracle highly recommends you use other programs to replace it by APEX, OA Framework, or ADF, but if your company has invested huge amount of time and effect in PL/SQL gateway code, and the code is working very well, then you could keep the code and let it run, similar in 11i environment.

Here are the steps:

Create the following directories:
$INST_TOP/ora/10.1.3/Apache/modplsql/cache
$INST_TOP/ora/10.1.3/Apache/modplsql/conf
$INST_TOP/ora/10.1.3/Apache/modplsql/logs

Create file $INST_TOP/ora/10.1.3/Apache/modplsql/conf/plsql.conf, with content similar the following. Substitute the environment variables and values in [] for your environment.

# load required module (Windows)
LoadModule plsql_module %IAS_ORACLE_HOME%/bin/modplsql.dll
# load required module (*nix)
LoadModule plsql_module $IAS_ORACLE_HOME/Apache/modplsql/bin/modplsql.so

#Directives specify for modplsql
PlsqlLogEnable on
PlsqlLogDirectory $INST_TOP/ora/10.1.3/Apache/modplsql/logs
PlsqlCacheEnable On
PlsqlCacheDirectory $INST_TOP/ora/10.1.3/Apache/modplsql/cache
PlsqlCacheTotalSize 20971520
PlsqlCacheMaxSize 1048576
PlsqlCacheMaxAge 30
PlsqlCacheCleanupTime Everyday 00:00

<Location /pls/[SID] >
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername apps
PlsqlDatabasePassword @BSvYt+H8Fv3C4YjspMEOP9k=
PlsqlDatabaseConnectString [DB Server]:[Port]:[SID] ServiceNameFormat
PlsqlAuthenticationMode Basic
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlRequestValidationFunction XX_MOD_PLSQL_CHECK
PlsqlErrorStyle DebugStyle
</Location>

You can use $IAS_ORACLE_HOME/Apache/modplsql/conf/dadobf to generate the obfuscated password.
Syntax: dadobf [password]

These folders and files will be gone when you do the cloning that all the files in INST_TOP directory will be re-generated.  So make sure you copy these folders and files to the clone instance after running adcfgclone.

For other possible parameters, you can check out this link
http://docs.oracle.com/cd/E23943_01/web.1111/e10144/under_mods.htm

Open $INST_TOP/ora/10.1.3/Apache/Apache/conf/oracle_apache.conf, search and take out the comment of this line:
include "$INST_TOP/ora/10.1.3/Apache/modplsql/conf/plsql.conf"

The above change is not permanent. If one run autoconfig, this config file will be re-generated, hence the changes will be reverted. So you need to make the changes in the template files as well.

Generate a template report:
$AD_TOP/bin/adtmplreport.sh contextfile=$CONTEXT_FILE

Read the log output, search for TARGET FILE: ...oracle_apache_conf
and then you can find out the template file:
$FND_TOP/admin/template/oracle_apache_conf_1013.tmp

Open this template file and take out the comment of line line include ...plsql.conf

Under System Administrator Responsibility -> Security -> Web PL/SQL (FNDSCPLS form)
Even though Oracle told you this Form is obsolete, this form will still used for to control which function/procedure/package to be able called in Web PL/SQL gateway.

First of all, you must enable the root function which invoke the PL/SQL gateway code ORACLESSWA

As specified in the Apache directive PlsqlRequestValidationFunction, a security function is needed to limit the usage of PL/SQL gatway code:

CREATE OR REPLACE function APPS.XX_MOD_PLSQL_CHECK(procedure_name varchar2) return boolean is
var_result varchar2(1);
begin
var_result := FND_WEB_CONFIG.CHECK_ENABLED(procedure_name);
if var_result='Y' then
return true;
else
return false;
end if;
end;
/

Form Function for PL/SQL gateway code must have a type of "SSWA plsql function"*, or "SSWA plsql function that opens a new window (Kiosk Mode)" and the HTML Call is the Stored Procedure name.

*In R12.2, the type "SSWA plsql function" is obsoleted.