Part 2: Import binary raw data files during APEX App Import

#!/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