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.