ORACLE [ORA-01735: invalid ALTER TABLE] error when executing DOWNS

[r.g.el...@evomatik.com]

Hello,
We are having an error when using evolutions and ORACLE 12G, it seems to work fine but whenever we made a change in a model, applying DOWNS script sends out this error:

screenshot 2016-12-28 13 11 55

Play Version (2.5.10)

API (Java )

Operating System (CentOS and MacOS )

JDK (Oracle 1.8.0_45)

java version "1.8.0_45"
Java(TM) SE Runtime Environment (build 1.8.0_45-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.45-b02, mixed mode)

Library Dependencies

ojdbc7.jar to connect to ORACLE 12G, mainly we added this driver to our lib folder however we're receiving this error any time we restart play server:

[info] application - Creating Pool for datasource 'default'
[warn] c.z.h.u.DriverDataSource - Registered driver with driverClassName=oracle.jdbc.driver.OracleDriver was not found, trying direct instantiation.

Our config file for ORACLE:

ORACLE

default.url="jdbc:oracle:thin:@//10.0.2.81:1521/orcl"
default.driver=oracle.jdbc.driver.OracleDriver
default.username="ok"
default.password="ok"

I don't know why and somehow if this can be the source of the main problem of this issue...

Expected Behavior

The expected behavior is to use automatic evolutions as it goes, in order to avoid creating UPS and DOWNS scripts by hand as when you turn off evolutions.

UPS script is working fine, we can successfully create database with auto generated script: 1.sql
If we make any change on any model, we required to apply evolutions
screenshot 2016-12-28 13 03 48

Actual Behavior

After we apply this evolution, the error comes out. It is obviously because there is no "If exists" clause in ORACLE.

# --- !Downs

alter table ps_set_detalles_contratos drop constraint if exists fk_ps_st_dtlls_cntrts_dt_st_1;
drop index if exists ix_ps_st_dtlls_cntrts_dt_st_1;

alter table ps_set_detalles_contratos drop constraint if exists fk_ps_st_dtlls_cntrts_s_lt;
drop index if exists ix_ps_st_dtlls_cntrts_s_lt;

alter table ps_set_detalles_contratos drop constraint if exists fk_ps_st_dtlls_cntrts_s_md;
drop index if exists ix_ps_st_dtlls_cntrts_s_md;

alter table ps_ses_detalles_contratos drop constraint if exists fk_ps_ss_dtlls_cntrts_ds_sr_1;
drop index if exists ix_ps_ss_dtlls_cntrts_ds_sr_1;

alter table ps_ses_detalles_contratos drop constraint if exists fk_ps_ss_dtlls_cntrts_ds_ss_2;
drop index if exists ix_ps_ss_dtlls_cntrts_ds_ss_2;

alter table ps_ses_detalles_contratos drop constraint if exists fk_ps_ss_dtlls_cntrts_s_lt;
drop index if exists ix_ps_ss_dtlls_cntrts_s_lt;

alter table ps_ses_detalles_contratos drop constraint if exists fk_ps_ss_dtlls_cntrts_s_md;
drop index if exists ix_ps_ss_dtlls_cntrts_s_md;

alter table ps_sol_ensolicitudes_contratos drop constraint if exists fk_ps_sl_nslctds_cntrts_ss__1;
drop index if exists ix_ps_sl_nslctds_cntrts_ss__1;

alter table ps_sol_ensolicitudes_contratos drop constraint if exists fk_ps_sl_nslctds_cntrts_ss__2;
drop index if exists ix_ps_sl_nslctds_cntrts_ss__2;

alter table ps_sol_ensolicitudes_contratos drop constraint if exists fk_ps_sl_nslctds_cntrts_s_lt;
drop index if exists ix_ps_sl_nslctds_cntrts_s_lt;

alter table ps_sol_ensolicitudes_contratos drop constraint if exists fk_ps_sl_nslctds_cntrts_s_md;
drop index if exists ix_ps_sl_nslctds_cntrts_s_md;

alter table ps_sol_ent_tipos_contratos drop constraint if exists fk_ps_sl_nt_tps_cntrts_st_s_1;
drop index if exists ix_ps_sl_nt_tps_cntrts_st_s_1;

alter table ps_sol_ent_tipos_contratos drop constraint if exists fk_ps_sl_nt_tps_cntrts_st_n_2;
drop index if exists ix_ps_sl_nt_tps_cntrts_st_n_2;

alter table ps_sol_ent_tipos_contratos drop constraint if exists fk_ps_sl_nt_tps_cntrts_s_lt;
drop index if exists ix_ps_sl_nt_tps_cntrts_s_lt;

alter table ps_sol_ent_tipos_contratos drop constraint if exists fk_ps_sl_nt_tps_cntrts_s_md;
drop index if exists ix_ps_sl_nt_tps_cntrts_s_md;

alter table ps_ent_solicitudes_contratos drop constraint if exists fk_ps_nt_slctds_cntrts_ns_s_1;
drop index if exists ix_ps_nt_slctds_cntrts_ns_s_1;

alter table ps_ent_solicitudes_contratos drop constraint if exists fk_ps_nt_slctds_cntrts_s_lt;
drop index if exists ix_ps_nt_slctds_cntrts_s_lt;

alter table ps_ent_solicitudes_contratos drop constraint if exists fk_ps_nt_slctds_cntrts_s_md;
drop index if exists ix_ps_nt_slctds_cntrts_s_md;

alter table ps_entregable_tipos_contratos drop constraint if exists fk_ps_ntrgbl_tps_cntrts_nt__1;
drop index if exists ix_ps_ntrgbl_tps_cntrts_nt__1;

alter table ps_entregable_tipos_contratos drop constraint if exists fk_ps_ntrgbl_tps_cntrts_s_lt;
drop index if exists ix_ps_ntrgbl_tps_cntrts_s_lt;

alter table ps_entregable_tipos_contratos drop constraint if exists fk_ps_ntrgbl_tps_cntrts_s_md;
drop index if exists ix_ps_ntrgbl_tps_cntrts_s_md;

If we copy&paste first sentence to SQLDeveloper:

Sintaxis error.... near "if exists"

Reproducible Test Case

Example of one of our model classes:

@Entity
@Table(name="PS_ENT_SOLICITUDES_CONTRATOS")
public class EntregableSolicitud extends Model {

@Id
@Column(name="ENS_ID_N")
@SequenceGenerator(name="gen", sequenceName="PS_ENS_SEQ",allocationSize=1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "gen")
public Long id;

@Column(name="ENS_NOMBRE_STR")
public String nombre;

@Column(name="ENS_DECRIPCION_STR")
public String descripcion;

@Column(name="ENS_PATH_ECM_STR")
public String pathEcm;

@ManyToOne
@JoinColumn(name="ENS_SOL_ID_N",referencedColumnName="SOL_ID_N")
@Column(name="ENS_SOL_ID_N")
public Solicitud solicitud;

@ManyToOne
@JoinColumn(name="USU_ALTA",referencedColumnName="USU_ID_N")
@Column(name="USU_ALTA")
public Usuario createdBy;

@ManyToOne
@JoinColumn(name="USU_MOD",referencedColumnName="USU_ID_N")
@Column(name="USU_MOD")
public Usuario updatedBy;

@JsonIgnore
@WhenCreated
@Column(name="FEC_ALTA")
public Date created;

@JsonIgnore
@WhenModified
@Column(name="FEC_MOD")
public Date updated;

@Column(name="BIT_ACTIVO")
public boolean activo;
Hope someone can help us out in here!

[Greg Methvin]

I'm not sure I can help with the actual answer, but it looks like you are using Ebean, and I'm assuming you're using the play-ebean module to generate the evolutions. If this is the case, then Ebean is creating those statements. So it may be an issue with Ebean and not Play.

--
You received this message because you are subscribed to the Google Groups "Play Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to play-framework+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/play-framework/ac4d1a0f-943b-43d2-8a65-dcccf89f68a4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Greg Methvin

Tech Lead - Play Framework

Lightbend, Inc.

[Thibault Meyer]

Hi,

you can try to upgrade Ebean dependency to newest version and sbt-play-ebean plugin version.

From 8.2 to 9.2 with the following plugin

addSbtPlugin("com.typesafe.sbt" % "sbt-play-ebean" % "3.1.0")

From 8.2 to 9.x with the following plugin (please read carefully the README to configure the application.conf file)

addSbtPlugin("com.payintech" % "sbt-play-ebean" % "16.12")

10.x with the following plugin (please read carefully the README to configure the application.conf file)

--> Will be released this week.

addSbtPlugin("com.payintech" % "sbt-play-ebean" % "17.01")

 (need sbt-play-ebean version 3.1.0 provided by typesafe) or 10.x (need sbt-play-ebean version 17.01 provided by payintech)

[r.g.el...@evomatik.com]

Yes! actually.... we are using Play with Ebean, since we are using "ebean.default = ["models.*"]" in application.conf file.

[Dušan Škerget]

Hi,
this is exactly what I am facing right now. Did you managed to get it to somehow work?

What I do currently is that I am writing my 1.sql,2sql ... files manually. And I do not use DOWNS at all, because it is a hassle to write drop if exists in Oracle. So at the end, if I screwed something with my sql script, I just go into database, remove all fields that were created with this SQL, remove last record from play_evolutions table and run the app again.

If anyone finds a swift solution that could enable DOWNS I would be more than glad to hear about it.