How to run mod_plsql with Release 12 (unsupported)

Hi All,

We just finished the big upgrade project for R12.
One of the problem was how to migrate the mod_plsql procedures from 11i to R12.


With Release 12, mod_plsql is no longer Supported or recommended with eBiz and is disabled by default.   This is explained in the Blog article "Mod_plsql Not Supported in Release 12" (http://blogs.oracle.com/stevenChan/entry/new_whitepaper_mod_plsql_and_e_1)    

Some customers have not been able to re-implement their Release 11i mod_plsql custom code in OA Framework but still want to migrate to Release 12.  

In this case, the only official suggestion is to implement a standalone OracleAS 10g instance running mod_plsql which then interfaces to Rel 12.  This proposed solution is still considered a temporary workaround until the customer is able to re-implement customizations in OA Framework, which is the only Supported solution.     Option A below lists the steps to implement this option.  Option B is also listed but is not currently considered a good solution for Production environments as it introduces security risks.


If you still decided to do that, here the action plan:


A.  Install standalone AS10g instance, or utilise existing instance if available


This can be Infrastructure (SSO) or Middle Tier (Portal) installation as both types will have mod_plsql already installed.

1.  Create new DAD entry

a.  Login to Application Server Control and select the "HTTP_Server" link under "System Components"
b.  Select "Administration" link then "PL/SQL Properties"
c.  Under the DADs section you have the button to "Create" new DAD entry
d.  Select DAD type of "General"  and enter the following information
            DAD Name or Location         For example "/pls/r12vis"
            Username                             Enter "APPS"
            Password                              Enter APPS user password
            Connect String           For example, myRDBMSHost.domain.com:1521:VIS
            Connect String Format           SIDFormat
            Authentication Mode  Set this to the blank entry (no value)
e.  Click "Apply" button to save these changes

2.  Restart HTTP Server for the changes to take effect

3.  You can then test your change by using the URL as below
            http://myAS10gHost.domain.com:PORT/pls/r12vis/fnd_web.ping
           
This should return the FND_WEB ping output page showing the SYSDATE, DATABASE_VERSION, etc


B.  Re-introduce mod_plsql into Release 12 Apache listener


This is not a recommended option, as it introduces additional security risks to your environment so should only be used for TEST environments as a temporary workaround.

1.  Modify the $ORACLE_CONFIG_HOME/../10.1.3/Apache/Apache/conf/oracle_apache.conf file to add the following lines at the end of the file

            # begin custom
            include "<>/Apache/modplsql/conf/plsql.conf"
            # end custom

where <> is the explicit directory for the $IAS_ORACLE_HOME variable
*** Pay attention, this change will be remove after next autoconfig run

NOTE  - on Microsoft platforms this will be located in a directory location similar to "DRIVE:\<ORACLE_BASE>\inst\apps\<CONTEXT_MACHINE>\ora\10.1.3\Apache\Apache\conf"  For example "E:\oracle\inst\apps\VIS_TESTBOX\ora\10.1.3\Apache\Apache\conf\oracle_apache.conf"

2.  Update the $IAS_ORACLE_HOME/Apache/modplsql/conf/dads.conf file to add an appropriate entry for your Release 12 RDBMS

For Example
            <Location /pls/r12vis>
             SetHandler pls_handler
             Order deny,allow
             Allow from all
             AllowOverride None
             PlsqlDatabaseUsername         apps
             PlsqlDatabasePassword         apps
             PlsqlDatabaseConnectString    myRDBMSHost.domain.com:1521:VIS
             PlsqlDefaultPage              fnd.web.ping
            </Location>

3.  Use dadTool.pl to obfuscate the clear text passwords in the dads.conf file
            perl dadTool.pl -o

4.  Restart HTTP Server for these changes to take effect

5.  You can then test your change by using the URL as below
            http://myRel12Host.domain.com:PORT/pls/r12vis/fnd_web.ping

Additional Note :
Once implemented, you can only launch PLSQL directly via URL if the package/procedure is registered as trusted.  To register PLSLQ processes or procedures you can  go to System Admin Responsibility--> Security--> "Web PL/SQL" in which you can register your own PLSQL.  
Alternatively you can directly update the underlying table
       insert into fnd_enabled_plsql values ('PROCEDURE', 'MY_PROCEDURE_NAME', 'Y', sysdate, 1, sysdate, 1, 1,'');

Good Luck ...