Script that Generates Report from Oracle DB

------------------------------Script---------------------------------

#!/bin/sh

ORACLE_HOME=/opt/oracle
export ORACLE_HOME
ORACLE_BASE=/opt/oracle
export ORACLE_BASE
ORACLE_OWNER=oracle
export ORACLE_OWNER
ORACLE_SID=DB_NAME
export ORACLE_SID
NLS_LANG="american_america.we8iso8859p1"
export NLS_LANG

PATH=/usr/bin:/bin:/usr/local/bin:$ORACLE_HOME/bin
export PATH

outfile="/export/home/`date +"%Y%m%d"`.txt"

sqlplus -s username/password@DB_Name @$HOME/gen_report.sql > $outfile

----------------------------SQL File--------------------------------

select USER_ID as A, B, to_char(TIMESTAMP,'hh:mi:ssam') as Time From Table WHERE to_char(TIMESTAMP,'DD-Mon-YY') = (select to_char(sysdate, 'DD-Mon-YY') from dual) AND CONDITION_A = 'true' order by timestamp;

whenever sqlerror exit 99
whenever oserror exit 88
set pages 1000 echo off verify off feedback off

alter session set nls_date_format='YYMMDD HH24:MI:SS';
set line 200
col user_id format a15
col remarks format a50

select USER_ID as A, B, to_char(TIMESTAMP,'DD-Mon-YY hh:mi:ssam') as Time From username.table_name WHERE to_char(TIMESTAMP,'DD-Mon-YY') = (select to_char(sysdate-1, 'DD-Mon-YY') from dual) AND Condition_A = 'true' order by timestamp;

exit

Get Date of Yesterday

----------------------------Ksh Shell Script--------------------------------

#! /usr/bin/ksh
# Get yesterday's date in YYYY-MM-DD format.
# With argument N in range 1..28 gets date N days before.
# Tapani Tarvainen January 2002
# This code is in the public domain.

OFFSET=${1:-1}

case $OFFSET in
  *[!0-9]* | ???* | 3? | 29) print -u2 "Invalid input" ; exit 1;;
esac

eval `date "+day=%d; month=%m; year=%Y`
typeset -Z2 day month
typeset -Z4 year

# Subtract offset from day, if it goes below one use 'cal'
# to determine the number of days in the previous month.
day=$((day - OFFSET))
if (( day <= 0 )) ;then
  month=$((month - 1))
  if (( month == 0 )) ;then
    year=$((year - 1))
    month=12
  fi
  set -A days `cal $month $year`
  xday=${days[$(( ${#days[*]}-1 ))]}
  day=$((xday + day))
fi

print $year-$month-$day
print $month/$day/${year#??}


------------------------Bash Shell Script------------------------------

#!/bin/bash

OFFSET=1;

eval `date "+day=%d; month=%m; year=%Y"`

# Subtract offset from day, if it goes below one use 'cal'

# to determine the number of days in the previous month.

day=`expr $day - $OFFSET`

if [ $day -le 0 ] ;then

month=`expr $month - 1`

if [ $month -eq 0 ] ;then

year=`expr $year - 1`

month=12

fi

set `cal $month $year`

xday=${$#}

day=`expr $xday + $day`

fi

echo $year-$month-$day


------------------------- Linux--------------------------

curdate=`date +%y%m%d -d "yesterday"`

Write Script with arithmetic Operations

#!/bin/sh
echo "enter two numbers"
read a b
c=$(($a+$b))
echo "$a + $b = $c"
# or
let c="$a+$b"
# or
c=`expr $a + 1`

echo "$a + $b is $c"
 

Useful monitoring command of Unix/Linux Vol.2

Change ownership of a directory: chown -Rv username somedir
Change usergroup or a directory: chgrp -Rv usergroup somedir
To set environment variable: setenv
To load the control file: source <filename> (Shell csh only)
To enable sqlplus/Oracle at the first run:

setenv ORACLE_HOME /opt/app/oracle/product/10.2.0/Db_1
setenv ORACLE_SID ASMDB003
setenv ORACLE_TRACE T
setenv ORAENV_ASK NO
setenv NLS_LANG American_America.we8iso8859p1

setenv LD_LIBRARY_PATH /usr/lib:/usr/ucblib:/usr/local/lib:/opt/SUNWspro/lib:$OR
ACLE_HOME/lib:$ORACLE_HOME/precomp/lib:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/jdbc/
lib:$ORACLE_HOME/dbjava/lib:/user/sfw/lib

setenv PATH /bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/cc
s/bin:/opt/SUNWspro/bin:${ORACLE_HOME}/bin
setenv PATH ${PATH}:${HOME}/bin

Difference on Changing Java SDK version between Solaris and Linux

Change Java SDK version

1. go to bin folder
/bin/ -> ls -l
2. change the symbolic link to point to the new versionln -s /usr/jdk/jdk1.6.0_20/bin/ControlPanel ControlPanel
ln -s /usr/jdk/jdk1.6.0_20/bin/HtmlConverter HtmlConverter
ln -s /usr/jdk/jdk1.6.0_20/bin/appletviewer appletviewer
ln -s /usr/jdk/jdk1.6.0_20/bin/apt apt
ln -s /usr/jdk/jdk1.6.0_20/bin/extcheck extcheck
ln -s /usr/jdk/jdk1.6.0_20/bin/idlj idlj
ln -s /usr/jdk/jdk1.6.0_20/bin/jar jar
ln -s /usr/jdk/jdk1.6.0_20/bin/jarsigner jarsigner
ln -s /usr/jdk/jdk1.6.0_20/bin/java java
ln -s /usr/jdk/jdk1.6.0_20/bin/javac javac
ln -s /usr/jdk/jdk1.6.0_20/bin/javadoc javadoc
ln -s /usr/jdk/jdk1.6.0_20/bin/javah javah
ln -s /usr/jdk/jdk1.6.0_20/bin/javap javap
ln -s /usr/jdk/jdk1.6.0_20/bin/javaws javaws
ln -s /usr/jdk/jdk1.6.0_20/bin/jdb jdb
ln -s /usr/jdk/jdk1.6.0_20/bin/keytool keytool
ln -s /usr/jdk/jdk1.6.0_20/bin/native2ascii native2ascii
ln -s /usr/jdk/jdk1.6.0_20/bin/orbd orbd
ln -s /usr/jdk/jdk1.6.0_20/bin/policytool policytool
ln -s /usr/jdk/jdk1.6.0_20/bin/rmic rmic
ln -s /usr/jdk/jdk1.6.0_20/bin/rmid rmid
ln -s /usr/jdk/jdk1.6.0_20/bin/rmiregistry rmiregistry
ln -s /usr/jdk/jdk1.6.0_20/bin/serialver serialver
ln -s /usr/jdk/jdk1.6.0_20/bin/servertool servertool
ln -s /usr/jdk/jdk1.6.0_20/bin/tnameserv tnameserv

For Linux used only:
Change Java SDK version

1. backup old java file
2. change the symbolic link to point to new version

e.g. 

mv java java.bk
ln -s /usr/local/jdk1.6.0_20/bin/java java
mv javac javac.bk
ln -s /usr/local/jdk1.6.0_20/bin/javac javac
mv javadoc javadoc.bk
ln -s /usr/local/jdk1.6.0_20/bin/javadoc javadoc
mv javah javah.bk
ln -s /usr/local/jdk1.6.0_20/bin/javah javah

Useful monitoring command of Unix/Linux Vol.1

For Solaris Only:

check number of core: prtdiag -v 
create new user: useradd jsmith
get user information: finger jsmith
set password: passwd jsmith
set default home directory: usermod -d /path/to/new/homedir/ username
set default Shell: change permenently the SHELL at etc/passwd
check Solaris release: cat /etc/release
Check current shell: echo $SHELL
Check shell version: /bin/bash --version

Frustrated Pointer

// my first pointer
#include <iostream>
using namespace std;

int main ()
{
  int firstvalue, secondvalue;
  int * mypointer;

  mypointer = &firstvalue;
  *mypointer = 10;
  mypointer = &secondvalue;
  *mypointer = 20;
  cout << "firstvalue is " << firstvalue << endl;
  cout << "secondvalue is " << secondvalue << endl;
  return 0;
}

   

firstvalue is 10
secondvalue is 20

Stored ProC: Insert Statement

EXEC SQL AT DB_NAME Insert into EMSD_SCHE(JOB_ID, SUBMIT_DATE, MANUAL_SUB, STAFF, ARRIVAL, STATUS)
        VALUES(:in_id, systimestamp, :in_sub, :in_staff, :in_man, :in_stat);
    EXEC SQL AT DB_NAME COMMIT;

Remember to Commit!

Stored ProC: Select Statement

//Select statement: 
varchar2 in_id[20];

int Poster::sendWLReq(char *arg_access_code, char *out_msg)
{
        char tmp_id[160];
      
        strncpy( tmp_id, arg_access_code, sizeof(tmp_id) - 1);
        tmp_id[sizeof(tmp_id) - 1] = '\0';

        EXEC SQL WHENEVER NOTFOUND goto notFound;

        memset((char*)in_id.arr, NULL, 15);
        strcpy((char*)in_id.arr, tmp_id);
        in_id.len=strlen(tmp_id);
        in_id.arr[in_id.len]='\0';

        cout << "arg_brand->" << (char*)in_id.arr << "<-arg_brand" << endl;

        EXEC SQL AT DB_NAME
                select STAFF into :out_msg_cont
                from STAFF_LIST
                where MSISDN = :in_id;

        cout << "sql error code " << sqlca.sqlcode << "   " << sqlca.sqlerrm.sqlerrmc << endl;

        strcpy(out_msg, (char*)out_msg_cont.arr);
      
        return 0;

        notFound:
        EXEC SQL WHENEVER NOTFOUND  CONTINUE;

        return 1;
}

This C function can be extended to any other oracle statement.

Spool from an Oracle DB as .CSV file

set echo off
set feedback off
set pagesize 0
Spool backup.csv;
Select cellno||','||calldate||','||answertime from temp;
Spool off;

C Style of Substring

#include <string.h> /* for strncpy() */
#include <stdio.h> /* for printf() */

int
main(void)
{
char msg[] = "Hello World!";
char submsg[10]; /* Must be long enough */

/* Copy the substring "o W" from msg to submsg */
strncpy(submsg, &msg[4], 3);

/* Terminate the resulting string since strncpy() doesn't */
submsg[3] = '\0';

printf("msg[] = '%s'\nsubmsg[] = '%s'\n", msg, submsg);

return 0;
}