In this blog I will talk about how to access Oracle's PLSQL packages from Oracle database using Tomcat7 and DBPrism. I already posted a blog on Tomcat7 and JOPA that can be accessed here http://goo.gl/gxVDR. I am installing all this on a Linux x86-64 box. Please see the linux/Unix syntax in this blog.
Steps:
1- Download and install JDK (Use above JOPA URL for details).
2- Download and install Tomcat7 (Use above JOPA URL for details).
3- Download the dpls.war file from here http://prdownloads.sourceforge.net/dbprism/prism-2.1.2.2-production.zip?download
4- Once you click on the above link, download starts automatically. Please unzip the file and copy the dpls.war from /stage/bin/dpls.war to $CATALINA_HOME/webapps/ . Assuming that you downloaded the file to /stage and unzipped there.
5- Restart the Tomcat and the dpls will be deployed with no issue.
6- cd $CATALINA_HOME/webapps/dpls/WEB-INF directory and add your DADs and make it available. Please note that I will be using the same PL/SQL example that I used in the JOPA blog for which I have provided the URL above.
a- edit the prism.xconf and please pay special attention here you can copy the existing DADs like one for APEX. I will talk about APEX in a separate blog and will post the URL here later. However that is not sufficient so example here is I have a DAD called change and here is how the prism.xconf looks like
At the top of the file there is Database connection stringedit it like:
<variable name="demo.db" value="jdbc:oracle:thin:@amghost2.cup.com:1521:soa"/>
In the Category name general add your new DAD like :
<property name="alias" value="sample java cms cmsd demo.jsp travel apex secure change"/>
and the DAD like:
<category name="DAD_change">
<property name="dbusername" value="CHANGE"/>
<property name="dbpassword" value="ch11g"/>
<property name="connectString" value="${demo.db}"/>
<property name="useProxyUser" value="false"/>
<property name="defaultPage" value="hellotest"/>
<property name="toolkit" value="4x"/>
<property name="compat" value="8i"/>
<property name="dbcharset" value="utf-8"/>
<property name="clientcharset" value="utf-8"/>
<property name="docAccessPath" value="docs"/>
<property name="nls_lang" value="AMERICAN_AMERICA.AL32UTF8"/>
</category>
b- edit the web.xml file and add at the bottom the role you want to grant for the security or just to test I put "*". However this can not be production ready. You need to put the security role as there are for APEX mentioned there. Add like like:
<security-role>
<role-name>*</role-name>
</security-role>
Just above the </web-app>
7- Copy the ojdbc6.jar file to $CATALINA_HOME/lib and $CATALINA_HOME/webapps/dpls/WEB-INF/lib. This can be copied from the database server (where database is installed) to the application server(where tomcat is installed). This is at $ORACLE_HOME/jdbc/lib. If the DB server and APP server are on same machine then follow accordingly.
8- Restart the Tomcat. Please make sure that all the process dies and there is nothing listening on the port you started the Tomcat on using ps-ef and netstat -an combination.
9- Test -- Wallah !! Works. Here is my result. The URL taked the forms of http://<hostname>:8080/dpls/<DAD>/<PLSQL Package> like below:
Also there are many DPLS tests that you can do by enabling the scott account in the database and changing the samples and secure DAD the result will be like below from the URL http://<hostname>:8080/dpls
Hope it helps and avoid using the license products from Oracle like OHS and 10gAS or Weblogic or apache that oracle ships.
