Persistence.xml and the Oracle hint FIRST_ROWS

I found out that it is important to specify the exact version of Oracle in the persistence.xml file. I am currently using EclipseLink as the persistence framework provider.

Oracle hints were introduced to speed up/optimize SQL queries. Starting with Oracle 10, some of them have become almost unecessary because the optimizer was greatly improved. Also, depending on the structure of the queries and the database design (use of primary keys or not, index are where they should be or not, etc), hints can be counterproductive since they slow down queries instead of speeding them up.

SELECT * FROM (
SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (
SELECT col1, col2 from table1)
a WHERE ROWNUM <= 20)
WHERE rnum > 0;

This SQL query is used for pagination, was coded with JPA criteria and was generated by EclipseLink using ORACLE as the target database as specified in the following persistence.xml file :

<property name="eclipselink.target-database" value="Oracle" />

We use Oracle 11g. In order to indicate EclipseLink to produce SQL code for Oracle11, the target database should be ORACLE11, not ORACLE:

<property name="eclipselink.target-database" value="Oracle11" />

In that case, the SQL query generated no longer contains the FIRST_ROWS hint :

SELECT * FROM (
SELECT a.*, ROWNUM rnum  FROM (
SELECT col1, col2 from table1)
a WHERE ROWNUM <= 20)
WHERE rnum > 0;

If we examine the source code of EclipseLink, we can see in OraclePlatform.java the initial value of the HINT variable :

<b>protected</b> String HINT = "/*+ FIRST_ROWS */ ";

In the Oracle10Platform class, which inherits from OraclePlatform, that value is overriden :

public Oracle10Platform(){
super();
//bug 374136: override setting the FIRST_ROWS hint as this is not needed on Oracle10g
HINT = "";
}

Generate the database schema with Hibernate3 Maven Plugin

There is a nice Maven plugin for JPA/Hibernate that makes it possible to quickly generate the database schema (SQL) and save it in a file.
The artifactId of this plugin is hibernate3-maven-plugin.
It will scan all JPA annotations in the class files of the entities and generate the corresponding SQL queries.
A persistence.xml file is required.

  1. With version 2.2 :

Content of the pom.xml :


<build>
  <plugins>
...
<plugin>
				<groupId>org.codehaus.mojo</groupId>
				<artifactId>hibernate3-maven-plugin</artifactId>	
                                <version>2.2</version>			
				<configuration>
		       	   <components>
						<component>
							<name>hbm2ddl</name>
							<implementation>jpaconfiguration</implementation>																									
						</component>							
					</components>
				   <componentProperties>
                    <drop>true</drop>
                    <create>true</create>
                    <export>false</export>
                    <format>true</format>                    <outputfilename>schema-${DataBaseUser}-${DatabaseName}.sql</outputfilename>
                    <persistenceunit>myPU</persistenceunit>
                    <propertyfile>src/main/resources/database.properties</propertyfile>
                </componentProperties>
			  </configuration>		
			  <dependencies>
			  	<dependency>
					<groupId>com.oracle</groupId>
					<artifactId>ojdbc14</artifactId>
					<version>10.2.0.2.0</version>
				</dependency>			  
			  </dependencies>					
		</plugin>
	  </plugins>
	</build>

Continue reading

Remotely accessing the database homepage from a browser

The last step to complete the installation of Oracle usually requires to configure the database (users, schemas, tables etc) through the apex web page.
If your server is running locally, then all you need to do is point your browser to the following URL:
http://localhost:8080/apex (or another port if you did not use the default one, 8080).

However if you have installed Oracle on a remote server, this URL will not work.
In order to make it work, I found out that you need to enable remote HTTP connection with SQL command line :

[me@somewhere admin]# sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 24 21:23:20 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter user-name: SYSTEM
Enter password: 
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
PL/SQL procedure successfully completed.

Link :
http://download.oracle.com/docs/cd/B25329_01/doc/admin.102/b25107/network.htm#BHCBCFBA

Running Oracle SQLPlus with Linux

Environment :
Linux kernel : 2.6.18-194.26.1.el5 (uname -r)
Distro : CentOS release 5.5 (Final) (cat /etc/issue)
Oracle : Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

If you get the following annoying message :

[me@somewhere]$sqlplus
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

then do not waste your time installing patches, changing files and folders permissions etc.
The problem resides in the environment variables settings.
You need to set up the ORACLE_HOME variable correctly.

If after setting that ORACLE_HOME environment variable correctly, you get this other annoying message :

[me@somewhere ~]# sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 23 17:48:03 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: SYSTEM
Enter password: 
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

then you need to set up other environment variables (ORACLE_SID, NLS_LANG, LD_LIBRARY_PATH).

Fortunately Oracle provides a script that contains all these environment variables with the right values.
This script is called oracle_env.sh and is located here :
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin

All you need to do is insert these lines in your .bash_profile and you’re ready to connect to SQLPlus in no time !