JaversException SQL_EXCEPTION: ORA-00920: invalid relational operator

[dkrogulec]

Following to this stackoverflow topic I have the same isse when try to use JaVers with SpringBoot v2.0.1.RELEASE and Oracle XE 11g.

It'll be very nice if somebody can fix this.

Thanks!

[bartoszwalacik]

This is duplicate to #796

Here are the facts:

• The fix is not hard do code, but
• Oracle is a paid and closed database, there is no easy way for me to test the fix on a real database server. I have spent some time yesterday trying to install Oracle Database on my Mac, but I failed. I'm not going to install VirtualBox + Vagrant + Docker and then download and configure almost 3GB of fully blown distribution of Oracle 11g. It's too complicated.

If you, Oracle guys, have some hints for me, how to write integration test for Javers running on Oracle, please share.

Otherwise, since only you have access to the holly Oracle, please contrubute a PR. I will merge.

[Pinellus]

Hi Bart,
considering that the problem is essentially of non-standard SQL code, and considering that the problem is also present on MS SQL Server, if it is easier you might think of downloading the Docker instance of SQL Server (much lighter than Oracle).
You can find references here:

https://database.guide/how-to-install-sql-server-on-a-mac/

In the meantime I've bypassed the problem using the shadow feature that does not present problems of SQL code.

javers.findShadows(QueryBuilder.byInstance(object).limit(1).build())

[bartoszwalacik]

@Pinellus , thanks for the hint with MS SQL Server, I will try it.
I have removed this obsolete clause ( false ). I will release the fix and than, you guys could give me feedback.

[bartoszwalacik]

fix is released
5.2.6
https://search.maven.org/search?q=javers-core
check it out

[dkrogulec]

@bartoszwalacik tested with Oracle XE 11g, everything is OK now.

Thanks for this really quick fix 🥇

[IlyaNerd]

@bartoszwalacik hey. having the same problem for this kind in oracle of query in 5.4.0:
javers.findChanges(QueryBuilder.byClass(class) .withSnapshotType(SnapshotType.UPDATE) .withNewObjectChanges() .build());
result query looks like this:
SELECT a.*, rownum FROM ( SELECT state, type, version, changed_properties, managed_type, commit_pk, author, commit_date, commit_date_instant, commit_id, g.local_id, g.fragment, g.owner_id_fk, o.local_id owner_local_id, o.fragment owner_fragment, o.type_name owner_type_name FROM INFR.jv_snapshot INNER JOIN INFR.jv_commit ON commit_pk = commit_fk INNER JOIN INFR.jv_global_id g ON g.global_id_pk = global_id_fk LEFT OUTER JOIN INFR.jv_global_id o ON o.global_id_pk = g.owner_id_fk WHERE 1 = 1 and ( (global_id_fk = ? AND version = ?) OR (global_id_fk = ? AND version = ?) OR (global_id_fk = ? AND version = ?) OR (global_id_fk = ? AND version = ?) OR (global_id_fk = ? AND version = ?) OR (global_id_fk = ? AND version = ?) OR false) ORDER BY snapshot_pk DESC ) a WHERE rownum <= ?

mb replace false with 1 != 1, which should work in all dbs.
example above with shadows works fine

[bartoszwalacik]

Guys, I don't have access to Oracle database, so I can't test javers' code against it. I don't know the easy way how install it on my machine. So if you found a bug, contribute a PR with a fix, I will merge it.

[bartoszwalacik]

It would be much easier to have dockerized test like this one for MsSQL https://github.com/javers/javers/blob/master/javers-persistence-sql/src/test/groovy/org/javers/repository/sql/integration/mssql/MsSqlIntegrationTest.groovy