Oracle Forms and Reports 11g init startup script

After pulling my hair out searching the web and trying to cobble together shell commands to start and stop Oracle Forms 11g, I finally started playing with nodemanager to script the startup and shutdown. Nodemanager can start and stop all components, except the opmn stuff, and it blocks execution until the service is started. This way I don’t have to try to time stuff correctly using sleep commands and it just works a lot better.

Anyway, put this in a file /etc/init.d/forms and change the environment variables to match your environment.
Then run the following commands to install the script:

chmod 755 /etc/init.d/forms
chkconfig --add /etc/init.d/forms
#!/bin/bash
#
# oracle Init file for starting and stopping Oracle Forms and Reports 11g
#
# chkconfig: 35 95 30
# description: Oracle Forms and Reports 11g startup script

# Source function library
. /etc/rc.d/init.d/functions

# Set Environment
WLS_USER=weblogic
WLS_PW=SUPERSECRET
WLS_DOMAIN=MYDOMAIN
ORACLE_OWNER="oracle"
MIDDLEWARE_HOME="/opt/local/oracle/Middleware"
DOMAIN_HOME="${MIDDLEWARE_HOME}/user_projects/domains/${WLS_DOMAIN}"
WLS_HOME="${MIDDLEWARE_HOME}/wlserver_10.3"
OPMN_CMD="${MIDDLEWARE_HOME}/asinst_1/bin/opmnctl"

# Define and ensure boot.peroperties.
BOOT_PROPERTIES="username=${WLS_USER}\npassword=${WLS_PW}"
mkdir -p ${DOMAIN_HOME}/servers/AdminServer/data/nodemanager
mkdir -p ${DOMAIN_HOME}/servers/WLS_FORMS/data/nodemanager
mkdir -p ${DOMAIN_HOME}/servers/WLS_REPORTS/data/nodemanager
echo -e ${BOOT_PROPERTIES} > ${DOMAIN_HOME}/servers/AdminServer/data/nodemanager/boot.properties
echo -e ${BOOT_PROPERTIES} > ${DOMAIN_HOME}/servers/WLS_FORMS/data/nodemanager/boot.properties
echo -e ${BOOT_PROPERTIES} > ${DOMAIN_HOME}/servers/WLS_REPORTS/data/nodemanager/boot.properties
chown -R oracle.oinstall ${DOMAIN_HOME}/servers/AdminServer/data/nodemanager
chown -R oracle.oinstall ${DOMAIN_HOME}/servers/WLS_FORMS/data/nodemanager
chown -R oracle.oinstall ${DOMAIN_HOME}/servers/WLS_REPORTS/data/nodemanager

# Define NodeManager startup script
START_CMD=$(cat <<EOF
startNodeManager(verbose='true', NodeManagerHome='${WLS_HOME}/common/nodemanager')
nmConnect('${WLS_USER}','${WLS_PW}','localhost','5556','${WLS_DOMAIN}')
nmStart('AdminServer')
arg=\"Arguments=\\\\\" -Dweblogic.management.server=localhost:7001\\\\\"\"
prps=makePropertiesObject(arg)
nmStart('WLS_FORMS',props=prps)
nmStart('WLS_REPORTS',props=prps)
exit()
EOF
)

# Define NodeManager shutdown script
STOP_CMD=$(cat <<EOF
nmConnect('${WLS_USER}','${WLS_PW}','localhost','5556','${WLS_DOMAIN}')
nmKill('WLS_REPORTS')
nmKill('WLS_FORMS')
nmKill('AdminServer')
stopNodeManager()
exit()
EOF
)

start() {
  echo "Starting WLS:"
  su - $ORACLE_OWNER -c "echo \"${START_CMD}\" | ${WLS_HOME}/common/bin/wlst.sh | grep --line-buffered -i 'Error\|Success' | grep -v INFO"
  su - $ORACLE_OWNER -c "${OPMN_CMD} startall"
  echo "OK"
}

stop() {
  echo "Stopping WLS:"
  su - $ORACLE_OWNER -c "${OPMN_CMD} stopall"
  su - $ORACLE_OWNER -c "echo \"${STOP_CMD}\" | ${WLS_HOME}/common/bin/wlst.sh | grep --line-buffered -i 'Error\|Success' | grep -v INFO"
  echo "OK"
}

restart() {
  stop
  start
}


case "$1" in
  start)
    start
  ;;
  stop)
    stop
  ;;
  restart)
    restart
  ;;
  *)
    echo $"Usage: $0 {start|stop|restart}"
esac

Drop empty columns in MySQL

Ever have a table with a ton of columns some of which aren’t used?

Below is a procedure to drop empty columns from a MySQL table:

CREATE PROCEDURE dropEmptyColumns(dbName varchar(64), tableName varchar(64), drop_log text)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE col_name varchar(256);

  -- Get Column Names
  DECLARE column_cur CURSOR FOR
      SELECT COLUMN_NAME
      FROM `information_schema`.`COLUMNS`
        WHERE (`TABLE_SCHEMA` = dbName)
          AND (`TABLE_NAME` = tableName);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET @amount = -1;
  SET @tableName = tableName;
  SET @dbname = dbName;
  SET @drop_log = '';
  SET @testSql = '';

  -- For each column name
  OPEN column_cur;
  check_column_loop: LOOP

    -- SELECT 'IN LOOP' INTO @drop_log;
    FETCH column_cur INTO col_name;

    -- Break loop if no more rows
    IF done THEN
      LEAVE check_column_loop;
    END IF;
    
    -- Build the query, with dynamic table and column. Store the result in @amount
    SELECT CONCAT('SELECT COUNT(', col_name ,') INTO @amount FROM ', @tableName ,' WHERE(', col_name ,' IS NOT NULL OR ', col_name ,' != "");') INTO @testSql;
    PREPARE testSqlStmt FROM @testSql;

    -- Execute
    EXECUTE testSqlStmt;

    -- Check if we have 0 rows, else we still have data and we do not want to drop.
    IF @amount = 0 THEN
        
      -- Build the query
      SELECT CONCAT('ALTER TABLE ', @tableName ,' DROP COLUMN ', col_name ,';') INTO @alterSql;

      -- Execute
      PREPARE alterSqlStmt FROM @alterSql;
      EXECUTE alterSqlStmt;
                
      -- Append Log
      SELECT CONCAT(@drop_log, col_name, ' DROPPED\r\n') INTO @drop_log;

    END IF;

  END LOOP;
  CLOSE column_cur;

END

Once you have created the procedure you can run it like this:

CALL dropEmptyColumns('<DB_NAME>', '<TABLE_NAME>', @drop_log);
SELECT @drop_log;