Using Oracle’s ListAgg with distinct

Oracle’s 11g ListAgg function is a great way to concat multiple rows into a single column. However, it has a major limitation (in addition to the light documentation) – you can’t use distinct in ListAgg, a serious limitation. Online solutions suggest subqueries or regular expressions. I decided to write my own aggregate function to replace ListAgg.
Here goes:

First of all, I created the type specification:

create or replace type TextAggregation as object
(
  aggString VARCHAR2(32767), 
  static function ODCIAggregateInitialize(sctx IN OUT TextAggregation) 
    return number,
  member function ODCIAggregateIterate(self IN OUT TextAggregation, 
    value IN VARCHAR2) return number,
  member function ODCIAggregateTerminate(self IN TextAggregation, 
    returnValue OUT VARCHAR2, flags IN VARCHAR2) return number,
  member function ODCIAggregateMerge(self IN OUT TextAggregation, 
    ctx2 IN TextAggregation) return number
);
/

Then, the type body:

create or replace type body TextAggregation is 
static function ODCIAggregateInitialize(sctx IN OUT TextAggregation) 
return number is 
begin
  sctx := TextAggregation('');
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT TextAggregation, value IN VARCHAR2) return number is
  location number;
begin
	location := instr(',' || aggString || ',' , ',' || value || ',');
	
	if location > 0 then
		return ODCIConst.Success;
	end if;
	
  if (aggString is null) then
    aggString := value;
  else
    aggString := aggString || ',' || value;
  end if;
  
	return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN TextAggregation, 
    returnValue OUT VARCHAR2, flags IN VARCHAR2) return number is
begin
  returnValue := self.aggString;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT TextAggregation, ctx2 IN TextAggregation) return number is
begin
  self.aggString := ctx2.aggString;
  return ODCIConst.Success;
end;
end;
/

And then the actual function

CREATE or replace FUNCTION MyListAgg (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING TextAggregation;
/

Using Liferay SSO with Oracle Access Manager 11g

Well, I was given a challenging task – using Oracle Access Manager 11g as an SSO provider for Liferay 6.0 (the community edition…).
Now, as you might know – there is no built-in OAM support for Liferay – so I was stuck with configuring one myself. Since I didn’t even have the OAM installed – I’ll detail all the steps I did. To simplify matters – I installed OAM on Microsoft Windows Server, but the same should hold for Linux.

All Oracle downloads were downloaded from edelivery.oracle.com. Version is 11.1.0.6.

OAM Installation

Install Oracle database. I didn’t install Oracle XE, but rather the Enterprise edition.

Alter the Oracle database.

Open sqlplus as sys and run the following commands

alter system set open_cursors=1000 scope=both;
alter system set processes=1000 scope=SPFILE;

Restart Oracle DB.
Run RCU (V33643-01), and check the Identity Managent checkbox. Proceed with the installation.
Install WebLogic Server (wls1036_generic)
Install SOA Suite (ofm_soa_generic_11.1.1.6.0_disk1_1of2 and ofm_soa_generic_11.1.1.6.0_disk1_2of2).
Install IdM (V33644-01_1of2 and V33644-01_2of2)
From your ORACLE_HOME/IDM_HOME/common/bin run the config.cmd file.
Install all the required components (especially all the Oracle Access Manager relevant components).
DO NOT START THE ADMIN SERVER.
Run the following WLST scripts (thank you Warren

$MW_HOME/oracle_common/common/bin/wlst.cmd $ORACLE_HOME/common/tools/configureSecurityStore.py -d $IAM_DOMAIN_LOCATION -m create     -c IAM -p $ORA_PASS
$MW_HOME/oracle_common/common/bin/wlst.cmd $ORACLE_HOME/common/tools/configureSecurityStore.py -d $IAM_DOMAIN_LOCATION -m validate

Where

$MW_HOME is where you put the Middleware home (e.g. ~/Oracle/Middleware)
$ORACLE_HOME
is the Oracle IAM home (e.g. ~/Oracle/Middleware/Oracle_IAM1)
$IAM_DOMAIN_
LOCATION is the domain home (e.g. ~/Oracle/Middleware/user_projects/domains/OAMDomain)
$ORA_PASS is the password needed to talk to the database

Now you can safely run the admin server. Connect to it using IP_ADDR:7001/em, and start the OAM managed server too.

Apache installation/configuration

On a separate machine (I used RedHat Linux 5.5):

Install Apache2.2
Configure WebGate (I used ZIP file oam_int_linux_v10_cd1.zip)
Configure Apache to act as a proxy for your Liferay server by using ProxyPass and ProxyPassReverse. For instance:

ProxyRequests Off
ProxyPass /web http://LIFERAY_SERVER:8080/web
ProxyPassReverse /web http://LIFERAY_SERVER:8080/web

Configure WebGate in the Apache. On my machine the configuration looked like this:

LoadModule obWebgateModule "/usr/local/webgate/product/access/oblix/apps/webgate/bin/webgate.so"

LoadFile "/usr/local/webgate/libgcc_s.so.1"
LoadFile "/usr/local/webgate/libstdc++.so.6"

        WebGateInstalldir "/usr/local/webgate/product/access"
        WebGateMode PEER
        #webgateload obWebgateModule "/usr/local/webgate/product/access/oblix/apps/webgate/bin/webgate.so"


        SetHandler obwebgateerr


        SetHandler obwebgateerr


        AuthType Oblix
        require valid-user

OAM Configuration

Open the Access Manager console, and click on the “New OAM10g WebGate”
Fill in the details, exactly as you did during the WebGate installation.
Go to “Application Domains”, and select the newly created Application Policy
Change any required value, and select “Authorization Policies”
Select the “Protected Resource Policy”
Select “Responses”
Add a new response – HTTP Header with the name of LIFERAY_SCREEN_NAME and value of uid

Liferay Configuration

Edit the portal-ext.properties file and add the following line:

auto.login.hooks=com.liferay.portal.security.auth.RequestHeaderAutoLogin

Restart Apache, and browse to it. You should get the OAM login page, and after login – you should see you have automatically logged-in into Liferay…

Tracking Locks in Oracle Database

A web application I was tracing had serious DB performance issues. I reviewed the AWR, found out that the IO was incredibly slow, but a weird behavior made me suspect some locks were also the reason for the poor performance.
While running JMeter, I saw a situation where no responses are received from the server, until, suddenly, everything started running – only to be stuck again.
So, how did I track locks in the database? I’ve used the following queries (of course, all require SYSDBA to run):

SELECT l.session_id||','||v.serial# sid_serial,
       l.ORACLE_USERNAME ora_user,
       o.object_name, 
       o.object_type, 
       DECODE(l.locked_mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive', 
          TO_CHAR(l.locked_mode)
       ) lock_mode,
       o.status,                                                                                                                               
       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl                                                                                            
FROM dba_objects o, gv$locked_object l, v$session v                                                                                            
WHERE o.object_id = l.object_id                                                                                                                
      and l.SESSION_ID=v.sid                                                                                                                   
order by 2,3;

I use the above query to see if actual locks exist in the DB (it runs pretty fast, so I use it often).

select l1.sid, ' IS BLOCKING ', 
       l2.sid 
from v$lock l1, 
     v$lock l2 
where l1.block =1 and 
      l2.request > 0 and 
      l1.id1=l2.id1  and 
      l1.id2=l2.id2;

I use the above query to see which session is locked (very useful to detect dead-locks)

select s1.username || '@' || s1.machine 
  || ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' 
  || s2.sid || ' ) ' AS blocking_status,
  a1.sql_text,
  a2.sql_text
from v$lock l1, 
     v$session s1, 
     v$lock l2, 
     v$session s2,
     v$sqlarea a1, 
     v$sqlarea a2
where s1.sid=l1.sid and 
      l1.BLOCK=1 and 
      s2.sid=l2.sid and 
      l2.request > 0 and 
      l1.id1 = l2.id1 and 
      l2.id2 = l2.id2 and 
      s1.sql_hash_value = a1.hash_value and 
      s1.sql_address = a1.address and 
      s2.sql_hash_value = a2.hash_value and 
      s2.sql_address = a2.address;

The above query is my favorite (alas, it is very slow…) as it shows the actual locking SQL – and the locked SQL. If you know your code – you can easily identify which query causes the locking problem.

Using Oracle tkprof with JDBC thin client application

When attempting to profile a Hibernate based application, I found a statement that was incredibly slow and caused the system to basically halt for a few seconds before resuming execution. I wanted to profile it at the database level, and the best tool for the job is Oracle’s own tkprof.
The input for tkprof is a session trace file, and enabling one is a bit tricky. The reason – a JavaEE application, with multiple threads, have multiple connections, multiple database sessions, and the SQL_TRACE is on a per session level (I didn’t want to configure it for the entire database – the trace file would be totally unusable…)
So, I took the code and ran it in a standalone Java application, and enabled SQL Trace. Here’s how:

package com.tona.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import oracle.jdbc.OracleCallableStatement;

public class TestJDBC {
	public static void main(String[] args) throws Exception {
		Connection c = DriverManager.getConnection(JDBC_CONNECTION_STRING);
		
                // Set SQL_Trace to on
		Statement stmt = c.createStatement();
		stmt.executeUpdate("ALTER SESSION SET SQL_TRACE=TRUE");
		stmt.close();
		
                // Set SQL Trace location
		stmt = c.createStatement();
		stmt.executeUpdate("alter system set USER_DUMP_DEST='/tmp'");
		stmt.close();

		// JDBC logic comes here...

		c.close();
	}
}

Note
Changing the USER_DUMP_DEST parameter did not have any effect, and the SQL_TRACE was written to the default trace log directory on the server (in my case it was ./u01/app/oracle/diag/rdbms/SID/SID/trace)

Batch Update with JPA

A customer of mine has a highly scalable system, with high database load. To improve performance, we’ve decided to use Batch update. Alas – the application uses JPA, so how do we do it?

SessionFactoryImplementor sfi = SessionFactoryImplementor)entityManager.unwrap(Session.class).getSessionFactory();
//Retrieve DB connection
connect = sfi.getConnectionProvider().getConnection();
PreparedStatement ps = connect.prepareStatement("INSERT INTO temp_table values(?,?)");
for (Data p : insertCommands) {
	ps.setInt(1, p.getId());
	if (p.isWorking() != null) {
		ps.setInt(2, p.isWorking() ? 1 : 0);
	} else {
		ps.setNull(2, Types.INTEGER);
	}
	ps.addBatch();
}
ps.executeBatch();

Some points to remember:

Do not close the connection!!! When closing the connection, it returns to the connection pool, without commit or rollback. You’ll get some exceptions in your code, but mainly – you’re open to database locks and dead-locks, as the connection keeps all the locks that were done in the batch update. And since other threads will reuse the connection – it will never close.
Do not use Statement.addBatch. Use PreparedStatement.addBatch. When I checked both solutions, the Statement one took anywhere between 30s-60s. The PreparedStatement version took <300ms…

Stale Connection in WebSphere Application Server 6.1

Well, a customer of mine has a very weird bug. Sometimes he gets a StaleConnection exception when executing a statement against his Oracle Connection. The client runs WebSphere App Server 6.1, Oracle Database 10g, and uses EJBs and an XA datasource.

Finally, we discovered that there is a scenario that always raises the Stale Connection Exception, and we started investigating the root of the problem.

First of all, WebSphere throws the Stale Connection exception as a wrapper to specific SQL Exceptions received from the JDBC connection.

But why is the connection suddenly closed? A step-by-step following with the debugger found that we get the connection open from the datasource, and then, suddenly, the connection is closed.

We tried to convert the datasource to a non-XA datasource, and received allot of exceptions. This showed us that the process in question needed the XA capabilities of WAS.

We then wrote wrappers over Oracle’s XA connection manager (oracle.jdbc.xa.client.OracleXADataSource) and Oracle’s Connection (oracle.jdbc.pool.OraclePooledConnection), and seen the debug messages.

We still have no solution, but there can be 2 options:

1. There is a bug with the WAS XA handler.

2. When using statement.getConnection().close(), we close the physical connection, and not the logical connection coming from the datasource. The customer now changes his code, to see if he can get away without the statement.getConnection() bit, and close the logical connection received from the datasource.

I’ll post more details later.