APEX_EXPORT and Version Control

-- file: export_frontend.sql

set verify off feedback off heading off 
set trimout on trimspool on pagesize 0 linesize 5000 long 100000000 longchunksize 32767
whenever sqlerror exit sql.sqlcode rollback


-- https://blogs.oracle.com/opal/sqlplus-101-substitution-variables
define logfile = "logs/export_frontend_from_&2._&3._&4..log"
spool "&logfile." replace


prompt
prompt Start frontend export for app &1. on &2.
prompt ==================================================
prompt Create global temporary table temp_export_files if not exist
BEGIN
  FOR i IN (SELECT 'TEMP_EXPORT_FILES' AS object_name FROM dual 
            MINUS
            SELECT object_name FROM user_objects) LOOP
    EXECUTE IMMEDIATE q'[
--------------------------------------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE temp_export_files (
  name       VARCHAR2(255),
  contents   CLOB
) ON COMMIT DELETE ROWS
--------------------------------------------------------------------------------
    ]';
  END LOOP;
END;
/


prompt Do the frontend export, relocate files and save to temporary table
DECLARE
  l_app_id  pls_integer := &1.;
  l_files   apex_t_export_files;
BEGIN
  l_files   := apex_export.get_application(
    p_application_id          => l_app_id,
    p_split                   => true,
    p_with_date               => true,
    p_with_ir_public_reports  => true,
    p_with_ir_private_reports => false,
    p_with_ir_notifications   => false,
    p_with_translations       => true,
    p_with_pkg_app_mapping    => false,
    p_with_original_ids       => true,
    p_with_no_subscriptions   => false,
    p_with_comments           => true,
    p_with_supporting_objects => 'Y');
  FOR i IN 1..l_files.count LOOP
    -- relocate files to own project structure
    l_files(i).name := replace(
      l_files(i).name, 
      'f'||l_app_id||'/application/',
      '../app_frontend/'
    );
    -- correct prompts for relocation
    l_files(i).contents := replace(
      l_files(i).contents, 
      'prompt --application/',
      'prompt --app_frontend/'
    );
    -- special handling for install file
    IF l_files(i).name = 'f'||l_app_id||'/install.sql' THEN
      l_files(i).name := 'install_frontend.sql';
      l_files(i).contents := replace(
        replace(
          l_files(i).contents,
          '@application/',
          '@../app_frontend/'
        ),
        'prompt --install',
        'prompt --install_frontend'
      );
    END IF;
  END LOOP;
  FORALL i IN 1..l_files.count
    INSERT INTO temp_export_files VALUES (
      l_files(i).name,
      l_files(i).contents
    );
END;
/


prompt Create intermediate script file to unload the table content into files
spool off
set termout off serveroutput on
spool "logs/temp_export_files.sql"
BEGIN
  -- create host commands for the needed directories (spool does NOT create missing directories)
  FOR i IN (
    WITH t1 AS (
      SELECT regexp_substr(name, '^((\w|\.)+\/)+') AS dir
        FROM temp_export_files
    )
    SELECT DISTINCT
            dir,
            -- This is for Windows to create a directory and suppress warning if it exist.
            -- Align the command to your operating system:
            'host mkdir "' || replace(dir,'/','\') || '" 2>NUL' AS mkdir
      FROM t1
      WHERE dir IS NOT NULL
  ) LOOP
    dbms_output.put_line('set termout on');
    dbms_output.put_line('spool "&logfile." append');
    dbms_output.put_line('prompt --create directory if not exist: ' || i.dir);
    dbms_output.put_line('spool off');
    dbms_output.put_line('set termout off');
    dbms_output.put_line(i.mkdir);
    dbms_output.put_line('-----');
  END LOOP;

  -- create the spool calls for unload the files
  FOR i IN (SELECT * FROM temp_export_files) LOOP
    dbms_output.put_line('set termout on');
    dbms_output.put_line('spool "&logfile." append');
    dbms_output.put_line('prompt --' || i.name);
    dbms_output.put_line('spool off');
    dbms_output.put_line('set termout off');
    dbms_output.put_line('spool "' || i.name || '"');
    dbms_output.put_line('select contents from temp_export_files where name = ''' || i.name || ''';');
    dbms_output.put_line('spool off');
    dbms_output.put_line('-----');
  END LOOP;
END;
/
spool off
set termout on serveroutput off
spool "&logfile." append


prompt Call the intermediate script file to save the files
spool off
@logs/temp_export_files.sql
set termout on serveroutput off
spool "&logfile." append


prompt Delete files from the global temporary table
COMMIT;


prompt ==================================================
prompt Export DONE :-) 
prompt

rem file: export_frontend.bat

echo off
setlocal
set systemrole=DEV
set connection=localhost:1521/orcl
set schema=HR
set app_id=100
set areyousure=N

rem align delimiters to your os locale
for /f "tokens=1-3 delims=. " %%a in ('date /t') do (set mydate=%%c%%b%%a)
for /f "tokens=1-2 delims=:"  %%a in ('time /t') do (set mytime=%%a%%b)

:PROMPT
echo.
echo. 
set /p areyousure=Export %schema% app %app_id% from %systemrole% (Y/N)?

if /i %areyousure% neq y goto END
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
set /p password=Please enter password for %schema% on %systemrole%:
echo exit | sqlplus -S %schema%/%password%@%connection% ^
  @export_frontend.sql ^
  %app_id% ^
  %systemrole% ^
  %mydate% ^
  %mytime%

:END
pause