Salient Soft

I was using Spring Data JPA with Oracle as part of an export process from CouchDB to Oracle. The CouchDB export application first exports my existing Places data from CouchDB, then maps it to the entity model I am using for JPA/Oracle, then persists it into Oracle, and finally performs some rudimentary listing of the Oracle data by logging it.

Some issues/points came to light during this excercise which I am noting here:-

1/ My connect string to Oracle was using a Pluggable database in Oracle XE. This needs to use a service name (xepdb1) rather than a SID (xe) for the connection. To do this I needed to use a slash before the service name in the application properties, to indicate a service name, rather than the colon I would normally use for a SID:

# Oracle settings
spring.datasource.url = jdbc:oracle:thin:@localhost:1521/xepdb1
spring.datasource.username = places
spring.datasource.password = xxxxxxxx

2/ I changed the Hibernate naming strategy in the application properties to prevent unwanted snake casing of Oracle column names:

# Hibernate settings
spring.jpa.hibernate.naming.physical-strategy = org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

3/ For persisting to Oracle, I had the choice of using Spring Data JPA, calling save() or saveAll() on the Individual Repository interfaces that extended CrudRepository (such as PlacesRepository), or I could use the Entity Manager. The Spring Data methods would persist or update fine, and could also save an iterable in one call. However, I had some small additional logic as I was passing and returning a Map. As I chose not to put this logic in the service layer, I therefore used an ordinary JPA Dao (repository) class, and autowired the entity manager via the @PersistenceContext annotation. I could therefore incorporate my logic in a single call and use entityManager.merge() to persist. I felt that situations vary and that it was not always appropriate to use Spring Data Interfaces for some use cases. However the Spring Data find queries were useful and clever for querying the database as they allowed for example a find by key with a like, and order by, just by declaring the appropriate method calls in the interface. I also used the @Query annotation for a custom query, which again was just annotated on a method in the interface.

4/ I had issues whereby the foreign keys on dependant entities were not being auto populated and threw up as null, even when I set nullable=false on the @JoinColumn annotations. In the end it was a simple case of not populating the objects in my model on both sides of the join before persisting – i.e. I was populating a list in a parent object for a one-to-many, but not populating the parent in the dependant object. Doing this resolved the problem and everything persisted automatically with a single parent persist/merge, with hibernate allocating primary keys via Oracle sequences, and then auto populating them in the foreign keys. This stack overflow post here pointed me in the right direction on this. I kept the nullable=false annotations where appropriate to correctly match the relationship I was using, and what was in Oracle.

5/ Once I was at the point of listing everything I had persisted, using Spring Data queries and some simple logging, I was initially hit with Hibernate multiple bag fetch exceptions when listing the data. At this stage I had annotated my repository methods as @Transactional rather than the service layer, and was therefore using explicit eager loading annotations as I needed everything to load before the methods returned. This post here details the issue, and it appears that Hibernate can only eagerly load one collection at a time. My solution to this was to take the more normal route of annotating my service methods as @Transactional rather than the repository, which then allowed a number of repository calls to be made from a single service method, all in the same transaction. Whilst this did expose the service layer to the transaction architecture and javax.transaction package, it is the more normal approach and gives flexibility in the service layer. In my case I could then just revert to the default lazy loading and perform the logging in the same service layer method (which I was happy to do as this was a very basic example just to demonstrate fetching the imported data from Oracle). Everything then worked fine.

Comments Off on Using Spring Data JPA with Oracle

I just installed the latest 21c version of XE, and having created the system user, I connected to this via SQL developer, using XE as the SID. All fine so far.

When I then went to create a new schema via the CREATE USER command, I received ORA-65096: invalid common user or role name. Upon researching this I found that Oracle now uses a hierarchy of pluggable databases or PDBs inside a container database or CDB. Schemas aka users can then sit inside a PDB as normal. Inside the CDB, where I was logged in, the rules are different and common users are required which have their own rules and naming conventions.

I just wanted a simple local develpment solution for JPA use, so did not especially want to learn all the ins and outs of this. I found that an XE installation creates a default PDB called XEPDB1, and managed to create an SQL developer connection to this using the already created system account. I tried using XEPDB1 as the SID rather than XE, but this failed as invalid. However when I tried XEPDB1 as a service name rather than a SID it all worked fine. I did not need to make any other changes to the connection settings – user (system), password, and port (1521) were identical.

Having then connected via this new connection, I was successfully able to create a new schema in the default PDB. As expected, to connect to this new schema I again had to use the service name of XEPDB1 and I was able to connect, once I had granted the CREATE SESSION privilege to the new user after creating it to allow me to log on. I could then proceed to grant the necessary privileges to the user and create/populate tables etc. Initially, in addition to granting CREATE SESSION, I just simply granted UNLIMITED TABLESPACE to allow the schema user to allow tables to be populated. With this I was successfully able to perform inserts, upates, deletes, and selects on all the tables present.

Comments Off on Oracle XE 21c – Container vs Pluggable Databases – CDBs vs PDBs

I had a legacy EA 12 model for the places guide database which I had used as the basis for the CouchDB implementation.

I wanted to explore a relational database as an alternative route, with the aim of not bothering with completely offline access due to the devlopment effort needed, and the lessening need due to improvements in mobile data access in the last few years. Initially I created an Oracle version of the model file, and tweaked the PK and FK  data types from UUID to long and checked a few other things.

I then tried selecting the domain model in the explorer, and using Package/Database Engineering/Generate Package DDL from the toolbar. This brought up the relevant dialogue to do the export, but try as I might it could not find any entities to export as DDL.

I then found this post here, which points out that you need to transform the relevant entities to DDL first to enable this to work. I did this using Package/Model Transformation (MDA)/Transform Current Package. You then select the entities to transform and select DDL on the right under transformations, and then hit Do Transform. This creates a DDL diagram. The original Generate Package DDL option above then worked – I was able to find all the desired entities and transform them. I had to visit each entity to select the target database DDL format, in my case Oracle, or else I got an error, but once this was done, it all worked fine.

I elected to create a single DDL SQL file and did not worry about the entity ordering – you can reorder when doing the DDL generation but I did not bother. The output DDL was pretty good as a starter and was clear. It also appeared to have been created in a way that was order independent – the tables were all created first, followed by all the foreign key contraints, so that all the tables existed prior to creating the constraints, making it order independent.

Some tweaking of the model will be needed, especially in the area of Place Features related tables, and also sequence generation, as I would likely use Oracle sequences for PK generation, via JPA/Hibernate. However, I had reached a good trial starting point which avoided the need to create all the DDL manually from scratch.

Comments Off on Exporting and Enterprise Architect 12 model as SQL/DDL statements

During installation, I received the following error message:-

“The installer is unable to instantiate the file …{…}\KEY_XE.REG”

The installation was mostly correct but I was unable to access the getting started/home page.

This post here details a workaround for the issue but this did not  work for me.

In the end, I recreated the Get_Started.url file under (in my case) E:\oraclexe\app\oracle\product\11.2.0\server.

This contained a parameter placeholder for the HTTP port, and I just edited the correct port in directly. Note that for some reason it was not possible to change the existing shortcut. I had to delete it and create a new one of the same name, which then worked fine.

Whilst doing this, I also changed the HTTP port that the web interface listens on. This is done by running a stored procedure, and is detailed in both this post and also this alternative post. Both posts detail the same method.

Note also for XE 11g R2 that the getting started page (which corresponds to the old 10g home page) no longer has the means to create schemas or to run sql queries. Schemas/users can be created via SQLplus, or via TOAD or SQLDeveloper.

Note that TOAD 8 is not compatible with 11g. The latest free TOAD now lasts for a year and is excellent with plenty of functionality, albeit with an occasional nag/advert for the paid version. The paid version is around $800 for a seat which is very high for a SQL development tool, even if it is a good one. Note that the free TOAD is available from Toad World here, but is not available from the main Quest Software site – this appears to be a marketing decision to keep the free version out of sight from people who may be persuaded to part with money for the paid one. I should not complain too much as they are after all providing an excellent free tool.

No Comments »

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 »