Salient Soft

Update 29/3/2012 – SP2-0310 error due to backslash “\” in parameter

SQLplus has a bug whereby if call to a subscript in the same directory has a command line parameter with a backslash in it, SQLplus messes up the default directory processing and cannot find the called script. It seems to think that the backslash in the parameter is part of the called script file path.

To get around this and avoid hard coding the subdirectory in this script, I pass the subdirectory in from the caller.

Note that this issue may well be the same issue listed below in connection with the use of forward slash “/” causing default directory issues.

A second bug is also present, whereby &1 only works the first time. When used a second time, it incorrectly replaces it with the &1 parameter for the last called script, rather than using the one passed in from our caller. It may be that these &1-&n parameters are global in nature, rather than per level. To get around this, I use an explicit named define for the &1 and use that instead.

The following illustrates the problems:-

–This works ok assuming AddDomain.sql is present in the same directory as this script
@@AddDomain ‘1’  ‘!’

–This will fail with an SP2-0310 error
@@AddDomain ‘1’ ‘\’

The following fix resolves the problem:-

Calling script Main.sql

–We pass the script its own subfolder to get around an SqlPlus SP2-0310 bug, see Lookups.sql
@@Lookups\Lookups ‘Lookups’

Called Script Lookups.sql

/*
* SQLplus has a bug whereby if a parameter has a backslash in it,
* SQLplus messes up the default directory processing and cannot find the called script
* It seems to think that the backslash in the parameter is part of the called script file path
* To get around this and avoid hard coding the subdirectory in this script,
* we pass it in from the caller.
*
* A second bug is also present, whereby &1 only works the first time.
* When used a second time, it incorrectly replaces it with the &1 parameter
* for the last called script, rather than using the one passed in from our caller
* It may be that these &1-&n parameters are global in nature, rather than per level.
* To get around this, we use an explicit named define for the &1 and use that instead.
*
* These 2 bugs are awful – I’m not impressed!
*/

define dir=&1
 
@@&dir\AddDomain ‘1’  ‘\’
@@&dir\AddDomain ‘2’  ‘\reports’

These fixes resolve the problem.

Update – Parameter passing, use of “/”  and default directory issues

These issues came about when switching to the command line sqlplus utility rather than using Toad as I had before.

1. If a parameter passed to a called script (in this case via @@) contains a slash “/”, Sql*Plus gives an SP2-0310 file open error. The error is nothing to do with opening the file, but is due to the slash. Escaping it was no help. I was using the slash as a date delimiter for a constant date string passed to the called script, so I just switched this to a dash “-“ instead and changed the to_date string in the called script to ‘YYYY-MM-DD HH24:MI:SS’. This solved the problem.
2. If a script called via “’@@” is followed by a “/”, then the script will be called twice. Whilst it is normal to follow an SQL command with the slash, this must not be done when calling a script.
3. When a relative path is used to call a script via the “@@” command, the default directory for the relative path is not the directory of the calling script, as you might expect, but rather the top level default directory. This is rather nasty and inconsistent in my view, but is simple to fix once you know.

Update – Return Codes from Sql*Plus

Sql*Plus can be configured to return an exit code to the calling operating system, to indicate either an OS error (such as a file open error), or an SQL error. This is extremely useful for detecting the success or failure of a script run – for example, I use it during JUnit testing when running scripts to set up a test, as there is then no need to scan log files and you can then just look at the test outcome (which is a central goal of JUnit).

To do this, just add the following at the head of your top level script. This will cause the script or any of its called scripts to abort with an exit code when it fails for any reason:-

WHENEVER OSERROR  EXIT 1
WHENEVER SQLERROR EXIT SQL.SQLCODE

The OSERROR unsurprisingly deals with OS related errors. Unfortunately, it does not appear to be possible to return the actual OS error, so in this case I have returned value of 1 which is a Windows ‘illegal function’ error, which is also what is returned when you copy or rename an invalid filename for example. The SQLERROR returns the actual Oracle error code. These codes are returned to the operating system, and for example under Windows may be detected by testing the %ERRORLEVEL% value as per normal with Windows batch files. I have yet to try this under Linux but the mechanism is similar.

Update – Verify and echo

When debugging a script, it is often useful to log each statement in the output (echo), and also to check how parameter substitution was performed (verify). To do this, just add the following to the head of your top level script. To disable them, just use off instead of on:-

set verify on
set echo on

Original Post

This post details some common pointers and gotchas when using nested scripts in Sql*Plus, and passing paramters to a nested script.
The examples were all executed with Toad, which is subset compatible with Sql*Plus, so the points here should work with either.

1. Use @filepath to call a nested script with a full file path. The default file type is .sql.
2. A common technique is to place all related scripts and sub scripts in the same directory. If you do this, use @@filename instead with just the filename, and SQL*Plus/Toad will assume that the called script is in the same directory as the executing one. This allows a collection of scripts to be moved to any desired directory and still call each other correctly with no changes.
3. Parameters may be defined with the define  command as in the example below. Defined  parameters are global and visible to a called scripted even if defined in the calling one.
4. Parameters are referred to using &name for a defined parameter, or &1, &2 for the first or second parameter passed to a script.
5. When passing parameters to a script you must delimit them with spaces and must have single quotes around the parameter values, which will allow the parameter value to contain spaces. Note however that you cannot pass an empty string using two quotes ‘’ as this gives a missing parameter error. Oracle treats an empty string as a null anyway, so you should pass the null value differently, e.g. using null.
6. If you use &name for a parameter you have not previously defined, you will be prompted for the value each time you refer to &name. If you use &&name you will only be prompted for the value the first time, and it will stick from then on and you will not be prompted. If a variable has been previously defined you just use &name all the time and the defined value is used – you will not be prompted.
7. When defining a parameter or passing one in a call to a sub script,  put single quotes around the value. These will be stripped off when you refer to the parameter, so you will need to add them back when referring to the parameter if you are using a quoted string, for example ‘&1’ or ‘&param’. Double quotes did not work correctly with Toad (not sure about Sql*Plus) so are best avoided.
8. Due to the above quote handling/stripping therefore, it is not straightforward/posible to add your own secondary quotes to pass a quoted string with the quotes already present. You must add the quotes in the called script, which means that you must decide whether you are passing a constant string (which will have quotes added by the called script), or an expression (which will not have quotes added). There is not a way I could find to pass a value which can either be a quoted string or expression. In practice, this tends not to be an issue and you can work within the limitation, unless you are trying to make scripts fly to the moon – don’t – they are not programs!
9. If you want to append alphanumerics immediately after a parameter expansion, place a dot after the parameter name, and the dot will be removed, for example &param.xxxx will substitute the definition of param and concatenate it directly with xxxx. Set Concat lets you define the concatenation character to be used, period (dot) is the default.

The documentation for Sql*Plus may be found here. In addition, this is a useful site detailing a number of the features (although it gets the meaning of & and && the wrong way around!)

Simple Example of Nested Scripts with Parameter passing

Test1.sql

define Table = ‘dual’
@@test2 ‘dummy’ ‘X’

Test2.sql

select * from &Table where &1 like ‘&2’
/

No Comments »

I found a number of issues when configuring a many to many relationship, but eventually found a working solution.

1/  This example for a many to many uses referencedColumnName when it does not need to – it was a hangover from an example using multiple join columns. If you do this with Eclipselink 1.1.2 and Oracle (in my case XE 10g), the columns are created with data types of varchar2(255) instead of the default of number(19)  :-

      @ManyToMany
      @JoinTable(name="AppUserRole",
                 joinColumns =@JoinColumn(name="AppUserID",
                                          referencedColumnName="AppUserID"),
                 inverseJoinColumns=@JoinColumn(name="AppRoleID",
                                          referencedColumnName="AppRoleID”))

The referencedColumnName attribute is the cause of this issue. Leaving it out causes correct column types. It is only needed for multiple column joins (which break anyway see 2/), and so should not be used. This therefore works correctly and is the recommended format to use  :-

      @ManyToMany
      @JoinTable(name="AppUserRole",
                 joinColumns =@JoinColumn(name="AppUserID"),
                 inverseJoinColumns=@JoinColumn(name="AppRoleID"))

2/ Using a many to many as in 1/ but with multiple join columns causes eclipselink bug 300485. (Although listed as a one to many bug it also happens with many to many). This is not due to be fixed until eclipselink 2.1. The bug gives a query parameter not found error for an internally generated query used when eclipselink lazily loads a relationship collection.

3/ Leaving out the @JoinTable and only having the @ManyToMany annotation works ok, but gives an XML column name resolution error from eclipse for one of the join columns. This is purely an ‘invalid validation’ however, as the code runs fine against a database created from it.

4/ using the xml annotations in orm.xml along with just an @ManyToMany annotation in the code works fine, but you do get some validation errors from Eclipse as the validation does not appear to merge the annotations and xml correctly when validating :-

<entity>
  <attributes>
   <many-to-many name="appRoles">
    <join-table name="AppUserRole">
     <join-column name="AppUserID" column-definition="number(19)"/>
     <inverse-join-column name="AppRoleID" column-definition="number(19)"/>
    </join-table>
   </many-to-many>
  </attributes>
 </entity>

This would be the preferred route if anything database specific  was needed, such as the column-definition attributes for Oracle in the example.  The “@ManyToMany” annotation on the entity may be superfluous in this case but is a helpful label. A comment in the code that there are overrides in orm.xml would be helpful. The above fragment was tested but without the column-definition attributes on the columns – these are shown as examples of how to add database specific column definitions without having to pollute the code with them via annotations.

The intention here would be to use xml in conjunction with annotations, with annotations used for all the standard metadata. Different versions of orm.xml and persistence.xml could then be swapped in and out for different back end databases, keeping the code standard. The same approach has been advocated to permit using Oracle sequences here.

No Comments »

I hit this problem trying to connect to a local Oracle XE 10.2.0.1 database.
The problem is due to a bug in Oracle’s networking layer. It cannot parse program locations containing parentheses, and by default Toad installs to “Program Files (x86)” which causes the bug.

More details on OTN here.

The fault is designated Bug 3807408, and whilst there is an Oracle patch for it, the patch is not available for Oracle XE.

The easy way around the problem is just to install TOAD in “Program Files” rather than “Program Files (x86)”. The different directories are purely to aid in distinguishing 32 bit applications from 64 bit ones – it does not matter where applications are installed.

I removed and reinstalled TOAD in “Program Files” and this completely eliminated the problem. The problem would also apply to other applications which access Oracle, so worth bearing in mind.

No Comments »

To use Oracle specific features in JPA with Eclipselink, such as using sequences for ID generation as described in this example here, it is necessary to set the correct property in persistence.xml to ensure that JPA/Eclipselink knows that an Oracle database is in use. By default, it does not discover this automatically and resorts to using table based ID generation even if you turn sequences on as per the above example.

A sample persistence.xml with the correct properties follows :-

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0"
 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">
 <persistence-unit name="JPATest" transaction-type="RESOURCE_LOCAL">
  <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
  <non-jta-data-source>jdbc/JPATestPool</non-jta-data-source>
  <mapping-file>META-INF/orm.xml</mapping-file>
  <class>uk.co.salientsoft.jpatest.domain.UserInfo</class>
  <properties>
   <property name="eclipselink.target-server" value="SunAS9" />
   <property name="eclipselink.logging.level" value="FINEST" />
   <property name="eclipselink.session-name" value="JPATestSession" />

   <!-- Ensure Eclipselink knows we have an Oracle Database
        so that Oracle specific features work correctly -->
   <property name="eclipselink.target-database" value="Oracle"/>

   <!-- This will cause EclipseLink to create the database schema
        automatically on every run.
        You can also do this from the JPA Tools/Create Tables from Entities
        project context menu option in Eclipse, which may well be preferrable-->
   <!--
   <property name="eclipselink.ddl-generation" value="drop-and-create-tables" />
   <property name="eclipselink.ddl-generation.output-mode" value="database" />
   -->

  </properties>
 </persistence-unit>
</persistence>

Comments Off on Using Oracle Specific features in JPA with Eclipselink

It is possible to use both annotations and descriptors in orm.xml to define entity mappings with JPA. The debate on which way to go has become very heated, but in my opinion there is a strong case for both.

1. Annotations are best for metadata which you would consider closely bound to the code. This promotes clarity, as elements which are closely bound together are also in close proximity, promoting clarity and maintainability.
2. XML is best for metadata which may change independantly of the code, and provides the ability to isolate platform specific issues from the code, for example to enhance persistence provider independance.

The OO design principle “Separate what changes from what stays the same” comes to mind clearly here.

I found the following simple example helpful in this context. The ID for the entity is defined via annotations in the code. However, I have defined the ID generator seperately in orm.xml. The generator name is fixed and referred to by the @GeneratedValue annotation, but the actual generator is in orm.xml and may be specified either as a table generator (which is database independant), or as a sequence generator which allows me to take advantage of Oracle’s sequences for primary key generation. Either generator may be defined in orm.xml, but the code does not change. This gives database independance, whilst still allowing leverage of the enhanced features of a specific database platform. In the example below, for illustration, orm.xml contains both generators and one has been commented out.

Note that to enable sequence generation in Oracle for this example to work, the correct properties must be set in persistence.xml, as by default Eclipselink will use table based ID generation even if you turn sequence generation on. The way to do this is detailed in this post here.

Class UserInfo

package uk.co.salientsoft.jpatest.domain;
import java.io.Serializable;
import javax.persistence.*;

@Entity
public class UserInfo implements Serializable {

 @Id
 @GeneratedValue(generator="UserID")
 private long userID;

 private String userName;
 private static final long serialVersionUID = 1L; 

 public UserInfo() {
  super();
 }  
 public long getUserID() {
   return this.userID;
 }
 public void setUserID(long userID) {
  this.userID = userID;
 }   
 public String getUserName() {
   return this.userName;
 }
 public void setUserName(String userName) {
  this.userName = userName;
 }
}

orm.xml

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="1.0" xmlns="http://java.sun.com/xml/ns/persistence/orm"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm
    http://java.sun.com/xml/ns/persistence/orm_1_0.xsd">

 <!--<table-generator name="UserID" />-->

 <sequence-generator name="UserID" sequence-name="UserID"/>

</entity-mappings>

No Comments »

Update 08/09/2022

Another interesting and helpful post on primary key generation may be found on Baeldung here.

Original Post

There are a number listed on eclipse.org, oracle.com, and Sun.com. The eclipse ones are often not all complete but still useful.

1. A general list of Eclipse tutorials is here
2. Eclipse/JPA/Tomcat web tutorial
3. Eclipselink/JPA/JSF web tutorial (looks incomplete and is missing the source code).
4. Eclipselink/JPA/Glassfish V2 Web Tutorial – also a work in progress.
5. Oracle – Build a Web Application (JSF) Using JPA
6. Oracle – Example: Web Application (JSF) Using JPA (another article using the same code example as the previous one)
7. The Sun Java ee 5 tutorial contains a JPA example.

No Comments »