Oracle APEX 22.1 installer - @apexins.sql error inside core/tab.sql

Andrzej Pauli-Oracle

When running @apexins.sql  on freshly created PDB I see error:

Timing command is obsolete.

#

# Inherit Privileges

#

Timing command is obsolete.

...apxsqler_exit.sql

Errors found. Drop APEX_220100 before re-starting the installation.

#

# Actions in Phase 1:

#

  ok 1 - BEGIN                            |  0.00

  ok 2 - Creating APEX_GRANTS_FOR_NEW_USERS_ROLE           |  0.00

  ok 3 - Creating SYS Objects                     |  0.02

  not ok 4 - Creating APEX Tables                   |  0.22

  # Message: ORA-01735: invalid ALTER TABLE option

  #

  # Statement: alter table wwv_flow_task_def_actions add constraint wwv_task_def_act_uk unique (task_def_id, name) using index compress 2

  #

  # alter table wwv_flow_task_def_actions add constraint wwv_task_def_act_ws_mod_ck

  #   check ( web_src_module_id is null or web_src_operation_id is null )

  #

  ok 5 - Installing Package Specs (Runtime)              |  0.42

  ok 6 - Installing Package Bodies (Runtime)             |  0.27

  ok 7 - Dev Grants                          |  0.00

This is stopping the whole installation for me (on DB 19c)

Looks like it is caused by core/tab.sql alter table (line 16512):

alter table wwv_flow_task_def_actions add constraint wwv_task_def_act_uk unique (task_def_id, name) using index compress 2

This is the fix IMVHO (but might wrong) in formof diff patch:

--- core/tab.sql    2022-05-08 14:01:26.894854550 +0000

+++ core/tab.sql    2022-05-08 14:01:47.637864851 +0000

@@ -16509,8 +16509,8 @@

   )

 lob (action_clob) store as (cache reads enable storage in row)

 /

-alter table wwv_flow_task_def_actions add constraint wwv_task_def_act_uk unique (task_def_id, name) using index compress 2

-

+alter table wwv_flow_task_def_actions add constraint wwv_task_def_act_uk unique (task_def_id, name) using index compress

+/

 alter table wwv_flow_task_def_actions add constraint wwv_task_def_act_ws_mod_ck

   check ( web_src_module_id is null or web_src_operation_id is null )

 /

Carsten Czarski-Oracle

Hi,

can you share the full installation log (either here or privately) ...?

regards

-Carsten

Carsten Czarski-Oracle

Hi Andrzej,

and ... did you install APEX using sqlcl, or SQL*Plus - and with which version?

regards

-Carsten

Andrzej Pauli-Oracle

https://community.oracle.com/tech/developers/discussion/comment/16835001#Comment_16835001

I'm doing docker container installation/runtime connecting to 19.14.0.0.0 EE version. I'm using sqlcl version Release 22.1.0.0 Production Build: 22.1.0.90.1611 (taken from  https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip. latest?).

I do test run on freshly created PDB:

CREATE PLUGGABLE DATABASE salespdb

ADMIN USER salesadm IDENTIFIED BY "P4ssw0rd"

STORAGE (MAXSIZE 2G)

DEFAULT TABLESPACE users 

DATAFILE '/opt/oracle/oradata/ORCLCDB/salespdb/users01.dbf' SIZE 20M 

AUTOEXTEND ON

PATH_PREFIX = '/opt/oracle/oradata/ORCLCDB/salespdb/'

FILE_NAME_CONVERT = ('/opt/oracle/oradata/ORCLCDB/pdbseed/', 

'/opt/oracle/oradata/ORCLCDB/salespdb/');

ALTER PLUGGABLE DATABASE SALESPDB OPEN READ WRITE;

I use this dockerfile repo https://github.com/ChaosEngine/dockerfiles/tree/apex22.1/apex-ords (branch apex22.1) with one exception: disabled patch in Dockerfile line 20:

RUN unzip -q apex*.zip && unzip -q -d ords ords-*.zip && cp -r apex/images/ ords/ #&& patch apex/core/tab.sql < "22.1_core_tab.sql.patch"

finally I'm attaching logs up until error happens and when later on degenerated apex-ords runtime takes over (still not producing proper install and run).

It all went correctly and smoothly with mentioned patch applied (

apex-ords_22.1_unpatched_failed_run.zip

https://github.com/ChaosEngine/dockerfiles/blob/apex22.1/apex-ords/22.1_core_tab.sql.patch)

Carsten Czarski-Oracle

Hi Andrzej,

thank you for reporting this; we have filed bug 34151611 for this issue and we're working on a fix. The interesting bit is that this appears to be dependent on the actual environment, as I never have seen it and it also came not up in quite a few installation tests.

Best regards

-Carsten

Andrzej Pauli-Oracle

https://community.oracle.com/tech/developers/discussion/comment/16835095#Comment_16835095

I could test it on differently DB versions (21c, 18c various versions). Maybe also on installing on CDB. The interesting part is - I did this container-approach for installation and regression tests for a long time with previous 20.X and 21.X (many patch-sets) and this is the first time it is failing like this.

If needed I can demonstrate it on slack or help with testing.

Carsten Czarski-Oracle

Hi Andrzej,

thank you for the offer - I think we're fine for now, as the root cause has been identified and we're working on a fix.

Best regards

-Carsten

Hilary Farrell-Oracle

Hi Andrzej,

Thanks again for raising this issue.

Just as a heads up, and in case it wasn't obvious - your post is to our external forum. For internal employees, questions can be raised on the APEX domain on Einstein. You can obtain the URL for Einstein on the Sign In page of apex.oraclecorp.com - our internal hosted instance.

Regards,

Hilary