My Learning's @ Work - by Lakshmipathi Kakarla: September 2013

Thursday, September 26, 2013

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

</source>

</source>

</mapSources>

</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>

Friday, September 6, 2013

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:

Go to the Weblogic Console > data sources and select your data source. For Ex: LocalXEDS
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;