sqlcl 20.3 apex execution vs sqlplus

Question

I have several environments where we have implemented sqlcl and apex versioning.

Oracle database 19c - SqlCL version 20.3

The only difference versus other environment is the OCI has a PDB defined.

When trying to implement changes on OCI environment i have a strange behavior on APEX type objects

1. on SqlCL, Error when "lb update -changelog f100.xml"-----> ORA-20001: Package variable g_security_group_id must be set
2. on SqlCL, I try to run an specific page manually -----> ORA-20001: Package variable g_security_group_id must be set
3. on SQLPLUS, I try to run specific page ---> No errors

Final test

SQLPLUS

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 17:26:25 2022
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter session set container = pdb;

Session altered.

SQL>    begin
        apex_application_install.set_workspace_id(6217319715855887);
        apex_application_install.generate_offset;
end;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

··································································································

SQLCL

SQLcl: Release 20.3 Production on Thu Mar 03 17:29:58 2022

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Thu Mar 03 2022 17:30:01 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter session set container = pdb;

Session altered.

SQL>     select workspace_id --into l_workspace_id
  2      from apex_workspaces
  3      where workspace = 'TEST';

       WORKSPACE_ID
___________________
   6217319715855887

SQL> begin
  2      apex_application_install.set_workspace_id(6217319715855887);
  3      apex_application_install.generate_offset;
  4  end;
  5  /

Error starting at line : 1 in command -
begin
        apex_application_install.set_workspace_id(6217319715855887);
        apex_application_install.generate_offset;
end;
Error report -
ORA-20987: APEX - Invalid workspace ID. - Contact your application administrator.
ORA-06512: at "APEX_050100.WWV_FLOW_ERROR", line 1033
ORA-06512: at "APEX_050100.WWV_FLOW_ERROR", line 1400
ORA-06512: at "APEX_050100.WWV_FLOW_APPLICATION_INSTALL", line 69
ORA-06512: at line 2

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

Updating SqlCL to version 21.4 looking for a different result

SQLcl: Release 21.4 Production on Tue Mar 08 15:32:22 2022

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 08 2022 15:32:25 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>   begin
  2          apex_application_install.set_workspace_id(6217319715855887);
  3          apex_application_install.generate_offset;
  4  end;
  5* /

Error starting at line : 1 in command -
  begin
        apex_application_install.set_workspace_id(6217319715855887);
        apex_application_install.generate_offset;
end;
Error report -
ORA-20987: APEX - Invalid workspace ID. - Contact your application administrator.
ORA-06512: at "APEX_050100.WWV_FLOW_ERROR", line 1033
ORA-06512: at "APEX_050100.WWV_FLOW_ERROR", line 1400
ORA-06512: at "APEX_050100.WWV_FLOW_APPLICATION_INSTALL", line 69
ORA-06512: at line 2

Answer 1

Solution>>>>

1. Go to the APEX MONITOR
2. Manage workspaces
3. Manage workspace to schema assignments
4. ADD the schema for the one managing version control (in my example DEVELOPER_APEX schema had even DBA privs but did not work)

It was a "apex privileges" error :-/ I have NOT done this process for implementations WITHOUT PDB.. in that case there are no errors.