Monthly Archives: August 2016


NotePad++: Replace all content after delimiter on each line

We have a configuration file for Skype, that on each line it describes the translation for each element of Skype.

The format on each line is as VARIABLE=VALUE.

We decided to remove all values automatically, to do that we used NotePad++.

Skype - Language File - Dialog

We pressed Ctrl+H to open up the Replace dialog. In the ‘Find what’ field we wrote =[^\n]*$ and in the ‘Replace with’ we wrote =. In the ‘Search Mode’ option we selected ‘Regular expression’.

The tool looked as follows:
Skype - Language File - Before

Hitting the ‘Replace All’ button, removed all values from each line as it can be viewed below:
Skype - Language File - After
=[^\n]*$ instructs NotePad++ to match everything including the = symbol until the end of the line. [^\n]* means to match everything except the new line character. $ matches the end of the line.


Bash: Check if file is found and contains data

The following code will check if the file described by variable FILENAME exists and has data.

if [ ! -f "$FILENAME" ]; then

    echo "Error: '$FILENAME' file not found.";
else

    if [ ! -s "$FILENAME" ]; then

        echo "Error: '$FILENAME' file is empty.";
    else

        echo "Info: '$FILENAME' file found and contains data.";
    fi
fi

Convert a list of integers from MySQL to a Bash array

The following code will connect to a MySQL server, it will get a list of integers and convert the results to a bash array that will be iterated using a for loop and they will be printed using zero padding.


IDS_RAW=$(mysql --user="myuser" --password="mypass" --host="db.example.com" --database="mydb" --batch --compress --skip-column-names --execute="
  SELECT
    Id
  FROM
    Users
  WHERE
    Status = 0;
");

OLDIFS=$IFS;
IFS=$'\n' command eval;
'IDS=($IDS_RAW)';
IFS=$OLDIFS;

echo "Will process the following user IDs '${IDS[@]}'";

for ID in "${IDS[@]}"; do
  LEADING_ZERO=$(printf %08d $ID);
  echo "ID $LEADING_ZERO";
done;

Bash: Get date N days ago in the format YYYY_MM_DD

DEFAULT_DAYS_BACK="0";
DAYS_BACK=${1:-$DEFAULT_DAYS_BACK};
PROCESSING_DATE=`date --date="$DAYS_BACK day ago" +%Y_%m_%d`;

The above code will populate variable PROCESSING_DATE with the date of the day the was DAYS_BACK ago using the format YYYY_MM_DD.

The value for DAYS_BACK is filled by the first argument of the script/function $1. In case the user did not provide an argument, we have the variable DEFAULT_DAYS_BACK that will be used as the default value.


Bash get script file name and location

The following code will populate the variables SCRIPT_NAME and SCRIPT_DIR with the name of the script currently being execute and the location this script is in:

SCRIPT_NAME=$(basename $(test -L "$0" && readlink "$0" || echo "$0"));
SCRIPT_DIR=$(cd $(dirname "$0") && pwd);

Notes for SCRIPT_NAME:

  • $0 expands to the name of the shell or shell script
  • test -L "$0" checks that input is a file that exists and is a symbolic link
  • && readlink "$0" will be executed if the above statement is true and it will print the resolved symbolic link
  • || echo "$0" will be executed if the test for symbolic link fails
  • finally, basename will strip directory and suffix from whatever is returned from the above statements

Notes for SCRIPT_DIR:

  • Will not resolve the correct folder if the last component of the path is a symbolic link (symlink). It will return the location of the symlink instead of the location of the file the symlink is pointing to
  • cd will return 0 if it successfully navigates to a directory or 1 when it fails to navigate to the directory
  • cd "$( dirname "$0" )" will use dirname to strip the last component from the expanded name and try to navigate to that location
  • if the above cd fails, we get the current location using && pwd. pwd will print name of current/working directory

In case you have a problem with $0, it is overwritten or the above function is called by a child script in another folder you can replace $0 with ${BASH_SOURCE[0]}.

SCRIPT_NAME=$(basename $(test -L "${BASH_SOURCE[0]}" && readlink "${BASH_SOURCE[0]}" || echo "${BASH_SOURCE[0]}"));
SCRIPT_DIR=$(cd $(dirname "${BASH_SOURCE[0]}") && pwd);

Installing Oracle SQL*Loader on CentOS without full server installation

Assuming you do not want to install the full Oracle suite on the current machine, the following guide will show you how we installed Oracle SQL*Loader on CentOS release 6.7 (Final) using specific packages only. For this tutorial to be successful though, you need to have somewhere the full installation somewhere else as we need to to copy two files from there (the sqlldr executable and the binary file ulus.msb).

On the machine that has the full Oracle suite installation

Execute locate sqlldr to find the location of sqlldr binary file. If it does not return a result use the following:

find / -name "sqlldr" 2>/dev/null

The above command will search for sqlldr in the whole filesystem and it will redirect all errors to /dev/null so that you will not get spammed by access right issues etc.

After you found the file, copy it to the machine you want to use it from, the exact location will be given below.

Then, you need to find ulus.msb using the following command

find / -name "ulus.msb" 2>/dev/null

Copy that file to the machine you want to use sqlldr from as well, the exact location will be given below.

On the machine you want to execute sqlldr from and does not have the full Oracle suite installed

After we get the above files, we need to download the appropriate files from http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html.

To access the files you need to click the Accept License Agreement radio button which means you agree to the ‘OTN Development and Distribution License Agreement for Instant Client‘ and finally you need to be logged in.

To login visit this page https://login.oracle.com/mysso/signon.jsp. If you are not registered yet, you need to create an account with a valid email from here https://profile.oracle.com/myprofile/account/create-account.jspx.

Once you are done with these steps, you need to download two rpm files for the client:

  • oracle-instantclientRELEASE-basic-VERSION.x86_64.rpm
  • oracle-instantclientRELEASE-sqlplus-VERSION.x86_64.rpm
  • oracle-instantclientRELEASE-jdbc-VERSION.x86_64.rpm
  • oracle-instantclientRELEASE-devel-VERSION.x86_64.rpm

The latest version at the time this document was written was version 12.1.0.2.0 and so we downloaded the following files:

Oracle-SQLLOADER

The files were then moved to the server that was to be installed and using an account with administration rights, they were installed using the the rpm -ivh  command:

rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
rpm -ivh oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
rpm -ivh oracle-instantclient12.1-jdbc-12.1.0.2.0-1.x86_64.rpm
rpm -ivh oracle-instantclient12.1-devel-12.1.0.1.0-1.x86_64.rpm

In case you placed the two rpm files in a folder by themselves you can install both at the same time using the following command which is version independent:

rpm -ivh oracle-instantclient*

The packages will be installed in /usr/lib/oracle/RELEASE. Use ls /usr/lib/oracle/ to find the RELEASE number of your installation (in case you are not sure) and copy the path to client64 folder. In our case it was /usr/lib/oracle/12.1/client64. We will use this path as the ORACLE_HOME variable later on.

Following, you need to setup the environment variables for bash.
If you want to set the environment variables in your account only, add the following in your ~/.bash_profile:

ORACLE_HOME=/usr/lib/oracle/12.1/client64
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_HOME
export LD_LIBRARY_PATH
export PATH

And execute source ~/.bash_profile  to reload your .bash_profile (alternatively, logout and login again).

If you want to set the environment variables for all users, create a .sh file in /etc/profile.d/ (e.g. oracle.sh) and add the content above in that file.

Installing SQL*Loader

Copy the sqlldr executable we got from the other machine to $ORACLE_HOME/bin/ (/usr/lib/oracle/RELEASE/client64/bin/). In our case it was /usr/lib/oracle/12.1/client64/bin/.

Then, copy the ulus.msb binary file to $ORACLE_HOME/rdbms/mesg/ (/usr/lib/oracle/RELEASE/client64/rdbms/mesg/). In our case it was /usr/lib/oracle/12.1/client64/rdbms/mesg/. If the folder does not exist, create it.

Finally, create a text file named tnsnames.ora in $ORACLE_HOME/network/admin/ (/usr/lib/oracle/RELEASE/client64/network/admin/). In our case it was /usr/lib/oracle/12.1/client64/network/admin/. If the folder does not exist, create it.

In tnsnames.ora we need to add the connection information for our databases and name them. The reason we need to do this, is because sqlldr does not accept the connection string from the command line like sqlplus as seen at the end of this tutorial here http://bytefreaks.net/applications/oracle/installing-oracle-sqlplus-on-centos. The structure of the connection string though, is exactly the same as the one used in sqlplus. Following the example from the other guide and the naming convention for the tnsnames.ora file, the format of the string would be:

connectionname=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HOSTNAME_OR_IP)(PORT=1521))(CONNECT_DATA=(SID=MY_SID)))

SQL*Loader will be available for use using the sqlldr command.

The following example will connect to server with

  • IP 192.168.10.1,
  • on the SID ASID
  • using the user myuser
  • the password pass
  • and we will use the control file profitLoader.ctl  to configure what the program will do once it starts (profitLoader.ctl is a file we wrote that describes the structure of the input file and the table to which the data should be added, code below for reference)

In tnsnames.ora file will add the following line:

myconnection=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.1)(PORT=1521))(CONNECT_DATA=(SID=ASID)))"

and we will use the connection information as follows:

sqlldr "username[alias][email protected]" control=profitLoader.ctl;

In our example we do not have an alias, so we will remove the [] part and use the rest. the command will be formed as follows:

sqlldr "[email protected]" control=profitLoader.ctl;

The content of profitLoader.ctl is as follows:

LOAD DATA
INFILE '/dev/shm/profitLoader.dat'
APPEND INTO TABLE PROFIT
FIELDS TERMINATED BY ','
(USERID, TRANSACTIONID, PROFIT)

In our scenario, we have a csv file that contains 3 columns and we want to load all the data to a table called PROFIT.


Installing Oracle SQL*Plus on CentOS 1

The following guide will show you how we installed Oracle SQL*Plus on CentOS release 6.7 (Final).

First of all we need to download the appropriate files from http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html.

To access the files you need to click the Accept License Agreement radio button which means you agree to the ‘OTN Development and Distribution License Agreement for Instant Client‘ and finally you need to be logged in.

To login visit this page https://login.oracle.com/mysso/signon.jsp. If you are not registered yet, you need to create an account with a valid email from here https://profile.oracle.com/myprofile/account/create-account.jspx.

Once you are done with these steps, you need to download two rpm files for the client:

  • oracle-instantclientRELEASE-basic-VERSION.x86_64.rpm
  • oracle-instantclientRELEASE-sqlplus-VERSION.x86_64.rpm

The latest version at the time this document was written was version 12.1.0.2.0 and so we downloaded the following files:

Oracle-SQLPLUS Download page

The files were then moved to the server that was to be installed and using an account with administration rights, they were installed using the the rpm -ivh  command:

rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
rpm -ivh oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm

In case you placed the two rpm files in a folder by themselves you can install both at the same time using the following command which is version independent:

rpm -ivh oracle-instantclient*.rpm

The packages will be installed in /usr/lib/oracle/RELEASE. Use ls /usr/lib/oracle/ to find the RELEASE number of your installation (in case you are not sure) and copy the path to client64 folder. In our case it was /usr/lib/oracle/12.1/client64. We will use this path as the ORACLE_HOME variable later on.

Following, you need to setup the environment variables for bash.
If you want to set the environment variables in your account only, add the following in your ~/.bash_profile:

ORACLE_HOME=/usr/lib/oracle/12.1/client64
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_HOME
export LD_LIBRARY_PATH
export PATH

And execute source ~/.bash_profile  to reload your .bash_profile (alternatively, logout and login again).

If you want to set the environment variables for all users, create a .sh file in /etc/profile.d/ (e.g. oracle.sh) and add the content above in that file.

SQL*Plus will be available for use using the sqlplus command.

sqlplus "username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HOSTNAME_OR_IP)(PORT=1521))(CONNECT_DATA=(SID=MY_SID)))"

The following example will connect to server with

  • IP 192.168.10.1,
  • on the SID ASID
  • using the user myuser
  • and the password pass
sqlplus "myuser/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.1)(PORT=1521))(CONNECT_DATA=(SID=ASID)))"