Safeguard your APEX workspaces and applications

August 7, 2024

1–2 minutes

Here’s a quick APEX tip. There are heaps of cool things within APEX to ensure you never lose or accidentally overwrite your applications, such as automatic backups and working copies.

But if you share my levels of paranoia 😁when it comes to worrying about the loss of code, data or anything else, here’s a simple script I use to regularly take a copy of all of the APEX workspace definitions and all of the applications within those workspaces.

Run it via SQLcl as an appropriate administrative user


with ws as 
( select workspace_id
  from apex_workspaces
  where workspace not in (
      'COM.ORACLE.CUST.REPOSITORY',
      'COM.ORACLE.APEX.REPOSITORY',
      'INTERNAL')
)
select 'apex export -expworkspace -workspaceid '||workspace_id
from ws
union all
select 'apex export -workspaceid '||workspace_id
from ws

set pages 0
set lines 200
set trimspool on
set feedback off
spool /tmp/backup_the_apex_universe.sql
/
spool off
@/tmp/backup_the_apex_universe.sql

You’ll get an export file for each workspace, and one for every app in your APEX instance.

Note: The blank line after “from ws” is deliberate and required.

9 responses to “Safeguard your APEX workspaces and applications”

jkstill

August 8, 2024 at 9:13 am

Dragging out an old sqlplus trick I see 👍

Hardly ever see anyone do that anymore.

I should try it in sqlcl and see what happens.

sqlcl still does not recognize recsep, but I do know who to call about that.

Reply
1. Connor McDonald
  
  August 8, 2024 at 10:09 am
  
  Normally I do this with a docker image clustered under Kubernetes remodelled with a React pumpkin scone framework using Donkey Toast unit test framework…. But a little SQL seemed the easier option 🙂
  
  Reply
Kim Berg Hansen

August 9, 2024 at 5:02 pm

I’d like to add “apex export -expFiles -workspaceid ….” to that script, so static files also are safeguarded.

And if I could, I’d also add some “apex instance” export, but that doesn’t exist, sadly.

So I’d recommend having a script with the “apex_instance_admin.set_parameter” calls needed for your APEX instance (which smtp server URL etc) so you can recreate your instance if needed on a blank installation. Or at least have it documented which instance settings you need, so you have a procedure for how to recreate your instance from scratch – a procedure showing how the instance should be configured and then how to run the backup scripts created by Connor’s nice little script 😉.

Reply
Torsten Kleiber

January 8, 2025 at 8:27 pm

Hi!

This runs into an error on Windows SQLcl 24.3..2:

Error at Command Line : 16 Column : 1 File @ file:/c:/temp/test.sql
Error report –
SQL Error: ORA-00933: SQL command not properly ended

Kind Regards

Torsten

Reply
1. Connor McDonald
  
  January 8, 2025 at 8:44 pm
  
  I can’t replicate
  
  SQLcl: Release 24.3 Production on Wed Jan 08 20:41:34 2025
  
  Copyright (c) 1982, 2025, Oracle. All rights reserved.
  
  Last Successful login time: Wed Jan 08 2025 20:41:34 +08:00
  
  Connected to:
  Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
  Version 21.16.0.0.0
  
  SQL> show version
  Oracle SQLDeveloper Command-Line (SQLcl) version: 24.3.1.0 build: 24.3.1.311.1631
  SQL> with ws as
  2 ( select workspace_id
  3 from apex_workspaces
  4 where workspace not in (
  5 ‘COM.ORACLE.CUST.REPOSITORY’,
  6 ‘COM.ORACLE.APEX.REPOSITORY’,
  7 ‘INTERNAL’)
  8 )
  9 select ‘apex export -expworkspace -workspaceid ‘||workspace_id
  10 from ws
  11 union all
  12 select ‘apex export -workspaceid ‘||workspace_id
  13 from ws
  14*
  SQL> set pages 0
  SQL> set lines 200
  SQL> set trimspool on
  SQL> set feedback off
  SQL> spool /tmp/backup_the_apex_universe.sql
  SQL> /
  
  apex export -expworkspace -workspaceid 6381523572932396
  apex export -expworkspace -workspaceid 1400548436526582
  apex export -expworkspace -workspaceid 4775984512374389
  apex export -expworkspace -workspaceid 9931498379883425
  apex export -expworkspace -workspaceid 10200547140812909
  apex export -expworkspace -workspaceid 8606419023854326
  apex export -expworkspace -workspaceid 3165783265282703
  apex export -expworkspace -workspaceid 28524152050985268
  apex export -expworkspace -workspaceid 14193580624111221
  apex export -workspaceid 6381523572932396
  apex export -workspaceid 1400548436526582
  apex export -workspaceid 4775984512374389
  apex export -workspaceid 9931498379883425
  apex export -workspaceid 10200547140812909
  apex export -workspaceid 8606419023854326
  apex export -workspaceid 3165783265282703
  apex export -workspaceid 28524152050985268
  apex export -workspaceid 14193580624111221
  SQL> spool off
  SQL> @/tmp/backup_the_apex_universe.sql
  Exporting Workspace 1400548436526582:’ASKTOM’
  File w1400548436526582.sql created
  Exporting Workspace ASKTOM – application 100:asktom
  File f100.sql created
  Exporting Workspace ASKTOM – application 101:Demo
  File f101.sql created
  Exporting Workspace ASKTOM – application 102:blah
  File f102.sql created
  Exporting Workspace ASKTOM – application 103:demo1000
  File f103.sql created
  Exporting Workspace ASKTOM – application 104:cols1000
  File f104.sql created
  Exporting Workspace ASKTOM – application 105:emptest
  File f105.sql created
  
  Reply
  1. Torsten Kleiber
    
    January 8, 2025 at 10:05 pm
    
    Seems you are on Mac or Unix with SQLcl 24.3.1, I’m on Windows with 24.3.2.
    Line 16 is the blank line, it seems that SQLcl on my side wait for the execution slash.
    
    Reply
    1. Connor McDonald
      
      January 8, 2025 at 10:15 pm
      
      Works fine in 24.3.2 too (I’m on Windows)
      
      SQL> show version
      Oracle SQLDeveloper Command-Line (SQLcl) version: 24.3.2.0 build: 24.3.2.330.1718
      SQL> with ws as
      2 ( select workspace_id
      3 from apex_workspaces
      4 where workspace not in (
      5 ‘COM.ORACLE.CUST.REPOSITORY’,
      6 ‘COM.ORACLE.APEX.REPOSITORY’,
      7 ‘INTERNAL’)
      8 )
      9 select ‘apex export -expworkspace -workspaceid ‘||workspace_id
      10 from ws
      11 union all
      12 select ‘apex export -workspaceid ‘||workspace_id
      13 from ws
      14*
      SQL> set pages 0
      SQL> set lines 200
      SQL> set trimspool on
      SQL> set feedback off
      SQL> spool /tmp/backup_the_apex_universe.sql
      SQL> /
      
      apex export -expworkspace -workspaceid 6381523572932396
      apex export -expworkspace -workspaceid 1400548436526582
      apex export -expworkspace -workspaceid 4775984512374389
      apex export -expworkspace -workspaceid 9931498379883425
      apex export -expworkspace -workspaceid 10200547140812909
      apex export -expworkspace -workspaceid 8606419023854326
      apex export -expworkspace -workspaceid 3165783265282703
      apex export -expworkspace -workspaceid 28524152050985268
      apex export -expworkspace -workspaceid 14193580624111221
      apex export -workspaceid 6381523572932396
      apex export -workspaceid 1400548436526582
      apex export -workspaceid 4775984512374389
      apex export -workspaceid 9931498379883425
      apex export -workspaceid 10200547140812909
      apex export -workspaceid 8606419023854326
      apex export -workspaceid 3165783265282703
      apex export -workspaceid 28524152050985268
      apex export -workspaceid 14193580624111221
      SQL> spool off
      SQL>
      
      Just copy/paste the entire code block from the block paste into a running SQLcl.
      
      Reply
      1. Torsten Kleiber
        
        January 8, 2025 at 10:22 pm
        
        Have seen in the meantime too, that the problem does not occur, if I run the command direct from SQLcl.
        I had put the commands in a file test.sql and run the file via @test.sql, in this case the error occurs.
        Have reproduced this on Unix (SLES 15.3) with SQLcl 24.3.2.
Torsten Kleiber

January 8, 2025 at 11:43 pm

Addtional problem was calling the spooled sql script from my created sql script.

So I have splitted now the execution.

I changed the first script and put the set command and spool start before the select.

Then I call first this changed script and after that the spooled script from SQLcl .

Reply