Total Pageviews

Thursday, March 22, 2012

Unix Shell Scripting KB

Cheat Sheet

Write a shell script to save the output of 'ls -ltr' every five minutes and after every hour it should print which all files are changed/modified/created. "Setup crontab entries to run the commands repeatedly.
find . -mtime 0 # find files modified between now and 1 day ago (i.e. within the past 24 hours)
find . -mtime -1 # find files modified less than 1 day ago (i.e. within the past 24 hours as before)
find . -mtime 1 # find files modified between 24 and 48 hours ago
find . -mtime +1 # find files modified more than 48 hours ago
find . -mmin +5 -mmin -10 # find files modified between 6 and 9 minutes ago"

How do you read arguments in a shell program - $1, $2 ? "#!/bin/sh
for i in $*
do
echo $i
done

Hot to check length of a variable
echo ${#var1}
echo "hello" | wc -c

On executing the above script with any number of command-line arguments it will display all the parameters.
$1 would be the first command line argument 
$2 the second and so on
$0 is the name of the script or function

Shell script accepts parameters in following format...
$1 : first
$2 : second....so on upto
$9 : 9th param
whereas $0 : gives script/function name

If your script has more than 9 params then accept in following way...
${12} : 12th param
${18} : 18th param
U can also use Shift for getting the behind 9th parameters.........
exec<$1 or exec<$2"

What are the different kinds of loops available in shell script?
"Broadly categorized in 3
for
while
until"

What is the difference between a shell variable that is exported and the one that is not exported?
"exported variable is visible to the child processes while the normal variables are not.
export LANG C
will make the variable LANG the global variable put it into the global environment. all other processes can use it.

LANG C
will change the value only in the current script.
try this simple script it will clear your concept :-
script a.sh :-
----------
export A=3
B=5
sh b.sh
--------
script b.sh :-
-----------
echo ""exported variable $A is :- $A""
echo ""Variable $B -- $B ""
---------
A persists in b.sh whereas B doesn't. "

If you have a string "one two three", Which shell command would you use to extract the strings? "echo ""one two three""
cut -d"" "" -f 1,2,3
or
echo ""one two three""
awk '{print $1 $2 $3}'
cut awk sed and others are not SHELL commands. One way could be with bash: string one two three tab ($string)$ echo ${tab[0]}one$ echo ${tab[1]}two$ echo ${tab[2]}threehttp://www.big-up.org
echo $string
tr "" "" ""n""
string one two three tab ($string)$ echo ${tab[0]}one ${tab[1]}two ${tab[2]}three"

How will you list only the empty lines in a file (using grep)? "grep ^$ filename.txt
grep ^[ ]*$ filename.txt
In character set (between [ and ] one space and tab is given)
this command will gives all the blank line including those having space and tabs (if pressed)only
We can do this efficiently through awk script
awk '{
if (NF 0)
{
print ""Here comes the empty line""
print $0
}
}' filename
NF number of fields in the current line
$0 current line

grep -v . filename

cat test.sh
grep ^$
grep -v . test.sh
grep '[ t]*$' file_name"

When you login to a c shell, which script would be run first? (before the terminal is ready for the user) ".profile file will execute first when a user logs in.
For C shell ,
first /etc/.login script is run & after that
~/.login is run & then ~/.cshrc is run. "

How would you get the character positions 10-20 from a text file? "cat filename.txt
cut -c 10-20
cut -c10-20
substring ${string_variable_name:starting_position:length}"

How would you print just the 25th line in a file (smallest possible script please)? "tail -n +25Â
head -1 OR head -n 25
tail -1
head -25 filename
tail -1OR
head -25 file
tail -1 goes wrong because if someone deletes some lines even then it gives last line as 25th line.
so the right option is
tail +25 file
head -1 OR
tail -n +25
head -1 OR
head -n 25
tail -1
sed -n '25p' filename.txt"

How To Connect To Data Base in Solaris "echo Enter User Name 

read name
echo Enter passwd stty -
echo read passstty 
echo sqlplus -s $name@oracle/$pass<"
How would you replace the n character in a file with some xyz? 

"sed 's/n/xyz/g' filename > new_filenamevi file name
then go to command mode
s/n/xyz/gtail -1 sample.txt
sed 's/.$/xyz/'

Check this
perl -p -i -e ""s/n/abc/g"" file_name
We can replace n characters by using the following command:
1 $s/./xyz/g
where 1 shows that the search string will start searching patterns from first line of the file.
'.' for any character.
g for global replacement.


FOLLOWING IS THE ANSWER FOR REPLACING RANGE OF CHARACTERS IN A LINE OF FILE. HERE 5TH TO 10TH CHARACTERS IN THE FILE text.txt ARE REPLACED BY ""XYZ""
cut -c5-10 text.txt
sed 's/^.*$/XYZ/g'search for the character n in filename and replace it with xyz.
sed 's/n/xyz/g' filename"

How to find how many users have logged in and logged out in last five minutes using shell scripts? "By writing ""who -Hu"" you get following details o/p
Using SIGALRM"


Error Handling
Shell Scripting Commands "echo $?
if the output of command is 0 then successfully executed
if the output is non-zero then not successfully executed.
If a command is executed successfully then the exit status of a command is 0. 

We can test whether a command is executed successfully or not by writing 2 line
[ command_name ] # command_name is any unix command
echo $?
# $? contains success/failure of the last command that has been executed"


Other methods of error handling
http://unix.stackexchange.com/questions/21151/error-handling-in-shell-script


$ cat test2.sh
if [ -f run_script.lck ]; then
  echo Script $0 already running
  exit 1
fi
trap "rm -f run_script.lck" EXIT
# rest of the script ...

set -e means that each exit status unequal 0 ends the execution of the script. Similar to that, with  
set -u every (probably accidental) use of an undefined variable ends the execution.
set -x means .....................

command1 && command2
command2 is executed if, and only if, command1 returns an exit status of zero.

command1 || command2
command2 is executed if, and only if, command1 returns a non-zero exit status.  

http://linuxcommand.org/wss0150.php

[me] $ true || echo "echo executed"
[me] $ false || echo "echo executed"
echo executed
[me] $ true && echo "echo executed"
echo executed
[me] $ false && echo "echo executed"

http://unix.stackexchange.com/questions/97101/how-to-catch-an-error-in-a-linux-bash-script

Since cd returns a non-zero status on failure, you could do:
cd -- "$1" && echo OK || echo NOT_OK
You could simply exit on failure:
cd -- "$1" || exit 1
Or, echo your own message and exit:
cd -- "$1" || { echo NOT_OK; exit 1; }
And/or suppress the error provided by cd on failure:
cd -- "$1" 2>/dev/null || exit 1

 
"Shell Scripting question : Find text in the file
nishanth.sed
1h
2,10{H;g}
$q
1,9d
N
D
input_file
aa
bb
cc
dd
ee
ff
gg
hh
ii
jj
kk
ll
mm" "script

sed -f nishanth.sed input_file "

What is the difference between writing code in shell and editor? "Code in the script (Shell is interprted) as shell is a interpreter
where as editor is not inter preter certain set of commands(predefined)
are used to handle editor"

"Shell script
You have current directory containing set of directories which contain files.
One file can reside in many directories.
Write script which returns number of unique file names in
all the subdirectories of a current dir." 

"ls -R sort uniq" should work

How do you search the string for vowel's occurrence and number of occurrences of each vowel "grep -io [aeiou] filename
wc -w
for a cat filename
tr -d 'eioubcdfghjklmnpqrstvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890()~`!@#$ &^*""()_+{}
:""<>? ./;][ -/n '
grep a
wc -m
for e cat filename
tr -d 'aioubcdfghjklmnpqrstvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890()~`!@#$ &^*""()_+{}
:""<>? ./;][ -/n '
grep e
wc -m
for i cat filename
tr -d 'aeoubcdfghjklmnpqrstvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890()~`!@#$ &^*""()_+{}
:""<>? ./;][ -/n '
grep i
wc -m
for o cat filename
tr -d 'aeiubcdfghjklmnpqrstvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890()~`!@#$ &^*""()_+{}
:""<>? ./;][ -/n '
grep o
wc -m
for u cat filename
tr -d 'aeiobcdfghjklmnpqrstvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890()~`!@#$ &^*""()_+{}
:""<>? ./;][ -/n '
grep u
wc -m
it will give the no of time that vowel has been occured
for searching the string
cat filename
grep 'aeiou'
hey please also add tr -d ' before wc -m
FE : cat filename
tr -d 'eioubcdfghjklmnpqrstvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890()~`!@#$ &^*""()_+{}
:""<>? ./;][ -/n '
grep a
tr -d '
wc -m
for counting the vowels
grep -io [aeiou] filename
wc -w
hey please also add tr -d ' before wc -m
FE : cat filename
tr -d 'eioubcdfghjklmnpqrstvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890()~`!@#$ &^*""()_+{}
:""<>? ./;][ -/n '
grep a
tr -d '
wc -m
for counting the vowels
Even the below command can be used:
grep -io [aeiou] filename
wc -w
For 'a'
cat file_name
tr -cd 'a'
wc -m
For 'e'
cat file_name
tr -cd 'e'
wc -m
For 'i'
cat file_name
tr -cd 'i'
wc -m
For 'o'
cat file_name
tr -cd 'o'
wc -m
For 'u'
cat file_name
tr -cd 'u'
wc -m
I am giving the 3 ways to solve this problem..
1) grep -io [aeiouAEIOU] filename
sort
uniq -c
2) cat filename
sed 's/[a-zA-Z]/&n/g'
sed 's/^ //'
grep [aeiouAEIOU]
sort
uniq -c
3) cat filename
fold -1
sort
sed -n '/^[aeiouAEIOU]/p'
uniq -c
"

"write a script to convert all dos style backslashes to unix style slashes in a list of files?
write a script to list all the differences between two directories ?
what is the command for to display the last newly appending lines of a file during appending data to the same file by same processes ?
what is the command to display top most processes which has utilised most of the cpu?" "for i in `ls *`;do cat $i
sed 'y
\
/
' > $i.newthis will create a new file for each of the replace files so remove the .new line to get the same updated.tail -f filenameshould display the updates made to a file.top -c -d2 should list the processes based on the current usage.
you can use this command to display top most processes which has utilised most of the cpu.

ps aux
sort -n +2
tail -1
lets say window.list has the file list
then this will do the trick

cat window.list
sed 's///\/g'
You can simply use dos2unix command which allow you to change all window special character to Unix format
dos2unix only removes the control-M characters at the end of the line. 

You need to use sed to do the search and replace for '' to '/'."

How to compare floating point number in shell scripting ? "a1 10.50
b1 10.01
if [ $a1 -gt $b1 ]
bc
then
echo ""greater""
else
echo ""lesser""
fi"

how to delete a word from a file using shell scripting??? 

"echo ""Other than the aesthetics there's the synthetics""
sed -e 's/word//g' filename
sed -e 's/apple//' sometext
echo ""Other than the aesthetics there's the synthetics""
sed 's/the//g'
echo ""Other than the aesthetics there's the synthetics""
sed 's///g'
echo ""Other than the aesthetics there's the synthetics""
sed 's/\//g'"

How to extract the second row of a text-file? 

"sed -n '2p' datafile
tail +2

head -1
sed -n '2p' datafile
awk '{print $2}' file_name
sed -n 2 2p file1
head -2 file.txt
tail -1"

How to compress files by using shell scripting? 

"To zip a single file use:-
$ gzip filename .
It will create a file named filename.gz .
To zip a folder first create a .tar file and then zip it.
Let the name of the folder be shell .
To create a .tar file use:-
$ tar cvf filename.tar shell/.
It will create a file named filename.tar
Now use the $ gzip filename.tar .
It will create a file named filename.tar.gz .
So in this way you can compress a file.
Note: To uncompress a file you can use gunzip command.
Fantastic explaination given above......
another way would be...
compress
which will create a filename.Z
and if we want the zip file to be in some other folder
compress -cf
to uncompress
use the following command...
uncompress
Though the explanations given are very good but in real time environment where the file size runs into MBs of data the proper way would be
1: If its a single file then gzip and compress and pack will serve the purpose. Out of these compress gives the more desired results
These commands use different compression algorithms take different amounts of time and reduce files by different amounts.
In general these commands can reduce the size of a file by 50-75 .
"

What is use of "cut" command ? 

Give some examples. 
Can we use "awk" or "sed" instead of "cut" ? 
If yes then give some examples? 
"This utility is use to cut out columns from a table or fields from each line of a file.
cut can be used as a filter.
Either the -b -c or -f option must be specified.
-b list
The list following -b specifies byte positions (for instance -b1-72 would pass the first 72 bytes of each line). When -b and -n are used together list is adjusted so that no multi-byte character is split.
-c list
The list following -c specifies character positions (for instance -c1-72 would pass the first 72 characters of each line).
-d delim
The character following -d is the field delimiter (-f option only). 

Default is tab. Space or other characters with special meaning to the shell must be quoted. delim can be a multi-byte character.
Cut - Utility used to cut/Strip out the required data/text from the source.
Cut can be used in three modes
Stripping by Character
cut -c 1-3
STriping by Byte length
cut -b -1-72
Stripping by delimiter and fields.
cut -d
-f1
where
-d
-> Delimiter used in input text to separate columns
-f1 -> Field/Column number
while processing Huge input files Cut's performance is far better than awk
Cut is a powerful command in Unix Cutting fields we can use the command for example we would like to extract first 3 col
cut -c1-3 sample (Sample is a file name)
To extract first and 3rd col
cut -d;-f1 -f3 sample


1. CUT is used to get the specific portion(column) of information from a files separated by a specific character(ex: CSV file)
ex: more /etc/passwd
cut -d"":"" -f1
The above command is used to list the user accounts on the server.


2. AWK
ex: ls -al
awk '{print $9}'
 

The above command is used to get the list of filenames in the current directory. Here the fields are need not be seperated by a specific charecter.


3. SED is similar to GREP command. The cut command cuts bytes characters or fields from each line of a file and writes these bytes characters or fields to standard output. If you do not specify the File parameter the cut command reads standard input.
To display fields using a blank separated list enter:
cut -f ""1 2 3"" -d : /etc/passwd
The cut command produces:
su:*:0
daemon:*:1
bin:*:2
sys:*:3
adm:*:4
pierre:*:200
joan:*:202
SED is a simple editor which will be editing files without a gui and to provide operations on the command line.sed -e s/word1/word2/g filenamehttp://en.wikipedia.org/wiki/SedAWK is a command line argument used for printing out values and arguments.

awk '{print $1}';http://en.wikipedia.org/wiki/awk
cut -d"":"" -f1 3 file
Delimitor "":""
Fields/Columns 1 and 3 are printed.
In this example awk can be used. sed can not be used.
awk can do all the jobs of cut. cut can give o/p byte wise which is not possible in awk."

How to find see the file which is created today, s date with time after 10 a.m to 5 p.m? 

"find . -atime 0 -type f -exec ls -ltr {} ; grep ""`date +%b %d`""
This command will show the files which are created today. 

But for ur second part of the question i need to analyse more,
Explanation:
find . -atime 0 -type f -exec ls -ltr {}
This will list the files where are accessed and created today.
grep ""`date +%b %d`"" This part of the command will filter out unwanted files which were not created today.
find . -atime 0 -type f -exec ls -ltr {} ;
grep `date + b d`
This command will show the files which are created today. But for ur second part of the quesiton i needto analyse more
Explanation:find . -atime 0 -type f -exec ls -ltr {}
This will list the files where are accessed and created today.
grep `date + b d`
This part of the command will filter out unwanted files which were not created today.
set -u -a
rm -f out2
find $PWD -atime 0 -type f -exec ls -ltr {} ;
grep `date '+ b d'`
tr -s
tr -d : > out2
 

echo -------Files Modified Today--------
cat out2
while read line
do
timechk `echo $line
cut -d -f8`
filename `echo $line
cut -d -f9`
if [ $timechk -gt 1000 -a $timechk -lt 1700 ]
then
echo $filename
fi
done
echo ----------------------------------- "

"Using Bourne shell : if you enter A B C D E F G.......................n after the command,
how will you write a programme to reverse these positional parameters? " 

"str=""alpha beta gamma""
echo $str
awk '{
for (i=NF; i>0; i--) printf (""%s "",$i);
printf(""n"");
}'
If you're talking about parameters passed into a Shell script, that can be modified to the following:
echo $*
awk '{
for (i=NF; i>0; i--) printf (""%s "",$i);
printf(""n"");
}'
The previous answer using ""awk"" reversed all the characters, resulting in:
$ echo $@
rev
i $#
while(($i> 0))
do
echo $i
((i $i-1))
done
str ""alpha beta gamma""
echo $str
awk '{
for (i NF; i>0; i--) printf ("" s "" $i);
printf(""n"");
}'
Results:
gamma beta alpha


If you're talking about parameters passed into a Shell script that can be modified to the following:
echo $*
awk '{
for (i NF; i>0; i--) printf ("" s "" $i);
printf(""n"");
}'"
 


Check if the directory exists [[ -e directoryname ]]

Check if the file exists [[-f filename]]


Check minutes from time `date +%M`

Sleep for # secs sleep #

Expressions expr

date in mm dd hour format date +%m%d%H

Change time for a file 

"touch -t





Crontab KB

Cheat Sheet

Crontab – Quick Reference

Setting up cron jobs in Unix and Solaris

cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix , solaris.  Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.

This document covers following aspects of Unix cron jobs

1. Crontab Restrictions
2. Crontab Commands
3. Crontab file – syntax
4. Crontab Example
5. Crontab Environment
6. Disable Email
7. Generate log file for crontab activity

1. Crontab Restrictions

You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use crontab if your name does not appear in the file /usr/lib/cron/cron.deny.

If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

2. Crontab Commands

export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e    Edit your crontab file, or create one if it doesn’t already exist.
crontab -l      Display your crontab file.
crontab -r      Remove your crontab file.
crontab -v      Display the last time you edited your crontab file. (This option is only available on a few systems.)

3. Crontab file

Crontab syntax :
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.
*   *   *  *   *  command to be executed
-    -   -   -   -
                   +----- day of week (0 - 6) (Sunday=0)
              +------- month (1 - 12)
          +--------- day of month (1 - 31)
     +----------- hour (0 - 23)
+------------- min (0 - 59)

* in the value field above means all legal values as in braces for that column.

The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).

Notes
A. ) Repeat pattern like /2 for every 2 minutes or /10 for every 10 minutes is not supported by all operating systems. If you try to use it and crontab complains it is probably not supported.
B.) The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

4. Crontab Example

A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.

30     18     *     *     *         rm /home/someuser/tmp/*

Changing the parameter values as below will cause this command to run at different time schedule below :
min   hour      day/month   month    day/week  Execution time

30     0          1                 1,6,12   *                 – 00:30 Hrs  on 1st of Jan, June & Dec.
0       20        *                 10         1-5              – 8.00 PM every weekday (Mon-Fri) only in Oct.
0        0         1,10,15       *           *                 – midnight on 1st ,10th & 15th of month
5,10   0         10               *           1                 – At 12.05,12.10 every Monday & on 10th of every month

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

5. Crontab Environment

cron invokes the command from the user’s HOME directory with the shell, (/usr/bin/sh).

cron supplies a default environment for every shell, defining:

HOME=user’s-home-directory
LOGNAME=user’s-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

6. Disable Email

By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1

7. Generate log file

To collect the cron execution execution log in a file :

* * * * * rm /home/xxxsomeuser/tmp/* > /home/xxxsomeuser/cronlogs/clean.log


SQLPlus KB

Get a list of these SET options in sql*plus with the command:

SQLPLUS> HELP SET

ARRAY[SIZE] {15|n}

Fetch size (1 to 5000) the number of rows that will be retrieved in one go.

AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}

Autocommit commits after each SQL command or PL/SQL block

AUTOP[RINT] {OFF|ON}

Automatic PRINTing of bind variables.(see PRINT)

AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

Display a trace report for SELECT, INSERT, UPDATE or DELETE statements

EXPLAIN shows the query execution path by performing an EXPLAIN PLAN.

STATISTICS displays SQL statement statistics.

Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS

BLO[CKTERMINATOR] {.|c|OFF|ON}

Set the non-alphanumeric character used to end PL/SQL blocks to c

CMDS[EP] {;|c|OFF|ON}

Change or enable command separator - default is a semicolon (;)

COLSEP { |text}

The text to be printed between SELECTed columns normally a space.

ECHO {OFF|ON}

Display commands as they are executed

EMB[EDDED] {OFF|ON}

OFF = report printing will start at the top of a new page.

ON = report printing may begin anywhere on a page.

ESC[APE] {\|c|OFF|ON}

Defines the escape character. OFF undefines. ON enables.

FEED[BACK] {6|n|OFF|ON}

Display the number of records returned (when rows >= n )

OFF (or n=0) will turn the display off

FLU[SH] {OFF|ON}

Buffer display output (OS)

(no longer used in Oracle 9)

HEA[DING] {OFF|ON}

print column headings

HEADS[EP] {||c|OFF|ON}

Define the heading separator character (used to divide a column heading onto > one line.)

OFF will actually print the heading separator char

see also: COLUMN command

LIN[ESIZE] {150|n}

Width of a line (before wrapping to the next line)

Earlier versions default to 80, Oracle 9 is 150

MARK[UP] HTML [ON|OFF]

[HEAD text] [BODY text] [TABLE text]

[ENTMAP {ON|OFF}][SPOOL {ON|OFF}]

[PRE[FORMAT] {ON|OFF}]

Output HTML text, which is the output used by iSQL*Plus.

NEWP[AGE] {1|n}

The number of blank lines between the top of each page and the top title.

0 = a form feed between pages.

NULL text

Replace a null value with 'text'

The NULL clause of the COLUMN command will override this for a given column.

NUMF[ORMAT] format

The default number format.

see COLUMN FORMAT.

NUM[WIDTH] {10|n}

The default width for displaying numbers.

PAGES[IZE] {14|n}

The height of the page - number of lines.

0 will suppress all headings, page breaks, titles

PAU[SE] {OFF|ON|text}

press [Return] after each page

enclose 'text' in single quotes

SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED] WOR[D_WRAPPED] TRU[NCATED]}]

whether to display the output of stored procedures (or PL/SQL blocks)

i.e., DBMS_OUTPUT.PUT_LINE

SIZE = buffer size (2000-1,000,000) bytes

SHOW[MODE] {OFF|ON}

Display old and new settings of a system variable

SQLBL[ANKLINES] {ON|OFF}

Allow blank lines within an SQL command. reverts to OFF after the curent command/block.

SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}

Convert the case of SQL commands and PL/SQL blocks

(but not the SQL buffer itself)

SQLCO[NTINUE] {> text}

Continuation prompt (used when a command is continued on an additional line using a hyphen -)

SQLN[UMBER] {OFF|ON}

Set the prompt for the second and subsequent lines of a command or PL/SQL block.

ON = set the SQL prompt = the line number.

OFF = set the SQL prompt = SQLPROMPT.

SQLP[ROMPT] {SQL>|text}

Set the command prompt.

SQLT[ERMINATOR] {;|c|OFF|ON}

Set the char used to end and execute SQL commands to c.

OFF disables the command terminator - use an empty line instead.

ON resets the terminator to the default semicolon (;).

SUF[FIX] {SQL|text}

Default file extension for SQL scripts

TAB {OFF|ON}

Format white space in terminal output.

OFF = use spaces to format white space.

ON = use the TAB char.

Note this does not apply to spooled output files.

The default is system-dependent. Enter SHOW TAB to see the default value.

TERM[OUT] {OFF|ON}

OFF suppresses the display of output from a command file

ON displays the output.

TERMOUT OFF does not affect the output from commands entered interactively.

TI[ME] {OFF|ON}

Display the time at the command prompt.

TIMI[NG] {OFF|ON}

ON = display timing statistics for each SQL command or PL/SQL block run.

OFF = suppress timing statistics

TRIM[OUT] {OFF|ON}

Display trailing blanks at the end of each line.

ON = remove blanks, improving performance

OFF = display blanks.

This does not affect spooled output.

SQL*Plus ignores TRIMOUT ON unless you set TAB ON.

TRIMS[POOL] {ON|OFF}

Allows trailing blanks at the end of each spooled line.

This does not affect terminal output.

UND[ERLINE] {-|c|ON|OFF}

Set the char used to underline column headings to c.

VER[IFY] {OFF|ON}

ON = list the text of a command before and after replacing substitution variables with values.

OFF = dont display the command.

WRA[P] {OFF|ON}

Controls whether to truncate or wrap the display of long lines.

OFF = truncate

ON = wrap to the next line

The COLUMN command (WRAPPED and TRUNCATED clause) can override this for specific columns.

Script example

/*

Multiple line comments

Can go between these delimiters

*/

SET TERM OFF

-- TERM = ON will display on terminal screen (OFF = show in LOG only)

SET ECHO ON

-- ECHO = ON will Display the command on screen (+ spool)

-- ECHO = OFF will Display the command on screen but not in spool files.

-- Interactive commands are always echoed to screen/spool.

SET TRIMOUT ON

-- TRIMOUT = ON will remove trailing spaces from output

SET TRIMSPOOL ON

-- TRIMSPOOL = ON will remove trailing spaces from spooled output

SET HEADING OFF

-- HEADING = OFF will hide column headings

SET FEEDBACK OFF

-- FEEDBACK = ON will count rows returned

SET PAUSE OFF

-- PAUSE = ON .. press return at end of each page

SET PAGESIZE 0

-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)

SET LINESIZE 80 (use 10000 pref)


-- LINESIZE = width of page (80 is typical)

SET VERIFY OFF

-- VERIFY = ON will show before and after substitution variables

-- Start spooling to a log file

SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--

SELECT * FROM GLOBAL_NAME;

SPOOL OFF

SQL*Plus commands

The following commands can be issued in SQL*Plus (in addition to the standard SQL commands.)

@pathname Run an SQL Script (START)

@MyScript.sql parameter1 parameter2 parameter3

In the SQL-Script, refer to the parameters as &1, &2, and &3.

@@pathname Run a nested SQL Script.

@variable A substitution variable

@@variable A substitution variable valid for the session

/ Execute (or re-execute) commands in the SQL*Plus buffer

does not list commands before running

ACCEPT User input

ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FORMAT format]

[DEFAULT default] [PROMPT text|NOPROMPT] [HIDE]

APPEND Add text to the end of the current line in the buffer.

A[PPEND] text_to_add

BREAK Specify where and how formatting will change.

BREAK ON {column|expr|ROW|REPORT} action

BTITLE Place and format a title at the bottom of each page.

BTITLE printspec [text|variable]

BTITLE [OFF|ON]

CHANGE Change text on the current line.

C /oldval/newval

CLEAR Clear the SQL*Plus screen and the screen buffer.

CLEAR {BREAKS|BUFFER|COLUMNS|COMPUTES|SCREEN|SQL TIMING}

COLUMN Change display width of a column.

COMPUTE Calculate and display totals.

CONNECT Connect to a database as a specified user.

connect username/password@SID

COPY Copy data from a query into a table (local or remote)

DEFINE User variables:

DEFINE varName = String

Display a user variable

DEFINE varName

Display all variables

DEFINE

DEL Delete the current line in the SQL buffer

DESC[RIBE] Describe a table, column, view, synonym, function

procedure, package or package contents.

DISCONNECT Logoff (but don't exit)

EDIT Load the SQL*Plus buffer into an editor.

By default, saves the file to AFIEDT.BUF

EXECUTE Run a single PLSQL statement

EXEC :answer := EMP_PAY.BONUS('SMITH')

EXIT [n] Commit, logoff and exit (n = error code)

EXIT SQL.SQLCODE

GET file Retrieve a previously stored command file

HELP topic Topic is an SQL PLUS command or HELP COMMANDS

HOST Execute a host operating system command

HOST CD scripts

INPUT Edit sql buffer - add line(s) to the buffer

LIST n m Edit sql buffer - display buffer lines n to m

For all lines - specify m as LAST

PAUSE message Wait for the user to hit RETURN

PRINT variable List the value of a bind variable or REF Cursor (see VARIABLE / SHOW)

PROMPT message Echo a message to the screen

REMARK REMARK comment or --comment-- or /* comment */

RUN Execute (or re-execute) commands in the SQL*Plus buffer

Lists the commands before running

RUNFORM Run a SQL*Forms application

SAVE file Save the contents of the SQL*Plus buffer in a command file

SAVE file [CRE[ATE] | REP[LACE] | APP[END]]

SET Display or change SQL*Plus settings

SHOW List the value of a system variable (see PRINT)

SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL]

SPOOL file Store query results in file

SPOOL OFF Turn off spooling

SPOOL OUT sends file to printer

SQLPLUS Start SQL*Plus and connect to a database.

STA[RT] Run an SQL Script (see @)

STARTUP [NOMOUNT|MOUNT|OPEN]

TIMING Record timing data TIMING {START |SHOW |STOP}

see CLEAR TIMING

TTITLE Define a page title

UNDEFINE Delete a user/substitution variable UNDEFINE varName (see DEFINE)

VARIABLE Define a bind variable (Can be used in both SQLPlus and PL/SQL)

VAR[IABLE] [variable {NUMBER|CHAR|CHAR(n)|REFCURSOR}]

A RefCursor bind variable can be used to reference PL/SQL cursor variables in stored procedures.

PRINT myRefCursor

EXECUTE somePackage.someProcedure(:myRefCursor)

VARIABLE on its own will display the definitions made.

WHENEVER OSERROR Exit if an OS error occurs

WHENEVER SQLERROR Exit if an SQL or PLSQL error occurs

SQL*Plus Prompt:

To display the currently connected UserName and SID, instead of just SQL>;

SET sqlprompt '>_user:&_connect_identifier >; '

Add the line above to the file: $ORACLE_SID/sqlplus/admin/glogin.sql (this tip requires Oracle 10g or greater)

Editing SQL scripts in SQL*Plus

To run your operating system's default text editor from the SQL*Plus command-line type Edit:

SQL > EDIT

The variable _EDITOR, may be used to setup a preferred text editor, for example, define vi as the SQL*Plus editor:

DEFINE _EDITOR = vi

or define Metapad.exe as the SQL*Plus editor:

DEFINE _EDITOR = Metapad.exe

Include an editor definition like the above in your user profile (login.sql) or site profile ($ORACLE_HOME/sqlplus/admin/glogin.sql) so that it is always enabled when you start SQL*Plus.

To create a new script with a text editor, enter EDIT followed by the name of the new file:

SQL > EDIT myscript.sql

The new file will be created in the current directory (wherever you started SQL*Plus)

When you save the script with the text editor, it is saved back into the same file.

You must include a semicolon at the end of each SQL command and a slash (/) on a line by itself after each PL/SQL block in the file.

You can include multiple SQL commands and PL/SQL blocks in a script.

In addition to external editors it is also possible to use the (rather limited) set of editing commands within SQL*Plus itself.

A text Append text at the end of the current line

C/old/new Change old to new in the current line

C/text Delete text from the current line

DEL Delete the current line

L List all lines in the SQL buffer

CLEAR BUFFER Delete all lines

Example
set markup html on
set pages 50000
set serveroutput off
set feedback off
set echo off
set trimspool off

Thursday, March 15, 2012

Cheat Sheets KB

sed KB

http://www.thegeekstuff.com/2009/10/unix-sed-tutorial-advanced-sed-substitution-examples/
http://www.grymoire.com/Unix/Sed.html
http://ss64.com/nt/syntax-esc.html


# substitute (find and replace) "foo" with "bar" on each line *******

sed 's/foo/bar/' # replaces only 1st instance in a line
sed 's/foo/bar/4' # replaces only 4th instance in a line
sed 's/foo/bar/g' # replaces ALL instances in a line
sed 's/\(.*\)foo\(.*foo\)/\1bar\2/' # replace the next-to-last case
sed 's/\(.*\)foo/\1bar/' # replace only the last case

# substitute "foo" with "bar" ONLY for lines which contain "baz"

sed '/baz/s/foo/bar/g'

# substitute "foo" with "bar" EXCEPT for lines which contain "baz"

sed '/baz/!s/foo/bar/g'

# change "scarlet" or "ruby" or "puce" to "red"

sed 's/scarlet/red/g;s/ruby/red/g;s/puce/red/g' # most seds


Remove M Characters (^M) with Unix commands


Using dos2unix command

dos2unix ORIG_FILENAME TEMP_FILENAME

mv TEMP_FILENAME ORIG_FILENAME

IMP NOTE: use dos2ux command if you are using HPUX

Using sed command

sed ‘s/^M//g’ ORIG_FILENAME > TEMP_FILENAME

mv TEMP_FILENAME ORIG_FILENAME

IMP NOTE: To get ^M in UNIX (Hold control key and then press v and m character).

Using vi Editor

ESCAPE :%s/^M//g ENTER

IMP NOTE: To get ^M in UNIX (Hold control key and then press v and m character).

awk KB

http://www.thegeekstuff.com/2010/01/awk-introduction-tutorial-7-awk-print-examples/

This program prints the maximum number of fields on any input line.

awk 'NF > 0' data
awk '{ if (NF > max) max = NF } END { print max }'
       
# Print first two fields in opposite order :

awk '{ print $2, $1 }' file

# Print length of string in 2nd column **********

awk '{print length($2)}' file

# Print lines longer than 72 characters : ******

awk 'length > 72' file

# Print fields in reverse order :

awk '{ for (i = NF; i > 0; --i) print $i }' file

# Add up first column, print sum and average : *****

awk '{ s += $1 } END { print "sum is", s, " average is", s/NR }' file

# Print all lines whose first field is different from previous one :

awk '$1 != prev { print; prev = $1 }' file

# Print column 3 if column 1 > column 2 :

awk '$1 > $2 {print $3}' file

# Print line if column 3 > column 2 :

awk '$3 > $2' file

# Print every line after erasing the 2nd field

awk '{$2 = ""; print}' file

Wednesday, March 14, 2012

vim KB

su - user
vi
se nu
:1,$s/20091116/20100104
set -o vi
bash
find . -name ""
vim
view
tail -100f
which sqlplus
:set number While vi is in edit mode and not inserting type the following
:set nonumber While vi is in edit mode and not inserting type the following
find -type f -exec dos2unix {} {}

set nobomb BOM (byte order mark) to remove special char

http://www.lagmonster.org/docs/vi.html

This article is part of the ongoing Vi / Vim Tips and Tricks series. Navigation is a vital part of text editing. To be very productive, you should be aware of all possible navigation shortcuts in your editor. In this article, let us review the following

8 Vi / Vim navigation options.

1.Line navigation
2.Screen navigation
3.Word navigation
4.Special navigation
5.Paragraph navigation
6.Search navigation
7.Code navigation
8.Navigation from command line

1. Vim Line Navigation

Following are the four navigation that can be done line by line.
■k – navigate upwards
■j – navigate downwards
■l – navigate right side
■h – navigate left side

By using the repeat factor in VIM we can do this operation for N times. For example, when you want to
go down by 10 lines, then type “10j”.

Within a line if you want to navigate to different position, you have 4 other options.

■0 – go to the starting of the current line.
■^ – go to the first non blank character of the line.
■$ – go to the end of the current line.
■g_ – go to the last non blank character of the line.

2. Vim Screen Navigation

Following are the three navigation which can be done in relation to text shown in the screen.

■H – Go to the first line of current screen.
■M – Go to the middle line of current screen.
■L – Go to the last line of current screen.
■ctrl+f – Jump forward one full screen.
■ctrl+b – Jump backwards one full screen
■ctrl+d – Jump forward (down) a half screen
■ctrl+u – Jump back (up) one half screen

3. Vim Special Navigation

You may want to do some special navigation inside a file, which are:

■N% – Go to the Nth percentage line of the file.
■NG – Go to the Nth line of the file.
G – Go to the end of the file.
■`” – Go to the position where you were in NORMAL MODE while last closing the file.
■`^ – Go to the position where you were in INSERT MODE while last closing the file.
g – Go to the beginning of the file.

4. Vim Word Navigation

You may want to do several navigation in relation to the words, such as:

■e – go to the end of the current word.

■E – go to the end of the current WORD.
■b – go to the previous (before) word.
■B – go to the previous (before) WORD.
■w – go to the next word.
■W – go to the next WORD.

WORD – WORD consists of a sequence of non-blank characters, separated with white space. word – word consists of a sequence of letters, digits and underscores.

Example to show the difference between WORD and word

■192.168.1.1 – single WORD
■192.168.1.1 – seven words.

5. Vim Paragraph Navigation

■{ – Go to the beginning of the current paragraph. By pressing { again and again move to the previous paragraph beginnings.
■} – Go to the end of the current paragraph. By pressing } again and again move to the next paragraph end, and again.

6. Vim Search Navigation

■/i – Search for a pattern which will you take you to the next occurrence of it.
■?i – Search for a pattern which will you take you to the previous occurrence of it.
■* - Go to the next occurrence of the current word under the cursor.
■# - Go to the previous occurrence of the current word under the cursor.

7. Vim Code Navigation

% – Go to the matching braces, or parenthesis inside code.

8. Go to the 143rd line of file$ vim +143 filename.txt

9. Open the file in read only mode.

$ vim -R /etc/filename

10. Vim Navigation from Command Line

Vim +N filename: Go to the Nth line of the file after opening it.

vim +10 /etc/passwd

Vim +/pattern filename: Go to the particular pattern’s line inside the file, first occurrence from first. In the following example, it will open the README file and jump to the first occurrence of the word “install”.

vim +/install README

Vim +?patten filename: Go to the particular pattern’s line inside the file, first occurrence from last. In the following example, it will open the README file and jump to the last occurrence of the word “bug”.

vim +?bug README
 
Set Line Number

How do I make the vi editor display or hide line numbers

Reference :
http://www.thegeekstuff.com/2009/03/8-essential-vim-editor-navigation-fundamentals/
http://en.wikibooks.org/
101 Linux Hacks book
 

Tuesday, March 13, 2012

Perl KB

Perl Quick Reference Card
Perl Cheat Sheet
http://www.cheat-sheets.org/
Perl Cheat Sheet
http://techcheatsheets.com/tag/perl/
Regular Exp in Perl
Perl Reg Exp Tutorial
Perl Regular Expressions by Example
Perl: Matching using regular expressions
Using Perl, how can I replace all whitespace in a file with newlines?
Trim whitespaces begining and end
Whitespace and perl developers
Perl Regular Expressions

Cheat Sheet and Interview Questions

1. What arguments do you frequently use for the Perl interpreter and what do they mean?
2. What does the command ‘use strict’ do and why should you use it?
3. What do the symbols $ @ and % mean when prefixing a variable?
4. What elements of the Perl language could you use to structure your code to allow for maximum re-use and maximum readability?
5. What are the characteristics of a project that is well suited to Perl?
6. Why do you program in Perl?
7. Explain the difference between my and local.
8. Explain the difference between use and require.
9. What’s your favorite module and why?
10. What is a hash?
11. Write a simple (common) regular expression to match an IP address, e-mail address, city-state-zipcode combination.
12. What purpose does each of the following serve: -w, strict, -T ?
13. What is the difference between for & foreach, exec & system?
14. Where do you go for Perl help? "> $name = ""bam"";
> $$name = 1; # Sets $bam
> ${$name} = 2; # Sets $bam
> ${$name x 2} = 3; # Sets $bambam
> $name->[0] = 4; # Sets $bam[0]
symbolic reference means using a string as a reference."
15. Name an instance where you used a CPAN module. "@ISA -> each package has its own @ISA array. this array keep track of classes it is inheriting.
ex:
package child;
@ISA ( parentclass);
@EXPORT this array stores the subroutins to be exported from a module.
@EXPORT_OK this array stores the subroutins to be exported only on request.
-------------------------------------------------------------------------------------------------------------------
Package NewModule;
use Exporter; # an application by ‘use’ operator.#
This Exporter package has @ISA array to look for a specific method this array keeps track of classes it is inheriting.
@ISA qw(Exporter);
@EXPORT qw(VAR1 VAR2 VAR3);
# The symbols VAR1 VAR2 VAR3 loaded by default
@EXPORT_OK qw(VAR4 VAR5);
# the symbols VAR4 and VAR5 only loaded if there is a request."

16. How do you open a file for writing? "use XYZ;
my $objref XYZ->new();"

17. How would you replace a char in string and how do you store the number of replacements? "Explain the difference between ""my"" and ""local"" variable scope declarations. ?
Both of them are used to declare local variables.
The variables declared with ""my"" can live only within the block it was defined and cannot get its visibility inherited functions called within that block, but one defined with ""local"" can live within the block and have its visibility in the functions called within that block.
The variables declared with my() are visible only within the scope of the block which names them. They are not visible outside of this block not even in routines or blocks that it calls. local() variables on the other hand are visible to routines that are called from the block where they are declared. Neither is visible after the end (the final closing curly brace) of the block at all.
Perl supports three scoping declarations that enable to create private variables (using my) selectively global variable (using our) and temporary copies of selected global variables (using local) ... my declares a variable to be scoped within the current block and when the block ends the varaible goes out of scope.. ""local"" is an temporary value to an gobal variable and the value lasts only for the duaration of the block it's just has an temporary value while it's being used within that block...

1) My

My creates a new variable and gives a lexical scope for that variable. The variable is not visible outside the block in which it is defined.

2) Local

Local saves the value of a global variable. It never creates a new variable. Here also the variable is not accessible outside the block but it is visible in the subroutine which is called from the original block. My is more preferring than local . But in various places local is useful where my is illegal

1) Changing the meaning of a special variable like $/
2) Localized file handles
3) Globing"

18. When would you not use Perl for a project? ": s/&zeta1;/ xcexb6 /gthis is working fine in vi.. u want it to rit as a perl code using some match for substitution?see for only some char which are taken as special meaning by perl u have to escape it using ' other wise it will replace nything u want.'
~s/&zeta1;/ xcexb6 /g; is working Fine. If there is any special character use in front of it.......
Then use escape character..
suppose / is the part of string then you can write like / where is escape character
: s/sunil/kartikey/kartikey/sunil/g"

What is the Use of Symbolic Reference in PERL?  "Perl is easy fast and its fun to code in perl.
Perl is rich with various packages N/w programming is very easy and there are lot more advantages to say.
Its portable i.e. it can be run Windows / Linux / Solaris and Unix with no changes or with minimum changes.
Unlike othere programming languages Perl has builting support for regex.
just cut down your peice as fast as you can with regex ( If you are master in regex ).
It is having the features of scripting (Shell) as well as programming (like C). Using PERL you can easily do the system administration works where comparitively difficult with Shell script.
* PERL is programmer friendly. It is easy to code and can do it in less number of lines. It does all the nice things with minimum fuzz.
* Text manipulation is unmatched.
* It has built-in regex.
* Has the support of world's largest archive of modules(CPAN)"

"What's the significance of @ISA, @EXPORT @EXPORT_OK
%EXPORT_TAGS list & hashes in a perl package?
With example?" "Refer perldoc. On the command line give perldoc command with various options like e.g.-f function name for function help etc.--Manjusha
I use search.cpan.org or the several Perl books from O'Reily. They are also available online through O'Reily's Safari service and older versions are published as the PERL Bookshelf.
-> perldoc -f function name
Ex: perldoc -f print
perldoc is best help to perl."

I have a variable named $objref which is defined in main package. I want to make it as a Object of Class XYZ. How could I do it? "Hi
The faviurite module is undoubtly CGI.pmIt simplifies the coding as we no need to worry about the intricacies involving the form processing etc.Thanks Abhishek jain"

Explain the difference between "my" and "local" variable scope declarations. ? CGI DBI etc are very common packages used from CPAN. there are thousands of other useful modules.

"I would like to replace some entities in the text file with symbols using Perl.
This is working fine for some special symbols but for the characters such as Zeta we should use Unicode/Hexadecimal values - xcex96. We are doing like this s/&zeta1;/""xcexb6""/g; . This is working fine only for 2 characters such as xb6. How to use this in Perl? How to make use unicode value for zeta in Perl.
Any kind of help will be appreciated." It provides a GUI

Why do you program in PERL "It is a formatter print it in double quotes to see what I mean.
$^ - Holds the name of the default heading format for the default file handle. Normally it is equal to the file handle's name with _TOP appended to it.
According to the book Perl core language-Little black book $^0 holds the name of the operating system for which the current perl was built.
STDOUT_TOP is the output if we print '$^0 in a console."

Where do you go for perl help? "When:
- There's a lot of text processing
- Web-based applications
- Fast/expidient development
- Shell scripts grow into libraries
- Heavy Data manipulation (auditing accounting checking etc... backend processing)
- Data extraction
- transform
- loading (database etc.)
- System admin etc...
When you are developing an application for a real time system in which processing speed is of utmost importnace"

Whats' your favourite module in PERL and why?

Name an instance you used in CPAN module? package PackageName;

what is a Tk module? WHere it is used? What's the use of it? Plse tell me .. "use POSIX;
use lib qw(/library/perl);
use DBI qw(:sqltypes);

what does this mean '$^0'? tell briefly plse.. $varname="VarCharValue"; OR $varname ="$anotherVar" OR $varname='NumberContant';

when do you not use PERL for a project? $VarName = "$LogDir/FileName.log.".UnixDate(ParseDate("today"), "%m%d"

Defining a package sub { }

Use a library "print ""-----msg----\n"";

Defining a variable and init the same my $varName = "value";
Appending time to log file package::subroutine()
Concatenation operator my $varDate = strftime('%Y-%m-%d %T %Z', localtime);
functions/subroutines $varName = shift(@_);
Print a message printf "[strftime('%Y-%m-%d %T %Z', localtime)] $message\n";
Assigning value to a local variable printf STDERR
Calling a subroutine in a package $#_
Get current time stamp if ($#_ >= 0)
Navigate thru an array time + 3600
Print message with time stamp @Array = localtime $now;
Print on STD ERROR $var = sprintf "%d%02d%02d"
Check no of params $Array[n]
Check if no of params > 0 my $var = new Date::Function(Param=>$var);
Date Time Addition if ($var != "")
Store local time in a array $element->sub();
Print formated time in an array "my $dbh = DBI->connect(""DBI:Oracle:SID"", ""user"", ""pwd"")
die ""Cannot connect to database. "" . DBI->errstr;
my $sql = ""select to_char(to_date(?,'YYYYMMDD'),'YYYYMMDD') from dual"";
my $g = $dbh->prepare($sql)

die ""Cannot prepare query. "" . DBI->errstr;
$g->bind_param(1,$abc, SQL_VARCHAR);
$g->bind_param(2, $id, SQL_INTEGER);
$g->execute

die ""error in shell::execute: $!"";
while ( ($row) = $g->fetchrow_array() )
{
$PortList{$row} = $count;
$count = $count + 1;
}
$g->finish();
return %VarList;"
Refering to nth element in a array sub routineName { return $var }
Call Package Function with Param "while ( ($row) = $g->fetchrow_array() )
{
$count = $count + 1;
$rule = $row;
}"

To check if the variable is blank "open(INDATA, ""$Dir/$FileName"") or die ""Cannot Open Command Pipe $Dir/$FileName"";
open(OUTFILE, "">$Dir/$FileName"") or die ""Cannot open Output file $Dir/FileName"";"

Call a Package subroutine $var == 1

DB Call syntax "sub getValue
{
my $sql = ""select * from table"";
my $g = $dbh->prepare($sql)
die ""Cannot prepare query. "" . DBI->errstr;
$g->execute
die ""error in sth::execute: $!"";
my @dbResult;
while ( (@row) = $g->fetchrow_array() )
{
push(@dbResult, join(',',@row));
}
$g->finish();
return (@dbResult);
};

Subroutine syntax "open(OUTFILE, "">>"" . ""$ARGV[0]"") or die ""Cannot open Output file $ARGV[0]"";
printf OUTFILE ""\n"";
close(OUTFILE);"

While #!/usr/local/bin/perl
Open File Create connection to Oracle Db
Comparison to no system($Path . $script . " " . $file . " >> ". $Log. $yyyymmdd . " 2>&1 &"
Perl to fetch resultset
Add new line charater at the end of the file
First Line
Create connection to Oracle Db
Call a script in Perl

Tutorial Cheat Sheet

Shebang line is the first line starting with #!

Escape charaters

\n new line
\t tab
\r carriage return begin of the current line
\$ end of the current line
\"
\\
\'
\
, = concatenate
printf("Welcome to Perl \n"); = printf "Welcome to Perl \n";
chomp $num1 removes return character at the end
$ scalar variable
$number1 = ;

Arithmetic Operators

Addition
Operator Precedence (order of evaluation)
*/^ same level left to right for different operators

exponential Right to left
() left to right
+ - last
$c = +7

post and pre increment
$c++
++$c

Associativity of Operators (direction left to right or right to left)
relational operation
== equality = assignment
numeric & string content, and undef example
if add cannot be done no to a string & string cannot be converted into a number then its final result is 0
. Is used for concatenation

Perl Regular Expression
. Evaluates num into string

concatenation . , + tries to conv till cannot

Top10' does not start with no

10Top10' = 20 start with no

Perl is case sensitive

use strict

numeric context undef var = 0
string context undef var = empty

Control Structure

do while vs do until
To compare nos use = < >
To compare strings use eq lt gt

Perl is not a strict data type

ternary operator ? _:_
Arrays & Hashes
Define a array @
Refer individual element $ c[0] $c[1]
Array starts from index 0
@array = { 'sales', 283, "three", 16.439};
Creating and looping
assignment
$array[0] = "Happy";
printf "@array \n"
print "@array \n";
print @array, "\n";
qw operator
@array = qw (this is an array);
print "\n array \n";
@array = {2 .. 5};
$#
$array[$#array]
$# gives last index no gives 9
printf "There are" , scalar(@array), "elements in \@array \n";
gives 10

Negative subscript

$array [-4] 4th element from last
$# array= 4 reduce size to 4
$array = { }; blank out the array
array slices &
@array = qw (zero one two …… nine};
print "@array \n\n";
print "@array[1,3,5,7,9] \n";
{$chiquita , $dole } ={"banana", "pineapple"};
print "\$chiquita = $chiquita \n \$dole=$dole \n";
print "@array \n\n";
{$chiquita, $dole } = {$dole, $chiquita};
print "After swapping values \n";
print "\$chiquita = $chiquita \n \$dole=$dole \n";
push, pop, shift, unshift, functions (stack)
insert extract
begin last
push (@array, $i); push at the end
$elem = pop (@array); push the last element
shift unshift
begin begin
FIFO
1
12
1 2 3
1 2
1

LIFO
1
2 1
3 2 1
2 1
1

Resources : Perl Camel bks

Keywords
undef
? :
qw operator can take array values with spaces
..
push
pop
shift
unshift
scalar
$#
, . +
= eq
= ==

chomp
shebang
\
@array
$array

Splice function
to replace (part of an array)
@replaced = splice (@array , 5, scalar(@array2), @array2);
@removed = splice (@array, 15, 3);
@chopped = splice (@array, 8);
splice (@array);
unless (@array);
{print "\@array has no elements remaining \n"; }
sort array
@reversed = reverse (@array);
@array = {100,23, 9,75,5,10,2,50};
@sorted lexically = sort @array2;
@sortedNumerically = sort {$a < => $b } @array2;

Linear Search / Binary Search
Hash Create and Access hash elements (key value pairs)

%hash ={width =>'300'
height =>'150');
print "\$hash['width'] = $hash['width']\n"
hash are not interpretated when included in " "
print "%hash \n"
print %hash, "\n";
%hash = (one => "I", two => "II")
%hash ={"I", "II")

Using key value each reverse

@keys = keys (% presidents);
while ($key = pop(@key))
{print "$key => $presidents $key} \n";
@values = values(%presidents);
%hash = reverse(%presidents);
each extract key value pairs
while (($key , $value) = each (%presidents));
print "$key => $value \n";
heap = garpage collector
stack = till in scope
Using delete, exists,defined
delete ($hash('Joe')); don’t have to init index increment and terminating condi, perl uses it own index
search any collection

Control Structures

foreach $number ( @array ) manipulations
"foreach $name('Amanda', 'Jeff', 'Sarah')
print ""$name"";
print ""$_"";
print;
all 3 prints the same output"

push (@smallNumbers, $_) if $_ < 6;
@smallNumbers2 = grep( $_ < 6, @numbers) ; continue
push (@doubleNumbers, $_ * 2); goto
@doubledNumbers2 = map( $_ * 2, @numbers); break
foreach ( sort keys (%hash ) ) { print "$_\t", "*" x continue the outer loop without testing
for labels
next exit
next LABEL;
last
redo
"blocks
LABEL: for ( $num = 1; $num <= 10; ++$num ) { next LABLE if ( $num % 2 ==0 ); print ""$num ""; }"
die default scalar variable
subroutines and functions default array

Functions

sub subroutine1 {} shifts arguments
subroutine1 ()
print "$_[$i]"
@_ calling subroutine not defined before use
"displayarguments( ""Sam"", ""Jones"");
sub displayarguments
{
print ""All arguments : @_ \n"";
for
{print ""$_[$i]""}}" not allowed () are required
shift()
for (1 .. 10) { print square ($_), " "; } sub queue { $value = shift(); return $value ** 2; } function to generate a random number
return can return multiple values
&defineBeforeWithoutArguments(); global variable
&defineBeforeWithoutArguments; local, pass by reference
&defineBeforeWithArguments(1,2,3); local, pass by value
rand() packages
srand() eq to import in java, include in c, using c#
our package.variable
my
local
perl modules
require tilt
$main::variable
use strict;
use warnings;
regular expression also returns no of replacements
~ to find digit in the string
if $string =~ m/snow/;
=~ s/world/planet/; digit
same as s(world)(planet); word
s/world/planet/g; white space
/\d/ alpha numeric
/[0-9]/ any non word
\d any non white space
\w and
\s =
\D <>
\W
\S
&&
=~
!~
m/hello
hi there/ 0-1
m/(hello|hi) there/ 1 or more
s/1\d*1/22/; 0 or more
s/1\d+1/22/;
s/1\d?1/22/;
?
+
*
pattern*
pattern+
pattern? first look behind assertion (?<=i ) tests whether the string matched I right before it matched "be". If so, "be" is replaced with "amm"
pattern[n] x for comments with a pattern
pattern[n,m]
s/N.*here\.// escape character
s/N.*?here\.//
s/(?<=i )be/am/;
$string =~ s/x
I\'ve';2
\
substr()
lc() last character of the string
lcfirst()
uc()
ucfirst() concatenation
length() like cut
chop()
chomp vs chop
index()
join()
split(/ /, $string); Portable Operating System Interface [for Unix][1] is the name of a family of related standards specified by the IEEE to define the application programming interface (API), along with shell and utilities interfaces for software compatible with variants of the Unix operating system, although the standard can apply to any operating system.
File Processing using Perl qw returns quoted string '/ / / /'
@ARGV Use library
use POSIX;
use lib qw(/ / /);
use Date::XXX;
$var=$ARGV[0]; For debugging
$var2="$ARGV[0].txt";
open(OUTFILE, ">$var") or die "Can not open output file $var1";
perl -d

  1. Perl for Everything
  2. Perl Regular Expressions
  3. Regular Expression Cheat Sheet
Links
http://psoug.org/browse.htm?cid=6

http://www.dwhworld.com/2011/03/perl-script-to-read-a-config-file/
How can Perl split a line on whitespace except when the whitespace is in doublequotes?
http://refcards.com/docs/forda/perl-debugger/perl-debugger-refcard-a4.pdf
http://www.thegeekstuff.com/2010/05/perl-debugger/
http://www.troubleshooters.com/codecorn/littperl/perlreg.htm

Thursday, March 8, 2012

Data Mining KB

  1. Setting Up Oracle Data Miner 11g Release 2
  2. Using Oracle Data Miner 11g Release 2
  3. ODM 11gR2–Attribute Importance

    Data Mining

    Data mining uses large quantities of data to create models. These models can provide insights that are revealing, significant, and valuable. For example, data mining can be used to:
    • Predict those customers likely to change service providers.
    • Discover the factors involved with a disease.
    • Identify fraudulent behavior.
    Data mining is not restricted to solving business problems. For example, data mining can be used in the life sciences to discover gene and protein targets and to identify leads for new drugs.
    Oracle Data Mining performs data mining in the Oracle Database. Oracle Data Mining does not require data movement between the database and an external mining server, thereby eliminating redundancy, improving efficient data storage and processing, ensuring that up-to-date data is used, and maintaining data security.
    For detailed information about Oracle Data Mining, see Oracle Data Mining Concepts.

    Oracle Data Mining Functionality

    Oracle Data Mining supports the major data mining functions. There is at least one algorithm for each data mining function.
    Oracle Data Mining supports the following data mining functions:
    • Classification: Grouping items into discrete classes and predicting which class an item belongs to; classification algorithms are Decision Tree, Naive Bayes, Generalized Linear Models (Binary Logistic Regression), and Support Vector Machines.
    • Regression: Approximating and predicting continuous numerical values; the algorithms for regression are Support Vector Machines and Generalized Linear Models (Multivariate Linear Regression).
    • Anomaly Detection: Detecting anomalous cases, such as fraud and intrusions; the algorithm for anomaly detection is one-class Support Vector Machines.
    • Attribute Importance: Identifying the attributes that have the strongest relationships with the target attribute (for example, customers likely to churn); the algorithm for attribute importance is Minimum Descriptor Length.
    • Clustering: Finding natural groupings in the data that are often used for identifying customer segments; the algorithms for clustering are k-Means and O-Cluster.
    • Associations: Analyzing "market baskets", items that are likely to be purchased together; the algorithm for associations is a priori.
    • Feature Extraction: Creating new attributes (features) as a combination of the original attributes; the algorithm for feature extraction is Non-Negative Matrix Factorization.
    In addition to mining structured data, ODM permits mining of text data (such as police reports, customer comments, or physician's notes) or spatial data.

    Oracle Data Mining Interfaces

    Oracle Data Mining APIs provide extensive support for building applications that automate the extraction and dissemination of data mining insights.
    Data mining activities such as model building, testing, and scoring are accomplished through a PL/SQL API, a Java API, and SQL Data Mining functions. The Java API is compliant with the data mining standard JSR 73. The Java API and the PL/SQL API are fully interoperable.
    Oracle Data Mining allows the creation of a supermodel, that is, a model that contains the instructions for its own data preparation. The embedded data preparation can be implemented automatically and/or manually. Embedded Data Preparation supports user-specified data transformations; Automatic Data Preparation supports algorithm-required data preparation, such as binning, normalization, and outlier treatment.
    SQL Data Mining functions support the scoring of classification, regression, clustering, and feature extraction models. Within the context of standard SQL statements, pre-created models can be applied to new data and the results returned for further processing, just like any other SQL query.
    Predictive Analytics automates the process of data mining. Without user intervention, Predictive Analytics routines manage data preparation, algorithm selection, model building, and model scoring so that the user can benefit from data mining without having to be a data mining expert.
    ODM programmatic interfaces include
    • Data mining functions in Oracle SQL for high performance scoring of data
    • DBMS_DATA_MINING PL/SQL packages for model creation, description, analysis, and deployment
    • DBMS_DATA_MINING_TRANSFORM PL/SQL package for transformations required for data mining
    • Java interface based on the Java Data Mining standard for model creation, description, analysis, and deployment
    • DBMS_PREDICTIVE_ANALYTICS PL/SQL package supports the following procedures:
      • EXPLAIN - Ranks attributes in order of influence in explaining a target column
      • PREDICT - Predicts the value of a target column
      • PROFILE - Creates segments and rules that identify the records that have the same target value

      Reference Oracle.com
OLAP tools


OLAP tools are widely used to analyze information from different perspectives and provide functions like drill-down, slice-and-dice and the vendors that sell OLAP tools promise a very high performance. In the table below a list of the OLAP & reporting tools is presented which are included in our 100% vendor independent BI & OLAP tool survey 2012, a comparison made on 103 criteria.

OLAP, ROLAP & MOLAP

OLAP is an abbreviation for On-line Analytical Processing and contains a multidimensional or relational datastore designed to provide quick access to pre-summarized data & multidimensional analysis. There are three flavours:

MOLAP: Multidimensional OLAP – enabling OLAP by provding cubes.

ROLAP: Relational OLAP – enabling OLAP using a relational database management system.

DOLAP: Desktop OLAP – enabling OLAP functionality on the local computer (simulation); DOLAP products do not contain an OLAP server.

List of reporting & OLAP tools

The following products which include OLAP tools were thoroughly examined on 103 criteria considered important for high productivity and reporting systems that actually add value to your organization. The OLAP tools are listed in random order.

Reporting & OLAP tools Version Vendor OLAP included?

Oracle Enterprise BI Server 11g1 Oracle
Microsoft BI & OLAP tools 2008/2010 Microsoft
IBM Cognos Series 10 10.1 IBM
QlikView 11 QlikTech
Board Management IntelligenceToolkit 7.1 Board International
Oracle Hyperion System 9 Oracle
SAP NetWeaver BI 7.3 SAP
Microstrategy 9 Microstrategy
SAP Business Objects Enterprise XI r4 SAP
SAS Enterprise BI Server 9.2 SAS Institute
BizzScore Suite 7.3 EFM Software
WebFOCUS 8 Information Builders
JasperSoft (open source) 4.5 JasperSoft
Style Intelligence 11 InetSoft
Pentaho BI suite (open source) 4 Pentaho
Tableau Software 6.1 Tableau Software

Links

http://apandre.wordpress.com/tools/comparison/

http://qlikviewvsolap.blogspot.com/2009/06/qlikview-vs-sas.html

http://mydatamine.com/?p=751

http://www.the-data-mine.com/Software/MostPopularDataMiningSoftware

http://www.salford-systems.com/doc/elder.pdf

http://www.cs.uvm.edu/~icdm/algorithms/10Algorithms-08.pdf