Marcelo Ochoa's personal blog

Implementing Oracle parallel shared server process in Java inside the Database

2011-11-28T12:33:00.001-08:00

Behind the implementation of latest LDI open source project and the OLS products there is a functionality not well know by Oracle Java database developers, I called it Parallel Shared Server process.

The idea is to have an Oracle shared server process running during the life-time of the instance, which means a process automatically started during database startup and stopped during database shutdown.

So which functionality this process can implement?, on LDI is an RMI server, on OLS is lightweight HTTP server, but basically you can implement anything you need for example getting information from another process and fill some table, getting statistical, consuming web services, etc. etc.

Let see in some example how it works.

We will create a TEST user and creates some Java classes running a simple Hello World RMI server.

SQL> conn / as sysdba

SQL> create user test identified by test
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;

SQL> grant connect,resource,create any job to TEST;

SQL> exec dbms_java.grant_permission( 'TEST', 'SYS:java.net.SocketPermission', 'localhost:1024-', 'listen,resolve');
SQL> exec dbms_java.grant_permission( 'TEST', 'SYS:java.net.SocketPermission', 'localhost:1024-', 'accept, resolve');
SQL> exec dbms_java.grant_permission( 'TEST', 'SYS:java.net.SocketPermission', 'localhost:1024-', 'connect, resolve');
SQL> exec dbms_java.grant_permission( 'TEST', 'SYS:java.lang.RuntimePermission', 'setContextClassLoader', '' );

The RMI interface and server implementation running on TEST user.

SQL> conn test/test
SQL> create or replace and compile java source named "mytest.Hello" as
package mytest;
import java.rmi.Remote;
import java.rmi.RemoteException;
public interface Hello extends Remote {
String sayHello() throws RemoteException;
int nextCount() throws RemoteException;
}
/
SQL> create or replace and compile java source named "mytest.HelloImpl" as
package mytest;
import java.rmi.Naming;
import java.rmi.RemoteException;
import java.rmi.RMISecurityManager;
import java.rmi.registry.LocateRegistry;
import java.rmi.server.UnicastRemoteObject;
public class HelloImpl extends UnicastRemoteObject implements Hello {
static int counter = 0;

public HelloImpl() throws RemoteException {
super();
}
public String sayHello() {
return "Hello World!";
}
public static void main(String[] args) {
// Create and install a security manager
if (System.getSecurityManager() == null) {
System.setSecurityManager(new RMISecurityManager());
}
try {
HelloImpl obj = new HelloImpl();
LocateRegistry.createRegistry(1099);
// Bind this object instance to the name "HelloServer"
Naming.rebind("//localhost/HelloServer", obj);
System.out.println("HelloServer bound in registry");
} catch (Exception e) {
System.out.println("HelloImpl err: " + e.getMessage());
e.printStackTrace();
}
}
public synchronized int nextCount() {
return counter++;
}
}
/
SQL> create or replace procedure HelloServ(srvName IN VARCHAR2) as LANGUAGE JAVA NAME
'mytest.HelloImpl.main(java.lang.String [])';
/

SQL> begin
-- Start a Cron like process (DBMS_SCHEDULER)
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'HelloServJob',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
HelloServ(''HelloServer'');
exception when others then
null;
end;',
start_date => SYSDATE,
enabled => false,
auto_drop => false);
DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
name => 'HelloServJob',
attribute => 'MAX_FAILURES');
end;
/
commit;

Now we can register two database instance trigger to automatically start and stop the job.

SQL> conn / as sysdba
SQL> CREATE OR REPLACE TRIGGER start_test_srv
AFTER STARTUP ON DATABASE
BEGIN
-- Start a Cron like process (DBMS_SCHEDULER)
DBMS_SCHEDULER.ENABLE('TEST.HelloServJob');
END;
/
SQL> CREATE OR REPLACE TRIGGER stop_test_srv
BEFORE SHUTDOWN ON DATABASE
BEGIN
-- Start a Cron like process (DBMS_SCHEDULER)
DBMS_SCHEDULER.STOP_JOB('TEST.HelloServJob',force=>true);
EXCEPTION WHEN OTHERS THEN
null;
END;
/

If we process to do a shutdown/startup sequence the server will up and running, also we can start the server manually by executing:

SQL> conn / as sysdba
SQL> exec DBMS_SCHEDULER.ENABLE('TEST.HelloServJob');
SQL> commit;

after doing that we can see at $ORACLE_BASE/diag/rdbms/orcl/orcl/trace a .trc file associated with the parallel shared server process which is up and running:

-bash-4.2$ cat orcl_j000_10411.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_10411.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11_2_0_2_0/dbhome_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.38.7-server-1mnb2
Version: #1 SMP Sun May 22 22:59:25 UTC 2011
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 10411, image: oracle@localhost.localdomain (J000)

*** 2011-11-28 18:05:41.091
*** SESSION ID:(151.35) 2011-11-28 18:05:41.091
*** CLIENT ID:() 2011-11-28 18:05:41.091
*** SERVICE NAME:(SYS$USERS) 2011-11-28 18:05:41.091
*** MODULE NAME:(DBMS_SCHEDULER) 2011-11-28 18:05:41.091
*** ACTION NAME:(HELLOSERVJOB) 2011-11-28 18:05:41.091

HelloServer bound in registry

and this process is listening into the default RMI port 1099, we can see that using:

-bash-4.2$ netstat -anp|grep ora_j0
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 :::19189 :::* LISTEN 10411/ora_j000_orcl
tcp 0 0 :::1099 :::* LISTEN 10411/ora_j000_orcl

and that's all, we can connect using an RMI client from another client session, for example:

SQL> create or replace and compile java source named "mytest.HelloClient" as
package mytest;
import java.rmi.Naming;
import java.rmi.RemoteException;
public class HelloClient {
Hello obj = null;
public HelloClient() {
try {
obj = (Hello)Naming.lookup("//localhost/HelloServer");
} catch (Exception e) {
System.out.println("HelloApplet exception: " + e.getMessage());
e.printStackTrace();
}
}
public String sayHello() throws RemoteException {
return obj.sayHello();
}
public int nextCount() throws RemoteException {
return obj.nextCount();
}
public static void main(String[] args) throws RemoteException {
HelloClient helloClient = new HelloClient();
System.out.println(helloClient.sayHello());
System.out.println(helloClient.nextCount());
}
}
/
SQL> create or replace procedure HelloClient(srvName IN VARCHAR2) as LANGUAGE JAVA NAME
'mytest.HelloClient.main(java.lang.String [])';
/

SQL> set define ?
SQL> set serverout on
SQL> exec dbms_java.set_output(32000);
SQL> exec HelloClient('HelloServer');
0
SQL> exec HelloClient('HelloServer');
1

Note that the server is state-full which means that preserve the state across calls, if we exit from above SQLPlus and connect again we will see that:

SQL> set define ?
SQL> set serverout on
SQL> exec dbms_java.set_output(32000);
SQL> exec HelloClient('HelloServer');
2
SQL> exec HelloClient('HelloServer');
3

Concluding this post I would like to remark that this parallel shared server process is running into RDBMS space and is not like starting an RMI server into the middle tier. the big difference is that all SQL access is implemented accessing directly to the RDBMS structures into the SGA because is using the internal JDBC driver.

New release of Lucene Domain Index based on 3.4.0 code

2011-11-25T11:44:00.001-08:00

This new release of Lucene Domain Index (LDI) has been in the new SVN for a long time, but due a lot of works with the commercial version Scotas never went public in binary installation :(
Several thing happen during this time:

New web site (thanks a lot to Peter Wehner for the conversion from the Google docs)
New hosting at SF.net (now is separate SVN from the previous one CVS at DBPrism)

The change log of this version is:

Use latest merge policy implementation TieredMergePolicy
Use total RAM reported by getJavaPoolSize() when setting MaxBufferedDocs
Better error reporting when an Analyzer is not found.
Replaced execute immediate with open-fech-close functionality to avoid core dump on 10g when double check for deleted rowid
Included a back-port version of JUnit4 to jdk1.4 version for 10g releases
Added a parallel updater process, when working in OnLine mode this process do write operations on LDI structure on behalf of the AQ process
Delete do not longer required a write exclusive lock on index storage, now deletes are also en-queued as inserts or updates
Updated source to Lucene 3.4.0 code, removed some deprecated API

Download latest binary distribution at 3.4.0 directory of SF.net download area (tested with 10g/11gR2).
The addition of a new parallel shared server process is the major change which speed up a lot DML operations, I'll write in a new post on how this parallel shared server technique works.
Please report any issue during the installation or bugs at the Support Area of the project.

Long time no post

2011-09-26T06:41:00.000-07:00

I found that my last post was on Sep. 14 2010, that's too bad.
The reason of that is the startup called Scotas I worked on that almost since December of 2010 to engineering a next level of Oracle and Lucene project integration, this is by adding the Solr stack.
Its a natural evolution, Solr provides a lot of common functionality working on top of Lucene required for the enterprise level solution.
But the integration required a set of implementation challenges to work, once of them is the implementation of long live shared server process running in Java and which implements a new dedicated HTTP listener working as an Oracle slave process, I will explain this topic on another technical post.
Another additions included in a set of products are the push technology spread on the integration with Solr running externally, ElasticSearch, Cassandra and HBase.
Behind this technology we extended the idea of Oracle/Solr integration, the Oracle ODCI API,. which enabled a NRT (Near Real Time) synchronization of the enterprise data with NoSQL layer for example.
Near Real Time means that once you commit the changes on the RDBMS layer thy are propagated automatically to the other side practically with 0 delay, and for the two way replicator such as Solr PC and ElasticSearch deletions are in real-time avoiding the false positive hits of deleted rows.
Near Real Time synchronization is declarative, no programming effort is required and allows multiples columns of one table, or multiples columns of many tables connected by a foreign key.
Well next week is the OOW 2011, I'll Be there and I Hope You Will, Too and for anybody that are coming to the event and are interested on that technology we could meet there, just drop me an email to mochoa-at-scotas.com

New Oracle Lucene Domain Index release based on Lucene 3.0.2

2010-09-14T14:39:00.000-07:00

Just a few words to announce a new release of Oracle Lucene Domain Index, this zip is valid for 10g and 11g database version (10g using back-ported classes from 1.5 to 1.4)
This release is compiled using Lucene 3.0.2 version and incorporates a set of new features added, here the list:

Added a long awaited functionality, a parallel/shared/slave search process used during a start-fetch-close and CountHits function
Added lfreqterms ancillary operator returning the freq terms array of rows visited
Added lsimilarity ancillary operator returning a computed Levenshtein distance of the row visited
Added a ldidyoumean pipeline table function using DidYouMean.indexDictionary storage
Added test using SQLUnit

The bigger addition is the Parallel-Shared-Slave search process, this architectural change was in my to-do list for a long time and finally I added in this release :)
The idea behind this is to have a new Oracle process started by the DBMS_SCHEDULER sub-system during the database startup process and stopped immediately before shutdown.
Now this process is responsible for implementing the ODCI methods start-fetch-close/count-hit on behalf of the client process (process associated to an specific user session) which connect to the shared-slave process by using RMI.
With this new architecture we have two principal benefits:

Reduce memory consumption
Increase Lucene Cache Hits

Less memory consumption because the internal OJVM implementation is attached to a client session, so the Java space used by Lucene structures is isolated and independent from another concurrent session, now all Lucene memory structures used during index scan process are created in a shared process and then not replicated.
Also if one session submits a Lucene search, this search is cached for subsequent queries, all subsequent queries coming from the same client session or any other which are associated to the same index and with the same Query string implies a hit.
I'll explain more in detail this new architecture in another post also showing how many parallel process can work together when using Parallel Indexing and Searching.
On the other hand next week I'll be at the Oracle OpenWorld 2010 in SFO presenting the session:

Schedule: Tuesday: 09:30AM
Session ID: S315660
Title: Database Applications Lifecycle Management
Event: JavaOne and Oracle Develop
Stream(s): ORACLE DEVELOP
Track(s): Database Development
Abstract: Complex applications, such as Java running inside the database, require an application lifecycle management to develop and delivery good code. This session will cover some best practices, tools, and experience managing and delivering code for running inside the database, including tools for debugging, automatic test, packaging, deployment, and release management. Some of the tools presented will include Apache Maven, JUnit, log4j, Oracle JDeveloper, and others integrated into the Oracle Java Virtual Machine (JVM) development environment.

See you there or at any of networking planned events :)

Near-shore development in Tandil-Argentina

2010-06-29T07:30:00.000-07:00

This post is bit off topic about Oracle, but not at all.
I remember read by a first time the term near-shore development during OOW08 in a Information Week newsletter free at OTN Lounge.
Two years ago I see the increasing on development using this model in my City Tandil, 400Km far from Buenos Aires in Argentina.
Specially, I am working as external consultant with the company Temperies which was founded and growing in Tandil unlike other companies which are part of the Technology Park and came from Buenos Aires.
But why this market still growing in Tandil with well know economy changes?
First Why Tandil?

Is a very nice small city (130000 habitants) surrounded by hills where the quality of life makes the difference in term of productivity of the development team, this is one of the bigger difference against other Argentine cities such as Buenos Aires, Cordoba or Rosario. Any worker can get his workplace in less than five minutes walking.
Has a recognized University in Computer Science.
Is cheap to live here, so salaries are not so high.

Second Why Argentina?
  - Is a country with a big difference in term of US$/Euro exchange rate compared with another countries like Chile for example, and believe me when an near-shore project is evaluated this point is very important because with the same money you can do the project several times :)
  - Most of the computer science graduated and under-graduated students have very good knowledge of English.
  - The time zone of Argentine is in middle of Europe and US West coast, so projects can be perfectly managed around the team synchronization, if we work with San Francisco, we work during our afternoon, if we work with Germany, we work during our morning, and thats all.
Finally I want to remark that there are plenty of offers in term of Software Factories in Tandil, from small companies with 10 to 20 employees to big factories with more than one hundred employees to fit with every need.
As I set at the beginning of this post I am working with Temperies a mid-range Software Factory which, in addition to the list of goodness exposed previously, have an strong commitment with Agile practices, Oracle and a widely experience with success stories in near-shoring projects around the world, and obviously the CIOs are good friends of mine :)
Well if anybody want to go deeper in this area just drop me an email to marcelo.ochoa gmail.com or see you at the Oracle Open World in September.

Dealing with JDK1.5 libraries on Oracle 10g

2010-06-08T14:04:00.000-07:00

Modern libraries are compiled with JDK 1.5 and the question is How to deal with these libraries on an Oracle 10g OJVM.
Some examples are Lucene 3.x branch or Hadoop. The solution that I tested is using a Java Retro Translator and some complementary libraries.
I have tested this solution in Lucene Domain Index 3.x branch with success.
As you can see on the CVS there is build.xml file which performs all the retro translator steps. Here an step by step explanation of the process:

Load all required libraries provided by Retro translator project which implements features not available on JDK 1.4/1.3 runtime, this is done on the target load-retrotranslator-sys-code. This target loads many libraries on SYS schema due are immutable, or with low probability of change. It will change if we upgrade a retro-translator version. All libraries are then compiled to assembler using NCOMP utility, target ncomp-runtime-retrotranslator-sys-code.
Then we can convert libraries compiled with JDK1.5, in this build.xml file the Lucene and Lucene Domain Index implementation, to a JDK1.4 target runtime. This is done on the targets backport-code-lucene and backport-code-odi, on first target We converts all Lucene libraries excluding JUnit and Test code, these libraries require as a dependency JUnit and retro-translator jars. Second target converts Lucene Domain Index jar depending on Lucene core and Oracle's libs. The back-port operation generates a file named lucene-odi-all-${version}.jar with Lucene and Lucene Domain Index code ready to run on JDK1.4 runtime.
Once We have the code back-ported to a JDK1.4 runtime We can upload and NCOMP into Oracle 10g, this is done on targets load-lucene-odi-backported-code and ncomp-lucene-all.

And that's all!!, the code works fine on my Oracle 10.2 database - Linux :), finally users of 11g and 10g databases can deploy Lucene Domain Index implementation using one distribution file.

Auto complete functionality with latest Lucene Domain Index

2010-05-08T08:44:00.000-07:00

A few days ago I uploaded two new releases of Oracle Lucene Domain Index, once based on Lucene 2.9.2 core base (10g, 11g) and another based on 3.0.1 release (10g/11g).
The question is why 3.0.1 release only have one installation file?
This is because the code base of Lucene 3.x branch is only compatible with JDK1.5 so to get Lucene 3.x release working on 10g databases which is based on JDK1.4 I included a retro-translator, this library gets code compiled in 1.5 format and converts it to 1.4, I'll explain more in details this process in another post.
The important point is, I want to still supporting Lucene Domain Index for Oracle 10g because the installed base of this release is big even with the end of official support next July.
On the other hand this new release includes another great contribution from Pedro Pinheiro, an auto-complete pipeline table function. This reinforce the goal of Lucene Domain Index that with a few new classes and some PLSQL wrapper you can extend LDI to your need.
Here a simple example:
I am creating and populating a simple table with a english-spanish dictionary lookup:

create table dicc (
term varchar(256),
def varchar2(4000))
/
-- Populate dictionary with 10K terms and definitions
@@dicc-values

then a Lucene Domain Index for auto-complete functionality:

create index dicc_lidx on dicc(term) indextype is lucene.luceneindex
parameters('ExtraCols:def;LogLevel:INFO;FormatCols:TERM(ANALYZED_WITH_POSITIONS_OFFSETS);PerFieldAnalyzer:TERM(org.apache.lucene.analysis.WhitespaceAnalyzer),DEF(org.apache.lucene.analysis.StopAnalyzer)');

Note that TERM column is analyzed storing term positions offset.
With this index created we can query using auto complete pipeline table function as follow:

SQL> select * from table(lautocomplete('DICC_LIDX','TERM','th',15)) t;

TERM DOCFREQ
there 3
theory 2
thaw 2
then 2
therefore 2
thence 1
their 1
thanks 1
theft 1
theatrical 1
the 1
theme 1
that 1
thermal 1
thank 1
15 rows selected.
Elapsed: 00:00:00.01

First argument of this function is your index name, second argument is the column used for auto complete, third argument is the string used for lookup and last argument is how many terms are returned. By default rows are returned order by docFreq descending. Here other example:

SQL> select * from table(lautocomplete('DICC_LIDX','TERM','spor',10)) t;
TERM DOCFREQ
sport 3
sportsman 1
sporadic 1
Elapsed: 00:00:00.02

For the example table which includes 10102 rows the execution time of above examples is around 21ms, not bad for a notebook.
Another new feature of this release is parallel index on RAM, which is enable by default with this release, indexing on RAM means that when you are working in OnLine mode a batch of new rows to be added to the index are processed in parallel (ParallelDegree parameter) more information is on Lucene Domain Index documentation on-line, if you have a server with multi-core processor or a RAC installation with sufficient RAM this feature speed up your indexing time by 40% eliminating the BLOB access during a partial index creation.
Well next post will be about how to deal with Libraries compiled using JDK1.5 on Oracle 10g Databases. Stay tunned...

New release of Lucene Domain Index based on Lucene 2.9.2

2010-03-29T07:12:00.000-07:00

Here we are, latest release of Lucene Domain Index is ready for downloading at SF.net download area.
As usual this is a new production release based on latest production release of Apache Lucene Project, but we always included some new functionalities :)
This release includes an interesting contribution from Pedro Pinheiro Lucene Did You Mean, to implement this functionality a dictionary can be created by calling:

BEGIN
DidYouMean.indexDictionary('SOURCE_BIG_LIDX','TEXT');
END;

this dictionary is orthogonal to the Domain Index updates and should be maintained externally by calling again above procedure if there are a lot of changes on the column(s) which was used as index terms.
Then you can easily query Did You Mean words by using a function:

SQL> select DidYouMean.suggest('SOURCE_BIG_LIDX','souce vody') from dual;
source body

More information about this package is at Lucene Domain Index documentation available for download as PDF or on-line through Google Docs.
Enjoy this new release!!!
Last minute comments: We are working with Lucene 3.0.1 release which works perfect on 11g databases because is only available for JDK1.5 API, but as everybody knows 10g installation are widely used aound the world, so We are working in a Lucene Domain Index 3.0.1 distribution back-ported to JDK1.4 using a retro-translator, once We have a full tested release it will be available for download, stay tunned.

New release of Lucene Domain Index based on Lucene 2.9.1

2010-02-16T04:39:00.000-08:00

We have released a new Lucene Domain Index (LDI) based on 2.9.1 core base.

Since Lucene 2.9.1 is on the road from several months ago why this new release of LDI came delayed?

The answer is because We added parallel processing support.

This new feature is enabled by a new LDI parameter ParallelDegree, by setting this parameter with a value greater than 1 LDI creates multiples Lucene directory storages to process insertions in parallel.

Let see a practical example:

create index source_big_lidx on test_source_big(text)
indextype is lucene.luceneindex parameters('BatchCount:250;ParallelDegree:2;SyncMode:OnLine;LogLevel:INFO;AutoTuneMemory:true;PerFieldAnalyzer:line(org.apache.lucene.analysis.KeywordAnalyzer),TEXT(org.apache.lucene.analysis.SimpleAnalyzer);FormatCols:line(0000);ExtraCols:line "line"');

Above example create a LDI with ParallelDegree equal to 2 and BatchCount equal to 250. Parallel degree is only used when SyncMode is OnLine, future LDI releases will includes parallel operations when SyncMode is Deferred.

Once this DML operation is executed LDI creates three OJVMDirectory Lucene stores, two for parallel index operations and the master store, then batches of 250 rows are enqueued for indexing in parallel mode.

Parallel insert implies a parallel operation (document creation and insertion in a secondary store) and serialized merge in the master store.

Machines with multi-core chips or RAC installations will speed up the LDI index creation/rebuild, obviously when an IO concurrence is the bottleneck there is no performance improvement when ParallelDegree>1. Look at this screenshot on my notebook

As you can see two background processes (AQ processes named ora_j*_test) are running consuming most of the CPU usage, these are LDI operations (insert|merge), also you can see two DB Writer process trying to write in parallel the information that LDI is generating.

A complete list of changes of this new release is at ChangeLog.txt file.

Downloads for 11g and 10g in binary format are SF.net project section download.

Source code, obviously, is available through CVS access.

On line documentation is available in PDF or as Google Document.

Revisiting JDBC Connection Pooling and Spring Data Source implementation

2009-12-14T16:11:00.000-08:00

Today, I have revisited the Oracle Implementation of OCI Connection Pooling.
Using my previous post

Using native Oracle Data Source and connection caching/pooling from Spring

I have updated the file examples:

The idea behind this tip is the same, do the connection pooling implementation by using the JDBC driver which is faster and safer.

If you are using an old database version, for example 10g, you can try installing at the middle tier the Install Client which is available for multiples platform, including Mac OS/X

HTH, Marcelo.

Experiences on Java programming within the databases, tips & tricks, tools, open source libraries and more

2009-10-06T10:56:00.000-07:00

This is the topic of my talk during unconference this year (Tuesday - Oct 13 - 10am - Overlook II) but, What is an unconference?
An unconference is a conference where the content of the sessions is driven and created by the participants, generally day-by-day during the course of the event, rather than by a single organizer, or small group of organizers, in advance (Source: Wikipedia). Unconferences came from the realization that "the sum of the expertise of the people in the audience is greater than the sum of expertise of the people on stage" (Source: Dave Winer).
During this talk I would like to transfer my experience on Java programming withing the Database since 1999.
Specially looking for three scenarios of work:

Traditional Java Stored Procedure programming
HTPP/HTTPS services through the XMLDB Servlet connector
Domain Index and pipe-line table function

This scenarios are covered by different Open Source projects used as practical examples DBPrism CMS, XMLDB Restlet Connector and Lucene Domain Index.
Each projects included several Open Source Libraries used and many tools for doing deployment and installing.
I hope I can see you all there next week... Marcelo

New release of Lucene Domain Index based on Lucene 2.9.0

2009-09-30T04:47:00.000-07:00

A new binary distribution of Lucene Domain Index (2.9.0.1.0) for Oracle 10g/11g has been released.

Lucene Domain Index is integration of Lucene Project running inside the Oracle JVM and integrated to the SQL layer by adding a new index type.
This new version uses latest Lucene 2.9.0 core libraries and introduces some of the changes on API.
Here complete list of changes:

Tested with Oracle 11gR2, 11gR1 and 10.2 databases.
DefaultUserDataStore do a SAX parsing to get text nodes and attributes from an XMLType value.
A SimpleLRUCache is used to load rowids and his associated Lucene doc id, this reduce memory consumption when querying very big tables. A new parameter has been added, CachedRowIdSize by default 10000 to control the size of the LRU cache.
Lucene Domain Index core was updated to use TopFieldCollector and to avoid computation time when lscore() is not used.
Two new parameter has been added NormalizeScore which control when to track the Max Score and when querying, both parameters are consequence of new Lucene Collector API and boost the performance when querying.
A table alias L$MT is defined for the master table associated to the index to be used in complex queries associating columns from master tables and columns from dependant tables.

Full documentation is at Google Doc.
Download binary version for Oracle 10g and 11g.

One of the biggest changed introduced into Lucene core libraries are in the TopCollector API, it introduces optimizations when you don't need to compute the score, track maximum score and preserve the Lucene document ID in order.

This changes are reflected in Lucene Domain Index through the usage of lscore() ancilliary operator. For example:

create table emails (
emailFrom VARCHAR2(256),
emailTo VARCHAR2(256),
subject VARCHAR2(4000),
emailDate DATE,
bodyText CLOB)
/

and an index created as:

create index emailbodyText on emails(bodyText) indextype is lucene.LuceneIndex
parameters('Analyzer:org.apache.lucene.analysis.StopAnalyzer;ExtraCols:emailDate "emailDate",subject "subject",emailFrom "emailFrom",emailTo "emailTo"');
-- required to Sort by subject
alter index emailbodyText parameters('FormatCols:subject(NOT_ANALYZED),emailFrom(NOT_ANALYZED),emailTo(NOT_ANALYZED)');
-- do not track max score
alter index emailbodyText parameters('NormalizeScore:false');

this queries reflect when score is computed or not.

SELECT /*+ DOMAIN_INDEX_SORT */ lscore(1) sc,subject
FROM emails where emailfrom like '%@gmail.com' and
lcontains(bodytext,'subject:lucene injection',1)>0
order by lscore(1) DESC;

Lucene score is computed and do not track maximum score, the result will be returned using relevance order descendant, default Lucene ordering no extra effort.

SELECT /*+ DOMAIN_INDEX_SORT */ subject
FROM emails
where lcontains(bodytext,'subject:lucene injection','subject:ASC',1)>0;

Lucene score is not computed and obviously the maximum score is not tracked.

Note that if we are querying using the optimizer hint DOMAIN_INDEX_SORT and lcontains(..,'subject:ASC',..) the score value is not relevant the result will be returned using the column subject ascending.

Similar result can be obtained using order by SQL, but to do that the RDBMS first collects all the rows that match to lcontains() operator and then do an order by, the difference in this simple test is an optimizer cost 3 over 2.

The NormalizeScore parameter is by default true, to get back compatibility, applications which assume an score in range 0..1 will run without any change, but if you want to get faster response time by avoiding max score computation you can change it by using alter index parameters DDL command.

To see which really means NormalizeScore parameter look a this example using WikiPedia English dump:

select /*+ FIRST_ROWS DOMAIN_INDEX_SORT */ lscore(1),
extractValue(object_value,'/page/title'),
extractValue(object_value,'/page/revision/timestamp') "revisionDate"
from pages where lcontains(object_value,'rownum:[1 TO 2] AND sport','revisionDate:ASC',1)>0;

with NormalizeScore:false returns:

1.56775963306427001953125 SCCA 25-FEB-02 03.43.11.000000000 PM +00:00
1.79172527790069580078125 Sports utility vehicle 04-APR-02 10.31.41.000000000 PM +00:00

with NormalizeScore:true returns:

0.875 SCCA 25-FEB-02 03.43.11.000000000 PM +00:00
1 Sports utility vehicle 04-APR-02 10.31.41.000000000 PM +00:00

Both results are logically because we are looking for a result order by revisionDate:ASC, but the score computed is different.

Well, I will be at Oracle Open World 09, if anybody want to know more about this kind of internal implementation I will talk at the Oracle Unconference and obviously we can meet at the OTN Lounge, see you there Marcelo.

Unleash Oracle ODCI API - OOW09 Voting Session

2009-06-17T05:28:00.000-07:00

Oracle Open World Voting session is a new way to create the conference session agenda.

I have submited two speaker session, one named "Unleash Oracle ODCI API" that is ready for voting at Oracle Mix comunity.

Oracle Data Cartridge API is provided to implement many powerful functionality such as new Domain Indexes, pipeline tables, and aggregated functions.

The presentation will include an introduction to this API showing many of his features using as example the code of a Lucene Domain Index which is a mix between Java running inside the OJVM and Oracle Object types.

Lucene Domain Index is an open source project which integrates the Apache Lucene IR library as a new Domain Index, providing features has free text searching, faceting, highlighting, filtering at index level, multi table/column indexes and more for 10g/11g databases.

Basically I would like to introduce this exciting API which allows developers to interact directly with the RDBMS engine and adding some examples in Java that are not included into the Oracle Documentation.

Well if you want to see this session at OOW09 please click here, see you there....

Oracle Web Services Manager book

2009-04-07T13:16:00.000-07:00

Recently I received a copy of the book Oracle Web Services Manager as courtesy of Pack Publishing editorial.
As everybody knows, I am more familiar with REST WS than SOAP and WS-* security standards, so I have read entirely the book :)
This book have a very good introduction to all security related stuff and also a comprehensive introduction to the product Oracle Web Services Manager.
For anybody who want to deploy secure WS using Oracle WS Manager the book will introduce you gradually ,and by examples, on all the steps related to authorization, authentication, digital signatures and many other topics.
On the client side the examples are in .Net technology (I prefer Java) but its a minor disappointment with the complete book content.
For anybody who want to take a closer look before buy the book, here a sample chapter on

Oracle Web services Manager-Authentication and Authorization

for downloading.
Enjoy the book....

Doing facets with latest Lucene Domain Index 2.4.1.1.0

2009-03-27T07:22:00.000-07:00

As usual, I am using the announce of new release of Lucene Domain Index to comments some internal detail of Oracle ODCI API.
Today, I will show how to use and works Lucene Domain Index facets.
Latest release implements an aggregate function lfacets() which performs bit set and grouping with Lucene. I am used this article as a jump start for doing facets.
Also I found another post on how to implement an aggregated function using ODCI API, but as with the pipeline table function implementation there is no documentation nor examples on how to do it with Java inside the database.
I am using same approach as the pipeline table function, a mix of PLSQL code plus an internal implementation using Java.
As you can see on the code there is an object type named facets_agg_type which is the implementation of lfacets() aggregated function, under the hood it creates the structure of the return type and call to the Java class which is doing a bit-set-AND operations on Lucene filters.
As an example let me show how to do facets with WikiPedia Spanish dump uploaded to Oracle XMLDB. I can query PAGES table with lcontains() operator as:

select /*+ DOMAIN_INDEX_SORT */ extractValue(object_value,'/page/title')
from pages where
lcontains(object_value,
'rownum:[20 TO 30] AND musica AND revisionDate:[20070101 TO 20070718]')>0;

But faceting is for doing grouping similar to
so using some categories already defined into WikiPedia Spanish web site.
I can create a simple table with the criteria for categorizing WikiPedia corpus.
Here a table with parent relation ship to organize the categories:

create table wiki_categories (
cat_code number(4),
cat_name varchar2(256),
cat_parent number(4),
CONSTRAINT PK_WIKI_CATEGORIES PRIMARY KEY (cat_code),
CONSTRAINT FK_CAT_PARENT FOREIGN KEY (cat_parent)
REFERENCES wiki_categories (cat_code)
);
/

insert into wiki_categories values (1,'Ciencias naturales y formales',null);
insert into wiki_categories values (2,'Ciencias humanas y sociales',null);
insert into wiki_categories values (3,'Artes',null);
insert into wiki_categories values (4,'Sociedad',null);
insert into wiki_categories values (5,'Tecnologías',null);

insert into wiki_categories values (6,'Astronomía',1);
insert into wiki_categories values (7,'Biología',1);
....
insert into wiki_categories values (38,'Exploración espacial',5);
insert into wiki_categories values (39,'Biotecnología',5);
insert into wiki_categories values (40,'Informática',5);
insert into wiki_categories values (41,'Ingeniería',5);
insert into wiki_categories values (42,'Medicina',5);

So with these categories I can use lfacets() aggregated function as:

select ljoin(lfacets('PAGES_LIDX_ALL,'||
case level when 1 then 'text:('|| cat_name||')'
ELSE 'text:('|| PRIOR cat_name||'),text:('|| cat_name||')'
END
)), cat_code,level
FROM wiki_categories
start with cat_parent is null
CONNECT BY PRIOR cat_code = cat_parent
group by cat_code,level;

or:

select ljoin(lfacets('PAGES_LIDX_ALL,'||
case level when 1 then 'text:('|| cat_name||')'
ELSE 'text:('|| PRIOR cat_name||'),text:('|| cat_name||')'
END
)), cat_parent
FROM wiki_categories
start with cat_parent is null
CONNECT BY PRIOR cat_code = cat_parent
group by cat_parent;

getting a result for the second case as:

text:(Ciencias naturales y formales),text:(Astronomía)(1280),text:(Ciencias naturales y formales),text:(Biología)(5144),text:(Ciencias naturales y formales),text:(Matemática)(4729),text:(Ciencias naturales y formales),text:(Física)(8708),text:(Ciencias naturales y formales),text:(Química)(4745),text:(Ciencias naturales y formales),text:(Botánica)(3697)

text:(Ciencias humanas y sociales),text:(Comunicación)(7319),text:(Ciencias humanas y sociales),text:(Filosofía)(6393),text:(Ciencias humanas y sociales),text:(Sociología)(2004),text:(Ciencias humanas y sociales),text:(Religión)(6429),text:(Ciencias humanas y sociales),text:(Psicología)(2508),text:(Ciencias humanas y sociales),text:(Política)(27356),text:(Ciencias humanas y sociales),text:(Mitología)(2672),text:(Ciencias humanas y sociales),text:(Lingüística)(2611),text:(Ciencias humanas y sociales),text:(Mitología)(2672),text:(Ciencias humanas y sociales),text:(Historia)(35727),text:(Ciencias humanas y sociales),text:(Egiptología)(91),text:(Ciencias humanas y sociales),text:(Derecho)(18586),text:(Ciencias humanas y sociales),text:(Economía)(9755)

text:(Artes),text:(Anime y manga)(1604),text:(Artes),text:(Arquitectura)(1981),text:(Artes),text:(Literatura)(2154),text:(Artes),text:(Teatro)(2818),text:(Artes),text:(Televisión)(1861),text:(Artes),text:(Pintura)(3323),text:(Artes),text:(Danza)(876),text:(Artes),text:(Música)(3643),text:(Artes),text:(Cine)(2235)
text:(Sociedad),text:(Gastronomía)(443),text:(Sociedad),text:(Videojuegos)(360),text:(Sociedad),text:(LGBT)(267),text:(Sociedad),text:(Deporte)(3647)

text:(Tecnologías),text:(Exploración espacial)(1375),text:(Tecnologías),text:(Biotecnología)(232),text:(Tecnologías),text:(Ingeniería)(1654),text:(Tecnologías),text:(Informática)(2017),text:(Tecnologías),text:(Medicina)(1085)

text:(Ciencias naturales y formales)(62689),text:(Tecnologías)(13772),text:(Ciencias humanas y sociales)(80857),text:(Sociedad)(25372),text:(Artes)(14515)

Note that I am using ljoin function which converts agg_tbl type (TABLE OF) to a coma separated string.
The computation of the whole WikiPedia dump, around 1 million of documents in my notebook took:

INFO: key '96' main category 'text:(Ciencias naturales y formales)'
INFO: sub category 'text:(Astronomía)'
INFO: sub category 'text:(Biología)'
INFO: sub category 'text:(Matemática)'
INFO: sub category 'text:(Física)'
INFO: sub category 'text:(Química)'
INFO: sub category 'text:(Botánica)'
INFO: Terminating category: 'text:(Ciencias naturales y formales)' Elapsed time: 58 millisecond.
INFO: key '97' main category 'text:(Ciencias humanas y sociales)'
INFO: sub category 'text:(Comunicación)'
INFO: sub category 'text:(Filosofía)'
INFO: sub category 'text:(Sociología)'
INFO: sub category 'text:(Religión)'
INFO: sub category 'text:(Psicología)'
INFO: sub category 'text:(Política)'
INFO: sub category 'text:(Mitología)'
INFO: sub category 'text:(Lingüística)'
INFO: sub category 'text:(Mitología)'
INFO: sub category 'text:(Historia)'
INFO: sub category 'text:(Egiptología)'
INFO: sub category 'text:(Derecho)'
INFO: sub category 'text:(Economía)'
INFO: Terminating category: 'text:(Ciencias humanas y sociales)' Elapsed time: 115 millisecond.
INFO: key '98' main category 'text:(Artes)'
INFO: sub category 'text:(Anime y manga)'
INFO: sub category 'text:(Arquitectura)'
INFO: sub category 'text:(Literatura)'
INFO: sub category 'text:(Teatro)'
INFO: sub category 'text:(Televisión)'
INFO: sub category 'text:(Pintura)'
INFO: sub category 'text:(Danza)'
INFO: sub category 'text:(Música)'
INFO: sub category 'text:(Cine)'
INFO: Terminating category: 'text:(Artes)' Elapsed time: 97 millisecond.
INFO: key '99' main category 'text:(Sociedad)'
INFO: sub category 'text:(Gastronomía)'
INFO: sub category 'text:(Videojuegos)'
INFO: sub category 'text:(LGBT)'
INFO: sub category 'text:(Deporte)'
INFO: Terminating category: 'text:(Sociedad)' Elapsed time: 18 millisecond.
INFO: key '100' main category 'text:(Tecnologías)'
INFO: sub category 'text:(Exploración espacial)'
INFO: sub category 'text:(Biotecnología)'
INFO: sub category 'text:(Ingeniería)'
INFO: sub category 'text:(Informática)'
INFO: sub category 'text:(Medicina)'
INFO: Terminating category: 'text:(Tecnologías)' Elapsed time: 18 millisecond.
INFO: key '101' main category '-ALL-'
INFO: sub category 'text:(Ciencias naturales y formales)'
INFO: sub category 'text:(Tecnologías)'
INFO: sub category 'text:(Ciencias humanas y sociales)'
INFO: sub category 'text:(Sociedad)'
INFO: sub category 'text:(Artes)'
INFO: Terminating category: '-ALL-' Elapsed time: 111 millisecond.

Not so bad considering that I am using a notebook and is not a server optimized for databases purpose. But Oracle have another good chance to speed up this query, a materialized view, let do that:

CREATE MATERIALIZED VIEW wiki_facets
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE AS
select ljoin(lfacets('PAGES_LIDX_ALL,'||
case level when 1 then 'text:('|| cat_name||')'
ELSE 'text:('|| PRIOR cat_name||'),text:('|| cat_name||')'
END
)), cat_parent
FROM wiki_categories
start with cat_parent is null
CONNECT BY PRIOR cat_code = cat_parent
group by cat_parent;

which obviously produce same result:

SQL> set timing on
SQL> select * from wiki_facets;
... same output here ....
Elapsed: 00:00:00.00
SQL>

but with no efforts!!!
You can refresh your materialized view at any point in time depend on your applications constraints.
Under the hood an aggregated function call to the ODCI API first to get the bit set (array of bits marked to 1, one for each document that match) of the first category, for example text:(Ciencias naturales y formales), then it will iterate over all his sub categories:
text:(Astronomía)(1280),
text:(Biología)(5144)
text:(Matemática)(4729)
text:(Física)(8708)
text:(Química)(4745)
text:(Botánica)(3697)
but on each iteration the main category is already computed. Also for caching purposes the Lucene Filter created to get a bit set with the matching documents is stored in Lucene Domain Index memory structures to be re-used by countHits function or lcontains() operator.
So if you execute a query like:

select LuceneDomainIndex.countHits('PAGES_LIDX_ALL','text:(Astronomía)') from dual;

A Lucene Filter required to get the number of hits is already filled by lfacets() function.
I have updated Lucene Domain Index documentation with latest functionality you can read on-line or download as PDF from SF.net web site, and remember that I'll talking about Java inside the database and some internal details of Oracle-Lucene integration next month at CLOUG 2009, see you there....

APEX proxy user support with DBPrism, aka middle tier authentication

2009-03-23T14:53:00.000-07:00

FYI a post which I had started in October 2008, now is ready but it was stored in this link.
Basically it covers a step by step guide to use APEX with proxy user support, aka middle tier authentication, very important if you want to use APEX with VPD database.

New binary release of Lucene Domain Index for 10g-11g

2009-03-09T10:44:00.000-07:00

Lucene Dev team just released a new production version (2.4.1) of the Lucene IR library, it was not announce yet, but its available at Apache download area.
So We released a new binary distribution compiled and tested with this new release.
There is only one change which fix an incompatibility issue between 10g and 11g database inside the pipeline table functions used for highlighting, the problem was related to how the internal JDBC driver return a column of SQL Date as object, 11g returns a java.sql.Timestamp and 10g returns a java.sql.Date. This affects the run-time behavior of the pipeline table functions which on the fly translates the Java Result Set to PLSQL execution layer.
I have made some test with Lucene 2.9 code base, available through CVS, and Lucene Domain Index works fine a fastest than previous releases, as usual with any Lucene code release.
This compilation can be done following the instruction of Lucene Domain Index documentation, section 2.3 - Install instruction for compiling from sources.
Finally remember that Lucene Domain Index is an updated solution for Oracle applications written in any language, Phyton, Ruby, .Net, PHP, PLSQL,... ;)

Highlighting features with latest Lucene Domain Index 2.4.0.1.0 - Part II - pipeline table function

2009-03-04T02:20:00.000-08:00

Some time highlighting functionality affects more than one column, so lhighlight() operator is not enough. I have plan to add more functionality to lhighlight() in next release, for example, to support highlighting on other columns rather than the master column, this parameter will eliminate previous limitation but will require that columns to highlight should be stored into Lucene index structure.
So, I decided to use pipeline table functions implemented in Java, another exciting functionality provided by ODCI API, but the problem is that the example and the documentation do not provides any hint on how to deal with transients types (AnyData, AnyDataSet and AnyType), AnyDataSet and AnyType allows you to create a function which returns any kind of queries, a required condition for a general purpose highlighting function.
Digging the web, I found only one valuable example of pipeline table function working with AnyType and AnyDataSet in PLSQL, the article from oracle-developer.net by Adrian Billington was a good jump-start for the solution.
According to Oracle 11g JDBC documentation there is support for AnyData, AnyDataSet and AnyType but are not allowed in Call Spec definition :( also this is only valid for 11g and I want to still supporting Lucene Domain Index for 10g databases.
Finally the solution was to use a mix between PLSQL and Java. Developers who are interested to see how can do that could take look at the code on line, CreatePipelineFunction.sql and Highlighter.java, the trick was to return from Java execution environment one row at time in object type having a place holder for each base SQL type, this type is rt_fetch_attributes and a collection RowInfo. If anyone have a better option please drop me an email or put a comment here!!.
OK, after this in depth introduction lets see some example of using highlighting features by using pipeline table functions, there are two options:

Phighlight(index_name VARCHAR2, qry VARCHAR2, cols VARCHAR2, stmt IN VARCHAR2) RETURN ANYDATASET
Rhighlight(index_name VARCHAR2, qry VARCHAR2, cols VARCHAR2, rType IN VARCHAR2, rws IN SYS_REFCURSOR) RETURN ANYDATASET

Phighlight() accepts a query source with the data to highlight as VARCHAR2 to be prepared and executed by using DBMS_SQL package. Rhighlight() accepts a SYS_REFCURSOR as argument but requires an extra argument to know which kind of result set type the query will return. For example:

SELECT * FROM
TABLE(phighlight(
'EMAILBODYTEXT',
'lucene OR mysql',
'SUBJECT,BODYTEXT',
'select lscore(1) sc,e.* from eMails e
where lcontains(bodytext,''security OR mysql'',''subject:ASC'',1)>0'
));

EMAILBODYTEXT is a Lucene Domain Index defined for table eMails as:

create table eMails (
emailFrom VARCHAR2(256),
emailTo VARCHAR2(256),
subject VARCHAR2(4000),
emailDate DATE,
bodyText CLOB)

create index emailbodyText on eMails(bodyText)
indextype is lucene.LuceneIndex
parameters('Analyzer:org.apache.lucene.analysis.StopAnalyzer;
ExtraCols:emailDate "emailDate",subject "subject",emailFrom "emailFrom",emailTo "emailTo"');

'lucene OR mysql' is Lucene Query used for highlighting, it should be equals to second argument of lcontains() to be consistent, except for the Lucene Domain Index extension rownum:[n TO m] AND used for pagination purpose.

'SUBJECT,BODYTEXT' are a coma separated list of columns to be highlighted, any other columns of the query result will be passed as is. Note that I am using uppercase because I am not defining alias at the SQL query, also only columns returned by JDBC as type String or CLOB will be highlighted.
Last argument of phighlight() is any SQL query to be parsed by DBMS_SQL package, this query must return columns of JDBC types String, BigDecimal, Timestamp, CLOB, TIMESTAMP, TIMESTAMPTZ and TIMESTAMPLTZ. Remember to use double single quotes ('') for escaping the SQL quote (') inside this argument.
SUBJECT column have results highlighted as "Re: <B>lucene</B> injection", ..., "<B>lucene</B> injection".
BODYTEXT column have values as "On Dec 21, 2006, at 4:56 AM, Deepan wrote:> I am bothered about security problems with <B>lucene</B>. Is it vulnerable to> any kind of injection like <B>mysql</B> injection? many times the query from> user is passed to <B>lucene</B> for search without validating.Rest easy. There are no known security issues with <B>Lucene</B>, and ithas even undergone a recent static code analysis by Fortify (see thelucene-dev e-mail list",..,"I am bothered about security problems with <B>lucene</B>. Is it vulnerable to any kind of injection like <B>mysql</B> injection? many times the query fromuser is passed to <B>lucene</B> for search without validating.".

A variant of phighlight() is rhighlight() which, in my opinion, is better than the above because eliminate the risk of SQL Injection by passing an insecure SQL string, here an example:

SELECT * FROM
TABLE(rhighlight(
'EMAILBODYTEXT',
'lucene OR mysql',
'SUBJECT,BODYTEXT',
'EMAILRSET',
CURSOR(select /*+ DOMAIN_INDEX_SORT FIRST_ROW */ lscore(1) sc,e.*
from eMails e where lcontains(bodytext,'security OR mysql','subject:ASC',1)>0)
));

'EMAILRSET' is collection of EMAILR type an object type defined to hold the result of the query passed as CURSOR(..). A SYS_REFCURSOR could be passed as argument from JDBC as .setObject(n,java.sql.ResultSet) method. This collection look like:

CREATE TYPE EMAILR AS OBJECT
(
sc NUMBER,
emailFrom VARCHAR2(256),
emailTo VARCHAR2(256),
subject VARCHAR2(4000),
emailDate DATE,
bodyText CLOB
);

CREATE OR REPLACE TYPE EMAILRSET AS TABLE OF EMAILR;

last argument uses CURSOR function to pass an SQL query as SYS_REFCURSOR.

The result of above query is the same as the previous one, but its using named types instead of transients types.
As you can see pipeline table functions are a powerful solution to do special treatment to queries. I mean some time you can not express all functionality in pure SQL or a functionality like highlighting can not be implemented in PLSQL, in that cases implementing pipeline table functions in Java using Oracle OJVM is the only solution.
At last, Lucene is close to get a next production release 2.4.1, my next post will be when this release is public and I'll upload a new version of Lucene Domain Index compiled a tested with it.
Also Lucene Domain Index and many other tips using Java inside the Database will be explained in my planned presentation at CLOUG 2009, see you there...

Highlighting features with latest Lucene Domain Index 2.4.0.1.0 - Part I - lhighlight() ancillary operator

2009-03-03T02:20:00.000-08:00

Once of the features more wanted of Lucene Domain Index was highlighting. Highlighting features in Lucene core distributions is provided from a long time ago, but the question was how to integrate it easy in the SQL syntax.
I decided to implement two kinds of highlighting in this release (2.4.0.1.0), which is a maintenance release for Lucene 2.4.0 core.
First is by using lhighlight(NUMBER):VARCHAR2 ancillary operator, ancillary operators are functions bounded to the current lcontains() execution, these are connected with the correlation ID, the NUMBER argument of lhighlight() and last argument of lcontains(), for example:

SQL> SELECT /*+ DOMAIN_INDEX_SORT */ lhighlight(1) txt,lscore(1) sc,subject
2 FROM emails where lcontains(bodytext,'security OR mysql','subject:ASC',1)>0;

Above query will return rows with a VARCHAR2 value having the matching text in bodytext column highlighted using a tag <B>security</B>, the rest of the row will be the score and the subject of the email.
There are two important points here, the piece of text highlighted is returned as VARCHAR2, it means his length will be less than 32K, this is not a big issue because usually the highlighted text is an small part of the whole text showed to users in order to provide more information for manually disambiguation. On the other hand ancillary operators receives from the RDBMS engine the text to highlight, not all SQL types are supported, current implementation is built for VARCHAR2, CLOB amd XMLType columns.
Unlike Lucene highlighting implementations columns to be highlighted are not stored in Lucene index structure, this is because the RDBMS engine automatically loads and transfers the content to highlight to the ancillary operator implementation.
The limitation of lhighlight() function is that it can only works with the master column of the index and Lucene Domain Index can index multiple columns at once, for example for an index created with the WikiPedia Spanish dump:

create index pages_lidx_all on pages p (value(p))
indextype is Lucene.LuceneIndex
parameters('PopulateIndex:false;DefaultColumn:text;SyncMode:Deferred;LogLevel:INFO;
Analyzer:org.apache.lucene.analysis.SpanishWikipediaAnalyzer;
ExtraCols:extractValue(object_value,''/page/title'') "title",
extractValue(object_value,''/page/revision/comment'') "comment",
extract(object_value,''/page/revision/text/text()'') "text",
extractValue(object_value,''/page/revision/timestamp'') "revisionDate";
FormatCols:revisionDate(day);IncludeMasterColumn:false;
LobStorageParameters:PCTVERSION 0 ENABLE STORAGE IN ROW CHUNK 32768 CACHE READS FILESYSTEM_LIKE_LOGGING');

In the above index definition the master column is not indexed as is, only the virtual columns title, comment, text and revisionDate are indexed, but highlighting features still evaluated with the whole row info which is of type XMLType, so an example query will look like:

SQL> select /*+ DOMAIN_INDEX_SORT */ lhighlight(1),extractValue(object_value,'/page/title') from pages where lcontains(object_value,'rownum:[1 TO 10] AND (musica tango rock)',1)>0;

<page xmlns="http://www.mediawiki.org/xml/export-0.3/" >
<title> <B>Música</B> de Argentina... [[Latinoamérica|latinoamericanos]] con más desarrollo en su [[<B>música</B>]].

Se encuentra una gran... argentinos, un instrumento tradicional andino]]
Aún se mantiene la <B>música</B> de los [[Indígenas_en_Argentina... de grandes corrientes de [[inmigración|inmigrantes]] europeos, la <B>música</B> argentina se enriqueció
Música de Argentina
musical emparentado con la [[habanera]] y el [[<B>tango</B> (<B>música</B>)|<B>tango</B>]].

==Diferencias con el <B>tango</B>==

Aunque tanto la milonga como el <B>tango</B> están en [[compás]] de 2/4, las 8 [[semicorchea]]s de la milonga están distribuidas en 3 + 3 + 2 en cambio el <B>tango</B> posee un ritmo más «cuadrado». Las letras...]] criticó en algún momento el <B>tango</B> y prefirió la milonga, que no trasmite la melancolía
Milonga (género musical)

Text in italic are only remarked to see the differences between the highlighted text and column title which is part of result.
On next post I'll talk about highlighting features using pipeline table functions implemented in Java, a big deal because there is not too much information and no native support for ANYDataSet results, stay tuned.

Using APEX into Oracle WebLogic Server 10.3

2008-11-10T02:58:00.000-08:00

This post is about how to use APEX applications running inside Oracle WebLogic Server 10.3
I am not an Web Logic specialist so with a little help from Oracle JDeveloper 11g I can deploy latest DBPrism Servlet Engine to run APEX applications like the Apache mod_plsql embedded gateway.
The easy way to deploy APEX applications is using a WAR file download from SourceForge.net web site.
1.- Download latest DBPrism dpls WAR and rename as dpls.war.
2.- Create JDeveloper project from a WAR file, remember start JDeveloper with a J2EE role. Following screenshots shows the step by step guide.3.- Download and install APEX if your target database do not have APEX installed. Remember:

3.a Change the Password for the ADMIN Account
3.b Unlock the APEX_PUBLIC_USER Database User
3.c Change the Password for the APEX_PUBLIC_USER Database User

4.- Edit prism.xconf file to define your target database, prism.xconf file is under [Web Content]/WEB-INF project directory, change properties.

4.a demo.db with your JDBC thin or OCI connect string
4.b DAD_apex dbpassword with the value defined in 3.c

5.- Create a WAR file with APEX's static resources following:

[mochoa@mochoa ~]$ cd /usr/local/apex/images/
[mochoa@mochoa images]$ zip -r /home/mochoa/jdevhome/mywork/cms-2.1-applib/i.zip .
.....
adding: jump.gif (deflated 21%)
adding: wwv_edit2_old.gif (deflated 86%)
[mochoa@mochoa images]$ mv /home/mochoa/jdevhome/mywork/cms-2.1-applib/i.zip /home/mochoa/jdevhome/mywork/cms-2.1-applib/i.war

6.- Create a JDeveloper project named APEX-static following the steps of section 2 but using i.war file created in the step 5. This project will look like:

6.a Edit web.xml file of APEX-static project removing Faces Servlet entries. This file is located at [Web Content]/WEB-INF project directory.
6.b Remove all libraries dependency defined in this project due it only contains static resources, righ click on the project node and choose Project properties->Libraries and Classpath, the list must be empty.

7.- Start Oracle Web Logic 10.3 embedded web server using JDeveloper menu Run -> Start Server Instance, console log will show something like:

/home/mochoa/.jdeveloper/system11.1.1.0.31.51.56/DefaultDomain/bin/startWebLogic.sh
[waiting for the server to complete its initialization...]
.

DefaultServer startup time: 39999 ms.
DefaultServer started.

8.- Deploy APEX-static project, right click on the project node and choose option Deploy->i->to->IntegratedWLSConnection. The log window will show something like:

---- Deployment started. ---- Nov 10, 2008 10:54:59 AM
Target platform is (Weblogic 10.3).
Running dependency analysis...
Building...
2008-11-10 10:55:03.712: Writing WAR file to /home/mochoa/jdeveloper/mywork/DBPrism/APEX-static/deploy/i.war
2008-11-10 10:55:08.837: Wrote WAR file to /home/mochoa/jdeveloper/mywork/DBPrism/APEX-static/deploy/i.war
Redeploying Application...
Application Redeployed Successfully.
Elapsed time for deployment: 22 seconds
---- Deployment finished. ---- Nov 10, 2008 10:55:20 AM

You can test i.war deployment with this URL http://localhost:7101/i/htmldb/apex_logo.gif an Oracle Application Express logo is shown.
9.- Deploy APEX-dpls project, rigth click on the project node and choose Deploy-dpls-to-IntegratedWLSConnection, console log will show something like:

---- Deployment started. ---- Nov 10, 2008 10:59:29 AM
Target platform is (Weblogic 10.3).
Running dependency analysis...
Building...
2008-11-10 10:59:40.775: Writing WAR file to /home/mochoa/jdeveloper/mywork/DBPrism/APEX-dpls/deploy/dpls.war
2008-11-10 10:59:40.917: Wrote WAR file to /home/mochoa/jdeveloper/mywork/DBPrism/APEX-dpls/deploy/dpls.war
Redeploying Application...
Application Redeployed Successfully.
Elapsed time for deployment: 56 seconds
---- Deployment finished. ---- Nov 10, 2008 11:00:25 AM

You can test dpls.war deployment with this URL http://localhost:7101/dpls/ a DBPrism project welcome page is shown.
10.- Start your Oracle database if is not already done.
11.- Log into APEX admin page using the link http://localhost:7101/dpls/apex/apex_admin using ADMIN password defined into the step 3.a

Finally I want to remark that APEX-WebLogic integration implemented with DBPrism mod_plsql emulation has some benefits from a traditional mod_plsql or mod_proxy deployment:

You can integrate to other J2EE development trough JSP forward or include tags, which means that you can create a J2EE application mixing traditional JSP/ADF Faces components with some parts implemented in APEX integrated through taglibs.
You have a lot of logging using Apache Log4J, logging information can be customized with the file log4j.properties located into APEX-dpls project, [Application Sources] project directory.
You can use Oracle Proxy user support into APEX application, I will write a post ASAP with changes into APEX-dpls project to enable JAAS authentication integration into APEX.

Enjoy APEX inside your Oracle WLS, Marcelo.

New binary release of Lucene Oracle Data Cartridge using Lucene 2.4.0

2008-10-10T13:28:00.000-07:00

A new binary release of Oracle Lucene Integration project is ready for downloading at SourceForge.net.
This release is tested with 10g and 11g databases on Linux, user must choose which release download according to the target database.
Thanks to Lucene developer group to release a new version faster than the others, obviously this new release of Lucene Data Cartridge for Oracle databases gets better results with the inclusion of latest binary components.
From my side this is a list of latest additions:

Added parameter for CLOB enconding
More Like this function
NGram analyzer
EnglishWikipediaAnalyzer
DataStore interface include API for setting current connection
Now analyzers, queries, snowball and wikipedia contrib packages are required

Remember that the full documentation of the projects is at Google Docs.
Also there is an On Line version of my presentation at the Oracle Open World 08 as Google Presenter.
This presentation is focused on the benefits of indexing inside the Database to get better performance by reducing network traffic and better solution for real time search engine applications by using a native/integrated solution with RDBMS engine.
Cheers, Marcelo.

New DBPrism CMS released

2008-10-09T05:03:00.000-07:00

Here a new release of DBPrism CMS!!!
After more than a year of usage of 2.1.1 without any bug reported this new release have a lot of new functionality implemented by our staff.
AFAIK DBPrism CMS is the only WCMS (Web Content Management System) which stores content's authors XML information inside the XMLDB repository.
Also is based on DBPrism Servlet Engine implementation and like Oracle APEX, it implements his functionality as Java Stored Procedure, but leaving the presentation concerns into the middle tier layer by using Cocoon Presentation Framework.
This architecture's design provides to web master/developer the functionality of a complete Web server re-design only by modifying a few XSL style sheets and CSS pages, once the changes are committed the complete web site is updated on the next click.
Also by using Apache document-v20.xsd document structure, the web site can be targeted to mobile devices, PDF or any other output format only by adding new XSL transformations.
Try it for free is Open Source ;)
Marcelo.

APEX proxy user support with DBPrism, aka middle tier authentication

2008-10-03T12:01:00.000-07:00

Here a simple quick list to get Apex using proxy user support with DBPrism standard distribution which by default includes Jetty web server.
The guide can be useful for other J2EE servlet container such as OC4J or WebLogic which can re-use Jetty WAR/EAR configuration to deploy Apex applications.
First Download and Install latest Apex distribution, if you don't have it already installed.
Download from:

http://www.oracle.com/technology/products/database/application_express/download.html

In my test using Oracle 11g standard edition One, I have unzipped it into /usr/local directory and executed

sqlplus "sys/syspass as sysdba" @apexins SYSAUX SYSAUX TEMP /i/

Requirements to run DBPrism and Jetty

JDK 1.5+
Ant 1.7.0+
DBPrism 2.1.2.1+
DBPrism CMS 2.1.2+ applib (it includes Jetty)

Compress as Jar archive all images and static Apex's resources.

-bash-3.2$ cd /usr/local/apex/images
-bash-3.2$ jar cvf /tmp/i.jar *
added manifest
adding: 16admin.gif(in = 130) (out= 133)(deflated -2%)
adding: 1pixelbeige.gif(in = 43) (out= 38)(deflated 11%)
....
-bash-3.2$ cp/tmp/ i.jar /home/mochoa/jdevhome/mywork/cms-2.1-applib/i.war

Edit Apache Ant build.xml file into DBPrism distribution and un-comment the step which un-compress above file after the WAR is deployed, build.xml file look like:

<target name="jetty-deploy" description="Deploy Jetty files on tmp">
....
<unzip src="${EXTRA_LIB_DIR}/i.war" dest="${tmp}/webapps/${project.web}/i"/>

Edit common.xml file with your target database, specially properties:

<property name="database" value="test"/>
<property name="thin_string" value="localhost:1521:test"/>
<property name="jdbc_string" value="jdbc:oracle:thin:@${thin_string}"/>
<property name="apex_pass" value="apex_pass" value="apex"/>

Unlock database user apex_public_user:

SQL> alter user apex_public_user identified by apex account unlock;

Edit a user who want to connect with proxy user support, for example SCOTT:

SQL> alter user scott grant connect through apex_public_user;

Edit $PRISM_HOME/conf/prism.xconf file and activate proxy user support for apex DAD:

<caetgory name="DAD_apex"/>
<property name="dbusername" value="apex_public_user"/>
<property name="dbpassword" value="@APEX_PASS@"/>
<property name="connectString" value="${demo.db}"/>
<property name="useProxyUser" value="true"/>
....
</category>

Another important point is that build.xml and common.xml files relies on JAVA_HOME and ORACLE_HOME environments variables, please check them before start Ant.
Edit $PRISM_HOME/conf/demoRealm.properties activating scott to be validated by middle tier authentication:

apex_demo: apex_demo,apex_users
scott: tiger,apex_users,apex_admins
admin: admin,apex_admins

Un-comment security section at $PRISM_HOME/conf/web.xml

<security-constraint>
<web-resource-collection>
<web-resource-name>DBPrism Apex Admin</web-resource-name>
<url-pattern>/apex/apex_admin</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>apex_admins</role-name>
</auth-constraint>
</security-constraint>

<security-constraint>
<web-resource-collection>
<web-resource-name>DBPrism Apex</web-resource-name>
<url-pattern>/apex/*</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>apex_admins</role-name>
<role-name>apex_users</role-name>
</auth-constraint>
</security-constraint>

Enable Basic or Form authentication for middle tier authentication:

<login-config>
<auth-method>FORM</auth-method>
<realm-name>DBPrism Demo Realm</realm-name>
<form-login-config>
<form-login-page>/logon.html?param=test</form-login-page>
<form-error-page>/logonError.html?param=test</form-error-page>
</form-login-config>
</login-config>

Start Jetty web server with:

ant run

Log with your browser into http://localhost:8888/
A DPPrism demo page is show
Also there are links to log into Apex administrative and regular pages:

http://localhost:8888/apex/
http://localhost:8888/apex/apex_admin

First Jetty will ask you for your middle tier credentials (defined into demoRealm.properties) use scott/tiger.
Then APEX will ask you for your application workspace/username and password.
With Apex administrations pages you can execute a SQL script to check your Oracle credentials (select user from dual;), instead of returning apex_public_user as connected user it should return scott (proxy user).
Also you can see the report Home>Manage Service>Session State>Recent Sessions.
If you any problem, edit $PRISM_HOME/conf/log4j.properties un-commenting last two lines and look for /tmp/dpls.log information, then reports problems using SourceForge.net Forums.
Remember that using proxy user support you can add to Apex applications Virtual Private Database support (VPD) or any other security layer provided by Oracle which relies on regular databases users, now your credential are a middle tier authenticated user (SCOTT in the example) not APEX_PUBLIC_USER!!!.
Also middle tier authentication includes any JAAS supported scheme such as SSO, LDAP, etc. check your application server documentation to see which JAAS modules are included.
Enjoy APEX/DBPrism combination....

Lucene Domain Index at OOW08

2008-09-01T10:14:00.000-07:00

Want to see Lucene Domain Index in action at Oracle Open World 08?
My friend Kuassi and I will present Lucene-Oracle integration during OOW08 (Oracle Develop: Database) session:

Data- and Compute-Intensive Processing: Middle Tier or Database? Trade-offs and Case Study
Monday 09/22/200811:30 - 12:30 Marriott Salon 01.

Also we can discuss about internal implementation, performance and future extensions.
See you there.
Marcelo.

PD: Remember that a full documentation about Oracle-Lucene integration by using a new Domain Index is available as Google document.

A Lucene-OJVM native REST WS

2008-07-15T05:11:00.000-07:00

I have done an example of using native REST Web Services implementing this Lucene WS API.
Also the example shows how to use Apache Maven to build and deploy Oracle-OJVM artifacts.
Information about how to install, compile and test the example is at Restlet Wiki, follow links under Using XDB Restlet Adapter within Maven projects section.
Have fun with REST inside Oracle OJVM.

XMLDB and Lucene Domain Index

2008-07-04T04:48:00.000-07:00

I am integrating DBPrism CMS with Lucene Domain Index project by replacing free text searching functionality implemented with Oracle Text.
DBPrism CMS uses XMLDB as storage system for content author documents.
CMS documents are in Apache document-v20 format, and are stored using this annotated XSD schema.
Mainly we choose XMLDB to get these benefits:

Documents are stored in an object relational table to get fast access in a relational way for some meta-data values.
Content authors information is stored as CLOB because only is searched using free text way.
XMLDB protocols handlers, content authors can use WebDAV or FTP to access to your documents instead of the CMS Front End for complex file system like operations such as copy, move, rename, backup operations.
We can use SQL duality to traverse the XMLDB folder structure using resource_view.
Get all the benefits of relational storage for storing non structured content, backup, concurrency access, etc.

As mentioned above a content authors information is stored in a CLOB columns and indexes with some free text searching functionality.
Now is indexed with Oracle Text with this script:

begin
ctx_ddl.create_preference('english_lexer','basic_lexer');
ctx_ddl.set_attribute('english_lexer','index_text','yes');
ctx_ddl.create_preference('spanish_lexer','basic_lexer');
ctx_ddl.set_attribute('spanish_lexer','index_text','yes');
ctx_ddl.create_preference('cms_docs_lexer','multi_lexer');
ctx_ddl.add_sub_lexer('cms_docs_lexer','default','english_lexer');
ctx_ddl.add_sub_lexer('cms_docs_lexer','spanish','spanish_lexer','es');
end;
/

create index cms_docs_xml_idx
on cms_docs c (value(c)) indextype is ctxsys.context
parameters('section group ctxsys.auto_section_group filter ctxsys.null_filter lexer cms_docs_lexer language column "XMLDATA"."lang"');

We index with Oracle Text using a top level attribute lang as language classifier to provide then the availability to choose between searching in Spanish or English document separately.
Now we will index this free text content using Lucene Domain Index with this definition:

create index cms_docs_xml_lidx
on cms_docs c (value(c)) indextype is lucene.LuceneIndex
parameters('LogLevel:ALL;IncludeMasterColumn:false;\
ExtraTabs:resource_view r;\
ExtraCols:extract(object_value,''//text()'') "text",\
substr(r.any_path,2,1) "path",\
extract(object_value,''/document/@lang'') "lang",\
extract(object_value,''//@href'') "links",\
extractValue(r.res,''/Resource/Owner'') "Owner",\
extractValue(r.res,''/Resource/ModificationDate'') "modTime";\
WhereCondition:extractValue(r.res,''/Resource/XMLRef'')=make_ref(cms_docs,object_id);\
FormatCols:path(UN_TOKENIZED),lang(UN_TOKENIZED)');

I will explain in detail each preferences when describing search use cases.

Use case 1 - search for some free text under public or private folders for an specific language
This use case basically use text field of Lucene Domain Index. Lucene Domain Index have a functionality called User Data Store which provides the availability of indexing virtually anything that you can extract in a simple or a joined query. At above example we will index information of the base table and information of resource_view (meta-data) joined by his document id (ExtraTabs and WhereCondition parameters). Note that we do not index a master column of the object relational table (his complete row) because we will choose using XMLDB extract function which information to index.
For this use case we need text, path and Owner Fields. Note that for path information we need to know only if a document is at /home or /public path, so only store one char (h or p). Following Oracle Text and Lucene query syntax example:
```
select /*+ domain_index_sort */ score(1),extractValue(c.object_value,'/document/header/title')
from cms_docs c,resource_view r  where under_path(r.res,'/public',2)=1 and
extractValue(r.res,'/Resource/XMLRef')=ref(c) and
extractValue(r.res,'/Resource/Owner')='DBPRISM_ADMIN' and
contains(c.object_value,
'Here a CTXCAT XML syntax for querying CMS in english',1)>0
order by score(1) DESC;
```
Lucene Domain Index syntax:
```
select /*+ domain_index_sort */
lscore(1),r.any_path,extractValue(c.object_value,'/document/header/title')
from cms_docs c,resource_view r
where extractValue(r.res,'/Resource/XMLRef')=make_ref(cms_docs,object_id) and
lcontains(c.object_value,
'text:CMS AND -lang:es AND path:p AND Owner:DBPRISM_ADMIN',1)>0
order by lscore(1) DESC;
```
Executions plans for both queries
Note that a highlighted text includes a double filter by execution as:
"P"."SYS_NC00019$"=SYS_XMLCONV('DBPRISM_ADMIN',7,2,2,1,4,23) AND "XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."XMLEXTRA","P"."XMLDATA"),'/public',2)=1

Highlighted filter on Lucene Domain Index syntax only includes a joined syntax with resource_view
"XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."XMLEXTRA","P"."XMLDATA"),'/',9999)=1
Also note that we use DOMAIN_INDEX_SORT optimizer hint to prevent sorting of the result due Oracle Text and Lucene returns rows order by score descending as natural order.
Note: In Lucene query syntax we use a negation of Spanish match field (-lang:es) to look for English documents, this negation includes document with lang="en" or without it which assume English.

Use case 2 - paginate over a result set efficiently
Obviously above queries need to be paginated in a real world application, to paginate above result using Oracle Text syntax we need to use Top-N syntax as follow:

select * from (select rownum as ntop_pos, q.* from (
select /*+ domain_index_sort */ score(1),extract(c.object_value,'/document/header/*')
from cms_docs c,resource_view r  where under_path(r.res,'/public',2)=1 and
extractValue(r.res,'/Resource/XMLRef')=ref(c) and
extractValue(r.res,'/Resource/Owner')='DBPRISM_ADMIN' and
contains(c.object_value,
'Here a CTXCAT XML syntax for querying CMS in english',1)>0
order by score(1) DESC
) q) where ntop_pos>=1 and ntop_pos<=10;

But using Lucene Domain Index its not necessary, look:

select /*+ domain_index_sort */
lscore(1),r.any_path,extractValue(c.object_value,'/document/header/title')
from cms_docs c,resource_view r
where extractValue(r.res,'/Resource/XMLRef')=make_ref(cms_docs,object_id) and
lcontains(c.object_value,
'rownum:[1 TO 10] AND text:CMS AND -lang:es AND path:p AND Owner:DBPRISM_ADMIN',1)>0
order by lscore(1) DESC;

Using Top-N syntax the cost of the query is 35 but with Lucene Domain syntax still in 30. See executions plans for both queries:

Use case 3 - filter a result set based a modification time of documents
Usually users want to filter and order documents based on his modification time, that is the reason to include resource_view information inside Lucene Domain Index, any information pushed inside Lucene Index will be faster if then is used to filter and sort the result without the intervention of Oracle where clause, on Oracle 11g Text you can do a similar task, but is designed for relational columns, not for and XML content joined with resource view, see Composite Domain Index (CDI) in Oracle Text.
Well here a filter and order by constraints for above queries:
select * from (select rownum as ntop_pos, q.* from (
select /*+ domain_index_sort */
score(1),r.any_path,extractValue(c.object_value,'/document/header/title'),extractValue(r.res,'/Resource/ModificationDate')
from cms_docs c,resource_view r
where under_path(r.res,'/home',2)=1 and
extractValue(r.res,'/Resource/XMLRef')=ref(c) and
extractValue(r.res,'/Resource/Owner')='CMS_PUBLIC' and
extractValue(r.res,'/Resource/ModificationDate') between
TO_TIMESTAMP('01.01.2006:00:00:00','DD.MM.YYYY:HH24:MI:SS') and
TO_TIMESTAMP('01.01.2007:00:00:00','DD.MM.YYYY:HH24:MI:SS') and
contains(c.object_value,
'Here a CTXCAT XML syntax for querying CMS',1)>0
order by extractValue(r.res,'/Resource/ModificationDate') ASC
) q) where ntop_pos>=1 and ntop_pos<=10;
Using Lucene Domain Index lcontains syntax:
select /*+ DOMAIN_INDEX_SORT */
extractValue(r.res,'/Resource/ModificationDate'),
extractValue(value(c),'/document/header/title')
from cms_docs c,resource_view r
where extractValue(r.res,'/Resource/XMLRef')=make_ref(cms_docs,object_id) and
lcontains(value(c),'rownum:[1 TO 10] AND text:CMS AND \
-lang:es AND path:h AND Owner:DBPRISM_ADMIN AND \
modTime:[20060101 TO 20070101]','modTime:ASC',1)>0;
Note that like pagination use case, here filter by and order by information is injected at lcontains() operator reducing a number of rows returned by the Domain Index and avoiding the filter view. Oracle Text syntax have a cost 35 and Lucene syntax down to 29 because no lscore() is computed. Following screenshots which depicts the execution plans:

This was the reason to store extractValue(r.res,''/Resource/ModificationDate'') "modTime during Lucene Domain Index build/update process.
Use case 4 - search for documents which have possible broken links to a moved/deleted page
DBPrism CMS have a functionality which advices in seconds which pages could have broken links when content authors moves, rename or delete CMS pages, not crawlers or spiders are required, only search his domain index. For example using Oracle Text syntax, if you rename a page named j1-Pages.html:
select path(2) path
from cms_docs c,resource_view r
where contains(c.object_value,'j1 Pages within a@href',1)>1 and
extractValue(r.res,'/Resource/XMLRef')=ref(c) and
under_path(r.res,'/home/DBPRISM_ADMIN/cms/en',2)=1;

and using Lucene Domain Index syntax:
select /*+ DOMAIN_INDEX_SORT */ r.any_path path
from cms_docs c,resource_view r
where extractValue(r.res,'/Resource/XMLRef')=ref(c) and
lcontains(value(c),'path:h AND -lang:es AND \
Owner:DBPRISM_ADMIN AND \
links:(+j1 +Pages)',1)>0;
Both queries return a page /home/DBPRISM_ADMIN/cms/en/j2-CMS/Users/frontend/index.xml which have a link to j1-Pages.html, the magic here is to use within operator with Oracle Text or look for the field links which was added during index build/update time with all the links extracted from the document under a@href tag. For above queries Oracle Text cost down to 34 because no score and order by is required and Lucene Domain Index still in 29.

Have fun with Lucene and XMLDB.

New DBPrism release - Apex users read this!!

2008-06-25T11:39:00.000-07:00

Two weeks ago I released a new DBPrism release, 2.1.2
DBPrism is a project which provides a J2EE mod_plsql replacement and is a core of DBPrism CMS which uses Java Stored Procedure support.
As mod_plsql replacement users can integrate Apex applications in any J2EE container without using Apache mod_plsql. This integration means that for example you can use JSP's include or forward tags to get content generated with Apex.
Also you can integrate your Apex application with your favorite SSO/JAAS strategy because in this release we include Oracle JDBC proxy user support. Proxy user support means that DBPrism will use a valid getUserPrincipal() or getRemoteUser() Servlet values to pass it as effective user to a JDBC connection which was initiated with a generic user also called big application user.
To know more about DBPrims proxy user support see DBPrism demo page information also you can find more information about how to work with Oracle proxy user support in this Blog page.
A full change log of this release is at Download page of DBPrism's web site.
Have fun, Marcelo

New binary release of Lucene Oracle Data Cartridge

2008-06-01T05:11:00.000-07:00

I am just released a new binary version of Lucene-Oracle integration.
Lucene Data Cartridge is a new Domain Index for Oracle 10g/11g databases running inside OJVM.
This release is integrated with Lucene 2.3.2 production version.
First consequence of using latest binary distribution is the indexing performance improvement, around 4x compared with previous release.
I have tested it using WikiPedia Spanish Dump, using a modest hardware (Pentium IV 2.26GHz 2G RAM/512K cache, SATA 110Gb disk) on 11G database with 800Mb SGA with this result:

Uploading speed 5500 pages/min.
Indexing speed 3520 pages/min.

Over 1.056.163 documents.
Note that upload speed is around 57% faster than indexing speed, but upload process do not includes parsing of text, only split the XML document and insert in a relational table. This is very important because is a measure of how fast is my hardware, also note that nobody will storage Wikipedia dump in my computer :)
Here another test using a VAIO notebook (Core 2 Duo 1.66GHz 2G RAM/1Mb cache, SATA 200Gb disk) on 11G database with 800Mb SGA:

Uploading speed 8478 pages/min.
Indexing speed 5374 pages/min.

Over a subset of 33.912 documents.
Search time is almost constant with a document set of 34K and 1M documents, for example:

SQL> select /*+ DOMAIN_INDEX_SORT */ extractValue(object_value,'/page/title')
2 from pages where lcontains(object_value,'rownum:[20 TO 30] AND text:musica*')>0;

EXTRACTVALUE(OBJECT_VALUE,'/PAGE/TITLE')
--------------------------------------------------------------------------------
Ribadeo
Pennisetum
Robert Nesta Marley
Argelia
Puccinellia
Nivel de energia
1144
Mañana
Marcha
Valsalabroso
Via Lactea

11 rows selected.

Elapsed: 00:00:00.45

Second execution (most of the structures are in cache Lucene+ROWIDs):

SQL> select /*+ DOMAIN_INDEX_SORT */ extractValue(object_value,'/page/title')
2 from pages where lcontains(object_value,'rownum:[20 TO 30] AND text:musica*')>0;

EXTRACTVALUE(OBJECT_VALUE,'/PAGE/TITLE')
--------------------------------------------------------------------------------
Ribadeo
Pennisetum
Robert Nesta Marley
Argelia
Puccinellia
Nivel de energia
1144
Mañana
Marcha
Valsalabroso
Via Lactea

11 rows selected.

Elapsed: 00:00:00.02

If first query is performed over 1 million documents it took 00:00:01.36, it means than for corpus about 26 times bigger the query is only 2 times slow, once the query is re-done over other window of the pagination which means all Lucene query structures are cached it took 00:00:00.08 without ROWID in cache and 00:00:00.02 after the ROWID are in cache.

Note: With 11g database there is an small glitch with JIT compiler which affects Lucene's DocumentWriters class, luckily the problem its not reproducible on 11.2 or 11.1.0.7 version, thanks to Lucene and Oracle Dev. teams to identified the problem. If you want to rebuild Lucene-OJVM code from sources just drop me an email to identified the problem.

Using Oracle VM templates on Linux with Xen

2008-05-06T14:34:00.000-07:00

Oracle provides a good tools with Oracle VM and his console tool. But for people like me who uses Mandriva Linux for historical reasons its possible to use Oracle EL and Oracle 11g templates.
For Mandriva users its simple as download the two templates, in my case:

Then install Xen support for Mandriva:

xen-3.1.0-8mdv2008.0
xen-doc-3.1.0-8mdv2008.0
kernel-xen-3.1.0-8mdv2008.0

And his related packages.
Restart your computer making sure that Xen Kernel is used (for example 2.6.18-xen), usually Xen kernel is placed at the boton of the list and its not the default kernel to boot.
Then start and enable Xen services as root:

# /etc/init.d/xend start
# chkconfig xend on

Uncompress above images into any directory, for example:

# cd /home
# tar xvfz OVM_EL5U1_X86_PVM_4GB.tgz
# cd OVM_EL5U1_X86_PVM_4GB
# cat ../oracle11g_x86.tar.bz2.*|tar xvfj -

Above directory will look like:

[root@escorpion OVM_EL5U1_X86_PVM_4GB]# ls
oracle11g_x86.img README README.oracle system.img vm.cfg
[root@escorpion OVM_EL5U1_X86_PVM_4GB]#

system.img file is an LVM volume with Oracle EL 5 operating system, oracle11g_x86.img file is a complete /u01 directory with an Oracle 11g database ready to use.
Finally using your favourite text editor creates a file named /etc/xen/oracle with:

memory = 1024
name = "oracle"
disk = [ 'file:/home/OVM_EL5U1_X86_PVM_4GB/system.img,hda,w',
'file:/home/OVM_EL5U1_X86_PVM_4GB/oracle11g_x86.img,hdb,w' ]
bootloader="/usr/bin/pygrub"
root = "/dev/hda1 ro"
extra = "3"
hostname = "oracle"
vif=['mac=00:16:3E:23:85:8B']

To start your Oracle VM using Xen command line execute:

xm create oracle

To connect to the virtual machine console execute:

xm console oracle

If everything is fine, you can log into the virtual machine using root/ovsroot as username and password.
The Oracle VM will gets his IP address using DHCP so may be you need to check vif parameter at above file if Xen virtual machine can't get a valid IP number due to conflicts with another machine.
If not, once you enter to the VM using the console you can change the file

/etc/sysconfig/network-scripts/ifcfg-eth0

with something like this:

DEVICE=eth0
BOOTPROTO=static
IPADDR=10.1.1.34
NETMASK=255.255.255.0
ONBOOT=yes
METRIC=10
MII_NOT_SUPPORTED=yes
GATEWAY=10.1.1.1
USERCTL=no
DNS1=10.1.1.5
DNS2=10.254.0.5
RESOLV_MODS=yes
IPV6INIT=no
IPV6TO4INIT=no

Knows caveats:

Xen kernels Dom0 do not have all drivers compiled, in my case a driver for a wifi cards was not included.
If you want to reduce Xen VM memory usage, first log into Oracle instance and reduce the parameter memory_target to a value which fits into a new VM memory.

Finally these tips are valid for any Linux distributions which include Xen support, using Oracle template running with Xen we have several benefits:

You can gets an Oracle 11g database running in Linux distros not certified by Oracle.
You don't care about touching your OS parameters, database users and groups, simple you can get an Oracle instance for development purpose ready to use unpacking a few files.
Oracle 11g running with Xen and Oracle EL paravirtualized version is faster than Oracle 11g running on VMWare or VirtualBox, Xen only impose a 0.4% of overhead into guest operating system.
If you have any problem with your database, simply restore your .img files and start again your Xen virtual machine, this is also valid if you want to perform a backup.
The memory footprint of the Xen virtual machine is similar as a native instance of the database, for example if you configure your Oracle instance to use 800Mb of RAM, your VM will use a little of megabytes extras.

Well this is my list of benefits may be other user will have your own list.

Can Oracle talk with you?

2008-04-14T04:51:00.000-07:00

Sure, it can.
Using Google Talk or any client which supports XMPP stack.
The jump start for this code example is the article published at OTN web site by Lucas Jellema.
But, Why do we need an XMPP stack supported inside the database?, here some ideas:

Instant DBA notifications with alerts, security, diskspace and memory usage, to a DBA GTalk mobile application.
Sent instant stock alerts in a retailer application.
Monitor workers presence (connected users into a private Jabber server) by a HR application.
Any application which sends email notifications and wants instant messaging replacement.

OK, here the script, the script performs these steps:

Create a new account to hold Smack Java XMPP stack implementation.
Load smack.jar using loadjava tool before create a MessageSender class, look at the comments inside the script.
Create a class MessageSender which is basically the same class showed at OTN web site but with a wrapper to create, compile and resolve inside the OJVM.
Crate a PLSQL callspec.
And finally a test case which creates a trigger attached to a sample table and which sends message to an specific Google account.

Hope this code example and tell me if there other sample applications of using XMPP stack inside the OJVM ;)
Finally, I would like to remark again the power of Java inside the Oracle database, this is another example of it. Remember that for sending email messages in older Oracle releases you need UTL_MAIL package which in many cases do not solve all the application need.

Is Oracle 11g REST ready?

2008-02-19T11:38:00.000-08:00

YES, if you have Oracle JVM installed!!
You only need to download an XMLDB REST adapter for Restlet framework and thats all.

But why users want to use REST stack inside Oracle databases if it comes with Native Web Services support using SOAP?
This is an open discussion over Internet, here one example at InfoQ web site - "Is REST winning?".
My personal list of benefits of REST over SOAP is:

Simplicity, from developer's perspective REST WS are quite straightforward.
Leverage of all HTTP features for caching, you can use Apache mod_cache or better than this Oracle Web Cache and ESI invalidation protocol to speed up your WS. Also the HTTP's 304 return code.
Less network traffic, a simple SOAP service requires 675 bytes at POST request and 306 bytes at response, similar REST WS only requires 79 bytes at GET request and 28 bytes at response. If you have a site with million of hits these differences are a lot wasted resources.

But anyway, you can implement now SOAP and REST WS inside your Oracle 11g database.
Performance result shows that you can get similar throughput (# request by second) either using SOAP native stack or REST Java stack, see this Google sheet with a preliminary result done using my notebook and Apache Benchmark.

Note that SOAP is 40% faster than REST, but SOAP example do not implement routing and many other things ready and used in Part12.java REST tutorial example, but look at a the performance improvement by simply using REST/Apache mod_cache combination.

- Requeriments

JDK 1.5 (included at your ORACLE_HOME/jdk directory)
Maven 2.0.x
an 11g target database
Internet connection to download public libraries into your local repository

- Install
XMLDB REST adapter for Oracle 11g database is build and deploy using Apache Maven tool, to install and test the code, open pom.xml file and edit section properties with your database parameter values.
Then in a command line execute:

mvn clean
mvn install

Maven will download all REST adapter libraries from public repositories (you need Internet connection at this point) except for Oracle libraries which are not in public sources. Maven will show that some dependency are not resolved satisfactorily. Using your ORACLE_HOME environment setting install it using for example:

mvn install:install-file -DgroupId=oracle -DartifactId=ojdbc5 \
-Dversion=11.1.0 -Dpackaging=jar -Dfile=$ORACLE_HOME/jdbc/lib/ojdbc5.jar

Repeat above process for each Oracle library required, all of them are at ORACLE_HOME directory, finally execute again install target.

- Test
Test your REST examples with your favorite browser using these URLs (they where configured as XMLDB Servlets by src/main/sql/registerServlet.sql script):

http://localhost:8080/search?kwd=marcelo+ochoa
http://localhost:8080/users/scott/orders/300
http://localhost:8080/restlet/xx
http://localhost:8080/hello

a POST example can be tested executing:

org.restlet.example.misc.SimpleClient

Tests using telnet
Some test can be tested using telnet to see which headers are sent and get as reponse from REST WS. At directory src/main/scripts there are two simple examples:
test_get.txt (similar to http://localhost:8080/users/scott/orders/300)
test_search.txt (similar to http://localhost:8080/search?kwd=marcelo+ochoa)
Use file information as follow:

[mochoa@mochoa ojvm-restlet]$ cat src/main/scripts/test_get.txt
GET /users/scott/orders/300 HTTP/1.0
Authorization: Basic c2NvdHQ6dGlnZXI=

[mochoa@mochoa ojvm-restlet]$ telnet localhost 8080
Trying 127.0.0.1...
Connected to live.dbprism.mochoa.dyndns.org (127.0.0.1).
Escape character is '^]'.
GET /users/scott/orders/300 HTTP/1.0
Authorization: Basic c2NvdHQ6dGlnZXI=

HTTP/1.1 200 OK
MS-Author-Via: DAV
DAV: 1,2,
Date: Thu, 07 Feb 2008 18:25:13 GMT
Server: Noelios-Restlet-Engine/1.1.1
Content-Type: text/plain; charset=ISO-8859-1
Content-Length: 28

Order "300" for user "scott"

Well this is a jump start for XMLDB REST Adapter, many things can be implemented from now, for example a XDBDirectory class which inherit from org.restlet.Directory to browse XMLDB repository, REST WS implemented with XQuery pages stored inside XMLDB, a Variant for XMLType objects and others, also We have a plan to integrate this extension to Restlet project main trunk, I'll announce this in another post, stay connected ....

Uploading WikiPedia Dumps to Oracle databases

2007-12-18T10:24:00.000-08:00

WikiPedia Dumps are a good set of documents if you want to test, for example, searching capabilities.
WikiMedia Download Web Site have XML dumps for all WikiPedia Web sites especially they have a very big compressed dumps of all the pages in English which his size is around 3Gbytes.
So with this very big XML file, We would like to load to a regular Oracle tables.
There is a plenty of utilities for uploading WikiPedia Dump to relational databases, but none of them are tailored for Oracle.
So, We can solve this missing part Creating XMLType Tables and Columns Based on XMLSchema and a SAX upload utility to upload the XML content.

Note: When you register an XML schema with Oracle XML DB, you can specify the storage model to use for the XML instance documents that reference the XML schema. If that specified storage model is binary XML, then you can only use that XML schema to create XMLType tables and columns that are stored as binary XML. Conversely, if the storage model specified for the registered XML schema is not binary XML, then you can only use that XML schema to create XMLType tables and columns that are not stored as binary XML, that is, stored object-relationally or as CLOB instances.

First, We need to annotate WikiPedia XSD export file to tell Oracle how to transform a page tag into an Object Type stored in a Object-Relational table, you can see a complete annotated XSD file here. It basically creates a new top element named "page" to give SAXLoader application a table to store all meta-data pages entries. It also creates two XMLDB self managed master detail relationship from pages to revisions and pages to uploads one to many relations.

You can register and creates all tables with:

BEGIN
DBMS_XMLSCHEMA.registerURI(
'http://www.mediawiki.org/xml/export-0.3/',
'/public/export-0.3.xsd',
LOCAL=>TRUE,GENTYPES=>TRUE, GENBEAN=>FALSE, GENTABLES=>TRUE);
END;
/

Before execute this PLSQL block upload export-0.3.xsd to XMLDB repository either by using ftp or WebDAV protocols to /public directory.
After executing registerURI procedure 3 new tables will be at your Oracle schema:

SQL> desc pages
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://www.mediawiki.org/xml/export-0.3/" Element "page") STORAGE Object-relational TYPE "PageType"

SQL> desc pages_revisions
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://www.mediawiki.org/xml/export-0.3/" Element "revision") STORAGE Object-relational TYPE "RevisionType"

SQL> desc pages_uploads
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://www.mediawiki.org/xml/export-0.3/" Element "upload") STORAGE Object-relational TYPE "UploadType"

With his underlying types:

SQL> desc "PageType"
"PageType" is NOT FINAL
Name
Null?
Type
SYS_XDBPD$
XDB.XDB$RAW_LIST_T
title

VARCHAR2(4000 CHAR)
id

NUMBER(38)
restrictions

VARCHAR2(4000 CHAR)
revision

XDB.XDB$XMLTYPE_REF_LIST_T
upload

XDB.XDB$XMLTYPE_REF_LIST_T

SQL> desc "RevisionType"
"RevisionType" is NOT FINAL
Name
Null?
Type
SYS_XDBPD$
XDB.XDB$RAW_LIST_T
id

NUMBER(38)
timestamp
VARCHAR2(4000 CHAR)
contributor
ContributorType
comment
VARCHAR2(4000 CHAR)
text

CLOB

SQL> desc "UploadType"
"UploadType" is NOT FINAL
Name
Null?
Type
SYS_XDBPD$
XDB.XDB$RAW_LIST_T
timestamp
VARCHAR2(4000 CHAR)
contributor
ContributorType
comment
VARCHAR2(4000 CHAR)
filename
VARCHAR2(4000 CHAR)
src

VARCHAR2(4000 CHAR)
size

NUMBER(38)

By using 3 tables We can choose to index some columns with any supported index provided by Oracle.
Also annotated schema tells to Oracle that use a CLOB column for storing user edited text (column text of table pages_revisions).
Shearing WikiDumps XML files with an XMLDB annotated schema provides an object-relational storage for an unstructured data.
Finally for uploading any WikiPedia Dump XML file using OTN example SAXLoader We use a modified configuration file like:

<?xml version='1.0' encoding='windows-1252'?>
<Connection>
<Driver>OCI</Driver>
<Hostname>localhost</Hostname>
<Port>1521</Port>
<ServiceName>orcl</ServiceName>
<SID>orcl</SID>
<ServerMode>DEDICATED</ServerMode>
<Schema>eswiki</Schema>
<Password>eswiki</Password>
<SourceXML>/home/backup/eswiki-20071202-pages-articles.xml</SourceXML>
<Element>page</Element>
<SchemaURL>http://www.mediawiki.org/xml/export-0.3/</SchemaURL>
<Table>PAGES</Table>
<ThreadCount>2</ThreadCount>
<CommitCharge>25</CommitCharge>
<Logger>FILE</Logger>
</Connection>

Using a modest hardware and uploading Spanish WikiPedia Dump We can see upload output as:

[mochoa@p1 build]$ more TraceOutputFile-20074317184301.log
Connection Settings =
<?xml version = '1.0' encoding = 'windows-1252'?>
<Connection>
<Driver>OCI</Driver>
<Hostname>localhost</Hostname>
<Port>1521</Port>
<ServiceName>orcl</ServiceName>
<SID>orcl</SID>
<ServerMode>DEDICATED</ServerMode>
<Schema>eswiki</Schema>
<Password>eswiki</Password>
<SourceXML>/home/backup/eswiki-20071202-pages-articles.xml</SourceXML>
<Element>page</Element>
<SchemaURL>http://www.mediawiki.org/xml/export-0.3/</SchemaURL>
<Table>PAGES</Table>
<ThreadCount>2</ThreadCount>
<CommitCharge>25</CommitCharge>
<Logger>FILE</Logger>
<StartCount>0</StartCount>
<EndCount>10</EndCount>
</Connection>

Driver = OCI
Hostname = localhost
Port = 1521
ServiceName = orcl
Server = DEDICATED
Schema = eswiki
Password = eswiki
Logger = FILE
Database URL = jdbc:oracle:oci8:@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(service_name=orcl)(server=DEDI
CATED)))
BaseApplication.getConnection(): Connecting as eswiki/eswiki@jdbc:oracle:oci8:@(description=(address=(host=localhost)(protocol=tcp)(port=1521)
)(connect_data=(service_name=orcl)(server=DEDICATED)))
BaseApplication.getConnection(): Database Connection Established
BaseApplication.getConnection(): Connecting as eswiki/eswiki@jdbc:oracle:oci8:@(description=(address=(host=localhost)(protocol=tcp)(port=1521)
)(connect_data=(service_name=orcl)(server=DEDICATED)))
Thread Writer_000001 started at Dec 17, 2007 6:43:01 PM ART
Thread Writer_000001 using SQL : insert into PAGES values (xmltype(?).createSchemaBasedXML('http://www.mediawiki.org/xml/export-0.3/'))
BaseApplication.getConnection(): Database Connection Established
Thread Writer_000002 started at Dec 17, 2007 6:43:02 PM ART
Thread Writer_000002 using SQL : insert into PAGES values (xmltype(?).createSchemaBasedXML('http://www.mediawiki.org/xml/export-0.3/'))
Thread Writer_000002 committed after 25 records.
..............
Thread Writer_000002 committed after 25 records.
Thread Writer_000001 completed at Dec 17, 2007 6:43:01 PM ART
Thread Writer_000002 completed at Dec 17, 2007 6:43:02 PM ART
Documents Queued = 583141. Docuuments De-Queued = 583141. Documents Written = 583141.
Writer_000001,Dec 17, 2007 6:43:01 PM ART,Dec 17, 2007 8:12:12 PM ART,293139,805,110,181
Writer_000002,Dec 17, 2007 6:43:02 PM ART,Dec 17, 2007 8:12:12 PM ART,290002,806,416,309

It took around of one hour and a half for uploading 583141 pages and 1.61Gbytes.

Note: this importing example works only on 11g databases because many WikiPedia pages have more than 64K of XML size and this is a limitation on 10g databases.

Adding more SQL like functionality to WikiDump XML information.

Adding a primary key constraint using XPath /page/id information:

alter table pages add CONSTRAINT pages_pkey PRIMARY KEY (XMLDATA."id");

This constraint will be helpful for queries like:

SELECT extract(object_value,'/page/revision/text/text()') "text"
FROM ESWIKI.PAGES where extractValue(object_value,'/page/id')=10

which produces an execution plan like:

Optimizer	Cost	Cardinality	Bytes	Partition Start	FILTER PREDICATES
SELECT STATEMENT	ALL_ROWS	1	1	43
SORT(AGGREGATE)			1	2069
NESTED LOOPS
NESTED LOOPS		3	1	2069
TABLE ACCESS(BY INDEX ROWID) ESWIKI.SYS_NTQY2zg8+I6PngQAB/AQAq6g==		2	1	37
INDEX(RANGE SCAN) ESWIKI.SYS_C007197		2	1		"NESTED_TABLE_ID"=:B1
INDEX(UNIQUE SCAN) ESWIKI.SYS_C007203		0	1		"SYS_ALIAS_0"."COLUMN_VALUE"="PAGES_REVISIONS"."SYS_NC_OID$"
TABLE ACCESS(BY INDEX ROWID) ESWIKI.PAGES_REVISIONS		1	1	2032		SYS_CHECKACL("ACLOID","OWNERID",xmltype(''))=1
TABLE ACCESS(BY INDEX ROWID) ESWIKI.PAGES		1	1	43		SYS_CHECKACL("ACLOID","OWNERID",xmltype(''))=1
INDEX(UNIQUE SCAN) ESWIKI.PAGES_PKEY	ANALYZED	0	1		"PAGES"."SYS_NC00010$"=10

and a unique constraint to prevent duplicate revision pages and faster access to an specific revision:

alter table pages_revisions add CONSTRAINT pages_revisions_ukey UNIQUE (XMLDATA."id");

pages_revisions table already have a primary key generated by the master detail relation discovered by the XSD to object-relational transformation, but with this unique constraint you can query directly pages_revisions table if you want to look-up an specific revision content:

SELECT extract(object_value,'/revision/text/text()') "text"
FROM ESWIKI.PAGES_REVISIONS where extractValue(object_value,'/revision/id')=13207462

which produces this execution plan:

Optimizer	Cost	Cardinality	Bytes	Partition Start	FILTER PREDICATES
SELECT STATEMENT	ALL_ROWS	1	1	2035
TABLE ACCESS(BY INDEX ROWID) ESWIKI.PAGES_REVISIONS		1	1	2035		SYS_CHECKACL("ACLOID","OWNERID",xmltype(''))=1
INDEX(UNIQUE SCAN) ESWIKI.PAGES_REVISIONS_UKEY	ANALYZED	0	1		"PAGES_REVISIONS"."SYS_NC00009$"=13207462

Finally We can index with Lucene Domain Index using:

create index pages_lidx on pages p (value(p))
indextype is Lucene.LuceneIndex
parameters('LogLevel:INFO;Stemmer:Spanish;ExtraCols:extractValue(object_value,''/page/title'') "title",extract(object_value,''/page/revision/text/text()'') "text";IncludeMasterColumn:false;SyncMode:OnLine;BatchCount:1000;LobStorageParameters:PCTVERSION 0 ENABLE STORAGE IN ROW CHUNK 32768 CACHE READS FILESYSTEM_LIKE_LOGGING');

Here a detailed parameter usage at Lucene Domain Index:

LogLevel:INFO, causes that Lucene Domain Index sent logging information to Oracle .trc files
Stemmer:Spanish, as you can see at SAXLoader configuration file We had uploaded Spanish WikiPedia Dump.
ExtraCols:extractValue(object_value,''/page/title'') "title",extract(object_value,''/page/revision/text/text()'') "text", We would like to index only title and text of XML page node.
IncludeMasterColumn:false, We don't want to index all XML content of PAGES's object type row, but We would like to be notified by ODCI API when this row is changed.
SyncMode:OnLine, Lucene Domain index will performs a full scan on PAGES table en-queueing for insert all his rowids and returns a control to the application, then DBMS_AQ CallBack will index PAGES table in background.
BatchCount:1000, to reduce Oracle databases resources consumed by DBMS_AQ Callback during initial indexing process batch process takes 1000 rows index them and exits.
LobStorageParameters:PCTVERSION 0 ENABLE STORAGE IN ROW CHUNK 32768 CACHE READS FILESYSTEM_LIKE_LOGGING, Oracle 11g specific parameter for using Secure LOB.

Immediately after create index command returns, you can start querying Lucene Domain Index because read-only access is permitted during background build process. For example:

SQL> select LuceneDomainIndex.countHits('ESWIKI.PAGES_LIDX','title:Argentina') from dual;

LUCENEDOMAININDEX.COUNTHITS('ESWIKI.PAGES_LIDX','TITLE:ARGENTINA')
------------------------------------------------------------------
117

After each batch of 1000 rows is done this value will be increased. Here other example:

select lscore(1),extractValue(object_value,'/page/title') from pages
where lcontains(object_value,'rownum:[1 TO 5] AND title:Argentina AND text:indigenas',1)>0
1 Argentina
0.641046464443206787109375 Noroeste argentino
0.625011622905731201171875 Paraná (Argentina)
0.623125135898590087890625 Córdoba (Argentina)
0.601441204547882080078125 Categoría:Indígenas de Argentina

With his execution plan:

Optimizer	Cost	Cardinality	Bytes	Partition Start	FILTER PREDICATES
SELECT STATEMENT	ALL_ROWS	3658	28633	281891885
TABLE ACCESS(BY INDEX ROWID) ESWIKI.PAGES		3658	28633	281891885		SYS_CHECKACL("ACLOID","OWNERID",xmltype(''))=1
DOMAIN INDEX ESWIKI.PAGES_LIDX					"LUCENE"."LCONTAINS"(SYS_MAKEXML('418347400648C734E040A8C0030115A3',4889,"XMLEXTRA","XMLDATA"),'rownum:[1 TO 5] AND title:Argentina AND text:indigenas',1)>0

Note: We are querying for pages which contains "indigenas" at text node, but this node is stored at PAGES_REVISIONS table but the query can be resolved without accessing to details table, all the information is stored at Lucene inverted structure and lcontains operator returns the correct set of rowids to Oracle engine to performs a fetch operation by index rowid which is a faster execution look-up plan. Also, We are using pagination information injected at lcontains operators which eliminates usage of Top-N Oracle syntax.

PLSQL sleep functionality

2007-11-21T02:55:00.000-08:00

Usually there is code which wants to sleep for certain amount of time and following his execution.
Java developers use Thread.sleep method for doing that, but PLSQL developer don't have a similar functionality.
There is a package named DBMS_LOCK with an sleep procedure, but his functionality expose other sensitive functionality and reading Oracle documentation you can see this warning:

"Consider granting the EXECUTE privilege only to specific users or roles."

With this point in mind and with a little help from Oracle JVM you can get an sleep procedure for your PLSQL applications or test suites, compile using SQLPlus:

set define ?

create or replace and compile java source named "my.Sleep" as
package my;
import java.lang.Thread;
public class Sleep {
public static void main(String []args) throws java.lang.InterruptedException {
if (args != null && args.length>0) {
int s = Integer.parseInt(args[0]);
Thread.sleep(s*1000);
} else
Thread.sleep(1000);
}
}
/

Above code creates a simple Java class which calls to Thread.sleep method passing an integer value expressing how many second you can sleep.
Now, to be ready to use from PLSQL you need to wrap it as procedure, here the code:

create or replace procedure waitSeconds(seconds IN VARCHAR2) as LANGUAGE JAVA NAME
'my.Sleep.main(java.lang.String [])';
/

Now you can simple test the procedure with SQLPlus:

SQL> exec waitSeconds('5');

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.01

You can compile this code as SYS and grant execute to public, it will not expose security holes.

Getting logging entering, exiting and throwing work at Oracle JVM

2007-11-21T02:21:00.000-08:00

Java Logging package comes with JDK 1.4 and up so is included inside Oracle JVM.
Log4j package is a powerful and standard defacto package for logging, I am used it a lot for several year in my code inside Oracle JVM.
But, if you don't want to add another external dependency in your code and do not need powerful logging capabilities, you can use java.util.logging package which is already installed on 10g and 11g databases.
TestLog.sql is a simple test suite which uses logging console handler, the output log using SQLPlus is:

SQL> Hello world
Nov 21, 2007 9:11:23 AM my.LogTest main
SEVERE: main forced exception/ by zero

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.80

As you can see entering, exiting and throwing methods does not print messages and logger setLevel is defined to ALL. This is because logging level used by these methods are controlled by ConsoleHandler level.
To get these methods work copy logging.properties file from your JAVA_HOME/jre/lib to ORACLE_HOME/javavm/lib directory and edit the line which includes level property:

# Limit the message that are printed on the console to INFO and above.
java.util.logging.ConsoleHandler.level = ALL
java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

Then shutdown and startup your Oracle database an re-test the code again, SQLPlus output will be:

SQL> exec Say('Hello world');
Dec 28, 2011 9:12:24 AM my.LogTest main
FINER: ENTRY Hello world
args[0]= Hello world
Dec 28, 2011 9:12:24 AM my.LogTest main
FINER: THROW
java.lang.ArithmeticException: / by zero
at my.LogTest.main(LogTest:26)
Dec 28, 2011 9:12:24 AM my.LogTest main
FINER: RETURN 1

PL/SQL procedure successfully completed.

This solve the problem and you can go back to another level by changing your code at the static initializer block.
Remember that users who want to use Java Logging package requires dbms_java.grant_permission('SCOTT','SYS:java.util.logging.LoggingPermission', 'control', '' ); for example.

Using native Oracle Data Source and connection caching/pooling from Spring

2007-10-05T10:04:00.000-07:00

A friend of mine told me that have several problem using and configuring connection pooling from Spring against Oracle.
So, I remember a nice hack to use Oracle native connection pooling and caching using Oracle DataSource object.
Since JDBC 10g (I think) Oracle provide a J2EE compliant DataSource with native connection pooling and caching, here latest documentation.
So why not use this Data Source direct within Spring applicationContext.xml file (see this example)
This bean (dataSource) then can be reference from any other Spring bean which requires a data source for connecting to the database.

URL property can be any thin or oci8 string, specially you can choose DEDICATED server mode, faster for middle tier-database traffic, or Transparent Failover configuration, here some examples extracted from Oracle docs, click here to see jdbc.properties example.

Hope this help.

Turbocharge your Oracle JVM applications with 11g

2007-09-25T07:02:00.000-07:00

As a part of performance testing of Oracle - Lucene integration, see previous post.
I had compared index creation time between Oracle 10gR2 and 11g.
Sure these are not official and well designed tests, but it shows many good reason to migrate Oracle JVM application to 11g.
I had created a simple test table on 10g database as:

create table test_source_big as (select * from all_sources);

Then, I had exported this table to 11g version to be sure that I am using similar data.
Finally creating a Lucene Domain Index at text column with:
11g version:

SQL> create index source_big_lidx on test_source_big(text)
2 indextype is lucene.LuceneIndex
3 parameters('MaxBufferedDocs:500;MergeFactor:500;LobStorageParameters:PCTVERSION 0 ENABLE STORAGE IN ROW CHUNK 32768 CACHE READS FILESYSTEM_LIKE_LOGGING');

Index created.
Elapsed: 00:04:54.86

10g version:

SQL> create index source_big_lidx on test_source_big(text)
2 indextype is lucene.LuceneIndex
3 parameters('MaxBufferedDocs:500;MergeFactor:500;LobStorageParameters:PCTVERSION 0 ENABLE STORAGE IN ROW CHUNK 32768 CACHE READS NOLOGGING');

Index created.
Elapsed: 00:14:47.27

Note Elapsed time!!!, but this is not all.
- 11g database is running on a VMWare Virtual Machine with Red Hat AS 4.0 hosted on Windows XP and configured with 1024Mb of RAM/Pentium Mobile 1.46Ghz
- 10g database is running native in a Mandriva 2007.1 with 2048Mb of RAM Pentium IV 2.26Gz.
There is not only difference between processor/memory, also there is difference running in native versus virtualized mode which usually is a 10 to 25% percent of overhead. All conditions are better for 10g but 11g still faster.
So why this number?
First 11g used a JIT technology compiling on the fly methods at classes executed intensively, second we are using latest SECURE LOB implementation.
On 10g I had already NCOMPed all Lucene classes but its not enough to get it faster than 11g.
Lucene Domain Index create operation is an intensive Java/BLOB operation and in this example there are:

4864 Temporary BLOB creation/deletion.
4864 insert into .... with rows including a previous BLOB information.
4420 update ... set deleted='Y' where name = ?, in means 4420 rows discarded when merge into new segments.
3537 select .... from ... where name = ?, these are segment reads before merge into a new one.

Finally the index size around 37Mb divided into 442 segments.

Running Lucene inside your Oracle JVM

2007-09-24T04:35:00.000-07:00

Since October of 2006 a new way to use Lucene is ready to use.
You can run Lucene inside your Oracle JVM because is pure Java Framework and get all benefits of them for your Oracle Application.
Why do I use Lucene inside Oracle?
Oracle include a full feature Enterprise Engine named Oracle Text made in C and fully integrated to the Oracle Engine, but:

you can not control which functionality will be included into next release
and you can not easily customize it for your needs

So why not load Lucene inside the Oracle JVM and integrate it to the Engine.
Luckily Oracle provides a Data Cartridge API, a powerful connector to implement custom index called Domain Index, Oracle Text is implemented using this API.
So uploading Lucene into the Oracle JVM and implementing a Domain Index is good option for many applications. OJVMDirectory is this option, the name is related to the component used to replace Lucene File System Storage by BLOB storage, here a simple list of points take into account to choose this storage:

Using traditional File System for storing the inverted index is not a good option for some users.
Using BLOB for storing the inverted index running Lucene outside the Oracle database has a bad performance because there are a lot of network round trips and data marshallings.
Indexing relational data stores such as tables with VARCHAR2, CLOB or XMLType with Lucene running outside the database has the same problem as the previous point.
The JVM included inside the Oracle database can scale up to 10.000+ concurrent threads without memory leaks or deadlock and all the operations on tables are in the same memory space!!

Also there are some important points integrating Lucene by using Oracle Data Cartridge API:

Changes on rows are automatically notified to Lucene, now these changes are enqueued using Oracle AQ. User can control if these changes are applied On Line or Deferred using Sync operation in batch mode.
Oracle optimizer can choose a proper execution plan if there is Domain Index created
You can mix lcontains() and lscore() operators in your queries.

Latest addition sponsored by Lending Club provides many other functionalities such as:

Synchronized with latest Lucene 2.2.0 production
Replaced in memory storage using Vector based implementation by direct BLOB IO, reducing memory usage for large index.
Support for User Data Stores, it means you can not only index one column at time (limited by Data Cartridge API on 10g), now you can index multiples columns at base table and columns on related tabled joined together.
User Data Stores can be customized by users, it means writing a simple Java Class users can control which column are indexed, padding functionality, storage and so on.
There is a DefaultUserDataStore which gets all columns of the query and built a Lucene Document with Fields representing each database columns, these fields are automatically padded if they have NUMBER or rounded if they have DATE data, for example.
lcontains() SQL operator support full Lucene's QueryParser syntax to provide access to all columns indexed.
Support for DOMAIN_INDEX_SORT and FIRST_ROWS hint, it means that if you want to get rows order by lscore() operator (ascending,descending) the optimizer hint will assume that Lucene Domain Index will returns rowids in proper order avoided an inline-view to sort it.
Automatic index synchronization by using AQ's Call Back.
Lucene Domain Index creates extra tables named IndexName$T and an Oracle AQ named IndexName$Q with his storage table IndexName$QT at user's schema, so you can alter storage's preference if you want.

Now we are working in another additions:

Implementing Array DML API, it means faster response time with operation such as insert into .. select .. from DML and faster dequeuing operations.
Independent column format for each indexed columns including left padding with 0 for NUMBER, left padding with any char for VARCHAR2 (specially for columns which represent codes), XPath expressions for XMLType and rounding for DATE/TIMESTAMP.
In Line pagination information at lcontains() operator using Lucene Query Parser's syntax, for example select .. from ... where lcontains('rownum:[1 TO 10] AND f2:xx AND f3:yy') instead of using Top-N query syntax.

Well anybody can join to this Open Source efforts :)

Automatize your Oracle Java VM tasks with Ant

2007-09-14T04:51:00.000-07:00

Why use Ant?

Ant is a defacto built tool for compiling, building, packaging, testing and many other Java project related task.

So why not use Ant to perform basic Server Side Java operation with Oracle.

Here a simple list of motivations:

after rebuild some packages automatically upload new jars to the test/production environment
independence of the build environment (Windows, Unix)
parametrized build scripts using properties
integrated build environment with other J2EE related projects

So which server side related tasks can be automatized?

loadjava and dropjava
JPublisher object types
SQLJ pre-compiler
SQLPlus executions
NComp classes for Oracle 10g

To re-use this functionality across all the projects we built Ant's macros.

Here the files loadjava.xml, jpub.xml, sqlj.xml, ncomp.xml and sqlplus.xml.

Using Ant's macros on your build.xml files

You can simply import the above files in your build.xml and start using it, all macros assume that ORACLE_HOME environment variable is properly set, either to an Oracle 10g/11g home directory or to a base directory of JDeveloper 10g/11g.

If you use JDeveloper layout directory SQLPlus and NComp macros will not work unless you copy sqlplus and ncomp binary files at jdev_home/bin directory and creates a directory network/admin with the tnsnames.ora file inside.

A little trick to use JDeveloper as Oracle Home layout directory is to install Oracle Instant Client package and perform the above steps.

Here a simple build.xml file which uses ncomp and loadjava macros, each XML file also has examples of usage inside.

Note about SQLPlus: Ant provides a JDBC task to perform basic SQL operations but have several limitations, one for example it can not log as sysdba.

Testing Java inside the Oracle VM

2007-09-12T04:21:00.000-07:00

Why test your code?

I will not explain the benefits of Test Driven Development (TDD), may be the book “Unit Testing in Java, How tests driver the code” by Johannes Link is the best source to do this. However, I agree with the author list of sentences and conclusions, for example:

I have no time for testing.
Software testing is boring and mindless.
My code is virtually faultless, good enough in any event.
The test department does the testing; they do this much better anyway.

Sure these points can be easily refused; for example if you don't test the code, the time involved solving bugs and debugging the code will be more than the time involved writing a good test case.

On the other hand if you write System.out sentences in your code sure you will need a test case for this and avoid the System.out.

The new agile methods, such as eXtreme Programming or Scrum among others, are test driven. This means that the development is centered in testing and this process is applied each time that is possible.

So the next paragraph shown how to test the Java code which run inside the Oracle JVM to get all the benefits of good code, faulty components, and code that can be easily tested with new libraries and database releases.

Testing Java Stored Procedures

Testing Java Stored Procedures do not differ from testing other Java code, but the naturalism of the execution environment is very different, this is a brief list of differences:

The JDBC drivers used is “Server-Side JDBC Internal Driver”, not conn.setAutoCommit(true) operation is supported for example.
The Oracle JVM uses different garbage collection techniques depending on the type of memory used. These techniques provide high efficiency and low overhead but impose some concepts different from traditional Java environments such as End of Call Migration objects.
The Class loader does not use the concept of CLASSPATH, instead of this a Library Manager is used with a complex mechanism for loading and resolving classes.
Threads on the OracleJVM are not used.

This list shown many differences, so testing the code inside the OracleJVM is very important to assure that the environment test is similar to the production environment.

Testing Call Specs

A simple way to test your Java code and the Call Specs is using SQLPlus, for example:


SQL> CALL pkg.delete_rows('emp', 'sal > 1500');Call completed.SQL> SELECT ename, sal FROM emp;ENAME          SAL--------- --------SMITH          800WARD          1250MARTIN        1250TURNER        1500ADAMS         1100JAMES          950MILLER        13007 rows selected.

But this test cannot be used in components that are not published using PLSQL Call Specs.

Testing the Code outside the OracleJVM

Alternative to the testing through the Call Specs you can test the Java code using JDeveloper or any other Java IDE, the point here is how to provide the JDBC connection, a traditional solutions is to use an static method like, see listing1.txt.

Then you can use the method on the application code as show, see listing2.txt.

But as you can see the connection attributes for the target database are hard coded or loaded using system properties, and many libraries such as Oracle XMLType implementation differs if you are creating the instance using JDBC Thin or Server Side drivers.

Testing the Code inside the OracleJVM

For testing your code inside the OracleJVM you can use simple System.out calls to print the result to the standard output.

If you need to redirect the System.out statement of the Java code these calls on the SQLPlus are required:


SQL> SET SERVEROUTPUT ON SIZE 5000

SQL> CALL dbms_java.set_output(5000);

SQL> CALL pkg.delete_rows('emp', 'sal > 1500');

Deleting rows on table 'emp' where 'sal > 1500'....



Call completed.

The JUnit framework

JUnit is a quasi-standard open source framework authored by Ken Beck and Erich Gamma and was a derivation off an SmallTalk project named SUnit also developed by Ken Beck. The official web site of the project is http://www.junit.org/; you can download the code, examples and tutorials from this site.

It provides the environment for automatic testing of Java code, the framework is very simple and do not depend on any other libraries.

The Framework design provides multiples way of launching and seeing the result of the test suites, by default it provides three different TestRunner classes:

junit.textui.TestRunner which uses the standard stdout for the results.
junit.awtui.TestRunner based on the Java AWT graphic toolkit.
junit.swingui.TestRunner based on the Java Swing toolkit.

A simplest way to start working with JUnit is to write a class which inherits from the abstract class junit.framework.TestCase. This abstract class provides a correct skeleton for writing your test case, your code will be a simple Java method starting with the “test” word, for example:


public class FooTest extend TestCase {

  public void testFoo() {

doSomething();

  };

}

If you need to do something before and after every testing method, you can override the setUp() and tearDown() methods, here an example, see listing3.txt.

Also with Oracle JDeveloper you can automatically generate test cases for any class using a wizard.

Installing JUnit in the OracleJVM

In the previous paragraph I mentioned that JUnit is a pure Java framework which does not depend on external libraries, so why not use JUnit inside the Oracle JVM?

Before using JUnit for testing your code you need to upload JUnit library into the Oracle JVM, to simplify the access to any database user who want to run unit test, a best place to upload junit.jar is on the SYS schema.

You can download JUnit 3.x branch from SourceForge web site, here the link of latest production version http://prdownloads.sourceforge.net/junit/junit3.8.2.zip?download , 4.0 release is not valid for Oracle OJVM because it uses the latest JDK1.5 assertion syntax.

Inside the ZIP file you can get the junit.jar file and upload it using loadjava command, see output1.txt.

-s flag creates public synonym to be available to other schemas

-g PUBLIC grant execute permission to everyone.

Now you are ready to run some JUnit test, inside the jar there is a Text UI Runner, so creating a Call Spec for this runner is the simplest way to start testing Java code inside the Oracle JVM.

A Call Spec for the Text UI Runner look like, see listing4.txt.

If you don't want to grant getClassLoader and other Oracle JVM permissions to PUBLIC, you can replace it for each database user who want to run JUnit test suites.

Writing your first test case

A simple Java test class extracted from the samples of JUnit distribution file can be uploaded using SQLPlus logged as SCOTT for example, see listing5.txt.

And also using SQLPlus you can run the test and get this output2.txt.

Obviously this usage of Text UI Runner can be optimized or specialized for running inside the Oracle JVM making an Oracle JVM Runner and an XML Printer class.

You can download these sources from ojvmtesting.sql, load these sources logged as SYS.

And create two Call Spec executing this script logged as SYS, see listing6.txt.

Now you can test the above Simple Test Suite using a new JUnit Runner logged as SCOTT, getting this output3.txt.

You can insert the XMLType instances with the result of the all the regression test of your project in a object relational table. Then you can make reports with this data suitable for a QA team.

Unit testing which uses tables

If your Unit test uses tables for selecting or inserting rows two caveats will appear:

You cannot insert the result of XMLType(OJVMRunnerAsString('c','junit.samples.SimpleTest')) into another table, an exception will be raised. To solve this problem, create an anonymous PLSQL block which stores the temporary result in a PLSQL variable, then insert this value in the object relational table.
If you load junit.jar into the SYS schema and use it through the above Call Specs using public synonym the code which is running with the rights of the definer will not see tables into another schema. To solve this problem, creates your own copies of OJVMRunner, OJVMTestSuiteLoader and XMLPrinter classes on the same schema of your table and create your own Call Specs. For example, using SCOTT account run this SQL script listing7.txt.

Conclusions and Future Works

The availability of the JUnit framework inside the Oracle JVM is a good starting point for writing better and more secure code, users who writes test suite for every components will get all the benefits of the new agile programming techniques.

The obviously extension is to provide a PLSQL environment for regression test which automatically run all the test suite and make usable reports as Apache Maven project.

Read the original WP here (included code).

Welcome

2007-04-10T15:07:00.000-07:00

Welcome to everyone!!!
I'll use this place as notepad for sharing experience, code and other related stuff to Oracle, Java and XML technologies.

Name	Null?	Type
SYS_XDBPD$		XDB.XDB$RAW_LIST_T
title		VARCHAR2(4000 CHAR)
id		NUMBER(38)
restrictions		VARCHAR2(4000 CHAR)
revision		XDB.XDB$XMLTYPE_REF_LIST_T
upload		XDB.XDB$XMLTYPE_REF_LIST_T

Marcelo Ochoa's personal blog

Implementing Oracle parallel shared server process in Java inside the Database

New release of Lucene Domain Index based on 3.4.0 code

Long time no post

New Oracle Lucene Domain Index release based on Lucene 3.0.2

Near-shore development in Tandil-Argentina

Dealing with JDK1.5 libraries on Oracle 10g

Auto complete functionality with latest Lucene Domain Index

New release of Lucene Domain Index based on Lucene 2.9.2

New release of Lucene Domain Index based on Lucene 2.9.1

Revisiting JDBC Connection Pooling and Spring Data Source implementation

Using native Oracle Data Source and connection caching/pooling from Spring

Experiences on Java programming within the databases, tips & tricks, tools, open source libraries and more

New release of Lucene Domain Index based on Lucene 2.9.0

Unleash Oracle ODCI API - OOW09 Voting Session

Oracle Web Services Manager book

Doing facets with latest Lucene Domain Index 2.4.1.1.0

APEX proxy user support with DBPrism, aka middle tier authentication

New binary release of Lucene Domain Index for 10g-11g

Highlighting features with latest Lucene Domain Index 2.4.0.1.0 - Part II - pipeline table function

Highlighting features with latest Lucene Domain Index 2.4.0.1.0 - Part I - lhighlight() ancillary operator

Using APEX into Oracle WebLogic Server 10.3

New binary release of Lucene Oracle Data Cartridge using Lucene 2.4.0

New DBPrism CMS released

APEX proxy user support with DBPrism, aka middle tier authentication

Lucene Domain Index at OOW08

A Lucene-OJVM native REST WS

XMLDB and Lucene Domain Index

New DBPrism release - Apex users read this!!

New binary release of Lucene Oracle Data Cartridge

Using Oracle VM templates on Linux with Xen

Can Oracle talk with you?

Is Oracle 11g REST ready?

Uploading WikiPedia Dumps to Oracle databases

Adding more SQL like functionality to WikiDump XML information.

PLSQL sleep functionality

Getting logging entering, exiting and throwing work at Oracle JVM

Using native Oracle Data Source and connection caching/pooling from Spring

Turbocharge your Oracle JVM applications with 11g

Running Lucene inside your Oracle JVM

Automatize your Oracle Java VM tasks with Ant

Why use Ant?

Using Ant's macros on your build.xml files

Testing Java inside the Oracle VM

Why test your code?

Testing Java Stored Procedures

Testing Call Specs

Testing the Code outside the OracleJVM

Testing the Code inside the OracleJVM

The JUnit framework

Installing JUnit in the OracleJVM

Writing your first test case

Unit testing which uses tables

Conclusions and Future Works

Suggested reading

Welcome