Thank you for visiting! If you liked the site, please add a bookmark, else add a critical comment! Would you like to visit Greece? Traditional Greek flag This real estate site is only available in Greek! The holy mountain

Tuesday 17 May 2011

Migrating an EJB 3 application to the new JDeveloper version

This new post discusses several exceptions which have occurred because of migration to the current version of JDev. As far as I can remember the application was working well with the previous version. It seems that code rots!
The first has to do with populating a primary key field with a sequence in an entity EJB:

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "BID_SEQUENCE" )
    @SequenceGenerator(name = "BID_SEQUENCE", sequenceName = "BID_SEQUENCE",
    allocationSize = 10)
       
If you get an:

 Exception Description: The sequence named [YOUR_SEQUENCE_NAME] is setup incorrectly. Its increment does not match its pre-allocation size.

 A way to avoid the exception is to  use 100 as a start value in sql create sequence statement. That will resolve the problem: by default the start value is 1, when Eclipselink attempts to use the first allocated sequence value it's negative 1 - 100 + 1, that causes the exception.

The second is more subtle to resolve, as the error message does not appear at once. If you get a mysterious error about a missing table which does not belong to the application db schema:

      Eclipse Persistence Services - 2.1.3.v20110304-r9073):
      org.eclipse.persistence.exceptions.DatabaseException Internal Exception:
      java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
      Error Code: 942 Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
      bind => [50, SEQ_GEN] Query: DataModifyQuery
      (sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")

a way to get rid of the exception is editing the persistense.xml as follows:


<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
             version="1.0">
  <persistence-unit name="Model">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/ActionBazaarDS</jta-data-source>
    <class>actionbazaar.persistence.Category</class>
    <class>actionbazaar.persistence.BillingInfo</class>
    <class>actionbazaar.persistence.Order</class>
    <class>actionbazaar.persistence.User</class>
    <class>actionbazaar.persistence.Bid</class>
    <class>actionbazaar.persistence.Bidder</class>
    <class>actionbazaar.persistence.Seller</class>
    <class>actionbazaar.persistence.Item</class>
    <class>actionbazaar.persistence.ContactInfo</class>
    <class>actionbazaar.persistence.ShippingInfo</class>
    <properties>
      <property name="eclipselink.target-server" value="WebLogic_10"/>
      <property name="javax.persistence.jtaDataSource"
                value="jdbc/ActionBazaarDS"/>
      <property name="eclipselink.target-database" value="Oracle11"/>
      <!--Addon in case of
      Eclipse Persistence Services - 2.1.3.v20110304-r9073):
      org.eclipse.persistence.exceptions.DatabaseException Internal Exception:
      java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
      Error Code: 942 Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
      bind => [50, SEQ_GEN] Query: DataModifyQuery
      (sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")>
      -->
      <property name="eclipselink.ddl-generation" value="create-tables"/>
    </properties>
  </persistence-unit>
</persistence>


Thus, the new SEQUENCE table will be automatically created and the application will at last commit the insert successfully.
The morale of the story is that frequent migrations due to upgrades can cause trouble. Perhaps, applying gradual patches instead, i.e. via the jdev online extensions way and waiting for a new full release would be safer. Otherwise you might spend hours searching about solutions on the internet, experimenting with each one and certainly overheating the computer. Always make sure you are in a well ventilated room, with full air conditioning when trying to migrate, or else high temperatures will cause your code to decay so much faster! Certain laptops such as Hewlett Packard Pavilion, overheat and power off immediately without prompting for save, very frequently at warm climates. After all, you don't want your code to go rot, do you?

Monday 11 April 2011

JDeveloper 11g: Setting up the newest java development kit

 This new post is about users who would like to update to the newest jdk, without having to waste time or hard disk space, by setting up from scratch JDeveloper 11g, each time a new jdk is available. Moreover, people who still use older versions of the database such as 9, may also find handy the jdbc connection localization properties hint which follows. If you 'd like to jump to it at once, please search for connecting to Oracle. A full sample of jdev.conf file is presented as a reference for your convenience. So let's get started!

First, you need first to setup the Sun 64bit jdk, then you need to edit the

C:\Oracle\Middleware\jdev_11114\jdeveloper\jdev\bin\jdev.conf file:

#
# Directive SetJavaHome is not required by default, except for the base
# install, since the launcher will determine the JAVA_HOME. On Windows
# it looks in ..\..\jdk, on UNIX it first looks in ../../jdk. If no JDK
# is found there, it looks in the PATH.
#
#SetJavaHome C:\Oracle\Middleware\jdev_11114\jdk160_21
SetJavaHome C:\Program Files\Java\jdk1.6.0_24
...

and you might get the following message the next time you start JDev
Do not overdo it!

 Finally the integrated weblogic server C:\Users\name\AppData\Roaming\JDeveloper\system11.1.1.4.37.59.23\DefaultDomain\bin\setDomainEnv.cmd script:

@ECHO OFF

@REM WARNING: This file is created by the Configuration Wizard.
@REM Any changes to this script may be lost when adding extensions to this configuration.

@REM *************************************************************************
@REM This script is used to setup the needed environment to be able to start Weblogic Server in this domain.
@REM
@REM This script initializes the following variables before calling commEnv to set other variables:
@REM
@REM WL_HOME - The BEA home directory of your WebLogic installation.
@REM JAVA_VM - The desired Java VM to use. You can set this environment variable before calling
@REM this script to switch between Sun or BEA or just have the default be set.
@REM JAVA_HOME - Location of the version of Java used to start WebLogic
@REM Server. Depends directly on which JAVA_VM value is set by default or by the environment.
@REM USER_MEM_ARGS - The variable to override the standard memory arguments
@REM passed to java.
@REM PRODUCTION_MODE - The variable that determines whether Weblogic Server is started in production mode.
@REM DOMAIN_PRODUCTION_MODE
@REM - The variable that determines whether the workshop related settings like the debugger,
@REM testconsole or iterativedev should be enabled. ONLY settable using the
@REM command-line parameter named production
@REM NOTE: Specifying the production command-line param will force
@REM the server to start in production mode.
@REM
@REM Other variables used in this script include:
@REM SERVER_NAME - Name of the weblogic server.
@REM JAVA_OPTIONS - Java command-line options for running the server. (These
@REM will be tagged on to the end of the JAVA_VM and
@REM MEM_ARGS)
@REM
@REM For additional information, refer to "Managing Server Startup and Shutdown for Oracle WebLogic Server"
@REM (http://download.oracle.com/docs/cd/E17904_01/web.1111/e13708/overview.htm).
@REM *************************************************************************

set COMMON_COMPONENTS_HOME=C:\Oracle\Middleware\jdev_11114\oracle_common
for %%i in ("%COMMON_COMPONENTS_HOME%") do set COMMON_COMPONENTS_HOME=%%~fsi

set WL_HOME=C:\Oracle\Middleware\jdev_11114\wlserver_10.3
for %%i in ("%WL_HOME%") do set WL_HOME=%%~fsi

set BEA_JAVA_HOME=

@REM On 16.3.2011 added C:\Program Files\Java\jdk1.6.0_24 instead of C:\Oracle\Middleware\jdev_11114\jdk160_21
set SUN_JAVA_HOME= C:\Program Files\Java\jdk1.6.0_24

if "%JAVA_VENDOR%"=="Oracle" (
set JAVA_HOME=%BEA_JAVA_HOME%
) else (
if "%JAVA_VENDOR%"=="Sun" (
set JAVA_HOME=%SUN_JAVA_HOME%
) else (
set JAVA_VENDOR=Sun
@REM On 16.3.2011 added C:\Program Files\Java\jdk1.6.0_24 instead of C:\Oracle\Middleware\jdev_11114\jdk160_21
set JAVA_HOME=C:\Program Files\Java\jdk1.6.0_24
)
)

@REM We need to reset the value of JAVA_HOME to get it shortened AND
@REM we can not shorten it above because immediate variable expansion will blank it

set JAVA_HOME=%JAVA_HOME%
for %%i in ("%JAVA_HOME%") do set JAVA_HOME=%%~fsi

set SAMPLES_HOME=%WL_HOME%\samples

set DOMAIN_HOME=C:\Users\Nick\AppData\Roaming\JDeveloper\system11.1.1.4.37.59.23\DefaultDomain
for %%i in ("%DOMAIN_HOME%") do set DOMAIN_HOME=%%~fsi

set LONG_DOMAIN_HOME=C:\Users\Nick\AppData\Roaming\JDeveloper\system11.1.1.4.37.59.23\DefaultDomain

if "%DEBUG_PORT%"=="" (
set DEBUG_PORT=8453
)

if "%SERVER_NAME%"=="" (
set SERVER_NAME=DefaultServer
)

set DERBY_FLAG=false

set enableHotswapFlag=

set PRODUCTION_MODE=

set doExitFlag=false
set verboseLoggingFlag=false
for %%p in (%*) do call :SET_PARAM %%p
GOTO :CMD_LINE_DONE
:SET_PARAM
for %%q in (%1) do set noQuotesParam=%%~q
if /i "%noQuotesParam%" == "nodebug" (
set debugFlag=false
GOTO :EOF
)
if /i "%noQuotesParam%" == "production" (
set DOMAIN_PRODUCTION_MODE=true
GOTO :EOF
)
if /i "%noQuotesParam%" == "notestconsole" (
set testConsoleFlag=false
GOTO :EOF
)
if /i "%noQuotesParam%" == "noiterativedev" (
set iterativeDevFlag=false
GOTO :EOF
)
if /i "%noQuotesParam%" == "noLogErrorsToConsole" (
set logErrorsToConsoleFlag=false
GOTO :EOF
)
if /i "%noQuotesParam%" == "noderby" (
set DERBY_FLAG=false
GOTO :EOF
)
if /i "%noQuotesParam%" == "doExit" (
set doExitFlag=true
GOTO :EOF
)
if /i "%noQuotesParam%" == "noExit" (
set doExitFlag=false
GOTO :EOF
)
if /i "%noQuotesParam%" == "verbose" (
set verboseLoggingFlag=true
GOTO :EOF
)
if /i "%noQuotesParam%" == "enableHotswap" (
set enableHotswapFlag=-javaagent:%WL_HOME%\server\lib\diagnostics-agent.jar
GOTO :EOF
) else (
set PROXY_SETTINGS=%PROXY_SETTINGS% %1
)
GOTO :EOF
:CMD_LINE_DONE

set MEM_DEV_ARGS=

if "%DOMAIN_PRODUCTION_MODE%"=="true" (
set PRODUCTION_MODE=%DOMAIN_PRODUCTION_MODE%
)

if "%PRODUCTION_MODE%"=="true" (
set debugFlag=false
set testConsoleFlag=false
set iterativeDevFlag=false
set logErrorsToConsoleFlag=false
)

@REM If you want to override the default Patch Classpath, Library Path and Path for this domain,
@REM Please uncomment the following lines and add a valid value for the environment variables
@REM set PATCH_CLASSPATH=[myPatchClasspath] (windows)
@REM set PATCH_LIBPATH=[myPatchLibpath] (windows)
@REM set PATCH_PATH=[myPatchPath] (windows)
@REM PATCH_CLASSPATH=[myPatchClasspath] (unix)
@REM PATCH_LIBPATH=[myPatchLibpath] (unix)
@REM PATCH_PATH=[myPatchPath] (unix)

call "%WL_HOME%\common\bin\commEnv.cmd"

set WLS_HOME=%WL_HOME%\server

set XMS_SUN_64BIT=256
set XMS_SUN_32BIT=256
set XMX_SUN_64BIT=512
set XMX_SUN_32BIT=512
set XMS_JROCKIT_64BIT=256
set XMS_JROCKIT_32BIT=256
set XMX_JROCKIT_64BIT=512
set XMX_JROCKIT_32BIT=512

if "%JAVA_VENDOR%"=="Sun" (
set WLS_MEM_ARGS_64BIT=-Xms256m -Xmx512m
set WLS_MEM_ARGS_32BIT=-Xms256m -Xmx512m
) else (
set WLS_MEM_ARGS_64BIT=-Xms512m -Xmx512m
set WLS_MEM_ARGS_32BIT=-Xms512m -Xmx512m
)

if "%JAVA_VENDOR%"=="Oracle" (
set CUSTOM_MEM_ARGS_64BIT=-Xms%XMS_JROCKIT_64BIT%m -Xmx%XMX_JROCKIT_64BIT%m
set CUSTOM_MEM_ARGS_32BIT=-Xms%XMS_JROCKIT_32BIT%m -Xmx%XMX_JROCKIT_32BIT%m
) else (
set CUSTOM_MEM_ARGS_64BIT=-Xms%XMS_SUN_64BIT%m -Xmx%XMX_SUN_64BIT%m
set CUSTOM_MEM_ARGS_32BIT=-Xms%XMS_SUN_32BIT%m -Xmx%XMX_SUN_32BIT%m
)

set MEM_ARGS_64BIT=%CUSTOM_MEM_ARGS_64BIT%

set MEM_ARGS_32BIT=%CUSTOM_MEM_ARGS_32BIT%

if "%JAVA_USE_64BIT%"=="true" (
set MEM_ARGS=%MEM_ARGS_64BIT%
) else (
set MEM_ARGS=%MEM_ARGS_32BIT%
)

set MEM_PERM_SIZE_64BIT=-XX:PermSize=128m

set MEM_PERM_SIZE_32BIT=-XX:PermSize=128m

if "%JAVA_USE_64BIT%"=="true" (
set MEM_PERM_SIZE=%MEM_PERM_SIZE_64BIT%
) else (
set MEM_PERM_SIZE=%MEM_PERM_SIZE_32BIT%
)

set MEM_MAX_PERM_SIZE_64BIT=-XX:MaxPermSize=512m

set MEM_MAX_PERM_SIZE_32BIT=-XX:MaxPermSize=512m

if "%JAVA_USE_64BIT%"=="true" (
set MEM_MAX_PERM_SIZE=%MEM_MAX_PERM_SIZE_64BIT%
) else (
set MEM_MAX_PERM_SIZE=%MEM_MAX_PERM_SIZE_32BIT%
)

if "%JAVA_VENDOR%"=="Sun" (
if "%PRODUCTION_MODE%"=="" (
set MEM_DEV_ARGS=-XX:CompileThreshold=8000 %MEM_PERM_SIZE%
)
)

@REM Had to have a separate test here BECAUSE of immediate variable expansion on windows

if "%JAVA_VENDOR%"=="Sun" (
set MEM_ARGS=%MEM_ARGS% %MEM_DEV_ARGS% %MEM_MAX_PERM_SIZE%
)

if "%JAVA_VENDOR%"=="HP" (
set MEM_ARGS=%MEM_ARGS% %MEM_MAX_PERM_SIZE%
)

if "%JAVA_VENDOR%"=="Apple" (
set MEM_ARGS=%MEM_ARGS% %MEM_MAX_PERM_SIZE%
)

@REM IF USER_MEM_ARGS the environment variable is set, use it to override ALL MEM_ARGS values

if NOT "%USER_MEM_ARGS%"=="" (
set MEM_ARGS=%USER_MEM_ARGS%
)

set ORACLE_DOMAIN_CONFIG_DIR=%DOMAIN_HOME%\config\fmwconfig
for %%i in ("%ORACLE_DOMAIN_CONFIG_DIR%") do set ORACLE_DOMAIN_CONFIG_DIR=%%~fsi
set WLS_JDBC_REMOTE_ENABLED=-Dweblogic.jdbc.remoteEnabled=false

set JAVA_PROPERTIES=-Dplatform.home=%WL_HOME% -Dwls.home=%WLS_HOME% -Dweblogic.home=%WLS_HOME%

set ALT_TYPES_DIR=%COMMON_COMPONENTS_HOME%\modules\oracle.ossoiap_11.1.1,%COMMON_COMPONENTS_HOME%\modules\oracle.oamprovider_11.1.1
set PROTOCOL_HANDLERS=oracle.mds.net.protocol

set PROTOCOL_HANDLERS=%PROTOCOL_HANDLERS:;="|"%

@REM To use Java Authorization Contract for Containers (JACC) in this domain,
@REM please uncomment the following section. If there are multiple machines in
@REM your domain, be sure to edit the setDomainEnv in the associated domain on
@REM each machine.
@REM
@REM -Djava.security.manager
@REM -Djava.security.policy=location of weblogic.policy
@REM -Djavax.security.jacc.policy.provider=weblogic.security.jacc.simpleprovider.SimpleJACCPolicy
@REM -Djavax.security.jacc.PolicyConfigurationFactory.provider=weblogic.security.jacc.simpleprovider.PolicyConfigurationFactoryImpl
@REM -Dweblogic.security.jacc.RoleMapperFactory.provider=weblogic.security.jacc.simpleprovider.RoleMapperFactoryImpl

set EXTRA_JAVA_PROPERTIES=-Dwsm.repository.path=%DOMAIN_HOME%\oracle\store\gmds %EXTRA_JAVA_PROPERTIES%

set EXTRA_JAVA_PROPERTIES=-Dcommon.components.home=%COMMON_COMPONENTS_HOME% -Djrf.version=11.1.1 -Dorg.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger -Ddomain.home=%DOMAIN_HOME% -Djrockit.optfile=%COMMON_COMPONENTS_HOME%\modules\oracle.jrf_11.1.1\jrocket_optfile.txt -Doracle.server.config.dir=%ORACLE_DOMAIN_CONFIG_DIR%\servers\%SERVER_NAME% -Doracle.domain.config.dir=%ORACLE_DOMAIN_CONFIG_DIR% -Digf.arisidbeans.carmlloc=%ORACLE_DOMAIN_CONFIG_DIR%\carml -Digf.arisidstack.home=%ORACLE_DOMAIN_CONFIG_DIR%\arisidprovider -Doracle.security.jps.config=%DOMAIN_HOME%\config\fmwconfig\jps-config.xml -Doracle.deployed.app.dir=%DOMAIN_HOME%\servers\%SERVER_NAME%\tmp\_WL_user -Doracle.deployed.app.ext=\- -Dweblogic.alternateTypesDirectory=%ALT_TYPES_DIR% -Djava.protocol.handler.pkgs=%PROTOCOL_HANDLERS% %WLS_JDBC_REMOTE_ENABLED% %EXTRA_JAVA_PROPERTIES%

set EXTRA_JAVA_PROPERTIES=-Djps.app.credential.overwrite.allowed=true %EXTRA_JAVA_PROPERTIES%

set JAVA_PROPERTIES=%JAVA_PROPERTIES% %EXTRA_JAVA_PROPERTIES%

set ARDIR=%WL_HOME%\server\lib

pushd %LONG_DOMAIN_HOME%

@REM Clustering support (edit for your cluster!)

if "%ADMIN_URL%"=="" (
@REM The then part of this block is telling us we are either starting an admin server OR we are non-clustered
set CLUSTER_PROPERTIES=-Dweblogic.management.discover=true
) else (
set CLUSTER_PROPERTIES=-Dweblogic.management.discover=false -Dweblogic.management.server=%ADMIN_URL%
)

if NOT "%LOG4J_CONFIG_FILE%"=="" (
set JAVA_PROPERTIES=%JAVA_PROPERTIES% -Dlog4j.configuration=file:%LOG4J_CONFIG_FILE%
)

set JAVA_PROPERTIES=%JAVA_PROPERTIES% %CLUSTER_PROPERTIES%

set JAVA_DEBUG=

if "%debugFlag%"=="true" (
set JAVA_DEBUG=-Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,address=%DEBUG_PORT%,server=y,suspend=n -Djava.compiler=NONE
set JAVA_OPTIONS=%JAVA_OPTIONS% %enableHotswapFlag% -ea -da:com.bea... -da:javelin... -da:weblogic... -ea:com.bea.wli... -ea:com.bea.broker... -ea:com.bea.sbconsole...
) else (
set JAVA_OPTIONS=%JAVA_OPTIONS% %enableHotswapFlag% -da
)

if NOT exist %JAVA_HOME%\lib (
echo The JRE was not found in directory %JAVA_HOME%. ^(JAVA_HOME^)
echo Please edit your environment and set the JAVA_HOME
echo variable to point to the root directory of your Java installation.
popd
pause
GOTO :EOF
)

if "%DERBY_FLAG%"=="true" (
set DATABASE_CLASSPATH=%DERBY_CLASSPATH%
) else (
set DATABASE_CLASSPATH=%DERBY_CLIENT_CLASSPATH%
)

if NOT "%POST_CLASSPATH%"=="" (
set POST_CLASSPATH=%COMMON_COMPONENTS_HOME%\modules\oracle.jrf_11.1.1\jrf.jar;%POST_CLASSPATH%
) else (
set POST_CLASSPATH=%COMMON_COMPONENTS_HOME%\modules\oracle.jrf_11.1.1\jrf.jar
)
if NOT "%PRE_CLASSPATH%"=="" (
set PRE_CLASSPATH=%COMMON_COMPONENTS_HOME%\modules\oracle.jdbc_11.1.1\ojdbc6dms.jar;%PRE_CLASSPATH%
) else (
set PRE_CLASSPATH=%COMMON_COMPONENTS_HOME%\modules\oracle.jdbc_11.1.1\ojdbc6dms.jar
)

if NOT "%DATABASE_CLASSPATH%"=="" (
if NOT "%POST_CLASSPATH%"=="" (
set POST_CLASSPATH=%POST_CLASSPATH%;%DATABASE_CLASSPATH%
) else (
set POST_CLASSPATH=%DATABASE_CLASSPATH%
)
)

if NOT "%ARDIR%"=="" (
if NOT "%POST_CLASSPATH%"=="" (
set POST_CLASSPATH=%POST_CLASSPATH%;%ARDIR%\xqrl.jar
) else (
set POST_CLASSPATH=%ARDIR%\xqrl.jar
)
)

@REM PROFILING SUPPORT

set JAVA_PROFILE=

set SERVER_CLASS=weblogic.Server

set JAVA_PROPERTIES=%JAVA_PROPERTIES% %WLP_JAVA_PROPERTIES%

set JAVA_OPTIONS=%JAVA_OPTIONS% %JAVA_PROPERTIES% -Dwlw.iterativeDev=%iterativeDevFlag% -Dwlw.testConsole=%testConsoleFlag% -Dwlw.logErrorsToConsole=%logErrorsToConsoleFlag%

if "%PRODUCTION_MODE%"=="true" (
set JAVA_OPTIONS= -Dweblogic.ProductionModeEnabled=true %JAVA_OPTIONS%
)

@REM -- Setup properties so that we can save stdout and stderr to files

if NOT "%WLS_STDOUT_LOG%"=="" (
echo Logging WLS stdout to %WLS_STDOUT_LOG%
set JAVA_OPTIONS=%JAVA_OPTIONS% -Dweblogic.Stdout=%WLS_STDOUT_LOG%
)

if NOT "%WLS_STDERR_LOG%"=="" (
echo Logging WLS stderr to %WLS_STDERR_LOG%
set JAVA_OPTIONS=%JAVA_OPTIONS% -Dweblogic.Stderr=%WLS_STDERR_LOG%
)

@REM ADD EXTENSIONS TO CLASSPATHS

if NOT "%EXT_PRE_CLASSPATH%"=="" (
if NOT "%PRE_CLASSPATH%"=="" (
set PRE_CLASSPATH=%EXT_PRE_CLASSPATH%;%PRE_CLASSPATH%
) else (
set PRE_CLASSPATH=%EXT_PRE_CLASSPATH%
)
)

if NOT "%EXT_POST_CLASSPATH%"=="" (
if NOT "%POST_CLASSPATH%"=="" (
set POST_CLASSPATH=%POST_CLASSPATH%;%EXT_POST_CLASSPATH%
) else (
set POST_CLASSPATH=%EXT_POST_CLASSPATH%
)
)

if NOT "%WEBLOGIC_EXTENSION_DIRS%"=="" (
set JAVA_OPTIONS=%JAVA_OPTIONS% -Dweblogic.ext.dirs=%WEBLOGIC_EXTENSION_DIRS%
)

set JAVA_OPTIONS=%JAVA_OPTIONS%

@REM SET THE CLASSPATH

if NOT "%WLP_POST_CLASSPATH%"=="" (
if NOT "%CLASSPATH%"=="" (
set CLASSPATH=%WLP_POST_CLASSPATH%;%CLASSPATH%
) else (
set CLASSPATH=%WLP_POST_CLASSPATH%
)
)

if NOT "%POST_CLASSPATH%"=="" (
if NOT "%CLASSPATH%"=="" (
set CLASSPATH=%POST_CLASSPATH%;%CLASSPATH%
) else (
set CLASSPATH=%POST_CLASSPATH%
)
)

if NOT "%WEBLOGIC_CLASSPATH%"=="" (
if NOT "%CLASSPATH%"=="" (
set CLASSPATH=%WEBLOGIC_CLASSPATH%;%CLASSPATH%
) else (
set CLASSPATH=%WEBLOGIC_CLASSPATH%
)
)

if NOT "%PRE_CLASSPATH%"=="" (
set CLASSPATH=%PRE_CLASSPATH%;%CLASSPATH%
)

if NOT "%JAVA_VENDOR%"=="BEA" (
set JAVA_VM=%JAVA_VM% %JAVA_DEBUG% %JAVA_PROFILE%
) else (
set JAVA_VM=%JAVA_VM% %JAVA_DEBUG% %JAVA_PROFILE%
)

Although the paths are from a win7 machine, I think one can get the general idea in order to proceed. For unix or linux users, one can find help here. Thus, one can avoid the risk of losing one's application or database server connections imposed by setting up JDev from scratch and migrating.
Finally, I don't know, does Oracle support officially certify jdev to work right, after a user has altered the default jdev jdk? That's why keeping the old default jdk entries as comments, is safer; in case the built in 32 bit jdk needs to be used once more.


A recent finding would be my attempt to use the profiler of JDeveloper which only works with the built in 32 bit jdk, provided one installs the 32 bit visual studio 2010 C++ redistributable file .When I tried to use the Microsoft Visual C++ 2010 Redistributable Package (x64) I got:

Error occurred during initialization of VM
Could not find agent library C:\Oracle\Middleware\jdev_11115\jdeveloper\jdev\lib\profiler_x64.dll in absolute path, with error: Can't load IA 32-bit .dll on a AMD 64-bit platform
Process exited.



So remember before you start swearing at me, that you were warned!




Having problems connecting to Oracle 9i? Try tweaking once more the configuration files

Speaking of database connections, I recently noticed that when one attempts to connect to an Oracle 9i database with Greek regional settings, one receives:


Test failed: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region  not found


Having heard of a similar solution working for SQL Developer, a similar method crosses one's mind. One needs to edit:

C:\Oracle\Middleware\jdev_11114\jdeveloper\jdev\bin\jdev.conf

as shown below:

 ##############################################################################
#
# Oracle JDeveloper Launcher Configuration File
# Copyright 2000-2008 Oracle Corporation.
# All Rights Reserved.
#
##############################################################################

IncludeConfFile ../../ide/bin/ide.conf

#
# Directive SetJavaHome is not required by default, except for the base
# install, since the launcher will determine the JAVA_HOME.  On Windows
# it looks in ..\..\jdk, on UNIX it first looks in ../../jdk. If no JDK
# is found there, it looks in the PATH.
#
#SetJavaHome C:\Oracle\Middleware\jdev_11114\jdk160_21
SetJavaHome C:\Program Files\Java\jdk1.6.0_24
#
# MaxPermSize is required to run JDeveloper with Sun Microsystems virtual
# machine (-client and -server).  The default value is 64M, which isn't
# enough to run JDeveloper successfully.  With the default value, JDeveloper
# will end up running out of memory at some point in time.  For technical
# details, see: http://java.sun.com/docs/hotspot/gc5.0/gc_tuning_5.html
#
AddVMOption  -XX:MaxPermSize=256M
#
#added on 10/04/2011 to connect to oracle 9 with Greek regional settings
AddVMOption -Duser.timezone=UTC+02:00
#
# Replace heavyweight AWT controls with lightweight implementations.
#
AddVMOption  -Xbootclasspath/p:../lib/lwawt.jar

#
# Don't swap the entire JDev process out of memory when minimizing the main window.
#
AddVMOption  -Dsun.awt.keepWorkingSetOnMinimize=true

#
# Prevent Swing painting issues and hangs on some graphics cards and/or
# multiple monitor setups and/or Windows 7.
#
AddVMOption  -Dsun.java2d.noddraw=true

#
# Automatically dump heap on OutOfMemoryError.
# The heap dump is in HPROF binary format, and so it can be analyzed using
# any tools that can import this format. Examples are jhat, NetBeans and YourKit.
# By default the heap dump is created in a file called java_pid.hprof in the
# working directory of the VM.
#
AddVMOption  -XX:+HeapDumpOnOutOfMemoryError

#
# Option to enable OWSM policy types RM and Management on the policy configuration
# dialog for a web service.
# The same option, if set to true, also shows the policy configuration dialog for
# web service client as well.
# This flag exists so that when OWSM support for RM and Management policy
# types for WLS becomes available in the server module, removing this flag or
# setting it to 'true' will bring those options in JDev WS policy configuration.
#
AddVMOption  -Doracle.jdeveloper.webservice.showAllOwsmPolicyTypes=false

#
# Controls the acess to OWSM policy property override dialog from the client policy
# configuration dialog.
#
AddVMOption  -Doracle.jdeveloper.webservice.hidePropertyOverride=false

#
# Prevents the desktop hanging when debugging JDev on Linux and hitting a breakpoint with a popup open.
# See Sun Bug: 6714678 (http://bugs.sun.com/view_bug.do?bug_id=6714678)
# Note: This only prevents the hang when debugging JDev itself. To prevent hanging with other
# projects add this flag to the java options for the process to be debugged.
#
AddVMOption  -Dsun.awt.disablegrab=true
....


What worries me most, is when each newer version of the I.D.E. or Sun jdk becomes available, if one is forced to setup from scratch,  might not remember all the necessary steps to edit  properly the new configuration files and continue with the current project. So backing up the jdev configuration files sounds like a good, proactive idea!

Sunday 27 March 2011

A handy way to tune one's SQL

Recently, a french project manager working in a 5 year long European commission project in Brussels, contacted me and complained that their fully working code (in testing  environment), using Oracle Text 10g, failed to meet the customer expectations. In  their RAC production environment the application was unbearably slow. The project deadline is early June this year. Not an unknown situation, isn't it?
One of the most usual issues when moving from the development, or testing  environment to  the production, is setting up properly or updating the database schema and tuning it. In this new post, a fast and handy way for web developers to create, or even drop, indexes, partition tables or materialized views will be demonstrated. No SQL plans or other hard core DBA stuff will be mentioned here, the SQL of the notorious TUHRA ADF model project will be used as an example. So, let's start!
 Consider for instance the following trivial ANSI query for the employees search page based on the email, first and last name criteria entered by the user:


SELECT /* + INDEX_COMBINE( Employees Departments )*/
Employees.EMPLOYEE_ID,
Employees.EMAIL,
Employees.FIRST_NAME,
Employees.LAST_NAME,
Employees.PHONE_NUMBER,
Departments.DEPARTMENT_NAME,
Departments.DEPARTMENT_ID,
Jobs.JOB_TITLE,
Jobs.JOB_ID
FROM EMPLOYEES Employees join DEPARTMENTS Departments on (Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID)
join JOBS Jobs on (Employees.JOB_ID = Jobs.JOB_ID)
ORDER BY LAST_NAME,FIRST_NAME



When one opens the relevant view object and observes the SQL  query to explain, one sees the image presented below:


The highlighted code below, the  where clause, is actually missing. You need  to enable ADFLogger logging, run the app and search for the actual full query in the output log which is rather time consuming. Besides, when you open sqlplus and enter:

Explain plan for

SELECT * FROM (SELECT /* + INDEX_COMBINE( Employees Departments )*/
Employees.EMPLOYEE_ID,
Employees.EMAIL,
Employees.FIRST_NAME,
Employees.LAST_NAME,
Employees.PHONE_NUMBER,
Departments.DEPARTMENT_NAME,
Departments.DEPARTMENT_ID,
Jobs.JOB_TITLE,
Jobs.JOB_ID
FROM EMPLOYEES Employees join DEPARTMENTS Departments on (Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID)
join JOBS Jobs on (Employees.JOB_ID = Jobs.JOB_ID)) QRSLT
WHERE ( ( (UPPER(EMAIL) LIKE UPPER('%' || :QueryEmail || '%') ) 
AND (UPPER(FIRST_NAME) LIKE UPPER('%' || :QueryFirstName || '%') )
AND (UPPER(LAST_NAME) LIKE UPPER('%' || :QueryLastName || '%') ) ) )
ORDER BY LAST_NAME,FIRST_NAME

select *
from table(dbms_xplan.display)
/

you get a new plan, which is naturally different. Many people would rightfully argue that creating a function based index, concatenating all the three columns in that order, would solve the tuning problem. But would that really solve it?

To index or not to index? That is the question!

The easiest way to fly is via the automatic pilot. You test fully your app, ideally in isolation and now you  can ask the SQL advisor, thanks to the enterprise manager, following the images below:


 Click on Advisor central to proceed. Next click on sql access advisor in order to create a db job and schedule it to run immediately. Next click on current and recent sql activity, proceed  to select indexes or materialized views  or partition tables, reach the review page where show sql, reveals what is happening under the cover:



DECLARE

taskname
varchar2(30) := 'SQLACCESSTuhra3';
task_desc
varchar2(256) := 'SQL Access Advisor';
task_or_template
varchar2(30) := 'SQLACCESS_EMTASK';
task_id
number := 0;
wkld_name
varchar2(30) := 'SQLACCESSTuhra3_wkld';
saved_rows
number := 0;
failed_rows
number := 0;
num_found
number;
BEGIN
/* Create Task */
dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,task_id,taskname,task_desc,task_or_template);
/* Reset Task */
dbms_advisor.reset_task(taskname);
/* Delete Previous Workload Task Link */
select count(*) into num_found from user_advisor_sqla_wk_map where task_name = taskname and workload_name = wkld_name;IF num_found > 0 THEN
dbms_advisor.delete_sqlwkld_ref(taskname,wkld_name);
END IF;/* Delete Previous Workload */
select count(*) into num_found from user_advisor_sqlw_sum where workload_name = wkld_name;IF num_found > 0 THEN
dbms_advisor.delete_sqlwkld(wkld_name);
END IF;/* Create Workload */
dbms_advisor.create_sqlwkld(wkld_name,
null);/* Link Workload to Task */
dbms_advisor.add_sqlwkld_ref(taskname,wkld_name);
/* Set Workload Parameters */
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'ORDER_LIST','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'INVALID_SQLSTRING_LIST','"@!"');
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'JOURNALING','4');
dbms_advisor.set_sqlwkld_parameter(wkld_name,
'DAYS_TO_EXPIRE','30');
dbms_advisor.import_sqlwkld_sqlcache(wkld_name,
'REPLACE',2,saved_rows,failed_rows);/* Set Task Parameters */
dbms_advisor.set_task_parameter(taskname,
'ANALYSIS_SCOPE','INDEX,TABLE,PARTITION');
dbms_advisor.set_task_parameter(taskname,
'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_task_parameter(taskname,
'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,
'TIME_LIMIT',10000);
dbms_advisor.set_task_parameter(taskname,
'MODE','LIMITED');
dbms_advisor.set_task_parameter(taskname,
'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_task_parameter(taskname,
'DML_VOLATILITY','TRUE');
dbms_advisor.set_task_parameter(taskname,
'WORKLOAD_SCOPE','PARTIAL');
dbms_advisor.set_task_parameter(taskname,
'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,
'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,
'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,
'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,
'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,
'CREATION_COST','TRUE');
dbms_advisor.set_task_parameter(taskname,
'JOURNALING','4');
dbms_advisor.set_task_parameter(taskname,
'DAYS_TO_EXPIRE','30');/* Execute Task */
dbms_advisor.execute_task(taskname);
END;



You end up as shown:


 where one can see the whole set of the sql statements issued. If you click on schedule implementation the recommended indexes are created, while clicking show sql gives you the script to run when you wish, on your own:




Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem

Rem
Username: SYSTEM
Rem
Task: SQLACCESSTuhra3
Rem
Execution date:
Rem

/* RETAIN INDEX "HR"."IMAGE_USAGES_PK" */
/* RETAIN INDEX "HR"."DEPT_ID_PK" */
/* RETAIN INDEX "HR"."JOB_ID_PK" */
/* RETAIN INDEX "HR"."IMAGE_USAGES_ASSOCIATED_IDX" */
/* RETAIN INDEX "HR"."JOBS_TITLE_IDX" */
/* RETAIN INDEX "HR"."EMP_DEPARTMENT_IX" */
/* RETAIN INDEX "HR"."EMP_EMAIL_UK" */
/* RETAIN INDEX "HR"."IMAGES_PK" */
/* RETAIN INDEX "APEX_030200"."WWV_FLOW_PLATFORM_PREFS_FKIDX" */
/* RETAIN INDEX "HR"."EMP_EMP_ID_PK" */
CREATE INDEX "HR"."EMPLOYEES_NAME_IDX"ON "HR"."EMPLOYEES"
("LAST_NAME","FIRST_NAME")
COMPUTE STATISTICS;





Thus, the aforementioned concatenated index is not being used, and is just decelerating inserts and taking up  valuable disk space.We can safely drop it for now. This does not mean that we are done! Since the production db will have millions of rows, other statistics,etc, the db job results, i.e.  about indexing, may frequently change. The morale of this story is that the earliest one starts to test against the full production data,  the higher the possibility of project success. Performance tuning is not an afterthought, but a gradually ongoing process, which must ideally begin with the first application release. Nonetheless, not many developers follow this rule. Cynicals say that experience is just a set of past, bitter failures. Germans say: Durch Schaden wird mann klug. The man on the street tends to forget more easily, whatever has been unpleasant in the past.  It seems that IT staff does not have that privilege!