Oracle cheat sheet v.1.1.2

-- File:OracleCheatSheet.sql v.1.1.2 docs at the end

-- DEFINE A NICE DATETIME FORMAT

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY.MM.DD HH24:MI:SS';

-- FIND FIRST THE NAME OF THE TABLE 

SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE '%RUN%'

; 

-- GENERATE A COLUMN SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_WFLOW_VAR_RUN'

;

-- GENERATE A TABLE_NAME.COLUMN_NAME SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || TABLE_NAME || '.' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_WFLOW_VAR_RUN'

;

-- GET THE COLUMN LIST FROM A TABLE 

SELECT ',' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_WFLOW_RUN' ; 

-- convert ids to human readable values 

select 

-- /*

, T2.WORKFLOW_NAME 

, T2.START_TIME

, T2.END_TIME

, decode (RUN_STATUS_CODE,

1 , 'Succeeded',

2,  'Disabled',

3,  'Failed',

4,  'Stopped',

5,  'Aborted',

6,  'Running',

15, 'Terminated')  Status

from OPB_WFLOW_RUN T2

WHERE 1=1

AND START_TIME >= to_date('2012-04-03 00:00:00','YYYY-MM-DD HH24:MI:SS')

AND END_TIME   <=   to_date('2012-04-04 00:00:00','YYYY-MM-DD HH24:MI:SS')

ORDER BY START_TIME DESC

; 

SELECT TABLE_NAME as TableName , COLUMN_NAME as ColumnName , FROM all_tab_columns ; 

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 

       -- referenced pk

       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk

  FROM all_cons_columns a

  JOIN all_constraints c ON a.owner = c.owner

                        AND a.constraint_name = c.constraint_name

  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner

                           AND c.r_constraint_name = c_pk.constraint_name

 WHERE c.constraint_type = 'R'

   -- AND a.table_name = 'OPB_CNX'

   -- and c_pk.table_name = 'OPB_CNX'

   AND c_pk.constraint_name LIKE '%%'

   AND OWNER='INFAPROD'

   ; 

-- FIND A COLUMN_NAME 

SELECT OWNER ||'.' ||  TABLE_NAME ||'.' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE 1=1 

-- AND COLUMN_NAME LIKE '%OBJECT%' 

AND OWNER = 'INFAPROD'

-- AND TABLE_NAME LIKE 'REP_SESS_WIDGET_CNXS'

-- AND COLUMN_NAME LIKE '%OBJECT%'

ORDER BY OWNER , TABLE_NAME

;

-- get the short SID name 

select name from v$database;

-- get the global TNS Service name 

select * from global_name;

-- LIST THE INSTANCE NAME AND HOSTNAME 

select instance_number, instance_name, host_name from v$instance;

-- how-to copy table from another table from one schema to another 

create table SOURCE_SHEMA.TARGET_TABLE unrecoverable as SELECT * from TARGET_SCHEMA.SOURCE_TABLE

;

-- check the priveledges of your current session

select * from session_privs

; 

/* Purpose: 

To provide a cheat sheet for oracle sql 

VersionHistory: 

1.1.2 ---  2012.04.26 - 15:27:14  --- Added current sessions privs query 

1.1.1 --- ysg --- Added how-to copy table 

1.1.0 --- ysg --- added generate col list 

1.0.0 --- ysg --- initial creation 

*/

Linux Cheat Sheet v.1.7.0

# File:LinuxCheatSheet.sh v.1.7.0 docs at the end 

# get a nice prompt 

export PS1="\u@\h \t \w\n\\$ "

# who , where and when

uname -a ;

id;

date +%Y.%m.%d-%H:%M:%S;

# start putty with preloaded session

cmd /c start /max putty -load username@hostname

# aliases

# show dirs with nice time newest modified on top 

alias ll='ls -alt --time-style=long-iso'

# find in files 

find / -name '*bash*' -exec grep -nHP '*APerlRegex*' {} \;

# The ultimate "find in files"

find /etc/httpd/ -type f -print0 | xargs --null grep -nHP 'StartServers\s+\d' | less

#  or even faster , be aware of funny file names xargs -0

find / -name '*bak' -print0 | xargs --null grep -nPH 'curl'

# find and replace recursively

find . -name '*.html' -print0 | xargs -0 perl -pi -e 's/foo/bar/g'

# find all the files greather than 100 MB , sort them by the size and print their sizes 

find / -type f -size +100M -exec du -B M {} \; | sort -r | less 

# disk usage of users under the /home directory in MB

clear;du -all -m --max-depth=3 /home | sort -n -r | less

# where I have been lately ?

history | grep cd

# what I have opening recently 

history | grep vi

#send my history 

history | mailx -s "my history" $MyEmail

tcpdump dst 10.168.28.22 and tcp port 22

tcpdump dst 172.21.212.212 

# record the current session via script

mkdir ~/scriptlogs

script -a ~/scriptlogs/`date +%Y%m%d%H%M%S`_script.log

#/usr/bin is for normal user executables, /usr/sbin is for superuser executables, /usr/sfw is for external software (like gnu one), but provided with bundle of OS, /usr/ccs is for development utilities, usually not need for daily tasks like make, lex, yacc, sccs

# take the last 5 commands for faster execution to the temp execution script

tail -n 5 /root/.bash_history >> /var/run.sh

# I saw the command cd /to/some/suching/dir/which/was/very/long/to/type

echo so I redid it and saved my fingers

!345

history | mail -s "test file sending" -c mail1@com yordan.georgiev(boom)gmail.com

# remove all trealing spaces from the history file works for TextPad

^([ ]*)([0-9]*)(.*)$

\3 --- replace with the pure commands

history | gawk -F1 'BEGIN {FS=" "};{print $2 , $3}' | less

# display the history oldest commands first withouth the line numbers ...

history | sort -nr | perl -i -pe 's/^([ ]*)([0-9]*)(.*)$/$3/gi'

# how to deal with command outputs

command | filtercommand > command_output.txt 2>errors_from_command.txt

#  find the files having os somewhere in their names and only those having linux

find . -name '*os*' | grep linux | less

# find all xml type of files and display only the rows having wordToFindInRow

find . -name '*.xml' -exec cat {} \;| grep wordToFindInRow | less

# putty , bash shortcuts 

Ctrl + A --- Go to the beginning of the line you are currently typing on

Ctrl + E --- Go to the end of the line you are currently typing on

# how-to mount an usb stick

# remember to change the path other wise you will get the device is busy errror

mkdir /mnt/usbflash

mount /dev/sdb1 -t vfat /mnt/usbflash

umount /mnt/usbflash

#display the first 20 lines of the file

head -n 20 tooLongFile 

# how to restart a process initiated at startup

/etc/rc.d/init.d/sendmail start | stop | status | restart

# see all the rules associated with the firewall

iptables -L -v

gunzip *file.zip

# start winscp with a stored session from Win box 

cmd /c start /max winscp oracle@192.168.255.12

# To start remote session click on the putty screen , configure putty

settings to pull full screen with alt + Enter

# right click on the title bar , settings , change the font , copy

paste from and to the terminal window text

# how to ensure the sshd daemon is running

ps -ef | grep sshd

# User and group management

useradd -p winscppass -r winscp -d /home/winscp --- to add a user with

specifig home directory name and pass

luserdel winscp --- delete the user

gpasswd: administer the /etc/group file

groupadd: Create a new group

groupmod: Modify a group

groupdel: Delete a new group

#useradd: Create a new user or update default new user information

#usermod: Modify a user account

#userdel: Delete a user account and related files

# how to kill process interactively

killall -v -i sshd

# the most efficent way to search your history is to hit Ctrl R and

#type the start of the command. It will autocomplete as soon as there’s

#a match to a history entry, then you just hit enter. If you want to

#complete the command (add to it ) use the right arrow to

#escape from the quick search box ...

#How to install Perl modules

gzip -dc yourmodule.tar.gz | tar -xof -

# create the make file

perl Makefile.PL

# test

make test

# install 

make install

#How to see better which file were opened , which directories were visited

#type always the fullpath after the vi - use the $PWD env variable to

#open files in the current directory , thus after opening the file

#after:

#vi /$PWD , press tab to complete the name of the current directory ,

#type the name of the file. THUS AFTER RUNNING

history | grep vi

the full list of opened files is viwed .

#of course the same could be seen from the /home/username/.viinfo file /files

#where to set the colors for the terminal (if you are lucky to have one

with colors ; )

/etc/DIR_COLORS

open a file containing "sh" in its name bellow the "/usr/lib" directory

:r !find /usr/lib -name *sh*

go over the file and gf

uname -a --- which version of Linux I am using

rmp -dev --- show all installed packages

# change the password for the specified user (own password)

passwd [username] 

#How to copy paste text in the putty window from client to server -

#click the right button of your pointing device

#How to copy paste text from the putty window from server to client -

#right-click the window title and select copy all to Clipboard.

#To restart a service

service sshd restart

#  

service --status-all --- show the status of all services

# change the owneership of the directory

chown -R userName:GroupName DirPath

# perform action recursively on a set of files

find . -name '*.pl' -exec perl -wc {} \;

for file in `find . -type f`;do echo cp $file ./backups/; done;

for file in `ls *.docx -1`;do echo cp $file ./backups/$file.`date +%Y%m%d%H%M%S`.docx;done;

# make Bash append rather than overwrite the history on disk:

shopt -s histappend

# henever displaying the prompt, write the previous line to disk:

PROMPT_COMMMOR='history -a'

gpm -- general "cut and paste" server

sh ScriptWithALotOfErrorMessages.sh | tee -a whereToSaveIt.log

# run first the following command

script -a The_Log_File_To_Append_as_well_as_display_diagnostig_messages.log

# than run the script

sh ScriptWithALotOfErrorMessages.sh

#how-to replace single char in file

tr '\t' ',' < FileWithTabs > fileWithCommas

df -k --- disk usage

# Allow access to the box from only one ip address

IPTables=/sbin/iptables

$ IPTables -A INPUT -s <Insert other Origin ip here> -p tcp

--source-port xxx -j ACCEPT

$ IPTables -A OUTPUT -d < Insert other Origin ip here > -p tcp

--destination-port xxx-j ACCEPT

# has the root logged in over an unencrypted network ?

# next_line_is_templatized

#  last | grep “^root “ | egrep –v “reboot|console” | morphus

# start command in the background

command1 &

# start another one

command2 &

# bring the command in the foreground

fg %1

# create a backup file based on the timestamp on bash

cp fileName.ext fileName.ext.`date +%Y%m%d%H%M%S`.bak

Unix commands:

FIX broken lines:

$ cat output.txt

a:b:c:1:2:3:2.3:henry

s:d:e:2:1:4:

54:user5

d:q:w:5:6:

3:5.2:alex

y:m:n:3:4:1:5.6:eiam

Output Required:

----------------

a:b:c:1:2:3:2.3:henry

s:d:e:2:1:4:54:user5

d:q:w:5:6:3:5.2:alex

y:m:n:3:4:1:5.6:eiam

$ awk '

BEGIN {

FS=":";

maxFLD=8;

}

{

while (NF < maxFLD || $0 ~ /\:$/ ) {

getline record;

$0 = $0 record

}

print $0

}

' file_name

Live example: 

2009-07-10 13:51:31.939439|2009-07-10|2|1.1.6401.0.614513|0570817|1|-3||production|2.0.0|Series60|V 11.0.021|11.0.021.01.01|11.0.021.C01.01|244|05|862e-d2ac-4f03-9cbb|xmldatabinding 5.0|Xmldatabinding 5.0 for N-Gage

|x-epoc/x-sisx-app|0|4|1|3|0x101f978e|1.0.6|Company|N97|Country_variants_of_UK_Ireland

--------------------------------------------------------------------------------------------------------------------------------------

$ awk 'BEGIN {FS="|";maxFLD=30;}{while (NF < maxFLD || $0 ~ /\:$/ ) {getline record;$0 = $0 record} print $0}' file_name

---------------------------------------------------------------------------------------------------------------------------------------

Display lines which starts with “|”:

- $ Grep ‘^|’ file_name

Paste = shift + insert

Copy = ctrl + insert

df  -h ***

id -u

ls –lrt

# get running processes

ps -ef

#unpack a file 

tar xvf “filename”

#unpack 

gzip -cd FileName.tar.gz | tar -xvf -

fg  (can be used to return to stopped window/process)

w (logged in users, idle or not?)

who (logged on users, from where)

who -r (show runlevel)

wc filename (lines, words, characters)

# print line number 52

sed -n '52p' # method 1

sed '52!d' # method 2

sed '52q;d' # method 3, efficient on large files

Remove/replace hex values from a file:

perl -i.bak -pe 's/(\xEF)(\xBF)(\xBD)/???/g' music_player_track.txt

Add user: useradd –u uid (esim. 2600)  –c ‘description’ –d /homedir/ -g group –m –s /bin/bash username

Change passwd: passwd username

Example:

useradd -u 10302011 -c "FirstName LastName, DeparmentName, firstname.lastname@company.com" -m -d /export/home/firstlast -s /bin/bash firstlast

passwd -f login = forces to change password when logging in for the first time

#Ei saa näyttää yhtään vastausta:

passwd -s -a | grep NP (=No Password)

#/var/adm/loginlog sisältää kaikki epäonnistuneet loginyritykset…

#Alustus:

#!/bin/sh

log=/var/adm/loginlog

test -f "$log" && {

        # "Good, $log exists already."

        exit 0

}

touch $log || exit 1

chown root $log || exit 1

chgrp sys $log || exit 1

chmod 600 $log || exit 1

# "Initialized $log."

Use SVCCFG:

bash-3.00# svccfg

svc:> select cde-login

svc:/application/graphical-login/cde-login>

svc:/application/graphical-login/cde-login> setprop dtlogin/args=astring: "-udpPort 0"

svc:/application/graphical-login/cde-login> quit

bash-3.00# svcadm cde-login restart

Display lines that contain A or B

/usr/bin/egrep - i “A|B”

Ex. # ps -ef | /usr/bin/egrep - i “PID1|PID2”

START/ STOP scripts ( /etc/init.d/rc.script)

-  create symbolic links from /etc/rc(0,1,2).d/K99sas --> /etc/init.d/rc.script

-  create symbolic link from /etc/rc3.d/S99sas --> /etc/init.d/rc.script

Startscripts are executed in order where smallest number is executed first on each runlevel

ln –s source target

Normal Case

Put SXX in rc3.d

Put KXX in rc0.d, rc1.d, rc2.d, rcS.d

cd /etc/rc3.d

ln –s /etc/init.d/rc.script S99rcscript

cd /etc/rc0.d

ln -s /etc/init.d/rc.script K99rcscript

cd /etc/rc1.d

ln -s /etc/init.d/rc.script K99rcscript

cd /etc/rc2.d

ln -s /etc/init.d/rc.script K99rcscript

cd /etc/rcS.d

ln -s /etc/init.d/rc.script K99rcscript

RC scripts & Run Levels 

Rc scripts performs the following functions : 

a) They check and mount the file systems 

b) Start and stop the various processes like network , nfs etc.

c) Perform some of the house keeping jobs.

System goes in to one of the following run level after booting depending on default run level and the commands issued for changing the run level to some other one.

0 Boot prom level ok> or > prompt in Sun.

1 Administrative run level . Single user mode 

2 Multiuser mode with no resource sharing .

3 Multiuser level with nfs resource sharing

4 Not used

5 Shutdown & power off (Sun 4m and 4u architecture )

6 Reboot to default run level

S s Single user mode user logins are disabled.

Broadly speaking the running system can be in any of the folloing state

Single user – Minimum processes running , user logins disabled and root password is required to gain access to the shell . 

Multiuser - All system processes are running and user logins are permitted 

Run level of a desired state is achieved by a number of scripts executed by the rc program the rc scripts are located in /etc/rc0.d , /etc/rc1.d , /etc/rc2.d , /etc/rc3.d & /etc/rcS.d directories . All the files of a particular run level are executed in the alphanumeric order .Those files beginning with letter S starts the processes and those beginning with K stops the processes.

These files are hard linked to the files in /etc/init.d in order to provide a central location for all these files and eliminating the need to change the run level in case these scripts needs to be run separately . The files in /etc/init.d directory are without any S , K and numeric prefix instead a stop / start argument has to be supplied whenever these scripts are to be executed .

By default system has a number of rc scripts needed for run level transition but sometimes it becomes necessary to start some custom scripts at the booting time and turn them off at the shutdown . Custom scripts can be put in any of the required rc directory but following major considerations has to be kept in mind :

•  The sequence number of the file should not conflict with other files.

•  The sevices needed should be available by previously executed scripts. 

•  File should be hard linked to the /etc/init.d directory .

•  The system looks for only those files beginning with letter K & S , any thing else is ignored , therefore, to make a file inactive simply changing uppercase K or S to lower case will cause system to ignore it . 

The scripts that begin with a K are used to kill processes when exiting a specific runlevel. In the listing above, the K scripts would be used when terminating runlevel 3. The scripts that start with an S are used when starting runlevel 3. None of the items in rc3.d, however, is really a startup script. They are logical links to the real scripts, which are located in the /etc/rc.d/init.d directory. For example, S80sendmail is linked to init.d/sendmail. This raises the question of why the scripts are executed from the directory rc3.d instead of directly from init.d where they actually reside. The reasons are simple. The same scripts are needed for several different runlevels. Using logical links, the scripts can be stored in one place and still be accessed by every runlevel from the directory used by that runlevel. 

Scripts are executed in alphabetical order. Thus S10network is executed before S80sendmail. This allows the system to control the order in which scripts are executed through simple naming conventions. Different runlevels can execute the scripts in different orders while still allowing the real scripts in init.d to have simple, descriptive names. A listing of the init.d directory shows these descriptive names: 

VI COMMMORS 

Move with cursor: h,j,k,l

Beginning of line: 0

Corrections during INSERT:

-  CTRL+H delete last character

Append after cursor: a

Append at the end of line: A

Yanked: y

Put: p – puts back yanked or deletet text after cursor

#Undo last change: 

u

#Restore curren line: 

U

#Repeat last change:

.

#Repeat last command

: n

#Replace (sed):   

:%s/old value/new value/

#Copy: CTRL+INSERT

#PASTe: SHIFT+INSERT

#EXTRACT *.tar.gz 

gzip -dc some.tar.gz | tar xvf –

Single file:

gzip -dc fileName.tar.gz | tar -xvf - filename

find . -name '*.log' -print | zip cipdq`date +%Y%m%d%H%M%S` -@

find / -type f | xargs grep -nH 'curl'

find . -type f -name '*.sh' -print -exec grep -n gpg {} \;

#where am I

uname -a ; 

#who am I 

id ; 

# when this is happening 

date "+%Y.%m.%d %H:%M:%S" ; 

useradd -u 12202211 -c "First Name , LastName, Teamp , firstName.LastName@company.com" -m -d /export/home/userName -s /bin/bash userName

#obsolete

skill -STOP -u userToKick

pkill -STOP -u userToKick

# reboot ... !!! BOOM BOOM BOOM !!!

shutdown -r now 

# check all available commands on my system

 echo -n $PATH | xargs -d : -I {} find {} -maxdepth 1  -type f -printf '%P\n' | sort -u | less

 #how-to kill a process 

ps aux | grep omniture

pidof omniture

kill -9 omniture

#what cpu am a running

less /proc/cpuinfo

# which processes are listening on my system

netstat --tcp --listening --programs

netstat --tcp

netstat --route

#source: http://www.yolinux.com/TUTORIALS/LinuxTutorialSysAdmin.html#MONITOR

#monitoring commands 

# show the top processes

top

# running processes status 

ps -auxw | less 

ps -ef | less 

#List all currently loaded kernel modules

lsmod | less 

#Displays the system's current runlevel.

/sbin/runlevel

# get the Processes attached to open files or open network ports:

lsoff | less 

#/prespan class= monitor the virtual memory 

vmstat 

#Display/examine memory map and libraries (so). Usage: pmap pid

ps -aux | grep <<MyProcToPMap>>

pmap   <<MyprocToPMapPID>>

history | gawk -F1 'BEGIN {FS=" "};{print $2 , $3}'

pmap -x 4041 | gawk -F1 'BEGIN {FS=" "};{print $2  ,"\t" , $5 , "\t" , $6 , "\t", $7 , $8}' | sort -nr

#how-to sort output by a delimited by single delimiter column 

# in this example the - char is used for delimiter , the output is 

# by their sending sequence , use proper file naming convention files 

# ls -1 gives us:

# fileBeginningTillFirstDelimiter-TheColumnToSortBy-TheRestFromTheFileNameDelimiter

ls -1 | awk -F1 'BEGIN {FS="-"};{print $2 "¤" $1 "-" $2 "-" $3 }' | sort -nr | cut -d ¤ -f 2,5 

# the same approach with perl

ls -1 | perl -p -i -e 's/^([^\-]*)(\-)([^\-]*)(\-)([^\-]*)/$3¤$1$2$3$4$5/g' | sort -nr | cut -d ¤ -f 2,5  

# how-to use sftp with remoteUserName having publicIdentity of PublicIdentityUserName

sftp -v -o "IdentityFile /var/www/.ssh-id/PublicIdentityUserName" -o "UserKnownHostsFile /var/www/.ssh-id/known_hosts" remoteUserName@ServerHostNameOrIpd

# ==================================================================

# START Jobs control 

# start some very long lasting command 

find / -name '*.crt' | less 

# now press Ctrl + Z 

# the terminal says "Jobs stopped"

# now check the open jobs 

jobs

# you should see something like 

# [1]+  Stopped                 find / -name '*.crt' | less

# now put the job in the background and start working on something else by Ctrl + Z 

bg 1

# run the next command 

# how-to copy file via scp by using specificy identity

scp -v -o "IdentityFile /home/userName/.ssh/id_rsa" /data/path/dir/* userName@ServerHostName.Domain.com:/Server/Target/Dir/

# now again stop the job first by Ctrl + Z 

# check again the running jobs 

jobs 

# use should see the both of the jobs started 

# now put the first on in the forground 

fg 1

# Repeat that several times untill you get it ; ) !!!

# END Jobs control 

# ==================================================================

nicedate=`date +%Z-%Y%m%d%H%M%S`

# kill a process by name 

ProcNameToKill=sftp

# ps -ef | grep wget | perl -ne 'split /\s+/;print "kill $_[7] with PID $_[1] \n";`kill -9 $_[1];`'

ps -ef | grep $ProcNameToKill | perl -ne 'split /\s+/;print "kill $_[7] with PID $_[1] \n";`kill -9 $_[1];`'

# how-to display human readable file sizes on systems with stupid du

# of course you would have to have perl next_line_is_templatized

find `pwd` -type f -exec du -k {} \; | perl -ne 'split /\s+/;my $SizesInMegs=$_[0]/1024;printf ( "%10d %10s \n" , "$SizesInMegs" , "MB $_[1]")' | sort -nr | morphus

export dir=/

echo sizes in MB

find $dir -type f -exec du -k {} \; | perl -ne 'split /\s+/;my $SizesInMegs=$_[0]/1024;printf ( "%10d %-100s \n" , "$SizesInMegs" , "$_[1]")' | sort -nr | more

#who has been accessing via ssh 

for file in `find /var/log/secure* | sort -rn` ; do grep -nHP 'mfe' $file ; done; | less

#print files recursively 

dir=/opt/path/to/dir

clear;find $dir -type f -exec ls -alt --time-style=long-iso --color=tty {} \; | perl -ne 'split(/\s+/);printf ( "%10s %2s %-20s \n" , "$_[5]", "$_[6]", "$_[7]") ; ' | sort -nr

#how-to print relative file paths to /some/DirName with perl one liner 

find /some/DirName -type f | perl -ne 'split/DirName\//;print "$_[1]"  '

# see nice dir recursively listing newest first

dir=/tmp

find $dir -name '*.tmp' -exec ls -alt --time-style=long-iso --color=tty {} \; | perl -ne 'split/\s+/;print "$_[5] $_[6] $_[7] \n" ;' | sort -nr | less

# how-to sort files based on a number sequence in their file names

# list dir files , grap a number from their names , print with NumberFileName, sort , print finally the names without the Number but sorted 

 ls -1 | perl -ne 'm/(\d{8})/; print $1 . $_ ;' | sort -nr | perl -ne 's/(\d{8})//;print $_'

#how-to create relative file paths tar package recursively fromm a dir

cd <<RootDirToStartRelativePathsFrom>>

tar -cvzpf <<PackageToCreate>>.tar <<DirToPackRecursively>>

# exctract tar file into cd  

tar -xvf <<PackageToExctractInCurrentDir>>.tar

#source:http://mywiki.wooledge.org/BashFAQ/002

# get the STDERR and STDOUTPUT 

output=$(command 2>&1)

# create pub priv keys on server

ssh-keygen -t rsa 

# add pub key from client to authorized_keys files on server

cat id_rsa.pub >> ~/.ssh/authorized_keys

# Purpose: 

# to provide a simple cheat sheet for most of the Linux related commands

# VersionHistory

# 1.7.0 --- ysg ---  removing HP-UX stuff

# 1.6.0 --- ysg ---  HP-UX check file sizes 

# 1.5.0 --- ysg ---  Added tar and ssh-keygen

# 1.4.0 --- ysg ---  Added kill proc by name and du -BM for HP-UX

# 1.3.0 --- ysg ---  Added jobs control 

# 1.2.0 --- ysg ---  Added system monitoring commands 

# 1.1.6 --- ysg ---  refined du , replaced echo's with #'s

# 1.0.0 --- ysg ---  Initial creation  

# you should see something like

it ain't right until its single-click

The majority of all you 0 readers ( or copy pasters ) of this blog already have noticed that most of the simple tasks and features presented here are "single-click" - e.g. scripts which are once called, perform their duty and exit.
Let's put is this way: The GUI varies from use case or even different usages on the same feature on the same host by the same user so much that words are simply not enough ... If you need to document an important configuration change, installation or whatever action by describing what is happening on the GUI you will get it wrong. Yes you will get it, because I could change my color theme to something very freaky and argue that it just does not look the same as in your snapshots ...

When I was responsible for the db updates of a SOX based system a spent 3 months to get it right - a single script installing the updates and producing logs as well a single script doing the back out. Each time a call one of my tools and it is a single click I know that they will work with 99% certainty ( sorry  we are not arrived at AI yet , even a "Hello World" could get you bug , why because it is run on processors and OS which have been designed with buggy software , you got it ?!...) , so I reserve the 1% for my own mistakes  and the rest of the bugs of the hardware and software workers having build the whole stack I am building now ...
Thus it ain't single click - it ain't right , and you cannot call it Done !

(Update: )
Google WTF I am trying to dump something rational about IT and you provide me with banners for dating Russians and Thai girls ... Halooo please fix that parser over there ...

gpg cheat sheet

#File: GpgCheatSheet.sh v.1.1.0 docs at the end 

man gpg

# how-to generate a personal gpg key 

gpg --gen-key

# how-to encrypt a file for personal use

# the long version

gpg --encrypt --recipient 'Yordan Georgiev' FileName

gpg --encrypt --recipient 'Yordan Georgiev' GpgCheatSheet.sh

gpg --encrypt --recipient 'Yordan Georgiev' $file 

#decrypted the encrypted file 

gpg --output FileName.txt     --decrypt FileName.txt.gpg

gpg --output GpgCheatSheet.sh --decrypt GpgCheatSheet.sh.gpg

gpg --output $file --decrypt $file.gpg

# how-to encrypt a file for someone else 

gpg --import SomeOneElsesKeyFile.asc

# check that the import was successful 

gpg --list-keys | less 

# how-to encrypt a file for someone else 

gpg --encrypt --recipient 'someone.else@domainName.com' foo.txt

# how-to decrypt a file from someone else 

# note: you have to have the public key of this someone imported 

gpg --output foo.txt --decrypt foo.txt.gpg

#encrypt all files in the current directory to myself 

find . -type f -exec gpg --encrypt --recipient 'Yordan Georgiev' {} \;

# 

# Purpose: 

# to provide a simple cheat sheet for the basic gpg commands 

# source: http://www.madboa.com/geek/gpg-quickstart/

# 

# VersionHistory: 

#

# 2012.04.19 - 23:54:19 --- ysg --- docs and to morphus 1.1.9

# 2011.06.24 - 12:24:43 --- ysg --- Initial creation

informatica cheat sheet

-- FILE: InformaticaCheatSheet.sql v.1.1.0 docs at the end 

-- DEFINE A NICE DATETIME FORMAT

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY.MM.DD HH24:MI:SS'

;

-- FIND FIRST THE NAME OF THE TABLE 

SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE '%OBJ%'

; 

-- FIND A COLUMN_NAME 

SELECT OWNER , TABLE_NAME , COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE 1=1 

AND COLUMN_NAME LIKE '%OBJECT%' 

AND OWNER = 'INFAPROD'

AND TABLE_NAME NOT LIKE '$'

AND COLUMN_NAME LIKE '%OBJECT%'

ORDER BY OWNER , TABLE_NAME

; 

-- AND THAN CHECK THE DATA OF THE TABLE

SELECT DISTINCT (SERVER_NAME) FROM OPB_WFLOW_RUN ; 

SELECT * FROM OPB_SESS_CNX_REFS

; 

-- GENERATE A COLUMN SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_WFLOW_RUN'

;

-- GENERATE A TABLE_NAME.COLUMN_NAME SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || TABLE_NAME || '.' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_SUBJECT'

;

-- HOW-TO list Informatica folder

select * from OPB_SUBJECT 

WHERE 1=1 

-- and SUBJ_NAME LIKE '%MAN%'

AND SUBJ_NAME = 'FOLDER_NAME'

; 

-- HOW-TO FIND A FOLDER ID BY ITS NAME

select * from OPB_SUBJECT 

WHERE 1=1 

-- and SUBJ_NAME LIKE '%MAN%'

AND SUBJ_NAME = 'FOLDER_NAME'

; 

-- GET THE LIST OF ALL THE SESSIONS FOR A FOLDER

SELECT   * FROM OPB_TASK 

WHERE 1=1 

AND SUBJECT_ID = 999 

-- GET ONLY THE VALID OBJECTS

AND IS_VALID=1

 -- AND TASK_TYPE = 68 

AND (UTC_LAST_SAVED, TASK_NAME) 

IN 

( 

  SELECT MAX(UTC_LAST_SAVED) , TASK_NAME FROM OPB_TASK 

  WHERE 1=1 

  AND SUBJECT_ID = 999 

  GROUP BY TASK_NAME

)

-- sessions 

-- AND TASK_TYPE = 68 

-- workflows

AND TASK_TYPE = 71 

-- Command Task

--AND TASK_TYPE=58

ORDER BY TASK_NAME ASC

; 

select OWNER , TABLE_NAME , COLUMN_NAME FROM ALL_TAB_COLUMNS 

WHERE 1=1

AND TABLE_NAME LIKE '%CNXS%'

-- AND COLUMN_NAME LIKE '%OBJECT_TYPE%' 

AND OWNER = 'INFAPROD'

;  

select * from INFAPROD.REP_SESSION_CNXS 

WHERE 1=1 

AND CONNECTION_NAME like ('%SAP%')

-- AND CONNECTION_NAME LIKE '%SAP%'

; 

SELECT  * FROM INFAPROD.REP_SESS_WIDGET_CNXS

WHERE 1=1

order by instance_name

-- AND CNX_NAME LIKE 'SAPELI_TEST'

-- AND INSTANCE_NAME = 'FACT_SAP'

; 

desc INFAPROD.REP_SESSION_CNXS

; 

select * from REP_SESS_WIDGET_CNXS

; 

--and object_type=74; 

-- LIST THE OBJECT TYPES

SELECT * FROM OPB_MMD_EXTN_ATTR

;

-- LIST REPOSITORIES

SELECT * FROM OPB_SERVER_INFO 

; 

-- PowerCenter folders table

SELECT * FROM OPB_SUBJECT 

; 

-- Tasks table like sessions, workflow etc

SELECT * FROM OPB_TASK 

;

SELECT * FROM OPB_TASK_INST

; 

-- Mappings table

SELECT * FROM OPB_MAPPING 

;  

-- Session & Mapping linkage table

SELECT * FROM OPB_SESSION 

; 

-- Task attributes tables

SELECT * FROM OPB_TASK_ATTR 

;

-- connectoin strings

SELECT * FROM OPB_CNX 

; 

-- Transformations table

SELECT * FROM OPB_WIDGET 

;

-- Transformation ports table

SELECT * OPB_WIDGET_FIELD 

;

-- Transformation properties table

SELECT * OPB_WIDGET_ATTR 

;

-- Expressions table

SELECT * OPB_EXPRESSION

;

-- Session Configuration Attributes

SELECT * FROM OPB_CFG_ATTR 

WHERE 1=1 

-- AND ATTR_VALUE IN '%SAP%'

;

-- HOW-TO FIND A FOLDER ID BY ITS NAME

select * from OPB_SUBJECT 

WHERE 1=1 

-- and SUBJ_NAME LIKE '%MAN%'

AND SUBJ_NAME = 'FOLDER_NAME'

; 

-- GET THE LIST OF SESSION FOR A FOLDER

SELECT  *  FROM OPB_TASK 

WHERE 1=1 

AND SUBJECT_ID = 999 

AND IS_VALID=0

 AND TASK_TYPE = 68 

AND (VERSION_NUMBER, TASK_NAME) IN 

(SELECT MAX(VERSION_NUMBER), TASK_NAME FROM OPB_TASK 

  WHERE 1=1 

  AND SUBJECT_ID = 999 

  AND TASK_TYPE = 68 

GROUP BY TASK_NAME)

ORDER BY LAST_SAVED ASC

; 

select distinct m.mapping_name 

from opb_widget_inst w, opb_mapping m, opb_targ t

where m.mapping_id = w.mapping_id

and t.target_id = w.widget_id

-- and t.target_name = 'FOLDER_NAME'

; 

-- OBJECT_TYPE 73,74

-- GET THE CONNECTIONS STRINGS BY NAME

select * from OPB_CNX

WHERE 1=1 

AND 

( OBJECT_NAME LIKE '%SAP%'

OR 

USER_NAME LIKE '%sap%'

)

; 

SELECT * FROM OPB_CNX_ATTR

WHERE 1=1 

AND OBJECT_TYPE=74

-- ATTR_ID IS NOT NULL 

-- AND ATTR_VALUE LIKE '%SAP%'

; 

-- the session 

SELECT * FROM OPB_CFG_ATTR

WHERE ATTR_VALUE LIKE '%$%'

; 

-- LIST WORFKLOWS,SESSIONS,MAPPINGS,SOURCES,TARGETS

SELECT

  F.SUBJ_NAME AS FOLDER_NAME

, WF.TASK_NAME AS WORKFLOW_NAME 

, SE.INSTANCE_NAME AS SESSION_NAME

, M.MAPPING_NAME  AS MAPPING_NAME

, SRC.INSTANCE_NAME AS SOURCE_NAME

, TGT.INSTANCE_NAME AS TARGET_NAME

 FROM 

OPB_SUBJECT F,

OPB_TASK WF,

( 

  SELECT 

  WORKFLOW_ID,

INSTANCE_ID,

TASK_ID,

TASK_TYPE,

INSTANCE_NAME,

MAX(VERSION_NUMBER)

FROM OPB_TASK_INST SESS

WHERE 

SESS.TASK_TYPE=68 

GROUP BY

WORKFLOW_ID,INSTANCE_ID,TASK_ID,TASK_TYPE,INSTANCE_NAME) SE,

(SELECT SESSION_ID,MAPPING_ID,MAX(VERSION_NUMBER) FROM OPB_SESSION GROUP BY SESSION_ID,MAPPING_ID) S,

opb_mapping m,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) SRC,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) TGT

where WF.IS_VISIBLE = 1

AND WF.SUBJECT_ID = F.SUBJ_ID

AND SE.WORKFLOW_ID = WF.TASK_ID

AND WF.TASK_TYPE = 71

AND se.task_id = s.session_id

AND s.mapping_id = m.mapping_id

AND M.IS_VISIBLE = 1

AND SRC.MAPPING_ID=m.mapping_id

AND SRC.WIDGET_TYPE=1

AND TGT.MAPPING_ID=m.mapping_id

AND TGT.WIDGET_TYPE=2

ORDER BY 

-- AND F.SUBJ_NAME = 'FOLDER_NAME'

   F.SUBJ_NAME 

 , WF.TASK_NAME 

 , SE.INSTANCE_NAME 

 , M.MAPPING_NAME 

 , SRC.INSTANCE_NAME 

 , TGT.INSTANCE_NAME 

; 

SELECT * FROM OPB_ATTR

WHERE 1=1

-- AND OBJECT_TYPE_ID IN (73)

; 

-- GET THE VALUES OF ALL SQL OVERRIDES 

select folder, wf_name, 

       sess_name, mapping_name, 

       transformation_name, attr_name, 

       line_no, sql_value

from (select f.subj_name folder, 

             wf.task_name wf_name, 

             sess.instance_name sess_name, 

             m.mapping_name mapping_name, 

             w_inst.instance_name transformation_name, 

             attr.line_no, attr.attr_value sql_value, 

             attr_type.attr_name attr_name,

             row_number() over (partition by wf.task_name, 

                                             sess.instance_name, 

                                             m.mapping_name, 

                                             w_inst.instance_name, 

                                             attr.line_no, 

                                             attr.attr_value

                                order by attr.session_task_id desc

                               ) rn

      from opb_task_inst wf_inst

           ,opb_task_inst sess

           ,opb_session s

           ,opb_mapping m

           ,opb_subject f

           ,opb_widget_attr attr

           ,opb_widget_inst w_inst

           ,opb_task wf

           ,(select o.object_type_id object_type_id, 

                    o_attr.attr_id attr_id, 

                    o.object_type_name||': '||o_attr.attr_name attr_name

             from opb_attr o_attr, 

                  opb_object_type o

             where o.object_type_id = o_attr.object_type_id

             and o_attr.attr_datatype = 2

             and o_attr.attr_value is null

             and upper(o_attr.attr_name) like '%SQL%'

            ) attr_type

      where wf_inst.task_id = sess.task_id

      and sess.task_type = 68

      and sess.task_id = s.session_id

      and wf.subject_id = f.subj_id

      and s.mapping_id = m.mapping_id

      and attr.widget_id = w_inst.widget_id

      and w_inst.mapping_id = m.mapping_id

      and w_inst.widget_type = attr_type.object_type_id

      and wf_inst.workflow_id = wf.task_id

      and wf.task_type = 71

      and (attr.session_task_id = s.session_id

           or attr.session_task_id = 0)

      and attr.attr_id = attr_type.attr_id

      and attr.attr_value is not null

      and attr.attr_value <> '0'

  --    and wf.task_name = 'WORKFLOW_NAME'

    --  and f.subj_name = 'FOLDER_NAME'

      )

where rn = 1

order by 1, 2, 3, 4, 5, 6, 7

; 

-- PURPOSE: 

-- Contain all the needed informatica repository sql in one place 

-- VersionHistory: 

-- 1.1.0 --- ysg --- Added repositories, improved formatting

-- 1.0.0 --- ysg --- Initial creation 

/pre

single click zip project folder and e-mail to smtp server

:: File: zip-and-sent-folder.cmd v.1.3.0 docs at the end 

@ECHO off

:: set the nicetime var

call GetNiceTime_on_%computername%.cmd

:: go the run dir

cd %~dp0

:: this is the dir containing the batch file

set MyDir=%CD%

for %%A in (%0) do set MyDriveLetter=%%~dA

for %%A in (%0) do set MyPath=%%~pA

for %%A in (%0) do set MyName=%%~nA

for %%A in (%0) do set MyExtension=%%~xA

:: go 4 levels up

for /L %%i in (1,1,5) do pushd ..

:: resolve the 4th dir up

set _DirLevel4Up=%CD%

:: find the BaseDir

set _ProductBaseDir=%_DirLevel4Up%

:: next_line_is_templatized

set _ProductName=morphus

set _ProductDir=%_ProductBaseDir%\%_ProductName%

:: next_line_is_templatized

set _ProductVersion=1.1.8

:: next_line_is_templatized

set _ProductType=dev

:: next_line_is_templatized

set _ProductOwner=ysg

set _EnvironmentName=%_ProductName%.%_ProductVersion%.%_ProductType%.%_ProductOwner%

set _ProductVersionDir=%_ProductDir%\%_EnvironmentName%

:: ECHO MyName                                             %0

:: ECHO MyDriveLetter is                                  %MyDriveLetter%

:: ECHO MyPath is                                            %MyPath%

:: ECHO MyName is                                          %MyName%

:: ECHO MyExtension is                                    %MyExtension%

:: define the project folder to zip and sent as the first cmd argument

set ProjectFolder=%1

for %%A in (%ProjectFolder%) do set ProjectDriveLetter=%%~dA

for %%A in (%ProjectFolder%) do set ProjectPath=%%~pA

for %%A in (%ProjectFolder%) do set ProjectName=%%~nA

:: go the ProjectDriveLetter

%ProjectDriveLetter%

:: go to the project folder

cd %ProjectFolder%

:: build the project name

:: build the zip file 

set _ZipFile=%ProjectName%.%computername%.zip

:: go one dir up 

cd ..

:: build the zip file path

set _ZipFilePath=%CD%\%_ZipFile%

set _ZipFileTimeStamped=%_ZipFile%.%NiceTime%.txt

:: echo cd is %cd%

:: pause

:: add the files into a package:: 

zip -r %_ZipFile% %ProjectName%\*

:: PAUSE

::debug echo nicetime is %niceTime%

::DEBUG PAUSE

:: DEBUG echo rename "%cd%\zipFile.zip" %niceTime%.zip.txt

:: DEBUG PAUSE

:: make it a txt file for easier e-mail deleting 

rename "%cd%\%_ZipFile%" "%_ZipFileTimeStamped%"

:: the perl script is the same as the file name + the pl extension

set PerlScript=%_ProductVersionDir%\libs\perl\sendEmail.pl

:: define the log file 

set MailLogFile=%MyDir%\%MyName%.log

:: define the error log file 

set MailErrorLogFile=%MyDir%\%MyName%.error.log

:: the "from" e-mail address

:: set /p FromEmail=Type here the e-mail to send the e-mail from

for /f "tokens=4 delims= " %%i in ('type %userprofile%\gate.csv ^| grep google') do set FromEmail=%%i

:: the "to" e-mail address 

:: set /p ToEmail=Type here the e-mail to send the e-mail to

for /f "tokens=4 delims= " %%i in ('type %userprofile%\gate.csv ^| grep google') do set ToEmail=%%i

:: the e-mail subject to use to the message 

set MailSubject="%ProjectName% --- %_NiceTime% "

:: define the text file holding the message 

set MessageFile=%ProjectFolder%\Mesage.html

:: add the project name and the current time stamp in the message

echo %ProjectName% --- %_NiceTime% > %MessageFile%

:: define the smtp server

:: set SmtpServerAndPort=<<Type here your smtp.domain.com:port>>

for /f "tokens=6 delims= " %%i in ('type %userprofile%\gate.csv ^| grep google') do set SmtpServerAndPort=%%i

echo SmtpServerAndPort is %SmtpServerAndPort%

:: PAUSE

:: define the smtp use to use for authentication against the smtp server

:: set SmtpUser=<<TypeHereYourSmtpUser>>

for /f "tokens=4 delims= " %%i in ('type %userprofile%\gate.csv ^| grep google') do set SmtpUser=%%i

echo SmtpUser is %SmtpUser%

:: PAUSE

:: define the password of the smtp user against the smpt server

:: set SmtpUserPass=<<SmtpUserPass>>

for /f "tokens=5 delims= " %%i in ('type %userprofile%\gate.csv ^| grep google') do set SmtpUserPass=%%i 

echo SmtpUserPass is %SmtpUserPass%

:: PAUSE

:: clear the result of the previous runs 

:: del /q %MyDir%\*.log

:: del /q %MyDir%\*.html

echo "%_NiceTime%" > %MailLogFile%

echo "%_NiceTime%" > %MailErrorLogFile%

:: Action !!!

:: perl %PerlScript% >%LogFile% 2>%ErrorLogFile%

perl %PerlScript% -v -v -l %MailLogFile% -f %FromEmail% -t %ToEmail% -u %MailSubject% -o message-file=%MessageFile% -s %SmtpServerAndPort% -o message-content-type=html -o tls=yes -o timeout=2 -xu %SmtpUser% -xp %SmtpUserPass% -a %MessageFile% %_ZipFileTimeStamped%

:: Purpose: 

:: zip a project and backup it an smpt server having tls support

:: client

:: Requirements:

:: Windows XP or newer

:: perl                                      http://strawberryperl.com/

:: sendMail.pl :                        http://caspian.dotconf.net/menu/Software/SendEmail/

:: win gnu zip for windows :    http://gnuwin32.sourceforge.net/packages/zip.htm

:: GetNiceTime_on_%computername%.cmd to generate time in YYYY.MM.DD hh:mm:ss format

:: access to an stmp server supporting tls 

:: Usage

:: Start - Run - type %0 <<FullPathToTheFolderToBackUP>>

:: F:\morphus\morphus.1.1.8.dev.ysg\sfw\cmd\zip\zip-and-sent-folder.cmd F:\morphus\morphus.1.1.8.dev.ysg\sfw\tools\imatica\list-the-failed-tasks-from-today

:: VersionHistory

:: 1.3.0 --- ysg --- Separation of code and configuration, doc update

:: 1.2.0 --- ysg --- Fixed bug of not comrpessing folder on different drive letter

:: 1.1.0 --- ysg --- Added GetNiceTime 

:: 1.0.0 --- ysg --- Initial version from previous tools 

:: DEBUG

:: PAUSE

how-to run an oracle report with sqlplus and sent it via e-mail (2)

/* File: sqlplus-reporter.sql v1.0.0 docs at the end */

/* do not print echo commands */

set echo off

/* do not require feedback from the user */

set feedback off

/* set a nice prompt */

set sqlprompt ''

/* -- separate columns with a comma */

set colsep ';'

/* -- only one header row */

set pagesize 0   

/*-- remove trailing blanks */

set trimspool on 

/* REMOVE THE WHITE SPACE */

set trimout on

/* -- this may or may not be useful...depends on your headings. */

set headsep ON

/* how- long the line should be */

set linesize 3000   

/* print the headers as well */

set heading on

/* request full html for output */

/* set markup html on spool off */

/* define the file where the output of the results should be stored */

spool "&1..html"

-- START SQL

select '<html> <head> <title>tables</title>   <style type="text/css">     table tbody tr td {       background-color: #eee;     }     table tr.odd  td {       background-color: #fff;     }   </style> </head><body><table>

' from dual ; 

-- specify grey ( #CCC ) and white colors 

-- open the row 

select '<tr class="' 

|| CASE mod(rownum,2) WHEN 0 THEN 'odd' ELSE 'even' END

|| '">' 

-- print a single table cell 

|| ' <td>' || OWNER || '</td>' 

-- print another single table cell

|| ' <td>' || TABLE_NAME || '</td>'        

-- close the row 

|| '</tr>'        FROM 

-- IF YOU NEED ORDER BY CLAUSE AS WELL, 

( 

SELECT OWNER , TABLE_NAME FROM ALL_TABLES ORDER BY OWNER , TABLE_NAME

) 

; 

select '</table></body></html>' from dual ; 

-- STOP SQL 

spool off ; 

/* exit the sqlplus tool */   

EXIT 0; 

/* 

Purpose: 

- to provide a single sql statement to run agains an oracle database 

- to output a single file passed as the first argument to the script 

VersionHistory: 

1.1.0 --- 2012.04.08 - 23:04:19  ---  ysg --- Added order by clause

1.0.0 --- 2012.04.08 - 22:07:19  ---  ysg --- Initial creation 

*/

how-to run an oracle report with sqlplus and sent it via e-mail ( 1)

:: File: sqlplus-reporter v.1.2.0 docs at the end 

@ECHO off

call GetNiceTime.cmd 

:: go the run dir

cd %~dp0

:: this is the dir containing the batch file

set MyDir=%CD%

for %%A in (%0) do set MyDriveLetter=%%~dA

for %%A in (%0) do set MyPath=%%~pA

for %%A in (%0) do set MyName=%%~nA

for %%A in (%0) do set MyExtension=%%~xA

:: ECHO MyName                                             %0

:: ECHO MyDriveLetter is                                  %MyDriveLetter%

:: ECHO MyPath is                                            %MyPath%

:: ECHO MyName is                                          %MyName%

:: ECHO MyExtension is                                    %MyExtension%

:: the ora script is the same as the file name + the sql extension

set OraSqlScript=%MyDir%\%MyName%.sql

:: the perl script is the same as the file name + the pl extension

set PerlScript=%MyDir%\sendEmail.pl

:: define the log file 

set MailLogFile=%MyDriveLetter%\%MyPath%\%MyName%.log

:: define the error log file 

set MailErrorLogFile=%MyDriveLetter%\%MyPath%\%MyName%.error.log

:: define the text file holding the message 

set MessageFile=%MyDir%\%MyName%.html

:: the "from" e-mail address

set FromEmail=firstName.lastName@gmail.com

:: the "to" e-mail address 

set ToEmail=firstName.lastName@oxit.fi

:: the e-mail subject to use to the message 

set MailSubject="%MyName% --- %_NiceTime% "

:: define the text file holding the message 

set MessageFile=%MyDir%\%MyName%.html

:: the smtp server

set SmtpServerAndPort=smtp.gmail.com:587

:: define the smtp use to use for authentication against the smtp server

set SmtpUser=firstName.lastName@gmail.com

:: define the password of the smtp user against the smpt server

set SmtpUserPass=TooSecretPass

:: clear the result of the previous runs 

:: del /q %MyDir%\*.log

:: del /q %MyDir%\*.html

:: define the oracle user name to use to connect to ora

set OracleUserName=SYSMAN

:: get user input

:: SET /P Pass=[Please, provide the password for the %OracleUserName%:]

:: the pass to use to connect to the service 

SET OraPass=TooSecreteOraPass

:: define the name of the service 

set TnsServiceName=ORASERVICENAME

:: define the log file 

set OraLogFile=%MyDriveLetter%\%MyPath%\%MyName%.log

:: define the error log file 

set OraErrorLogFile=%MyDriveLetter%\%MyPath%\%MyName%.error.log

echo "%_NiceTime%" > %OraLogFile%

echo "%_NiceTime%" > %OraErrorLogFile%

echo "%_NiceTime%" > %MailLogFile%

echo "%_NiceTime%" > %MailErrorLogFile%

:: clear the result of the previous runs 

del /q %MyDir%\*.html

:: Action !!!

:: call with the listener service name 

:: sqlplus %OracleUserName%/%Pass%@%TnsServiceName @%OraSqlScript% %MyName% > %LogFile% 2>%ErrorLogFile%

:: call without the listener service name 

sqlplus %OracleUserName%/%OraPass% @%OraSqlScript% %MyName% > %OraLogFile% 2>%OraErrorLogFile%

:: DEBUG

:: PAUSE

:: Action !!!

:: perl %PerlScript% >%LogFile% 2>%ErrorLogFile%

perl %PerlScript% -v -v -l %MailLogFile% -f %FromEmail% -t %ToEmail% -u %MailSubject% -o message-file=%MessageFile% -s %SmtpServerAndPort% -o message-content-type=html -o tls=yes -o timeout=2 -xu %SmtpUser% -xp %SmtpUserPass% -a %MessageFile% %MyDir%\me.jpg

:: DEBUG

:: PAUSE

:: Purpose: 

:: create a generic wrapper for executing oracle sql commands trough the sqplplus

:: client

:: Requirements:

:: Windows XP or newer

:: GetNiceTime.cmd to generate time in YYYY.MM.DD hh:mm:ss format

:: sqlplus client on local windows

:: access to oracle database 

:: %MyName%.sql with the proper sqlplus syntax to execute the sql statememtns

:: perl sendMail.pl : http://caspian.dotconf.net/menu/Software/SendEmail/

:: access to an stmp server supporting tls 

:: Usage: 

:: call from dos or double-click in Explorer

:: VersionHistory

:: 1.2.0 --- ysg --- Added multiple attachments sending

:: 1.1.0 --- ysg --- Added GetNiceTime 

:: 1.0.0 --- ysg --- Initial version from previous tools