Maurice Wilhelm

#!/bin/bash

# create_custom_install_and_file_upload_scripts.sh
#
# This script saves encoded data
# of workspace and application
# files in an upload SQL file
# and creates a customized install
# script for the APEX application

## Global Constants and Settings
readonly IMPORT_APEX_BASE_DIR_LOC="/tmp"
readonly IMPORT_APEX_DIR="apex"
readonly IMPORT_APEX_SHARED_COMPONENTS_PATH="${IMPORT_APEX_BASE_DIR_LOC}/${IMPORT_APEX_DIR}/application/shared_components"
readonly IMPORT_APEX_WORKSPACE_FILES_DIR="workspace_files"
readonly IMPORT_APEX_APP_FILES_DIR="application_files"
readonly IMPORT_APEX_APP_UPLOAD_FILES_NAME="upload_files.sql"
readonly IMPORT_APEX_APP_INSTALL_FILE_NAME="install_apex_app_with_files.sql"

readonly FONT_YELLOW='\033[1;33m'
readonly FONT_NOCOLOR='\033[0m'

## Functions

# Help text displayed to user in case of invalid script calls or via -help option
printHelpText()
{
   echo ""
   echo "Usage: $0 -apexAppId=<APEX_APP_ID> [-apexAppAlias=<APEX_APP_ALIAS>] [-workspaceName=<WORKSPACE_NAME>] [-schemaName=<SCHEMA_NAME>]"
   echo ""
   echo "Available Options:"
   echo -e "\t${FONT_YELLOW}-apexAppId${FONT_NOCOLOR}      - APEX App Id"
   echo -e "\t${FONT_YELLOW}-apexAppAlias${FONT_NOCOLOR}   - APEX App Alias (optional)"
   echo -e "\t${FONT_YELLOW}-workspaceName${FONT_NOCOLOR}  - Workspace Name (optional)"
   echo -e "\t${FONT_YELLOW}-schemaName${FONT_NOCOLOR}     - Schema Name (optional)"
   echo -e "\t${FONT_YELLOW}-help${FONT_NOCOLOR}           - Prints this help text"
   echo ""
}

# Assigns given option args to vars
assignOptsToVars()
{
  while [ "$1" != "" ];
  do
    opt=`echo $1 | awk -F= '{print $1}'`
    optarg=`echo $1 | awk -F= '{print $2}'`
    case "$opt" in
      -help ) printHelpText
              exit ;;
      -apexAppId ) APEX_APP_ID="$optarg" ;;
      -apexAppAlias ) APEX_APP_ALIAS="$optarg" ;;
      -workspaceName ) WORKSPACE_NAME="$optarg" ;;
      -schemaName ) SCHEMA_NAME="$optarg" ;;
      * ) echo "ERROR: Unknown option provided: \"$opt\"." 
          echo ""
          printHelpText
          exit 1 ;;
    esac
    shift
  done
}

# Verifies given option args
verifyOptions()
{
  if [ "$APEX_APP_ID" == "" ]; then
    echo "ERROR: Please provide all necessary option arguments. Use -help to show a list of all options."
    echo ""
    exit 1
  fi
}

# Prints Stage to output
printStage()
{
   echo ""
   echo "#"
   echo "# $1"
   echo "#"
   echo ""
}

# Prints Stage Result to output
printStageResult()
{
   echo ""
   echo ".. $1"
   echo ""
}

# Cleans up custom files in case of Errors
cleanup()
{
   local UPLOAD_FILE_LOC="${IMPORT_APEX_BASE_DIR_LOC}/${IMPORT_APEX_DIR}/${IMPORT_APEX_APP_UPLOAD_FILES_NAME}"
   local INSTALL_FILE_LOC="${IMPORT_APEX_BASE_DIR_LOC}/${IMPORT_APEX_DIR}/${IMPORT_APEX_APP_INSTALL_FILE_NAME}"

   if [ -f "${UPLOAD_FILE_LOC}" ]; then rm "${UPLOAD_FILE_LOC}"; fi
   if [ -f "${INSTALL_FILE_LOC}" ]; then rm "${INSTALL_FILE_LOC}"; fi
}

# exits script with error or success
exitScript()
{
   local EXIT_WITH_ERROR="$1"
    
   if [ "$EXIT_WITH_ERROR" == "Y" ]
   then
     echo "Some scripts failed."
     exit 1
   else
     echo "Finished."
     exit 0
   fi
}

# generates files upload script content
generateFilesUploadScriptContent()
{
  local DIR
  local DIR_PATH_FILE_NAME
  local FILE_NAME
  local INDEX
  
  echo "SET SERVEROUTPUT ON"
  echo "DECLARE
  TYPE VARCHAR2_TAB_T  IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

  l_apex_app_id        NUMBER := ${APEX_APP_ID};
  l_workspace_id       NUMBER;
  l_dir_path_file_name VARCHAR2(500);
  l_file_name          VARCHAR2(200);
  l_mime_type          VARCHAR2(128);
  l_file_content       VARCHAR2_TAB_T;

  FUNCTION get_mime_type(
    pi_file_name IN VARCHAR2
  ) RETURN VARCHAR2
  IS
    l_mime_type VARCHAR2(128);
  BEGIN
    SELECT mime_type
    INTO l_mime_type
    FROM XMLTABLE(
      XMLNAMESPACES(
      DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'),
        '//mime-mappings/mime-mapping' 
        PASSING xdb.dbms_xdb.cfg_get()
      COLUMNS
        extension VARCHAR2(50) PATH 'extension',
        mime_type VARCHAR2(128) PATH 'mime-type' 
    )
    WHERE LOWER(extension) = LOWER(SUBSTR(pi_file_name, INSTR(pi_file_name, '.', -1) + 1));
  
    RETURN l_mime_type;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_mime_type;

  FUNCTION varchar2_tab_to_blob(
    pi_vc2_table IN VARCHAR2_TAB_T
  ) RETURN BLOB
  IS
    l_blob BLOB;
    l_size NUMBER;
  BEGIN
    DBMS_LOB.createtemporary(l_blob, true, DBMS_LOB.session);
 
    FOR i IN 1 .. pi_vc2_table.COUNT
    LOOP
      l_size := LENGTH(pi_vc2_table(i)) / 2;
      DBMS_LOB.writeappend(l_blob, l_size, hextoraw(pi_vc2_table(i)));
    END LOOP;

    RETURN l_blob;
  EXCEPTION
    WHEN OTHERS THEN
      IF DBMS_LOB.istemporary(lob_loc => l_blob) = 1
      THEN
        DBMS_LOB.freetemporary(lob_loc => l_blob);
      END IF;
      RAISE;
  END varchar2_tab_to_blob;
  "

 echo "BEGIN
  SELECT workspace_id
  INTO l_workspace_id
  FROM apex_applications
  WHERE application_id = l_apex_app_id;

  APEX_UTIL.set_security_group_id(p_security_group_id => l_workspace_id);"
 
  DIR="${IMPORT_APEX_SHARED_COMPONENTS_PATH}/${IMPORT_APEX_WORKSPACE_FILES_DIR}"
  for FILE in $(find $DIR/* -type f -name '*' 2>/dev/null);
  do
    DIR_PATH_FILE_NAME=${FILE#*$DIR"/"}
    FILE_NAME=$(basename -- "$FILE")

    echo "
    BEGIN
      l_dir_path_file_name := '${DIR_PATH_FILE_NAME}';
      l_file_name := '${FILE_NAME}';
      l_mime_type := get_mime_type(pi_file_name => l_file_name);
    "

    echo "      l_file_content := NEW VARCHAR2_TAB_T();"

    INDEX=1
    for LINE in $(od -t x1 -An -v "$FILE" | tr -d '\n ' | fold -w 200)
    do
      echo "      l_file_content("$INDEX") := '"$LINE"';"
      INDEX=$(($INDEX+1))
    done

    echo "
      WWV_FLOW_API.create_workspace_static_file(p_file_name    => l_dir_path_file_name,
                                                p_mime_type    => nvl(l_mime_type, 'application/octet-stream'),
                                                p_file_charset => 'utf-8',
                                                p_file_content => varchar2_tab_to_blob(pi_vc2_table => l_file_content)
                                               );
      DBMS_OUTPUT.put_line('INFO: Workspace File ' || l_dir_path_file_name || ' uploaded successfully.');
    EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000, 'ERROR: Workspace File ' || l_dir_path_file_name || ' could not be uploaded - ' || SQLERRM || ' - ' || DBMS_UTILITY.format_error_backtrace);
    END;"
  done

  DIR="${IMPORT_APEX_SHARED_COMPONENTS_PATH}/${IMPORT_APEX_APP_FILES_DIR}"
  for FILE in $(find $DIR/* -type f -name '*' 2>/dev/null);
  do
    DIR_PATH_FILE_NAME=${FILE#*$DIR"/"}
    FILE_NAME=$(basename -- "$FILE")

    echo "
    BEGIN
      l_dir_path_file_name := '${DIR_PATH_FILE_NAME}';
      l_file_name := '${FILE_NAME}';
      l_mime_type := get_mime_type(pi_file_name => l_file_name);
    "

    echo "      l_file_content := NEW VARCHAR2_TAB_T();"

    INDEX=1
    for LINE in $(od -t x1 -An -v "$FILE" | tr -d '\n ' | fold -w 200)
    do
      echo "      l_file_content("$INDEX") := '"$LINE"';"
      INDEX=$(($INDEX+1))
    done

    echo "
      WWV_FLOW_IMP_SHARED.create_app_static_file(p_flow_id      => l_apex_app_id,
                                                 p_file_name    => l_dir_path_file_name,
                                                 p_mime_type    => nvl(l_mime_type, 'application/octet-stream'),
                                                 p_file_charset => 'utf-8',
                                                 p_file_content => varchar2_tab_to_blob(pi_vc2_table => l_file_content)
                                                );
      DBMS_OUTPUT.put_line('INFO: Application File ' || l_dir_path_file_name || ' uploaded successfully.');
    EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000, 'ERROR: Application File ' || l_dir_path_file_name || ' could not be uploaded - ' || SQLERRM || ' - ' || DBMS_UTILITY.format_error_backtrace);
    END;"
  done

  echo "COMMIT;"
  echo "END;"
  echo "/"
  echo ""
}

# generates custom install script content
generateInstallScriptContent()
{
  echo "BEGIN"
  echo "  APEX_APPLICATION_INSTALL.generate_offset();"
  echo "  APEX_APPLICATION_INSTALL.set_application_id(${APEX_APP_ID});"
  if [ "$APEX_APP_ALIAS" != "" ]; then
    echo "  APEX_APPLICATION_INSTALL.set_application_alias('${APEX_APP_ALIAS}');"
  fi
  if [ "$WORKSPACE_NAME" != "" ]; then
    echo "  APEX_APPLICATION_INSTALL.set_workspace('${WORKSPACE_NAME}');"
  fi
  if [ "$SCHEMA_NAME" != "" ]; then
    echo "  APEX_APPLICATION_INSTALL.set_schema('${SCHEMA_NAME}');"
  fi
  echo "END;"
  echo "/"
  echo ""

  echo "PROMPT # Install APEX application"
  echo "@install.sql"
  echo ""
  echo "PROMPT # Upload static workspace and application files"
  echo "@${IMPORT_APEX_APP_UPLOAD_FILES_NAME}"
}

# creates files upload script
createFilesUploadScript()
{
  local UPLOAD_FILE_LOC="${IMPORT_APEX_DIR}/${IMPORT_APEX_APP_UPLOAD_FILES_NAME}"

  printStage "Create files upload script"

  generateFilesUploadScriptContent > "${UPLOAD_FILE_LOC}"

  if [ -f "${UPLOAD_FILE_LOC}" ]
  then
    printStageResult "Done: ${UPLOAD_FILE_LOC}"
  else
    printStageResult "ERROR: Failed to create files upload script."
    exitScript "Y"
  fi
}

# creates custom install script
createInstallScript()
{
  local INSTALL_FILE_LOC="${IMPORT_APEX_DIR}/${IMPORT_APEX_APP_INSTALL_FILE_NAME}"

  printStage "Create custom install script"

  generateInstallScriptContent > "${INSTALL_FILE_LOC}"

  if [ -f "${INSTALL_FILE_LOC}" ]
  then
    printStageResult "Done: ${INSTALL_FILE_LOC}"
  else
    printStageResult "ERROR: Failed to create install script."
    exitScript "Y"
  fi
}

# entry point
main()
{
  ## Set Options to Vars
  assignOptsToVars "$@"

  ## Verify given Options
  verifyOptions

  cd $IMPORT_APEX_BASE_DIR_LOC

  ## Create files upload script
  createFilesUploadScript

  ## Create custom install script
  createInstallScript

  exitScript "N"
}

main "$@"

BEGIN
  APEX_APPLICATION_INSTALL.generate_offset();
  APEX_APPLICATION_INSTALL.set_application_id(141);
END;
/

PROMPT # Install APEX application
@install.sql

PROMPT # Upload static workspace and application files
@upload_files.sql

prompt --application/shared_components/files/icons_app_icon_32_png
begin
--   Manifest
--     APP STATIC FILES: 141
--   Manifest End
wwv_flow_imp.component_begin (
 p_version_yyyy_mm_dd=>'2022.10.07'
,p_release=>'22.2.11'
,p_default_workspace_id=>19274859372729475
,p_default_application_id=>141
,p_default_id_offset=>16094782927472913
,p_default_owner=>'DATABASESCHEMA'
);
wwv_flow_imp.g_varchar2_table := wwv_flow_imp.empty_varchar2_table;
wwv_flow_imp.g_varchar2_table(1) := '89504E470D0A1A0A0000000D4948445200000020000000200806000000737A7AF4000000017352474200AECE1CE9000003DB4944415458479D57CB4E534118FE0E050A358458902890B444034408246014909562C2D2C4B54B8DCFE0C28589F804BAD0C4';
wwv_flow_imp.g_varchar2_table(2) := '8D4FA09B920862DC881A1A342A14D4588C1A958B9808A5B43D666EE7CCCC9939A7DA459BCE7FFBFEFB8C333037E3C2051C0770E1C0FB76C10E09919FD35FCA4B45D84790091797A1544FCC610497FF0A39618B02D00EC95F62DAB321191167029A0F8E51';
wwv_flow_imp.g_varchar2_table(3) := '849C0C9B9C3A20B0C4AF647190033079242BB71994704A91D261A95CBE670E1C16015FBD8555512E3C9542C7E82E53558D362F92048094794B3254537690664A30F0BE0B0E4901535F2D6E1B5F98BC89C6AA85A740ADE87F87C2C32F0740AE622530AA76';
wwv_flow_imp.g_varchar2_table(4) := '0A20605069415B01F925EF964AA8ECEC50C69A44024E6D6D50C8D049B4A5590AC2ABC09A7317A8ECEF61B02181743289DFBBBBF8F0731D9F630E9C78BCAA527106E666F9D430F337FFDAC6D1A626EAEEDAD606C68EB4FB4308C0C2C63A46BB7BF0757313';
wwv_flow_imp.g_varchar2_table(5) := 'C54A19E9D643C864B39848A5B0B0FD0B5F128950206A0D184A71B85CC6644F2F55329D5BC6D4C86945E195A74F30D4D5858F6B6B483434E0705B1BE6575771EFCC046E2F66F1A05854A79A90E68D1500A0C38D027069F6114E7477E3DBD6162A950A3A5B';
wwv_flow_imp.g_varchar2_table(6) := '5AF072650577CF9E6300F68BC608881AF50158C6ED90210232EB9D37AF514C1C407D1D293C177BFB65C477FEE072FF006E2D66F19003B036AFBA0BC472F1D9D5082C616A645CF168BBB887AB2FE631984AD3F357F94F983A3982E6FA3803A0A74073544A';
wwv_flow_imp.g_varchar2_table(7) := '81BE09991D1DC08D91716551119EFBEFDEE23DF7F4585D3D2E1EEFA3854A52202260AB4469129A59AC35C083241CBAFEFC195570EDD4A8A7484E81772856BFCB06341F440E5C3E9075F7A28A90CD539E3ACD07B908FD7DE02F2D2AEAEF82E8086472CBB8';
wwv_flow_imp.g_varchar2_table(8) := 'A9B4A16E59FD2F2210BA25061ECFB04168F9B4AEAF23954C526A7E731363ED1D8C93C7DEAC9C1197BEFFC0EAC166BBF2C032E2AC542937C052D04329D3B91CD21D9D8AC242A984D57C1E17FAFB51138B29B44C2E876CAC463AD31611A987401B0A76BE90';
wwv_flow_imp.g_varchar2_table(9) := '86CB254C76F7D23C934998EAE85403E60214C49A0EC24526B7A2010806237217F4158A38DFDFC723B0EC45409F5B85FD20083502C16491BB2A03C0AF52A66491355B2EECB29C1B3E1E104331C41A1BE97A0E1334A6C0D3651CCFE27E6BC5145A744ACF92';
wwv_flow_imp.g_varchar2_table(10) := '5920D631EB53A9BCF93FC531F90D20BF184C970DAA4A7D0B182F66D622A460A4EBA4D188790E88D3A8AB1DAF01F3C3C416C728A52A6C99DB2CA94C42DBDD35EA52FEBF7214AC7E29ADC6C3882A93DE8BE1DAFC36B4F5986586856D80B0688837AAEC809F';
wwv_flow_imp.g_varchar2_table(11) := '025B2547B95BB59CB91ED81C30B457985D45157F79EBFC7E27C8ED4D5EEA0E5CFA8E679FBF36F6013F40676AA90000000049454E44AE426082';
wwv_flow_imp_shared.create_app_static_file(
 p_id=>wwv_flow_imp.id(50871026532675204)
,p_file_name=>'icons/app-icon-32.png'
,p_mime_type=>'image/png'
,p_file_charset=>'utf-8'
,p_file_content => wwv_flow_imp.varchar2_to_blob(wwv_flow_imp.g_varchar2_table)
);
wwv_flow_imp.component_end;
end;
/

#!/bin/bash

# export_apex_app_with_files_using_stdout.sh
#
# This script exports APEX apps with 
# workspace and application files in 
# their uncoded binary raw format
# using the standard output and xxd

## Global Constants and Settings
readonly EXPORT_APEX_BASE_DIR_LOC="/tmp"
readonly EXPORT_APEX_DIR="apex"
readonly EXPORT_APEX_SHARED_COMPONENTS_PATH="${EXPORT_APEX_BASE_DIR_LOC}/${EXPORT_APEX_DIR}/application/shared_components"
readonly EXPORT_APEX_WORKSPACE_FILES_PATH="${EXPORT_APEX_SHARED_COMPONENTS_PATH}/workspace_files"
readonly EXPORT_APEX_APP_FILES_PATH="${EXPORT_APEX_SHARED_COMPONENTS_PATH}/application_files"

readonly FONT_YELLOW='\033[1;33m'
readonly FONT_NOCOLOR='\033[0m'

# Settings script for returning the BLOB data
readonly SETTINGS_SCRIPT="
WHENEVER SQLERROR EXIT 1;
SET SERVEROUTPUT ON SIZE UNLIMITED
SET FEEDBACK OFF
SET HEADING OFF
SET ECHO OFF
SET PAGESIZE 0
SET LINESIZE 32767
SET TRIMSPOOL ON
SET LONG 900000
"

# Get Workspace Id script
readonly EXPORT_WORKSPACE_ID_SCRIPT="
$SETTINGS_SCRIPT

SELECT TO_CHAR(workspace_id)
FROM apex_applications
WHERE application_id = _APP_ID_;

QUIT;
"

# Get Workspace file contents script
readonly EXPORT_WORKSPACE_FILE_SCRIPT="
$SETTINGS_SCRIPT

SELECT file_content
FROM apex_workspace_static_files
WHERE workspace_id = _WORKSPACE_ID_
AND file_name = '_FILENAME_';

QUIT;
"

# Get Application file contents script
readonly EXPORT_APPLICATION_FILE_SCRIPT="
$SETTINGS_SCRIPT

SELECT file_content
FROM apex_application_static_files
WHERE application_id = _APP_ID_
AND file_name = '_FILENAME_';

QUIT;
"

## Functions

# Help text displayed to user in case of invalid script calls or via -help option
printHelpText()
{
   echo ""
   echo "Usage: $0 -sqlclExec=<\"PATH/TO/SQLCL/BIN/sql\"> -apexAppId=<APEX_APP_ID> -dbHost=<DB_HOST> -dbPort=<DB_PORT> -dbServiceName=<DB_SERVICE_NAME> -dbExecUser=<DB_EXEC_USER> [-dbExecUserPassword=<DB_EXEC_USER_PASSWORD>]"
   echo ""
   echo "Available Options:"
   echo -e "\t${FONT_YELLOW}-sqlclExec${FONT_NOCOLOR}           - Path to executables of SQLcl"
   echo -e "\t${FONT_YELLOW}-apexAppId${FONT_NOCOLOR}           - APEX App Id"
   echo -e "\t${FONT_YELLOW}-dbHost${FONT_NOCOLOR}              - Hostname"
   echo -e "\t${FONT_YELLOW}-dbPort${FONT_NOCOLOR}              - Port"
   echo -e "\t${FONT_YELLOW}-dbServiceName${FONT_NOCOLOR}       - Service Name"
   echo -e "\t${FONT_YELLOW}-dbExecUser${FONT_NOCOLOR}          - User"
   echo -e "\t${FONT_YELLOW}-dbExecUserPassword${FONT_NOCOLOR}  - Password (optional; password prompt will be displayed)"
   echo -e "\t${FONT_YELLOW}-help${FONT_NOCOLOR}                - Prints this help text"
   echo ""
}

# Assigns given option args to vars
assignOptsToVars()
{
  while [ "$1" != "" ];
  do
    opt=`echo $1 | awk -F= '{print $1}'`
    optarg=`echo $1 | awk -F= '{print $2}'`
    case "$opt" in
      -help ) printHelpText
              exit ;;
      -sqlclExec ) SQLCL_EXEC="$optarg" ;;
      -apexAppId ) APP_ID="$optarg" ;;
      -dbHost ) DB_HOST="$optarg" ;;
      -dbPort ) DB_PORT="$optarg" ;;
      -dbServiceName ) DB_SERVICE="$optarg" ;;
      -dbExecUser ) DB_USERNAME="$optarg" ;;
      -dbExecUserPassword ) DB_PASSWORD="$optarg" ;;
      * ) echo "ERROR: Unknown option provided: \"$opt\"." 
          echo ""
          printHelpText
          exit 1 ;;
    esac
    shift
  done
}

# Verifies given option args
verifyOptions()
{
  if [ "$DB_USERNAME" != "" ] && [ "$DB_PASSWORD" == "" ]; then
    stty -echo
    read -p "Enter Password of User ${DB_USERNAME}: " DB_PASSWORD
    stty echo
    echo ""
  fi

  if [ "$SQLCL_EXEC" == "" ] || [ "$APP_ID" == "" ] || [ "$DB_HOST" == "" ] || [ "$DB_PORT" == "" ] || [ "$DB_SERVICE" == "" ] || [ "$DB_USERNAME" == "" ] || [ "$DB_PASSWORD" == "" ]; then
    echo "ERROR: Please provide all necessary option arguments. Use -help to show a list of all options."
    echo ""
    exit 1
  fi
}

# Prints Stage to output
printStage()
{
   echo ""
   echo "#"
   echo "# $1"
   echo "#"
   echo ""
}

# Prints Stage Result to output
printStageResult()
{
   echo ""
   echo ".. $1"
   echo ""
}

# Prints Step to output
printStep()
{
   echo ".... $1"
}

# Prints Step Result to output
printStepResult()
{
   echo "........ $1"
}

# Cleans up dirs in case of Errors or script termination
cleanup()
{
   local CLEANUP_ALL="$1"

   if [[ -f "${EXPORT_APEX_BASE_DIR_LOC}/files_${WORKSPACE_ID}.sql" ]]; then rm "${EXPORT_APEX_BASE_DIR_LOC}/files_${WORKSPACE_ID}.sql"; fi
   if [[ -d "${EXPORT_APEX_BASE_DIR_LOC}/f${APP_ID}" ]]; then rm -rf "${EXPORT_APEX_BASE_DIR_LOC}/f${APP_ID}"; fi
   
   if [ "$CLEANUP_ALL" == "N" ]
   then
     if [[ -d "${EXPORT_APEX_SHARED_COMPONENTS_PATH}/files" ]]; then rm -rf "${EXPORT_APEX_SHARED_COMPONENTS_PATH}/files"; fi
   else
     if [[ -d "${EXPORT_APEX_BASE_DIR_LOC}/${EXPORT_APEX_DIR}" ]]; then rm -rf "${EXPORT_APEX_BASE_DIR_LOC}/${EXPORT_APEX_DIR}"; fi
   fi
}

# exits script with error or success
exitScript()
{
   local EXIT_WITH_ERROR="$1"
    
   if [ "$EXIT_WITH_ERROR" == "Y" ]
   then
     echo "Some scripts failed."
     exit 1
   else
     echo "Finished."
     exit 0
   fi
}

# Tests the database connection
testDatabaseConnection()
{
  echo "exit" | "$SQLCL_EXEC" -L "${DB_USERNAME}/${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" | grep -q "ORA-" > /dev/null

  if [ $? -eq 0 ]
  then
    echo "ERROR: The database connection test failed. Please check the user credentials."
    exitScript "Y"
  fi
}

# Determine workspace id
determineWorkspaceId()
{
   local EXPORT_WORKSPACE_ID_SCRIPT_MOD="${EXPORT_WORKSPACE_ID_SCRIPT/_APP_ID_/$APP_ID}"
   local SCRIPT_FAILED

   printStage "Determine Workspace ID"

   WORKSPACE_ID=$("$SQLCL_EXEC" -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" <<< "$EXPORT_WORKSPACE_ID_SCRIPT_MOD")

   if [ $? -eq 0 ]
   then
     WORKSPACE_ID=$(echo "$WORKSPACE_ID" | grep -oP "\d*")
     if [[ "$WORKSPACE_ID" =~ ^[[:digit:]]{6,}$ ]]
     then
       printStageResult "Done: ${WORKSPACE_ID}."
     else
       SCRIPT_FAILED="Y"
     fi
   else
    SCRIPT_FAILED="Y"
   fi

   if [ "$SCRIPT_FAILED" == "Y" ]
   then
    printStageResult "ERROR: Failed to get Workspace ID."
    cleanup "Y"
    exitScript "Y"
   fi
}

# Exports APEX application with application static files
exportApplicationWithEncodedFiles()
{
   printStage "SQLcl: Export APEX Application with Static Application Files (hex encoded)"

   "$SQLCL_EXEC" -s 2>&1 "${DB_USERNAME}/${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" <<< "apex export -applicationid ${APP_ID} -skipExportDate -expOriginalIds -split"

   if [ -d "f${APP_ID}" ]
   then
    printStageResult "Done."
   else
    printStageResult "ERROR: Failed to export APEX application."
    cleanup "Y"
    exitScript "Y"
   fi

   # Rename export app folder
   mv "f${APP_ID}" "$EXPORT_APEX_DIR"
}

# Exports hex encoded workspace static files
exportEncodedWorkspaceFiles()
{   
   printStage "SQLcl: Export APEX Static Workspace Files (hex encoded)"

   "$SQLCL_EXEC" -s 2>&1 "${DB_USERNAME}/${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" <<< "apex export -workspaceid ${WORKSPACE_ID} -expOriginalIds -expFiles"

   if [ -f "files_${WORKSPACE_ID}.sql" ]
   then
    printStageResult "Done."
   else
    printStageResult "ERROR: Failed to export APEX Static Workspace Files."
    cleanup "Y"
    exitScript "Y"
   fi
}

# Exports binary raw workspace files
exportBinaryRawWorkspaceFiles()
{
   local EXPORT_WORKSPACE_FILE_SCRIPT_MOD
   local BLOB
   local FILEPATH

   printStage "SQLcl: Export APEX Static Workspace Files (binary raw)"

   # Create directory for workspace files
   mkdir -p "$EXPORT_APEX_WORKSPACE_FILES_PATH"

   # loop through exported workspace static files script
   grep -oPs "(?<=,p_file_name=>').*(?=')" "files_${WORKSPACE_ID}.sql" | while IFS= read -r FILENAME
   do
    printStep "Found Workspace File: ${FILENAME}"
    EXPORT_WORKSPACE_FILE_SCRIPT_MOD="${EXPORT_WORKSPACE_FILE_SCRIPT/_WORKSPACE_ID_/$WORKSPACE_ID}"
    EXPORT_WORKSPACE_FILE_SCRIPT_MOD="${EXPORT_WORKSPACE_FILE_SCRIPT_MOD/_FILENAME_/$FILENAME}"
    BLOB=$("$SQLCL_EXEC" -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" <<< "$EXPORT_WORKSPACE_FILE_SCRIPT_MOD")
     if [ $? -eq 0 ] && [[ $BLOB ]]
     then
      FILEPATH=$(dirname "${EXPORT_APEX_WORKSPACE_FILES_PATH}/${FILENAME}")
      mkdir -p "$FILEPATH"
      if ! xxd -r -p <<< "$BLOB" > "${EXPORT_APEX_WORKSPACE_FILES_PATH}/${FILENAME}"; then
        printStepResult "ERROR: Workspace File ${FILENAME} could not be created."
        exit 1
      else
        printStepResult "Workspace File ${FILENAME} created."
      fi
     else
      printStepResult "ERROR: BLOB data of File ${FILENAME} could not be returned via standard output."
      exit 1
     fi
   done

   if [ $? -eq 0 ]
   then
    printStageResult "Done."
   else
    printStageResult "ERROR: Failed to export APEX Static Workspace File BLOB data."
    cleanup "Y"
    exitScript "Y"
   fi
}

# Exports binary raw application files
exportBinaryRawApplicationFiles()
{
   local EXPORT_APPLICATION_FILE_SCRIPT_MOD
   local BLOB
   local FILEPATH

   printStage "SQLcl: Export APEX Static Application Files (binary raw)"

   # Create directory for application files
   mkdir -p "$EXPORT_APEX_APP_FILES_PATH"

   # loop through exported application static files scripts
   for FILE in $(find ${EXPORT_APEX_SHARED_COMPONENTS_PATH}/files/* -type f -name '*.sql');
   do
    grep -oPs "(?<=,p_file_name=>').*(?=')" "$FILE" | while IFS= read -r FILENAME
     do
      printStep "Found Application File: ${FILENAME}"
      EXPORT_APPLICATION_FILE_SCRIPT_MOD="${EXPORT_APPLICATION_FILE_SCRIPT/_APP_ID_/$APP_ID}"
      EXPORT_APPLICATION_FILE_SCRIPT_MOD="${EXPORT_APPLICATION_FILE_SCRIPT_MOD/_FILENAME_/$FILENAME}"
      BLOB=$("$SQLCL_EXEC" -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" <<< "$EXPORT_APPLICATION_FILE_SCRIPT_MOD")
      if [ $? -eq 0 ] && [[ $BLOB ]]
      then
       FILEPATH=$(dirname "${EXPORT_APEX_APP_FILES_PATH}/${FILENAME}")
       mkdir -p "$FILEPATH"
       if ! xxd -r -p <<< "$BLOB" > "${EXPORT_APEX_APP_FILES_PATH}/${FILENAME}"; then
         printStepResult "ERROR: Application File ${FILENAME} could not be created."
         exit 1
       else
         printStepResult "Application File ${FILENAME} created."
       fi
      else
       printStepResult "ERROR: BLOB data of File ${FILENAME} could not be returned via standard output."
       exit 1
      fi
     done
   done

   if [ $? -eq 0 ]
   then
    printStageResult "Done."
   else
    printStageResult "ERROR: Failed to export APEX Static Application File BLOB data."
    cleanup "Y"
    exitScript "Y"
   fi
}

# Adjusts install.sql file of APEX app export
adjustInstallFile()
{
   printStage "Remove references to hex encoded files from install.sql script of APEX application export"

   # Remove calling of single app files inside install.sql
   find "${EXPORT_APEX_DIR}" -type f -name "install.sql" -exec sed -i -e '/^@@application\/shared_components\/files/d' {} +

   if [ $? -eq 0 ]
   then
    printStageResult "Done."
   else
    printStageResult "ERROR: Failed to remove file references."
    cleanup "Y"
    exitScript "Y"
   fi
}

# entry point
main()
{
  ## Set Options to Vars
  assignOptsToVars "$@"

  ## Verify given Options
  verifyOptions

  ## Test the database connection
  testDatabaseConnection

  cd $EXPORT_APEX_BASE_DIR_LOC

  ## Clean up before
  cleanup "Y"

  ## Determine Workspace Id
  determineWorkspaceId

  ## Export APEX application with app files (encoded)
  exportApplicationWithEncodedFiles

  ## Workspace files export (encoded)
  exportEncodedWorkspaceFiles

  ## Export binary raw files
  exportBinaryRawWorkspaceFiles
  exportBinaryRawApplicationFiles
 
  ## Adjust APEX install.sql script
  adjustInstallFile

  cleanup "N"
  exitScript "N"
}

main "$@"

#!/bin/bash

# export_apex_app_with_files_using_utl_file.sh
#
# This script exports APEX apps with 
# workspace and application files in 
# their uncoded binary raw format
# using the PL/SQL UTL_FILE package

## Global Constants and Settings
readonly EXPORT_APEX_BASE_DIR_LOC="/tmp"
readonly EXPORT_APEX_DIR="apex"
readonly EXPORT_APEX_SHARED_COMPONENTS_PATH="${EXPORT_APEX_BASE_DIR_LOC}/${EXPORT_APEX_DIR}/application/shared_components"
readonly EXPORT_APEX_WORKSPACE_FILES_DIR_NAME="workspace_files"
readonly EXPORT_APEX_APPLICATION_FILES_DIR_NAME="application_files"
readonly EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT="DIRECTORY_EXPORT_WORKSPACE_FILES"
readonly EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT="DIRECTORY_EXPORT_APP_FILES"

readonly FONT_YELLOW='\033[1;33m'
readonly FONT_NOCOLOR='\033[0m'

# Script to create directory objects for each dir
readonly CREATE_DRECTORY_OBJECTS_SCRIPT="
WHENEVER SQLERROR EXIT 1;
SET SERVEROUTPUT ON SIZE UNLIMITED
SET FEEDBACK OFF

DECLARE
  c_directory                  CONSTANT VARCHAR2(50) := '_EXPORT_APEX_SHARED_COMPONENTS_PATH_/';
  TYPE VARCHAR2_TAB_T          IS TABLE OF VARCHAR2(4000);

  l_workspace_id               NUMBER;
  l_directory_object           VARCHAR2(50);
  l_directory                  VARCHAR2(70);
  l_directories                VARCHAR2(1000);
  l_dir_object_workspace_files VARCHAR2(50)   := '_EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT_';
  l_dir_object_app_files       VARCHAR2(50)   := '_EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT_';
  l_dir_workspace_files        VARCHAR2(70)   := c_directory || '_EXPORT_APEX_WORKSPACE_FILES_DIR_NAME_';
  l_dir_application_files      VARCHAR2(70)   := c_directory || '_EXPORT_APEX_APPLICATION_FILES_DIR_NAME_';
  l_directory_objects_table    VARCHAR2_TAB_T := NEW VARCHAR2_TAB_T();

  l_apex_app_id                NUMBER         :=  _APEX_APP_ID_;
  l_db_running_user            VARCHAR2(50)   := '_DB_RUNNING_USER_';
  l_db_privileged_user         VARCHAR2(50)   := '_DB_PRIVILEGED_USER_';
BEGIN
  SELECT workspace_id
  INTO l_workspace_id
  FROM apex_applications
  WHERE application_id = l_apex_app_id;
 
  -- Get all directory paths of files
  FOR c_directories IN (SELECT flow_id,
                               file_path,
                               rtrim(upper(replace(file_path, '/', '_')), '_') as file_path_name
                        FROM (SELECT flow_id,
                                     regexp_replace(file_name, '[^/]+$', '') as file_path
                              FROM (SELECT 0 as flow_id,
                                           file_name
                                    FROM apex_workspace_static_files
                                    WHERE workspace_id = l_workspace_id
                                  UNION ALL
                                    SELECT application_id as flow_id,
                                           file_name
                                    FROM apex_application_static_files
                                    WHERE application_id = l_apex_app_id
                                   )
                              ORDER BY flow_id ASC
                              )
                        GROUP BY flow_id, file_path
  ) LOOP

    BEGIN
      IF c_directories.flow_id = 0
      THEN
         l_directory_object := l_dir_object_workspace_files;
         l_directory := l_dir_workspace_files;
      ELSE
         l_directory_object := l_dir_object_app_files;
         l_directory := l_dir_application_files;
      END IF;

      IF c_directories.file_path IS NOT NULL
      THEN
        l_directory_object := l_directory_object || '_' || c_directories.file_path_name;
        l_directory := l_directory || '/' || c_directories.file_path;
      END IF;

      EXECUTE IMMEDIATE '
          CREATE DIRECTORY
               ' || l_directory_object || '
          AS ''' || l_directory || '''
      ';

      -- Save directory names temporarily in case of errors
      l_directory_objects_table.extend;
      l_directory_objects_table(l_directory_objects_table.last) := l_directory_object;
      
      l_directories := l_directories || ':' || l_directory;
      
      IF upper(l_db_running_user) != upper(l_db_privileged_user)
      THEN
        EXECUTE IMMEDIATE '
            GRANT READ, WRITE ON DIRECTORY
               ' || l_directory_object || '
            TO ' || l_db_running_user || '
        ';
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        -- Drop all previously created directories
        FOR c_dir in l_directory_objects_table.FIRST .. l_directory_objects_table.LAST
        LOOP
          BEGIN
            EXECUTE IMMEDIATE 'DROP DIRECTORY ' || l_directory_objects_table(c_dir) || '';
          EXCEPTION
            WHEN OTHERS THEN
              CONTINUE;
          END;
        END LOOP;
        RAISE;
    END;

  END LOOP;

  DBMS_OUTPUT.put_line(ltrim(l_directories, ':'));
END;
/

QUIT;
"

# Script for cleanup directory objects
readonly DROP_DRECTORY_OBJECTS_SCRIPT="
SET FEEDBACK OFF

DECLARE
  l_dir_object_workspace_files VARCHAR2(50)   := '_EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT_';
  l_dir_object_app_files       VARCHAR2(50)   := '_EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT_';
BEGIN
  FOR c_directories IN (SELECT directory_name
                        FROM all_directories
                        WHERE (   directory_name LIKE l_dir_object_workspace_files || '%'
                               OR directory_name LIKE l_dir_object_app_files || '%'
                              )
  ) LOOP

    BEGIN
      EXECUTE IMMEDIATE 'DROP DIRECTORY ' || c_directories.directory_name || '';
    EXCEPTION
      WHEN OTHERS THEN
        CONTINUE;
    END;

  END LOOP;
END;
/

QUIT;
"

# Custom PL/SQL export script
readonly EXPORT_FILES_SCRIPT="
WHENEVER SQLERROR EXIT 1;
WHENEVER OSERROR EXIT 2;
SET FEEDBACK OFF

DECLARE
  l_file                       UTL_FILE.FILE_TYPE;
  l_buffer                     RAW(32767);
  l_amount                     BINARY_INTEGER := 32767;
  l_position                   NUMBER         := 1;
  l_blob_length                NUMBER;
  l_workspace_id               NUMBER;
  l_directory_object           VARCHAR2(50);
  l_dir_object_workspace_files VARCHAR2(50)   := '_EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT_';
  l_dir_object_app_files       VARCHAR2(50)   := '_EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT_';

  l_apex_app_id                NUMBER         :=  _APEX_APP_ID_;
BEGIN
  SELECT workspace_id
  INTO l_workspace_id
  FROM apex_applications
  WHERE application_id = l_apex_app_id;

  -- Get all files accessable in the workspace
  /*
   * Alternatively accessable over wwv_flow_files:
   *
   * You need to set the security group id first.
   *
   * APEX_UTIL.set_workspace('<WorkspaceName>');
   *
   * wwv_flow_files.flow_id:
   *   = 0 - workspace file
   *   > 0 - application file
   */
 
  FOR c_files IN (SELECT flow_id,
                         file_content,
                         file_path,
                         rtrim(upper(replace(file_path, '/', '_')), '_') as file_path_name,
                         path_file_name,
                         regexp_substr(path_file_name, '[^/]*$') as file_name
                    FROM (SELECT flow_id,
                                 file_content,
                                 regexp_replace(file_name, '[^/]+$', '') as file_path,
                                 file_name as path_file_name
                          FROM (SELECT 0 as flow_id,
                                       file_content,
                                       file_name
                                FROM apex_workspace_static_files
                                WHERE workspace_id = l_workspace_id
                              UNION ALL
                                SELECT application_id as flow_id,
                                       file_content,
                                       file_name
                                FROM apex_application_static_files
                                WHERE application_id = l_apex_app_id
                               )
                          )
                    ORDER BY flow_id ASC
  ) LOOP

    BEGIN
      IF c_files.flow_id = 0
      THEN
         l_directory_object := l_dir_object_workspace_files;
      ELSE
         l_directory_object := l_dir_object_app_files;
      END IF;

      IF c_files.file_path IS NOT NULL
      THEN
        l_directory_object := l_directory_object || '_' || c_files.file_path_name;
      END IF;

      -- Create and open file in write byte mode (binary) within directory
      l_file := UTL_FILE.fopen(l_directory_object, c_files.file_name, 'wb', 32767);

      -- Get length of BLOB, read bytes based on max chunk size and write it to file on OS
      l_blob_length := DBMS_LOB.getlength(c_files.file_content);

      WHILE l_position <= l_blob_length
      LOOP
        DBMS_LOB.read(c_files.file_content, l_amount, l_position, l_buffer);
        UTL_FILE.put_raw(l_file, l_buffer, TRUE);
        l_position := l_position + l_amount;
      END LOOP;

      l_position := 1;

      UTL_FILE.fclose(l_file);
    EXCEPTION
      WHEN OTHERS THEN
        IF UTL_FILE.is_open(l_file)
        THEN
          UTL_FILE.fclose(l_file);
        END IF;
        RAISE;
    END;

  END LOOP;

END;
/

QUIT;
"

## Functions

# Help text displayed to user in case of invalid script calls or via -help option
printHelpText()
{
   echo ""
   echo "Usage: $0 -sqlclExec=<\"PATH/TO/SQLCL/BIN/sql\"> -apexAppId=<APEX_APP_ID> -dbHost=<DB_HOST> -dbPort=<DB_PORT> -dbServiceName=<DB_SERVICE_NAME> [-dbPrivUser=<DB_PRIV_USER> -dbPrivUserPassword=<DB_PRIV_USER_PASSWORD>] -dbExecUser=<DB_EXEC_USER> [-dbExecUserPassword=<DB_EXEC_USER_PASSWORD>]"
   echo ""
   echo "Available Options:"
   echo -e "\t${FONT_YELLOW}-sqlclExec${FONT_NOCOLOR}          - Path to executables of SQLcl"
   echo -e "\t${FONT_YELLOW}-apexAppId${FONT_NOCOLOR}          - APEX App Id"
   echo -e "\t${FONT_YELLOW}-dbHost${FONT_NOCOLOR}             - Hostname"
   echo -e "\t${FONT_YELLOW}-dbPort${FONT_NOCOLOR}             - Port"
   echo -e "\t${FONT_YELLOW}-dbServiceName${FONT_NOCOLOR}      - Service Name"
   echo -e "\t${FONT_YELLOW}-dbPrivUser${FONT_NOCOLOR}         - User with privileges for maintaining Directory Objects (optional; if not specified, user defined by -dbExecUser will be used)"
   echo -e "\t${FONT_YELLOW}-dbPrivUserPassword${FONT_NOCOLOR} - Password of privileged User (optional; password prompt will be displayed)"
   echo -e "\t${FONT_YELLOW}-dbExecUser${FONT_NOCOLOR}         - User"
   echo -e "\t${FONT_YELLOW}-dbExecUserPassword${FONT_NOCOLOR} - Password (optional; password prompt will be displayed)"
   echo -e "\t${FONT_YELLOW}-help${FONT_NOCOLOR}               - Prints this help text"
   echo ""
}

# Assigns given option args to vars
assignOptsToVars()
{
  while [ "$1" != "" ];
  do
    opt=`echo $1 | awk -F= '{print $1}'`
    optarg=`echo $1 | awk -F= '{print $2}'`
    case "$opt" in
      -help ) printHelpText
              exit ;;
      -sqlclExec ) SQLCL_EXEC="$optarg" ;;
      -apexAppId ) APP_ID="$optarg" ;;
      -dbHost ) DB_HOST="$optarg" ;;
      -dbPort ) DB_PORT="$optarg" ;;
      -dbServiceName ) DB_SERVICE="$optarg" ;;
      -dbPrivUser ) DB_PRIV_USERNAME="$optarg" ;;
      -dbPrivUserPassword ) DB_PRIV_PASSWORD="$optarg" ;;
      -dbExecUser ) DB_USERNAME="$optarg" ;;
      -dbExecUserPassword ) DB_PASSWORD="$optarg" ;;
      * ) echo "ERROR: Unknown option provided: \"$opt\"." 
          echo ""
          printHelpText
          exit 1 ;;
    esac
    shift
  done
}

# Verifies given option args
verifyOptions()
{
  if [ "$DB_PRIV_USERNAME" != "" ] && [ "$DB_PRIV_PASSWORD" == "" ]; then
    stty -echo
    read -p "Enter Password of privileged User ${DB_PRIV_USERNAME}: " DB_PRIV_PASSWORD
    stty echo
    echo ""
  fi
  if [ "$DB_USERNAME" != "" ] && [ "$DB_PASSWORD" == "" ]; then
    stty -echo
    read -p "Enter Password of User ${DB_USERNAME}: " DB_PASSWORD
    stty echo
    echo ""
  fi
  if [ "$DB_USERNAME" != "" ] && [ "$DB_PRIV_USERNAME" == "" ]; then
    DB_PRIV_USERNAME="$DB_USERNAME"
    DB_PRIV_PASSWORD="$DB_PASSWORD"
  fi

  if [ "$SQLCL_EXEC" == "" ] || [ "$APP_ID" == "" ] || [ "$DB_HOST" == "" ] || [ "$DB_PORT" == "" ] || [ "$DB_SERVICE" == "" ] || [ "$DB_USERNAME" == "" ] || [ "$DB_PASSWORD" == "" ]; then
    echo "ERROR: Please provide all necessary option arguments. Use -help to show a list of all options."
    echo ""
    exit 1
  fi
}

# Prints Stage to output
printStage()
{
  echo ""
  echo "#"
  echo "# $1"
  echo "#"
  echo ""
}

# Prints Stage Result to output
printStageResult()
{
  echo ""
  echo ".. $1"
  echo ""
}

# Prints Step to output
printStep()
{
  echo ".... $1"
}

# Prints Step Result to output
printStepResult()
{
  echo "........ $1"
}

# Cleans up dirs and Directory Objects in case of Errors or script termination
cleanup()
{
  local DROP_DRECTORY_OBJECTS_SCRIPT_MOD="${DROP_DRECTORY_OBJECTS_SCRIPT/_EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT_/$EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT}"
        DROP_DRECTORY_OBJECTS_SCRIPT_MOD="${DROP_DRECTORY_OBJECTS_SCRIPT_MOD/_EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT_/$EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT}"
  local CLEANUP_ALL="$1"
   
  # Clean up directories
  if [[ -d "${EXPORT_APEX_BASE_DIR_LOC}/f${APP_ID}" ]]; then rm -rf "${EXPORT_APEX_BASE_DIR_LOC}/f${APP_ID}"; fi

  # Drop Directory Objects
  "$SQLCL_EXEC" -s 2>&1 "${DB_PRIV_USERNAME}/${DB_PRIV_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" <<< "$DROP_DRECTORY_OBJECTS_SCRIPT_MOD"
   
   if [ "$CLEANUP_ALL" == "N" ]
   then
     if [[ -d "${EXPORT_APEX_SHARED_COMPONENTS_PATH}/files" ]]; then rm -rf "${EXPORT_APEX_SHARED_COMPONENTS_PATH}/files"; fi
   else
     if [[ -d "${EXPORT_APEX_BASE_DIR_LOC}/${EXPORT_APEX_DIR}" ]]; then rm -rf "${EXPORT_APEX_BASE_DIR_LOC}/${EXPORT_APEX_DIR}"; fi
   fi
}

# exits script with error or success
exitScript()
{
  local EXIT_WITH_ERROR="$1"
    
  if [ "$EXIT_WITH_ERROR" == "Y" ]
  then
     echo "Some scripts failed."
     exit 1
  else
     echo "Finished."
     exit 0
  fi
}

# Tests the database connection
testDatabaseConnection()
{
  echo "exit" | "$SQLCL_EXEC" -L "${DB_USERNAME}/${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" | grep -q "ORA-" > /dev/null

  if [ $? -eq 0 ]
  then
    echo "ERROR: The database connection test failed for user ${DB_USERNAME}. Please check the user credentials."
    exitScript "Y"
  fi

  if [ $DB_PRIV_USERNAME != $DB_USERNAME ]
  then
    echo "exit" | "$SQLCL_EXEC" -L "${DB_PRIV_USERNAME}/${DB_PRIV_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" | grep -q "ORA-" > /dev/null

    if [ $? -eq 0 ]
    then
      echo "ERROR: The database connection test failed for user ${DB_PRIV_USERNAME}. Please check the user credentials."
      exitScript "Y"
    fi
  fi
}

# Exports APEX application with application static files
exportApplicationWithEncodedFiles()
{
  printStage "SQLcl: Export APEX Application with Static Application Files (hex encoded)"

  "$SQLCL_EXEC" -s 2>&1 "${DB_USERNAME}/${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" <<< "apex export -applicationid ${APP_ID} -skipExportDate -expOriginalIds -split"

  if [ -d "f${APP_ID}" ]
  then
    printStageResult "Done."
  else
    printStageResult "ERROR: Failed to export APEX application."
    cleanup "Y"
    exitScript "Y"
  fi

  # Rename export app folder
  mv "f${APP_ID}" "$EXPORT_APEX_DIR"
}

# Create Directory Objects and paths
createDirectories()
{
  local CREATE_DRECTORY_OBJECTS_SCRIPT_MOD="${CREATE_DRECTORY_OBJECTS_SCRIPT/_APEX_APP_ID_/$APP_ID}"
        CREATE_DRECTORY_OBJECTS_SCRIPT_MOD="${CREATE_DRECTORY_OBJECTS_SCRIPT_MOD/_EXPORT_APEX_WORKSPACE_FILES_DIR_NAME_/$EXPORT_APEX_WORKSPACE_FILES_DIR_NAME}"
        CREATE_DRECTORY_OBJECTS_SCRIPT_MOD="${CREATE_DRECTORY_OBJECTS_SCRIPT_MOD/_EXPORT_APEX_APPLICATION_FILES_DIR_NAME_/$EXPORT_APEX_APPLICATION_FILES_DIR_NAME}"
        CREATE_DRECTORY_OBJECTS_SCRIPT_MOD="${CREATE_DRECTORY_OBJECTS_SCRIPT_MOD/_DB_RUNNING_USER_/$DB_USERNAME}"
        CREATE_DRECTORY_OBJECTS_SCRIPT_MOD="${CREATE_DRECTORY_OBJECTS_SCRIPT_MOD/_DB_PRIVILEGED_USER_/$DB_PRIV_USERNAME}"
        CREATE_DRECTORY_OBJECTS_SCRIPT_MOD="${CREATE_DRECTORY_OBJECTS_SCRIPT_MOD/_EXPORT_APEX_SHARED_COMPONENTS_PATH_/$EXPORT_APEX_SHARED_COMPONENTS_PATH}"
        CREATE_DRECTORY_OBJECTS_SCRIPT_MOD="${CREATE_DRECTORY_OBJECTS_SCRIPT_MOD/_EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT_/$EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT}"
        CREATE_DRECTORY_OBJECTS_SCRIPT_MOD="${CREATE_DRECTORY_OBJECTS_SCRIPT_MOD/_EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT_/$EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT}"
  local SCRIPT_OUTPUT
  local PATHS
  local SCRIPT_FAILED

  printStage "SQLcl: Create Directories"

  # Run custom PL/SQL script from above for creating dynamic directory objects
  SCRIPT_OUTPUT=$("$SQLCL_EXEC" -s "${DB_PRIV_USERNAME}/${DB_PRIV_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" <<< "$CREATE_DRECTORY_OBJECTS_SCRIPT_MOD")

  if [ $? -eq 0 ]
  then
    SCRIPT_OUTPUT=$(echo -n "$SCRIPT_OUTPUT" | tr -d '\n')
    # Check if directory paths have no spaces
    if [ "$SCRIPT_OUTPUT" == "${SCRIPT_OUTPUT%[[:space:]]*}" ]
    then
      # For each directory path returned from database, create directory path on OS file system
      IFS=":" read -ra PATHS <<< "$SCRIPT_OUTPUT"
      for i in "${PATHS[@]}"
      do
        printStep "Returned Path: ${i}"
        mkdir -p "$i"
        if [ $? -eq 0 ]
        then
          printStepResult "Path ${i} created."
        else
          printStepResult "ERROR: Path ${i} could not be created."
          SCRIPT_FAILED="Y"
          break
        fi
      done
    else 
      SCRIPT_FAILED="Y"
    fi
  else
    SCRIPT_FAILED="Y"
  fi

  if [ "$SCRIPT_FAILED" != "Y" ]
  then
    printStageResult "Done."
  else
    printStageResult "ERROR: Failed to create directories."
    cleanup "Y"
    exitScript "Y"
  fi
}

# Export binary raw files
exportBinaryRawFiles()
{
  local EXPORT_FILES_SCRIPT_MOD="${EXPORT_FILES_SCRIPT/_APEX_APP_ID_/$APP_ID}"
        EXPORT_FILES_SCRIPT_MOD="${EXPORT_FILES_SCRIPT_MOD/_EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT_/$EXPORT_APEX_WORKSPACE_FILES_DIR_OBJECT}"
        EXPORT_FILES_SCRIPT_MOD="${EXPORT_FILES_SCRIPT_MOD/_EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT_/$EXPORT_APEX_APPLICATION_FILES_DIR_OBJECT}"
  
  printStage "SQLcl: Export APEX Static Workspace and Application Files (binary raw)"

  # Run custom PL/SQL script from above for exporting app and workspace files
  "$SQLCL_EXEC" -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}" <<< "$EXPORT_FILES_SCRIPT_MOD"

  if [ $? -eq 0 ]
  then
    printStageResult "Done."
  else
    printStageResult "ERROR: Failed to export binary raw Files."
    cleanup "Y"
    exitScript "Y"
  fi
}

# Adjusts install.sql file of APEX app export
adjustInstallFile()
{
  printStage "Remove references to hex encoded files from install.sql script of APEX application export"

  # Remove calling of single app files inside install.sql
  find "${EXPORT_APEX_DIR}" -type f -name "install.sql" -exec sed -i -e '/^@@application\/shared_components\/files/d' {} +

  if [ $? -eq 0 ]
  then
    printStageResult "Done."
  else
    printStageResult "ERROR: Failed to remove file references."
    cleanup "Y"
    exitScript "Y"
  fi
}

# entry point
main()
{
  ## Set Options to Vars
  assignOptsToVars "$@"

  ## Verify given Options
  verifyOptions

  ## Test database connection
  testDatabaseConnection

  cd $EXPORT_APEX_BASE_DIR_LOC

  ## Clean up before
  cleanup "Y"

  ## Export APEX application with app files (encoded)
  exportApplicationWithEncodedFiles

  ## Create Directory Objects and paths
  createDirectories

  ## Export binary raw files
  exportBinaryRawFiles

  ## Adjust APEX install.sql script
  adjustInstallFile

  cleanup "N"
  exitScript "N"
}

main "$@"