JRockit R28 StartUp Issues - WLS 10.3.6

Recently, I configured a WebCenter Portal PS5 Environment having following configuration:

Linux 2.6.32-300.41.1.el5uek x86_64
Weblogic 10.3.6
WebCenter Portal 11.1.1.6.0 BP5
Oracle JRockit(R) (build R28.2.7-7-155314-1.6.0_45-20130329-0641-linux-x86_64)

And when I tried to start Weblogic Server, I found following error/warning messages in the Server log.

[WARN ] Use of -Djrockit.optfile is deprecated and discouraged.
[ERROR][osal   ] Unable to discover information about /mnt/hugepages
[WARN ][memory ] Could not acquire large pages for Java heap.
[WARN ][memory ] Falling back to normal page size.

First, I tried to resolve -Djrockit.optfile warning. To resolve this, I followed below steps.

[WARN ] Use of -Djrockit.optfile is deprecated and discouraged.

1. Open <DOMAIN_HOME>/bin/setDomainEnv.sh
2. Search for property -Djrockit.optfile. You will find it in EXTRA_JAVA_PROPERTIES
3. Replace  existing
-Djrockit.optfile=${COMMON_COMPONENTS_HOME}/modules/oracle.jrf_11.1.1/jrocket_optfile.txt
with
-XX:+UnlockDiagnosticVMOptions -XX:OptFile=${COMMON_COMPONENTS_HOME}/modules/oracle.jrf_11.1.1/jrocket_optfile.txt and Save the file.
4. Open ${COMMON_COMPONENTS_HOME}/modules/oracle.jrf_11.1.1/jrocket_optfile.txt file and change the content as follows and save the file.

Exisitng:
- oracle/xquery/parser/XPathTokenManager.*
- oracle/adf/view/rich/render/RichRenderer.createSubId*
- oracle/adf/view/rich/render/RichRenderer.createClientSubId*

Convert to:
{
match: ["oracle.xquery.parser.XPathTokenManager.*",
"oracle.adf.view.rich.render.RichRenderer.createSubId*",
"oracle.adf.view.rich.render.RichRenderer.createClientSubId*"],
enable: jit
}

Note: content of jrocket_optfile.txt varies depends on environment.

Now let us take a look into error/warning about hugepages.

[ERROR][osal   ] Unable to discover information about /mnt/hugepages
[WARN ][memory ] Could not acquire large pages for Java heap.
[WARN ][memory ] Falling back to normal page size.

This error will occur due to -XX:+UseLargePagesForHeap added to Weblogic Startup command. We can handle this error in two ways.

Approach 1: Comment/remove -XX:+UseLargePagesForHeap from setDomainEnv.sh script.
if [ "${JAVA_VENDOR}" = "Oracle" ] ; then
        EXTRA_JAVA_PROPERTIES="-XX:+UseLargePagesForHeap ${EXTRA_JAVA_PROPERTIES}"
        export EXTRA_JAVA_PROPERTIES
fi

Approach 2: Configure HugePages on Linux  and increase the number of HugePages on the OS using "sysctl -w vm.nr_hugepages" command

Refer following documentation to configure HugePages on Linux.

Kernel Documentation
Note 361468.1: HugePages on Oracle Linux 64-bit
Note 361323.1: HugePages on Linux: What It Is... and What It Is Not...

Refresh af:OutputText value using JavaScript

Applicable to FMW 11.1.1.6.0

Y'day, I was trying to write a Java Script to change the Page header(af:outputText) based on link(af:goLink) clicked by user. I tried following approaches to achieve it.

Approach 1:
JSPX page source: 
<af:iterator>
  ...........................
   <af:goLink text="#{side_menu_item.title}" id="gk1"
              destination="#{side_menu_item.goLinkPrettyUrl}"
              targetFrame="#{side_menu_item.attributes['Target']}">
         <af:clientListener type="click" method="changeHeader"/>
         <af:clientAttribute name="Title" value="#{side_menu_item.title}"/>
         <af:serverListener type="setHeader" method="#{pageFlowScope.PortalBean.setNavHeader}"/>
   </af:goLink>
   ...........................
</af:iterator>

Note: No need to set clientComponent=true , if you are adding af:clientListener

   <af:outputText value="#{pageFlowScope.PortalBean.navHeader}" id="pt_ot2"
                  clientComponent="true"
                  binding="#{pageFlowScope.PortalBean.navHeaderText}"
                  noWrap="true">
   </af:outputText>

Java Script:
    function changeHeader(evt) {
       var goLink = evt.getSource();
       var title = goLink.getProperty("Title");
       AdfCustomEvent.queue(goLink,"setHeader",{param1:title},true);
    }

Managed Bean Method: (This server side method will be called using af:serverListener)
public void setNavHeader(ClientEvent ce) {
        String param = (String)ce.getParameters().get("param1");
        setNavHeader(param);
        AdfFacesContext.getCurrentInstance().addPartialTarget(getNavHeaderText());      
    }

    public void setNavHeader(String navHeader) {
        this.navHeader = navHeader;
    }

    public String getNavHeader() {
        return navHeader;
    }

Approach 2:
Later, I thought to set the <af:outputText> using only JavaScript instead of calling Server side bean.
function changeHeader(evt) {
       var goLink = evt.getSource();
       var title = goLink.getProperty("Title");
       var headerText = goLink.findComponent("pt_ot2");
       headerText.setValue(title);
    }

Note: In this case, the component can be find using its id.

Approach 3:
Again, I tried another approach to set the <af:outputText>.
function changeHeader(evt) {
       var goLink = evt.getSource();
       var title = goLink.getProperty("Title");
       document.getElementById('pt1:pt_ot2').value=title;
    }

Note: In this case, the component can be find using id generated on client side(browser). You can get the client side id by accessing page source.

When I run the page, it worked fine in IE 9, but not in Mozilla(17.0.11) & Chrome(31.0.1650.57 m) browsers. Then I made small change to javascript, which worked across all browsers.

function changeHeader(evt) {
       var goLink = evt.getSource();
       var title = goLink.getProperty("Title");
       document.getElementById('pt1:pt_ot2').innerHTML=title;
    }

Note: If you are using af:GoLink, it will refresh the complete global container. Hence the <af:outputText> value will be set to NULL. In this case, you can try to use browser sessionStorage to store values even after browser refresh.

    function storeValue(key, value) {
        if (window.sessionStorage) {
            sessionStorage.setItem(key, value);
        }
    }

    function getStoredValue(key) {
        if (window.sessionStorage) {
            return sessionStorage.getItem(key);
        }
    }

storeValue('header', title);
getStoredValue('header');

WebCenter: Issue with /tmp directory permission

Applicable to WebCenter 11.1.1.6.0

Recently, I was not able to access Weblogic server console and when I checked the Server logs, found  below mentioned error domain logs.

Error 1: found in WebCenter Collaboration Server log & WLS Domain log
<Dec 3, 2013 12:53:11 PM CST> <Error> <com.jivesoftware.Jive-ERR> <BEA-000000> <Permission denied java.io.IOException: Permission denied
        at java.io.UnixFileSystem.createFileExclusively(Native Method)
        at java.io.File.createNewFile(File.java:883)
        at org.apache.lucene.store.FSDirectory$1.obtain(FSDirectory.java:363)
        at org.apache.lucene.store.Lock.obtain(Lock.java:51)
        at org.apache.lucene.store.Lock$With.run(Lock.java:98)
        at org.apache.lucene.index.IndexReader.open(IndexReader.java:141)
        at org.apache.lucene.index.IndexReader.open(IndexReader.java:136)
        at com.jivesoftware.forum.database.DbSearchManager.getReader(DbSearchManager.java:1169)
        at com.jivesoftware.forum.database.DbSearchManager$UpdateTask.run(DbSearchManager.java:1662)

The error stack did not provide any details about folder/file path and was not able to identify the root cause for this error. When I executed grep command, all the Admin & Managed server processes are showing status as Running, but not responding.

And when I tried to shutdown the Managed/Admin servers using stopManagedWebLogic.sh/stopWebLogic.sh, I got following error in the CLI console.

Error 2:
Initializing WebLogic Scripting Tool (WLST) ...
Jython scans all the jar files it can find at first startup. Depending on the system, this process may take a few minutes to complete, and WLST may not return a prompt right away.
*sys-package-mgr*: can't create package cache dir, '/tmpWLSTTemp<user>/packages'

Cause: Some how the /tmp folder permissions changed to 755.

Fix: executed chmod 1777 /tmp to reset permissions. (1 refers to Sticky bit)

After changing /tmp folder permissions, both Error 1 & 2 resolved.

Enable APEX application to load in Frames

Applicable to APEX Version 4.2 and FMW 11.1.1.6.0

Recently, I tried to load APEX application in ADF/WebCenter application page region using ADF faces component af:InlineFrame. The result is empty page, the af:InlineFrame component was not able to render the APEX application page.

After lot of trails, Luckily I walk through APEX user guide and understood the security constraints on APEX page to avoid "ClickJacking" attacks.

After changing Browser Security settings to allow Embed In Frames(as mentioned in user guide), the af:InlineFrame component was able to render the APEX application page.

Steps to change Browser Security settings in APEX:
1. Login to APEX main page
2. Click on Application Builder --> Application

3. Go to Shared Components and Click on Security Attributes

4. In Security Attributes page, go to Browser Security section. Select Allow for Embed in Frames option.

WebCenter Content - Admin Applets Error

Applicable to 11g PS5:

Recently when I tried to access WebCenter Content Server Admin Applets, I got following error.

Error: 
Unable to start the application ConfigMan. Failed to initialize. Runtime error:
java.security.AccessControlException: access denied
("java.util.PropertyPermission" "user.timezone" "write")

Cause:
Admin applets are not opening due to browser java plugin update to version 1.7.0_45.

Solution:
Download and Install latest MLR patch for your Oracle WebCenter Content version from Oracle Support. Refer to Oracle Support Document#1592799.1 for more details.

If you are facing the issue even after applying the patch, clear the cache on the browser and try again.

Weblogic Startup Error : command not found

If you get following error message while starting Weblogic Admin server (or) Manager servers using startWebLogic.sh (or) startManagedWebLogic.sh, create a boot.properties file under security folder of each server.

Error:

<Security> <BEA-090065> <Getting boot identity from user.>

Enter username to boot WebLogic server:weblogic

-bash: weblogic: command not found

Fix:

1. Go to <DOMAIN_HOME>/servers/<ServerName>/security directory(If security folder is not available, create a folder)

 

2. create a file named boot.properties

Unix: vi boot.properties

3 Add username & password to the file and save it.

username=weblogic

password=welcome

4. Try executing scripts 

nohup ./startWebLogic.sh &

(or) 

nohup ./startManagedWebLogic.sh ManagedServer1 &

5. Once server started, the boot.properties file content will be encrypted.

WC Spaces Startup Error: Cannot open Web producer connection

Applicable to 11g PS5:

If you are facing following error message in log files during WebCenter Spaces managed server start up, check whether WebCenter Portlet managed server is already up and running.

<Error> <oracle.portlet.client> <WCS-40144> <Import of producer /oracle/adf/portlet/export/oracle/adf/portlet/wc-WebClipping_51f3fd31-8a45-406c-ba3d-81757d93f702 failed. The failure has been stored for possible retry.
oracle.portlet.client.container.PortletConnectionException: Cannot open Web producer connection.
        at oracle.portlet.client.connection.web.WebProducerConnection.open(WebProducerConnection.java:373)
        at oracle.portlet.client.techimpl.web.WebClientImpl.registerProducer(WebClientImpl.java:519)

To avoid this error, make sure that you have started Collaboration, Portlet, Utilities managed servers before starting Spaces managed server.

ORA-01005: null password given

Applicable to 11g PS5(11.1.1.6.0)

When we try to access ADF application (developed using database objects), it throws following error even though the data source connection created in weblogic server has no issues.

>>oracle.jbo.DMLException: JBO-26061: Error while opening JDBC connection

>>java.sql.SQLException: ORA-01005: null password given; logon denied

To resolve this error, add -Djps.app.credential.overwrite.allowed=true to EXTRA_JAVA_PROPERTIES entry in the setDomainEnv.sh file located in <MW_HOME>/user_projects/domains/<domain>/bin and restart the server.

ADF Managed Server Issue - java.lang.ClassNotFoundException

Applicable to 11g PS5(11.1.1.6.0)

After installing ADF runtime libraries on top of  Weblogic 10.3.6 base installation, extend the Weblogic domain to contain Admin Server and Managed Server (to deploy ADF applications).

Login to EM console --> Go to Weblogic Domain --> <Domain> --> Click on ADF Managed Server

To complete the ADF installation on Managed server, click on Apply JRF(Java Required Files) Template button.

The server must be restarted to for this operation to complete.

Create a simple ADF application and try to deploy it to ADF managed server. The managed server will throw following error.

<WebLogicServer> <BEA-000286> <Failed to invoke startup class "JRF Startup Class", java.lang.ClassNotFoundException: oracle.jrf.wls.JRFStartup

<Deployer> <BEA-149265> <Failure occurred in the execution of deployment request with ID '1384433391426' for task '0'. Error is: 'weblogic.management.DeploymentException: '
Caused By: java.lang.ClassNotFoundException: oracle.adf.share.weblogic.listeners.ADFApplicationLifecycleListener

Given class not found exceptions occurs when ADF libraries classpath did not set properly.

To avoid this error, nodemanager.properties file needs to be updated as mentioned below.

1. Stop ADF managed server and node manager
2. Open nodemanager.properties file located under <WLS_HOME>\wlserver_10.3\common\nodemanager directory

3. Set StartScriptEnabled to 'true'. This property will enable to start ADF managed server with start up parameters configured in start up scripts.
4. Restart the node manager and start the ADF managed server from Console.

CSS - Search Box with Inline Symbol

In this post, let's try to display a simple search box with magnifying glass symbol.

First, create a CSS file with following entry.(also add search.png to img directory under public_html dir)

.filterField af|inputText::content
{
  background-image: url("../../img/search.png");
  background-position: right center;
  background-repeat: no-repeat;
}

Next add af:inputText component to page and refer the CSS class in styleClass property

                            <af:inputText id="it1"
                                          label="Search" styleClass="filterField"/>

If you run the page, the search box will be displayed as shown

You can implement similar pattern by adding necessary styles in contentStyle property. But creating a class in CSS will help you to reuse the style across application.

                            <af:inputText id="it1"
                                          contentStyle='background-image: url("../img/search.png");background-position: right center;background-repeat: no-repeat;'
                                          label="Search"/>

JQuery Usage in ADF - Simple Example

I just tried to use JQuery in ADF with simple DatePicker example. Refer following steps to show datepicker using JQuery.

• Import required JQuery & CSS scripts into page using af:resource tag (In this example, I referred scripts from Google CDN & JQuery CDN):

            <af:resource type="javascript" source="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"/>
            <af:resource type="javascript"
                         source="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/jquery-ui.min.js"/>
            <af:resource type="css" source="http://code.jquery.com/ui/1.10.3/themes/start/jquery-ui.css"/>

• Add Java Script function to show datepicker: (Additional properties enabled for datepicker to change month, year etc)

            <af:resource type="javascript">
              function pickdate() {
                  $("input[name=it1]").datepicker({
                  showButtonPanel: true,
                  dateFormat: "DD, d MM, yy",
                  changeMonth: true,
                  changeYear: true
                  });
              }
            </af:resource>

• Add af:inputText component with af:clientListener to trigger Java Script:

            <af:form id="f1">
                <af:panelStretchLayout id="psl1" styleClass="AFStretchWidth" dimensionsFrom="children">
                    <f:facet name="center">
                        <af:panelGroupLayout id="pgl1" halign="center" layout="horizontal">
                            <af:panelBox text="Test JQuery" id="pb1" showDisclosure="false" type="flow">
                                <f:facet name="toolbar"/>
                                <af:inputText label="Select Date" id="it1">
                                    <af:clientListener method="pickdate" type="mouseOver"/>
                                </af:inputText>
                            </af:panelBox>
                        </af:panelGroupLayout>
                    </f:facet>
                </af:panelStretchLayout>
            </af:form>

• Run your page and click on Input text field to show Date Picker.

Character Count & Maximum Length Validation using JavaScript

In this post, let us discuss about displaying Character count and an alert when character count matches with maximum no of allowed characters.

First, add af:inputtext component to page surrounded with af:panelbox component.
Inside af:inputtext component, add af:clientListener & af:clientAttribute to trigger java script with required attributes. Add af:outputText component inside toolbar facet of af:panelbox to display Character count.

            <af:panelBox text="Character Count" id="pb1" clientComponent="true" showDisclosure="false"
                             partialTriggers="it1">
                    <f:facet name="toolbar">
                        <af:outputText id="CharCount" clientComponent="true" value=""
                                       inlineStyle="font-weight:normal;"/>
                    </f:facet>
                    <af:inputText label="Enter Name" id="it1" maximumLength="8" clientComponent="true" required="true"
                                  rows="1" columns="10">
                        <af:clientListener method="charCount" type="keyUp"/>
                        <af:clientAttribute name="Warning" value="You have reached maximum no of characters!!!"/>
                    </af:inputText>
                </af:panelBox>

Next add java script as shown below to count no of characters and display an alert when it reached to maximum no of characters.

            <af:resource type="javascript">
              function charCount(evt) {
                  var sourceId = evt.getSource().getClientId();
                  var source = evt.getSource();
                  var textfield = evt.getCurrentTarget();
                  var textfield_current_content = textfield.getSubmittedValue();
                  var textfield_current_content_length = textfield_current_content.length;
                  var maxLength = textfield.getMaximumLength();
                  var lengthdiff = maxLength - textfield_current_content_length - 1;
                  if (lengthdiff &gt;= 0) {
                     if(sourceId.indexOf("it1") != -1) {                  
                      var counter = source.findComponent("CharCount");
                     }
                   counter_value = counter.getValue();
                   counter.setValue(textfield_current_content_length + " characters (out of " + maxLength + ")");
                      showAlert = true;
                  }
                  else {
                     if(sourceId.indexOf("it1") != -1) {
                        var counter = source.findComponent("CharCount");
                        counter.setValue(textfield_current_content_length + " characters (out of " + maxLength + ")");
                        var warning = evt.getSource().getProperty('Warning');
                       }
                     if (showAlert == true) {
                        alert(warning);
                        showAlert = false;
                       }
                  }
              }
            </af:resource>

Now run the page and test the logic. An alert will be displayed when Character count in Input text field reaches to Maximum length.

Notes: 

• Given java script contains some additional logic(if condition), which is not required when you have only one af:inputtext field. You can reuse same java script by adding additional if condtions based on af:inputtext ID, when you need same logic for few more af:inputtext fields.
• af:clientAttribute -- If you want to display alert message from property bundle(i18n), just replace hard coded value with reference to key value in property bundle.

          <af:clientAttribute name="Warning" value="#{viewcontrollerBundle.WARNING_EN}"/>

Prevent User Input while Server Side Activity In Progress

Sometimes, we may need to block the user input in ADF application while server side activity is still active. Following post will help you to achieve this.

Case1:

• First of all add a Popup (af:popup) ADF Faces component to the page with an embedded Dialog (af:dialog) component in it as shown below.

        <af:popup id="busyPopup" clientComponent="true" contentDelivery="immediate" childCreation="deferred">

            <af:dialog id="busyDialog" type="none" closeIconVisible="false" title="Loading, please wait..." contentWidth="250">

                <af:image source="/progress_bar.gif" shortDesc="Loading..." id="i2"/>

            </af:dialog>        

        </af:popup>   

            progress_bar.gif:

• Next, add following java script to the page with af:resource tag of type 'javascript'.

        <af:resource type="javascript">

          function showBusyPopup(evt) {

              var popup = AdfPage.PAGE.findComponentByAbsoluteId('busyPopup');

              if (popup != null) {

                  AdfPage.PAGE.addBusyStateListener(popup, busyStateListener);

                  evt.preventUserInput();

              }

          }

          function busyStateListener(evt) {

              var popup = AdfPage.PAGE.findComponentByAbsoluteId('busyPopup');

              if (popup != null) {

                  if (evt.isBusy()) {

                      popup.show();

                  }

                  else if (popup.isPopupVisible()) {

                      popup.hide();

                      AdfPage.PAGE.removeBusyStateListener(popup, busyStateListener);

                  }

              }

          }

        </af:resource>

• Next, add af:clientlistener component of type 'action' for af component in page and refer showBusyPopup as method to be triggered.

            <af:commandButton text="Show PopUp" id="cb1" partialSubmit="true">

               <af:clientListener method="showBusyPopup" type="action"/>                 

            </af:commandButton>

If we run the application with these changes, the popup will be displayed when you click on Button displayed on page. And the popup will get disappeared automatically when server side task get finished.

Popup:

I recommend to perform above mentioned steps 1 & 2 in template page, so that you can reuse same functionality across application.

Case2:

Now let us say, we want to prevent user input for each server side activity. To achieve this, remove af:clientlistener at component level and add it at page level with type 'load'.

<af:clientListener method="showBusyPopup" type="load"/>

I faced an issue when I tried to handle each server side activity i.e. case2. Its not working properly with pages having LOV's.

Case3:

If you want to just prevent the user input till event get completed, try following simple java script. No pop up will be displayed as in Case 1 & 2.

<af:resource type="javascript">

 function showBusyPopup(evt){

    evt.preventUserInput(); 

  }

 </af:resource>

Java Embedding Activity in BPEL 1.1 and BPEL 2.0

In this post, let us take a look at Java Embedding Activity usage in BPEL 1.1 and BPEL 2.0.

The Java Embedding Activity syntax in BPEL 2.0 is slightly different when compared to BPEL 1.1. The bpelx:exec extension and Java code are wrapped in an <extensionActivity> element.

Similarly the Custom Java classes import syntax also varies between both the versions.

BPEL 1.1:
   <bpelx:exec import="oracle.xml.parser.v2.XMLElement"/>
   <bpelx:exec name="Logging" version="1.5" language="java">
        <![CDATA[    
        try{        
           XMLElement input= (XMLElement)getVariableData("inputVariable", "payload","/client:process/client:input");              
           addAuditTrailEntry("Input Value is: " + input.getTextContent());    
           }      
           catch(Exception e)          
           {          
           e.printStackTrace();          
           }]]>
    </bpelx:exec>

BPEL 2.0:
    <import location="oracle.xml.parser.v2.XMLElement" importType="http://schemas.oracle.com/bpel/extension/java"/>
    <extensionActivity>
      <bpelx:exec name="Logging" language="java">
        <![CDATA[    
        try{        
           XMLElement input= (XMLElement)getVariableData("inputVariable", "payload","/client:process/client:input");              
           addAuditTrailEntry("Input Value is: " + input.getTextContent());    
           }      
           catch(Exception e)          
           {          
           e.printStackTrace();          
           }]]>
      </bpelx:exec>
    </extensionActivity>

XSLT : Filter Child node based on Parent node value

This blog post will help you to filter child node elements based on parent node values in XSL transformation.

Given solution will help you to create master and detail relationship, when you are getting parent and child data from different sources.

Simple steps:

• Create a variable to hold a key value from parent node to identify child nodes.(Note: The variable declared immediately after for-each tag in given sample XSLT)
• Refer that variable value in child node to filter the results.

Sample XSLT with solution to filter employees node results by department ID:

<?xml version="1.0" encoding="UTF-8" ?>

<?oracle-xsl-mapper 

  <mapSources>
<!-- SOURCE 1 - Departments Data. -->

    <source type="XSD">

      <schema location="../xsd/DEPARTMENTS.xsd"/>

      <rootElement name="OutputParameters" namespace="http://xmlns.oracle.com/pcbpel/adapter/db/HR/GET_DEPT/"/>

    </source>
<!-- SOURCE 2 - Employees Data. -->

    <source type="XSD">

      <schema location="../xsd/EMPLOYEES.xsd"/>

      <rootElement name="OutputParameters" namespace="http://xmlns.oracle.com/pcbpel/adapter/db/HR/GET_EMPLOYEES/"/>

      <param name="varGetEmployeesDBAdapterOutput.OutputParameters" />

    </source>

  </mapSources>

  <mapTargets>

    <target type="XSD">

      <schema location="../xsd/QueryDept.xsd"/>

      <rootElement name="QueryEmpResponse" namespace="http://www.lkakarla.com/2013/09/QueryEmp"/>

    </target>

  </mapTargets>

?>

<xsl:stylesheet version="1.0"

                xmlns:db="http://xmlns.oracle.com/pcbpel/adapter/db/HR/GET_DEPT/"

                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                xmlns:tns="http://www.lkakarla.com/2013/09/QueryEmp"

                xmlns:ns1="http://xmlns.oracle.com/pcbpel/adapter/db/HR/GET_EMPLOYEES/"

                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

                xmlns:xsd="http://www.w3.org/2001/XMLSchema"

                exclude-result-prefixes="xsi xsl db ns0 ns1 xsd tns xp20 bpws aia mhdr bpel oraext dvm hwf med ids bpm xdk xref bpmn ora socket ldap">

  <xsl:param name="varGetEmployeesDBAdapterOutput.OutputParameters"/>

  <xsl:template match="/">

    <tns:QueryEmpResponse>

      <tns:ResponseBody>

        <xsl:for-each select="/db:OutputParameters/db:OUT_DEPT_CUR/db:OUT_DEPT_CUR_Row">

          <xsl:variable name="DeptID" select="db:DEPT_ID"/>

          <tns:Identity>

              <tns:DepartmentID>

                <xsl:value-of select="db:DEPT_ID"/>

              </tns:DepartmentID>

              <tns:DepartmentName>

                <xsl:value-of select="db:DEPT_NAME"/>

              </tns:DepartmentName>

             <tns:Employees>

              <xsl:for-each select="$varGetEmployeesDBAdapterOutput.OutputParameters/ns1:OutputParameters/ns1:OUT_EMPLOYEE_CUR/ns1:OUT_EMPLOYEE_Row[(ns1:DEPT_ID = $DeptID)]">

                <tns:Employee>

                  <tns:EmployeeID>

                    <xsl:value-of select="ns1:EMPLOYEE_ID"/>

                  </tns:EmployeeID>

                  <tns:EmployeeName>

                    <xsl:value-of select="ns1:EMPLOYEE_NAME"/>

                  </tns:EmployeeName>

                </tns:Employee>

              </xsl:for-each>

            </tns:Employees>

          </tns:Identity>

        </xsl:for-each>

      </tns:ResponseBody>

    </tns:QueryEmpResponse>

  </xsl:template>

</xsl:stylesheet>

Resolving ORA-04061 Issue

In this post, let's discuss about fixing ORA-04061:existing state of package has been invalidated issue when calling Database standard procedure/package from SOA web service using database adapter.

Introduction, sometimes the SOA web service start returning following error message during invocation of standard procedure/package.

Error:
ORA-04061: existing state of package "HR.EMPLOYEES_API" has been invalidated
ORA-04065: not executed, altered or dropped package "HR.EMPLOYEES_API"
ORA-06508: PL/SQL: could not find program unit being called: "HR.EMPLOYEES_API"

But when I check the state of package in database, its in valid state and it worked without any issues, when I try to execute the same program unit from database.

Considering above analysis, I concluded that there is no issue with database program unit. Then I started looking into SOA Web service and Weblogic server data source configuration.

Further investigation on SOA web service testing reveals that, the database program unit is getting invoked sometimes without any issues.

During my R&D, I did resetting data source connection pool. From then, I did not faced this issue.

Final Report:
This issue raises, when the database program being altered/created/compiled by another database user and SOA web service tries to invoke it using old data source connection pool i.e. created before altering database program.

Here comes the Weblogic data source connection pooling mechanism, which holds few logical connections created before I alter the database program with different user.

When I reset the data source, it will destroy the existing connections and will re-create them with current state of the database programs and resolves the issue.

Steps to Reset Data Source in WLS:

1. Go to the Weblogic Console > data sources and select your data source. For Ex: LocalXEDS
2. Go to the tab ‘Control’ and select the servers to which the data source is targeted and click ‘Reset’.

Handle Table Type in PLSQL

This post is all about how to populate and retrieve data from Table type in PLSQL.

Sample Object Type:

CREATE OR REPLACE

TYPE type_employee AS OBJECT

(

   EMPLOYEE_ID NUMBER (6),

   FIRST_NAME VARCHAR2 (20),

   LAST_NAME VARCHAR2 (25),

   SALARY NUMBER (8),

   EMAIL VARCHAR2 (25)

);

Sample Table Array Type:

CREATE OR REPLACE

TYPE employee_array AS TABLE OF type_employee;

PLSQL Anonymous Block:

DECLARE

   emp_array employee_array;

BEGIN

-- Use Object Type to Cast the data from table and use Bulk Collect to fetch large volumes of data at once

   SELECT type_employee(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, EMAIL)

   BULK COLLECT INTO emp_array

   FROM  EMPLOYEES;

   

-- Method 1 --retrieve data from Table Array

   FOR c1 IN (

      SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,EMAIL

      FROM TABLE( CAST( emp_array AS employee_array)) )

   LOOP

      DBMS_OUTPUT.PUT_LINE('Employee ID : ' || c1.EMPLOYEE_ID);

      DBMS_OUTPUT.PUT_LINE('Employee Name : ' || c1.FIRST_NAME || ' ' || c1.LAST_NAME);

      DBMS_OUTPUT.PUT_LINE('Employee Salary : '|| c1.SALARY);

      DBMS_OUTPUT.PUT_LINE('Employee Email : '|| c1.Email);

   END LOOP;

-- Method 2 --retrieve data from Table Array

   FOR i IN 1..emp_array. COUNT LOOP

      DBMS_OUTPUT.PUT_LINE('Employee ID : ' || emp_array(i).EMPLOYEE_ID);

      DBMS_OUTPUT.PUT_LINE('Employee Name : ' || emp_array(i).FIRST_NAME || ' ' || emp_array(i).LAST_NAME);

      DBMS_OUTPUT.PUT_LINE('Employee Salary : '|| emp_array(i).SALARY);

      DBMS_OUTPUT.PUT_LINE('Employee Email : '|| emp_array(i).Email);   

   END LOOP;    

END;

Handling REF CURSOR in SOA Layer

Refer to my previous post on handling strong & weak REF CURSOR output in PLSQL.

In this post, let's discuss about handling REF CURSOR output in SOA layer.

Consider following sample procedure having weak REF CURSOR as output. Compile this procedure in database.

CREATE OR REPLACE PROCEDURE PROC_WEAK_REF_CUR(
                                                in_dept_id  IN  DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                               out_ref_cur OUT SYS_REFCURSOR)
IS
BEGIN
       OPEN    out_ref_cur FOR
       SELECT EMP.*
       FROM   EMPLOYEES EMP,
                     DEPARTMENTS DEPT,
                     LOCATIONS LOC
       WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
       AND       DEPT.LOCATION_ID = LOC.LOCATION_ID
       AND       DEPT.DEPARTMENT_ID = in_dept_id;
END;

Refer following steps to convert Weak XSD to Strong XSD:

• Create a sample SOA composite application in JDeveloper.
• Open composite.xml and Drag Database adapter to External References lane.
• Choose Operation Type 'Call a Stored Procedure or Function'
• In the next step 'Specify Stored Procedure', click on Browse and select the procedure given above. Here verify the output parameter type.

•  In the next step 'RowSet', by default, database adapter generates Weak XSD as shown below. 

• The XML output from Weak XSD is hard to use. It is very difficult to write an Xpath expression or XSL based on a weakly typed XSD and column names as attribute values instead of element names.

• To convert Weak XSD to Strong XSD, enter valid value for input parameters. Click on Introspect.

• If the stored procedure or function returns a row set with at least 1 row, the RowSets page is updated to display a strongly typed XSD in the XSD text field a shown below. 

• In case of Strongly typed XSD, it's easy to identify elements and write an transformation map.

Strong and Weak REF CURSOR Structure - PLSQL

Strong REF CURSOR: Structure of REF cursor is well known during compile time.
Weak REF CURSOR: Not known during compile time.

Following 3 samples will give different ways to use REF CURSOR in PLSQL. These examples created using default HR schema available with XE database.

Weak REF CURSOR Example:
CREATE OR REPLACE PROCEDURE PROC_WEAK_REF_CUR(
                                                in_dept_id  IN  DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                               out_ref_cur OUT SYS_REFCURSOR)
IS
BEGIN
       OPEN    out_ref_cur FOR
       SELECT EMP.*
       FROM   EMPLOYEES EMP,
                     DEPARTMENTS DEPT,
                     LOCATIONS LOC
       WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
       AND       DEPT.LOCATION_ID = LOC.LOCATION_ID
       AND       DEPT.DEPARTMENT_ID = in_dept_id;
END;


DECLARE
        out_ref_cur SYS_REFCURSOR;
        employee EMPLOYEES%ROWTYPE;
BEGIN
        PROC_WEAK_REF_CUR(20,out_ref_cur);
        LOOP
                FETCH out_ref_cur INTO employee;
                EXIT WHEN out_ref_cur%NOTFOUND;
                dbms_output.put_line(employee.EMPLOYEE_ID);
                dbms_output.put_line(employee.FIRST_NAME);
                dbms_output.put_line(employee.LAST_NAME);
                dbms_output.put_line(employee.EMAIL);
                dbms_output.put_line(employee.HIRE_DATE);
                dbms_output.put_line(employee.JOB_ID);
                dbms_output.put_line(employee.SALARY);
                dbms_output.put_line(employee.COMMISSION_PCT);
                dbms_output.put_line(employee.MANAGER_ID);
                dbms_output.put_line(employee.DEPARTMENT_ID);
         END LOOP;
        CLOSE out_ref_cur;
END;

Strong REF CURSOR Example:

CREATE OR REPLACE PACKAGE PKG_STRONG_REF_CUR AS

   TYPE ref_cur_type IS RECORD (

                                EMPLOYEE_ID             EMPLOYEES.EMPLOYEE_ID%TYPE,

                        FIRST_NAME                EMPLOYEES.FIRST_NAME%TYPE,

                                DEPARTMENT_ID        DEPARTMENTS.DEPARTMENT_ID%TYPE,

                                DEPARTMENT_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE,

                                LOCATION_ID              LOCATIONS.LOCATION_ID%TYPE,

                                CITY                                LOCATIONS.CITY%TYPE );

   TYPE ref_cur IS REF CURSOR RETURN ref_cur_type;

   PROCEDURE PROC_STRONG_REF_CUR(

                                  in_dept_id   IN     DEPARTMENTS.DEPARTMENT_ID%TYPE,

                                  out_ref_cur  OUT ref_cur);

END PKG_STRONG_REF_CUR;

CREATE OR REPLACE PACKAGE BODY PKG_STRONG_REF_CUR AS

   PROCEDURE PROC_STRONG_REF_CUR(

                                                        in_dept_id   IN     DEPARTMENTS.DEPARTMENT_ID%TYPE,

out_ref_cur  OUT ref_cur)

   IS

   BEGIN

      OPEN   out_ref_cur FOR

      SELECT EMP.EMPLOYEE_ID,

                     EMP.FIRST_NAME, 

                     DEPT.DEPARTMENT_ID,

                     DEPT.DEPARTMENT_NAME, 

                     LOC.LOCATION_ID, 

                     LOC.CITY

      FROM   EMPLOYEES EMP,

                    DEPARTMENTS DEPT,

                    LOCATIONS LOC

      WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID

      AND       DEPT.LOCATION_ID = LOC.LOCATION_ID

      AND       DEPT.DEPARTMENT_ID = in_dept_id;  

   END;

END PKG_STRONG_REF_CUR;

DECLARE

      v_ref_cur      PKG_STRONG_REF_CUR.ref_cur;

      v_ref_cur_type PKG_STRONG_REF_CUR.ref_cur_type;

BEGIN

     PKG_STRONG_REF_CUR.PROC_STRONG_REF_CUR(20,v_ref_cur);

     LOOP

         FETCH v_ref_cur INTO v_ref_cur_type;

    EXIT WHEN v_ref_cur%notfound;

         dbms_output.put_line(v_ref_cur_type.EMPLOYEE_ID);

         dbms_output.put_line(v_ref_cur_type.FIRST_NAME);

         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_ID);

         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_NAME);

         dbms_output.put_line(v_ref_cur_type.LOCATION_ID);

         dbms_output.put_line(v_ref_cur_type.CITY);

  END LOOP;

END;

Another Example:

CREATE OR REPLACE PROCEDURE PROC_STRONG_REF_CUR(

                                                 in_dept_id  IN     DEPARTMENTS.DEPARTMENT_ID%TYPE,

                                                 out_ref_cur OUT SYS_REFCURSOR)

IS

BEGIN

  OPEN   out_ref_cur FOR

  SELECT EMP.EMPLOYEE_ID,

                 EMP.FIRST_NAME, 

                 DEPT.DEPARTMENT_ID,

                 DEPT.DEPARTMENT_NAME, 

                 LOC.LOCATION_ID, 

                 LOC.CITY

  FROM   EMPLOYEES EMP,

                DEPARTMENTS DEPT,

                LOCATIONS LOC

  WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID

  AND       DEPT.LOCATION_ID = LOC.LOCATION_ID

  AND       DEPT.DEPARTMENT_ID = in_dept_id;

END;

DECLARE

TYPE ref_cur_type IS  RECORD(

                                EMPLOYEE_ID             EMPLOYEES.EMPLOYEE_ID%TYPE,

                        FIRST_NAME                EMPLOYEES.FIRST_NAME%TYPE,

                                DEPARTMENT_ID        DEPARTMENTS.DEPARTMENT_ID%TYPE,

                                DEPARTMENT_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE,

                                LOCATION_ID              LOCATIONS.LOCATION_ID%TYPE,

                                CITY                                LOCATIONS.CITY%TYPE );

TYPE ref_cur IS  REF CURSOR RETURN ref_cur_type;

      v_ref_cur ref_cur;

      v_ref_cur_type ref_cur_type;

  BEGIN

       PROC_WEAK_REF_CUR(20,v_ref_cur);

       LOOP

            FETCH v_ref_cur INTO v_ref_cur_type;

       EXIT WHEN v_ref_cur%NOTFOUND;

         dbms_output.put_line(v_ref_cur_type.EMPLOYEE_ID);

         dbms_output.put_line(v_ref_cur_type.FIRST_NAME);

         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_ID);

         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_NAME);

         dbms_output.put_line(v_ref_cur_type.LOCATION_ID);

         dbms_output.put_line(v_ref_cur_type.CITY);

  END LOOP;

  CLOSE v_ref_cur;

END;

JDeveloper Compilation Error - Android SDK

When deploying ADF mobile application using JDeveloepr 11.1.2.4.0 to Android Emulator, following error appears on deployment log and won't allow to deploy application.

It happens due to non-availability of following files/directories in android-sdk/platform-tools directory. In the latest release of Android SDK(Android 4.2.2 API 17), listed files/directories moved from platform-tools to build-tools directory. But still JDeveloper refers to platform-tools directory. Instead of waiting for fix/update from Oracle, following quick fix will help to finish deployment.

Files:
aapt.exe
dx.bat

Directory:
\lib

Quick Fix Steps:
1. Open command prompt with admin privileges in Windows
2. Create symbolic links in platform-tools directory for the listed files exists in build-tools directory.

mklink E:\Android\sdk\platform-tools\aapt.exe E:\Android\sdk\build-tools\android-4.2.2\aapt.exe

mklink /D E:\Android\sdk\platform-tools\lib E:\Android\sdk\build-tools\android-4.2.2\lib

mklink E:\Android\sdk\platform-tools\dx.bat E:\Android\sdk\build-tools\android-4.2.2\dx.bat

Encrypt Data Using MD5 and SHA - Oracle Database

Following Oracle database packages enables you to encrypt critical data like passwords using SHA or MD5 encryption algorithms.

MD5: dbms_obfuscation_toolkit.md5
SHA: dbms_crypto.hmac_sh1

Sample usage:

MD5: 
utl_i18n.string_to_raw(dbms_obfuscation_toolkit.md5(input_string => <value to be encrypted>))

SHA:
encryption_key := dbms_random.string('X',16);
dbms_crypto.mac(utl_i18n.string_to_raw(<value to be encrypted>),
                           dbms_crypto.hmac_sh1,
                           utl_i18n.string_to_raw(encryption_key));

For more info, refer link.

SQLJUTL Package Error - Database Adapter Creation - Oracle XE

When we try to create a database adapter to execute procedure or function exists in XE database, following error message will be displayed during adapter configuration.

java.sql.SQLSyntaxErrorException: ORA-00904: "SYS"."SQLJUTL"."HAS_DEFAULT": invalid identifier

Given error happens due to missing of required SQLJUTL package from XE.

To resolve this error, you have to compile SQLJUTL package script in XE database. The SQLJUTL package script can be found in Enterprise Database installation SQLJ directory.

For Ex: /home/oracle/healthcare/product/11.2.0/dbhome_1/oc4j/sqlj/lib

Pagination with ROWNUM

Following SQL query will help you to get number of records in between certain range using ROWNUM.

SELECT *

FROM

  (SELECT a.*, ROWNUM rnum

   FROM

     (SELECT *

      FROM employees

      ORDER BY employee_id, rowid) a

   WHERE ROWNUM <= 10

 )

 WHERE rnum >= 8;

You can find more details in following LINK

Exposing ADF BC as Service Interface

Here I have provided detailed steps to expose ADF Business Components as a Web service interface using sample application.

Steps:

• Create an ADF application

• Go to Model project and right click New

• Select Business Components from Tables

• Select Database connection and click OK

• Select required tables as entity objects

• Select required View Objects

• Choose Application Module

• Click Finish to create Business Components in Model project

• Click on Application Module and go to Service Interface section. Click on Green plus icon.

• Check ‘Generate Asynchronous Web Service Methods’

• Select required component instances and respective operations

• Click Finish to create Service Interface

• Right click on Model project --> select New --> Go to Deployment Profiles section. Select Business Components Service Interface

• Provide Deployment profile name and click OK

• Go to Application Properties --> Deployment --> New

• Select Archive Type as EAR file and provide name

• In the EAR Deployment Profile editor, select the node Application Assembly and select only MiddleTier and click OK.

• Back in the Deployment page for the Application Properties; uncheck the checkbox “Auto Generate and Synchronize weblogic-jdbc.xml Descriptors During Deployment”

• Go to ApplicationModule --> Configuration. Set AM Service configuration as Default Configuration

• Go to Model Project Properties --> Java EE Application --> Change Web application name and Web Context root as needed

• Create required Data Source in Weblogic Console

• Deploy to Weblogic Server

• After successful deployment, Login to WLS console and go to deployments. Click on deployed application --> go to Testing tab. Check the Test URL. Alternatively, you can login to EM console to get WSDL details

• Open browser and access deployed application WSDL

• Copy the service location URL from WSDL and access the URL from browser. Check the list of operations available

• Select any one operation and test it