Invalid Oracle migration script in 5.0

[Jacopo47]

Bug description
Oracle migration script fail with error: ORA-00905: Missing keyword on statement:

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS MODIFY COLUMN TYPE_CD PARAMETER_TYPE VARCHAR(100); 

But inspecting next statements all these are affected by the same error:

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS MODIFY COLUMN TYPE_CD PARAMETER_TYPE VARCHAR(100);
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS MODIFY COLUMN KEY_NAME PARAMETER_NAME VARCHAR(100);
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS MODIFY COLUMN STRING_VAL PARAMETER_VALUE VARCHAR(2500);

ALTER TABLE BATCH_JOB_EXECUTION MODIFY COLUMN CREATE_TIME TIMESTAMP(9);
ALTER TABLE BATCH_JOB_EXECUTION MODIFY COLUMN START_TIME TIMESTAMP(9);
ALTER TABLE BATCH_JOB_EXECUTION MODIFY COLUMN END_TIME TIMESTAMP(9);
ALTER TABLE BATCH_JOB_EXECUTION MODIFY COLUMN LAST_UPDATED TIMESTAMP(9);

ALTER TABLE BATCH_STEP_EXECUTION MODIFY COLUMN CREATE_TIME TIMESTAMP(9);
ALTER TABLE BATCH_STEP_EXECUTION MODIFY COLUMN START_TIME TIMESTAMP(9);
ALTER TABLE BATCH_STEP_EXECUTION MODIFY COLUMN END_TIME TIMESTAMP(9);
ALTER TABLE BATCH_STEP_EXECUTION MODIFY COLUMN LAST_UPDATED TIMESTAMP(9);

Digging a bit on it seems to me that Oracle does not support column rename and type change in the same statement.
An it should be split in two different statement. Like:

--- From
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS MODIFY COLUMN TYPE_CD PARAMETER_TYPE VARCHAR(100);

--- to
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS MODIFY TYPE_CD VARCHAR(100);
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS RENAME COLUMN TYPE_CD TO PARAMETER_TYPE;

And more generically the MODIFY keyword does not requires COLUMN (http://www.dba-oracle.com/t_alter_table_modify_column_syntax_example.htm)

--- From
ALTER TABLE BATCH_JOB_EXECUTION MODIFY COLUMN CREATE_TIME TIMESTAMP(9);
--- to
ALTER TABLE BATCH_JOB_EXECUTION MODIFY CREATE_TIME TIMESTAMP(9);

Applying these changes to the script it becomes like:

ALTER SEQUENCE BATCH_STEP_EXECUTION_SEQ ORDER;
ALTER SEQUENCE BATCH_JOB_EXECUTION_SEQ ORDER;
ALTER SEQUENCE BATCH_JOB_SEQ ORDER;

ALTER TABLE BATCH_STEP_EXECUTION ADD CREATE_TIME TIMESTAMP DEFAULT TO_TIMESTAMP('1970-01-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss') NOT NULL;
ALTER TABLE BATCH_STEP_EXECUTION MODIFY START_TIME TIMESTAMP NULL;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS DROP COLUMN DATE_VAL;
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS DROP COLUMN LONG_VAL;
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS DROP COLUMN DOUBLE_VAL;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS MODIFY TYPE_CD VARCHAR(100);
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS RENAME COLUMN TYPE_CD TO PARAMETER_TYPE;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS MODIFY KEY_NAME VARCHAR(100);
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS RENAME COLUMN KEY_NAME TO PARAMETER_NAME;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS MODIFY STRING_VAL VARCHAR(2500);
ALTER TABLE BATCH_JOB_EXECUTION_PARAMS RENAME COLUMN STRING_VAL TO PARAMETER_VALUE;

ALTER TABLE BATCH_JOB_EXECUTION MODIFY CREATE_TIME TIMESTAMP(9);
ALTER TABLE BATCH_JOB_EXECUTION MODIFY START_TIME TIMESTAMP(9);
ALTER TABLE BATCH_JOB_EXECUTION MODIFY END_TIME TIMESTAMP(9);
ALTER TABLE BATCH_JOB_EXECUTION MODIFY LAST_UPDATED TIMESTAMP(9);

ALTER TABLE BATCH_STEP_EXECUTION MODIFY CREATE_TIME TIMESTAMP(9);
ALTER TABLE BATCH_STEP_EXECUTION MODIFY START_TIME TIMESTAMP(9);
ALTER TABLE BATCH_STEP_EXECUTION MODIFY END_TIME TIMESTAMP(9);
ALTER TABLE BATCH_STEP_EXECUTION MODIFY LAST_UPDATED TIMESTAMP(9);

And it completes successfully.

Environment
Spring Batch version: 5.0
Oracle version 21

Steps to reproduce
Apply migration-oracle.sql script 5.0 to a schema in version 4.3.x

Expected behavior
Apply migration script without errors

Minimal Complete Reproducible example

1. Instantiate a fresh Oracle DB instance.

docker run -d -p 1521:1521 -e ORACLE_PASSWORD=P4ssw0rd! gvenzl/oracle-xe:21-slim-faststart

2. Connect to it at localhost 1521 with a SQL client
3. Initialize tables with script at version 4.3
   I referred to this script for the CREATE statements
4. Apply migration to v. 5.0 script
   Apply this script migration 5.0 oracle

[fmbenhassine]

Thank you for raising this! I will plan the fix for v5.0.1.

This will also be a good opportunity to upgrade the Docker image used in the OracleJobRepositoryIntegrationTests from 11g to 21c.