Blue Spur - my online notepad: 2011

Friday, December 2, 2011

Find out web server port number

select substr(CONNURL,regexp_instr(CONNURL,':',1,2,2),4),CONNURL from PSGATEWAY

if same as report server:

select substr(URL,regexp_instr(URL,':',1,2,2),4) from PS_CDM_DIST_NODE

useful if single sign-on on same server to multiple dbs, just change url on address bar.

Thursday, September 29, 2011

Regular Expressions

http://download.oracle.com/javase/1.4.2/docs/api/java/util/regex/Pattern.html

Construct	Matches

Characters
x	The character x
`\\`	The backslash character
`\0`n	The character with octal value `0`n (0 `<=` n `<=` 7)
`\0`nn	The character with octal value `0`nn (0 `<=` n `<=` 7)
`\0`mnn	The character with octal value `0`mnn (0 `<=` m `<=` 3, 0 `<=` n `<=` 7)
`\x`hh	The character with hexadecimal value `0x`hh
`\u`hhhh	The character with hexadecimal value `0x`hhhh
`\t`	The tab character (`'\u0009'`)
`\n`	The newline (line feed) character (`'\u000A'`)
`\r`	The carriage-return character (`'\u000D'`)
`\f`	The form-feed character (`'\u000C'`)
`\a`	The alert (bell) character (`'\u0007'`)
`\e`	The escape character (`'\u001B'`)
`\c`x	The control character corresponding to x

Character classes
`[abc]`	`a`, `b`, or `c` (simple class)
`[^abc]`	Any character except `a`, `b`, or `c` (negation)
`[a-zA-Z]`	`a` through `z` or `A` through `Z`, inclusive (range)
`[a-d[m-p]]`	`a` through `d`, or `m` through `p`: `[a-dm-p]` (union)
`[a-z&&[def]]`	`d`, `e`, or `f` (intersection)
`[a-z&&[^bc]]`	`a` through `z`, except for `b` and `c`: `[ad-z]` (subtraction)
`[a-z&&[^m-p]]`	`a` through `z`, and not `m` through `p`: `[a-lq-z]`(subtraction)

Predefined character classes
`.`	Any character (may or may not match line terminators)
`\d`	A digit: `[0-9]`
`\D`	A non-digit: `[^0-9]`
`\s`	A whitespace character: `[ \t\n\x0B\f\r]`
`\S`	A non-whitespace character: `[^\s]`
`\w`	A word character: `[a-zA-Z_0-9]`
`\W`	A non-word character: `[^\w]`

POSIX character classes (US-ASCII only)
`\p{Lower}`	A lower-case alphabetic character: `[a-z]`
`\p{Upper}`	An upper-case alphabetic character:`[A-Z]`
`\p{ASCII}`	All ASCII:`[\x00-\x7F]`
`\p{Alpha}`	An alphabetic character:`[\p{Lower}\p{Upper}]`
`\p{Digit}`	A decimal digit: `[0-9]`
`\p{Alnum}`	An alphanumeric character:`[\p{Alpha}\p{Digit}]`
`\p{Punct}`	Punctuation: One of !"#$%&'()*+,-./:;<=>?@[\]^_`{\|}~
`\p{Graph}`	A visible character: `[\p{Alnum}\p{Punct}]`
`\p{Print}`	A printable character: `[\p{Graph}]`
`\p{Blank}`	A space or a tab: `[ \t]`
`\p{Cntrl}`	A control character: `[\x00-\x1F\x7F]`
`\p{XDigit}`	A hexadecimal digit: `[0-9a-fA-F]`
`\p{Space}`	A whitespace character: `[ \t\n\x0B\f\r]`

Classes for Unicode blocks and categories
`\p{InGreek}`	A character in the Greek block (simple block)
`\p{Lu}`	An uppercase letter (simple category)
`\p{Sc}`	A currency symbol
`\P{InGreek}`	Any character except one in the Greek block (negation)
`[\p{L}&&[^\p{Lu}]]`	Any letter except an uppercase letter (subtraction)

Boundary matchers
`^`	The beginning of a line
`$`	The end of a line
`\b`	A word boundary
`\B`	A non-word boundary
`\A`	The beginning of the input
`\G`	The end of the previous match
`\Z`	The end of the input but for the final terminator, if any
`\z`	The end of the input

Greedy quantifiers
X`?`	X, once or not at all
X`*`	X, zero or more times
X`+`	X, one or more times
X`{`n`}`	X, exactly n times
X`{`n`,}`	X, at least n times
X`{`n`,`m`}`	X, at least n but not more than m times

Reluctant quantifiers
X`??`	X, once or not at all
X`*?`	X, zero or more times
X`+?`	X, one or more times
X`{`n`}?`	X, exactly n times
X`{`n`,}?`	X, at least n times
X`{`n`,`m`}?`	X, at least n but not more than m times

Possessive quantifiers
X`?+`	X, once or not at all
X`*+`	X, zero or more times
X`++`	X, one or more times
X`{`n`}+`	X, exactly n times
X`{`n`,}+`	X, at least n times
X`{`n`,`m`}+`	X, at least n but not more than m times

Logical operators
XY	X followed by Y
X`\|`Y	Either X or Y
`(`X`)`	X, as a capturing group

Back references
`\`n	Whatever the n^th capturing group matched

Quotation
`\`	Nothing, but quotes the following character
`\Q`	Nothing, but quotes all characters until `\E`
`\E`	Nothing, but ends quoting started by `\Q`

Special constructs (non-capturing)
`(?:`X`)`	X, as a non-capturing group
`(?idmsux-idmsux)`	Nothing, but turns match flags on - off
`(?idmsux-idmsux:`X`)`	X, as a non-capturing group with the given flags on - off
`(?=`X`)`	X, via zero-width positive lookahead
`(?!`X`)`	X, via zero-width negative lookahead
`(?<=`X`)`	X, via zero-width positive lookbehind
`(?X)`	X, via zero-width negative lookbehind
`(?>`X`)`	X, as an independent, non-capturing group

Backslashes, escapes, and quoting

The backslash character ('\') serves to introduce escaped constructs, as defined in the table above, as well as to quote characters that otherwise would be interpreted as unescaped constructs. Thus the expression \\ matches a single backslash and \{ matches a left brace.

It is an error to use a backslash prior to any alphabetic character that does not denote an escaped construct; these are reserved for future extensions to the regular-expression language. A backslash may be used prior to a non-alphabetic character regardless of whether that character is part of an unescaped construct.

Backslashes within string literals in Java source code are interpreted as required by the Java Language Specification as either Unicode escapes or other character escapes. It is therefore necessary to double backslashes in string literals that represent regular expressions to protect them from interpretation by the Java bytecode compiler. The string literal "\b", for example, matches a single backspace character when interpreted as a regular expression, while "\\b" matches a word boundary. The string literal "$hello$" is illegal and leads to a compile-time error; in order to match the string (hello) the string literal "\$hello\$" must be used.

Character Classes

Character classes may appear within other character classes, and may be composed by the union operator (implicit) and the intersection operator (&&). The union operator denotes a class that contains every character that is in at least one of its operand classes. The intersection operator denotes a class that contains every character that is in both of its operand classes.

The precedence of character-class operators is as follows, from highest to lowest:

1 Literal escape \x

2 Grouping [...]

3 Range a-z

4 Union [a-e][i-u]

5 Intersection [a-z&&[aeiou]]

1	Literal escape	`\x`
2	Grouping	`[...]`
3	Range	`a-z`
4	Union	`[a-e][i-u]`
5	Intersection	`[a-z&&[aeiou]]`

Note that a different set of metacharacters are in effect inside a character class than outside a character class. For instance, the regular expression . loses its special meaning inside a character class, while the expression - becomes a range forming metacharacter.

Line terminators

A line terminator is a one- or two-character sequence that marks the end of a line of the input character sequence. The following are recognized as line terminators:

A newline (line feed) character ('\n'),
A carriage-return character followed immediately by a newline character ("\r\n"),
A standalone carriage-return character ('\r'),
A next-line character ('\u0085'),
A line-separator character ('\u2028'), or
A paragraph-separator character ('\u2029).

If UNIX_LINES mode is activated, then the only line terminators recognized are newline characters.

The regular expression . matches any character except a line terminator unless the DOTALL flag is specified.

By default, the regular expressions ^ and $ ignore line terminators and only match at the beginning and the end, respectively, of the entire input sequence. If MULTILINE mode is activated then ^ matches at the beginning of input and after any line terminator except at the end of input. When in MULTILINE mode $ matches just before a line terminator or the end of the input sequence.

Groups and capturing

Capturing groups are numbered by counting their opening parentheses from left to right. In the expression ((A)(B(C))), for example, there are four such groups:

1 ((A)(B(C)))

2 (A)

3 (B(C))

4 (C)

1	`((A)(B(C)))`
2	`(A)`
3	`(B(C))`
4	`(C)`

Group zero always stands for the entire expression.

Capturing groups are so named because, during a match, each subsequence of the input sequence that matches such a group is saved. The captured subsequence may be used later in the expression, via a back reference, and may also be retrieved from the matcher once the match operation is complete.

The captured input associated with a group is always the subsequence that the group most recently matched. If a group is evaluated a second time because of quantification then its previously-captured value, if any, will be retained if the second evaluation fails. Matching the string "aba" against the expression (a(b)?)+, for example, leaves group two set to "b". All captured input is discarded at the beginning of each match.

Groups beginning with (? are pure, non-capturing groups that do not capture text and do not count towards the group total.

Use connect by level to parse a string with delimiter

select regexp_substr('a:sd:dfg:31456:dasd: :sdfsdf', '[^:]+',1,level) element
from dual
connect by level <= length(regexp_replace('a:sd:dfg:31456:dasd: :sdfsdf','[^:]+')) + 1

Wednesday, September 28, 2011

Change Firefox icon

http://www.aqua-soft.org/forum/topic/28029-help-change-firefox-taskbar-icon/

"There's another way to do this;

1. Go to the folder you installed Firefox in (e.g. C:Program FilesMozilla Firefox) and then go to the subfolder chrome.
2. While in chrome folder, create a new subfolder called ''icons'', then go to that folder and create yet another subfolder called ''default''. The full path to this folder could be C:Program FilesMozilla Firefoxchromeiconsdefault.
3. Choose the icon you want to use and place it in the ''default'' folder and rename the icon to ''main-window', e.g. main-window.ico on Windows and main-window.xpm on Linux.

In addition to the main window, you can also change the icon on the Bookmark Manager and JavaScript Console. The icon names are ''bookmark-window''.[ext] and ''jsconsoleWindow''.[ext], respectively.

After this is done, restart Firefox and you should be able to see your chosen icon in the title of the browser windows."

Saturday, August 27, 2011

Change Yahoo Mail links 2

Another round of YM changes -

Wednesday, July 20, 2011

EDI File Parser

A utility to quickly parse and validate EDI file. Support for direct file path or EC Run Control.
This example uses delivered VCHR definition.

Note: assumed some field positions here, best is to read Map for field position

#!/bin/ksh93

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# EDI Pattern Matching script - parse / check EDI files
#
# Env: Redhat Linux/ksh
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
scr=$(basename $0)
sqf=/tmp/"$scr"_$$.sql # temp sql file
spool=/tmp/"$scr"_$$.lst # sqlplus spool file

# cleanup at process end
trap "rm $sqf 2>/dev/null;rm $spool 2>/dev/null; rm $0.$$ 2>/dev/null;" 0
trap "echo Process Cancelled; exit 1" 1 2 3 15

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# execute a SQL statement - output stored in $rc & spool file
#
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
execSQL() {

echo "set pagesize 0 feedback off echo off heading off verify off linesize 200;" >$sqf
echo "define SPOOL_FILE='$spool'" >>$sqf
echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" >>$sqf
echo "WHENEVER OSERROR EXIT 1" >>$sqf
echo "spool &SPOOL_FILE" >>$sqf
echo "$sql" >>$sqf
echo "/" >>$sqf
echo "spool off" >>$sqf
echo "quit" >>$sqf

$ORACLE_HOME/bin/sqlplus -s $usr/$pwd @$sqf > /dev/null

if [ $? -ne 0 ]; then
# error running sql

echo 'Error running SQL statement:'$sql

if [ -f $spool ]; then
cat $spool
fi

exit 1

fi

return 0

}

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# parseEDI - parse EDI file
#
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
function parseEDI
{

if [ $# -ne 1 ]; then
echo 'Error: parseEDI() input file missing'
return 1
fi

input_file=$1

awk -v dict="$spool" -v vbose=$vbose -v debug=$debug -v hdr=$hdr '

function ltrim(s) { sub(/^[ \t]+/, "", s); return s }
function rtrim(s) { sub(/[ \t]+$/, "", s); return s }
function trim(s) { return rtrim(ltrim(s)); }

BEGIN {
i1=0;
i2=0;
i3=0;
i4=0;
i5=0;
c1=0;
c2=0;
c3=0;
c4=0;
c5=0;

while ((getline < dict ) > 0)
{
if ( match(substr($1,1,3),"000"))
{
vh_no[i1] = $2;
vh_name[i1] = $3;
vh_beg[i1] = $4;
vh_len[i1++] = $5;
}
else
if ( match(substr($1,1,3),"001"))
{
vl_no[i2] = $2;
vl_name[i2] = $3;
vl_beg[i2] = $4;
vl_len[i2++] = $5;
}
else
if ( match(substr($1,1,3),"002"))
{
dl_no[i3] = $2;
dl_name[i3] = $3;
dl_beg[i3] = $4;
dl_len[i3++] = $5;
}
else
if ( match(substr($1,1,3),"003"))
{
xl_no[i4] = $2;
xl_name[i4] = $3;
xl_beg[i4] = $4;
xl_len[i4++] = $5;
}
else
if ( match(substr($1,1,3),"00V"))
{
vn_no[i5] = $2;
vn_name[i5] = $3;
vn_beg[i5] = $4;
vn_len[i5++] = $5;
};
}
close(dict)

}

{
if ( match(substr($1,1,3),"000"))
{
print "==============================================================================#"NR" (hdr)";
if (vbose==1)
for (idx=0; idx< i1;idx++ ) printf("%-20s(%04d-%04d)=%s.\n", vh_name[idx],vh_beg[idx],vh_beg[idx]+vh_len[idx]-1,substr($0,vh_beg[idx],vh_len[idx]))
else
if (debug==1)
for (idx=0; idx< i1;idx++ ) {if (trim(substr($0,vh_beg[idx],vh_len[idx]))!="") printf("%d:%-20s(%04d-%04d)=%s.\n",NR,vh_name[idx], vh_beg[idx],vh_beg[idx]+vh_len[idx]-1, substr($0,vh_beg[idx],vh_len[idx]))}
else
for (idx=0; idx< i1;idx++ ) if (trim(substr($0,vh_beg[idx],vh_len[idx]))!="") printf("%-20s=%s.\n",vh_name[idx], substr($0,vh_beg[idx],vh_len[idx]));
c1++;
}
else
if ( hdr==0 && match(substr($1,1,3),"001"))
{
print "------------------------------------------------------------------------------#"NR" (line)";
if (vbose==1)
for (idx=0; idx< i2;idx++ ) printf("%-20s(%04d-%04d)=%s.\n",vl_name[idx],vl_beg[idx],vl_beg[idx]+vl_len[idx]-1,substr($0,vl_beg[idx],vl_len[idx]))
else
if (debug==1)
for (idx=0; idx< i2;idx++ ) {if (trim(substr($0,vl_beg[idx],vl_len[idx]))!="") printf("%d:%-20s(%04d-%04d)=%s.\n",NR,vl_name[idx],vl_beg[idx],vl_beg[idx]+vl_len[idx]-1, substr($0,vl_beg[idx],vl_len[idx]))}
else
for (idx=0; idx< i2;idx++ ) if (trim(substr($0,vl_beg[idx],vl_len[idx]))!="") printf("%-20s=%s.\n",vl_name[idx],substr($0,vl_beg[idx],vl_len[idx]));
c2++;
}
else
if ( hdr==0 && match(substr($1,1,3),"002"))
{
print "------------------------------------------------------------------------------#"NR" (dist)";
if (vbose==1)
for (idx=0; idx< i3;idx++ ) printf("%-20s(%04d-%04d)=%s.\n",dl_name[idx],dl_beg[idx],dl_beg[idx]+dl_len[idx]-1,substr($0,dl_beg[idx],dl_len[idx]))
else
if (debug==1)
for (idx=0; idx< i3;idx++ ) {if (trim(substr($0,dl_beg[idx],dl_len[idx]))!="") printf("%d:%-20s(%04d-%04d)=%s.\n",NR,dl_name[idx],dl_beg[idx],dl_beg[idx]+dl_len[idx]-1, substr($0,dl_beg[idx],dl_len[idx]))}
else
for (idx=0; idx< i3;idx++ ) if (trim(substr($0,dl_beg[idx],dl_len[idx]))!="") printf("%-20s=%s.\n",dl_name[idx],substr($0,dl_beg[idx],dl_len[idx]));
c3++;
}
else
if ( hdr==0 && match(substr($1,1,3),"003"))
{
print "------------------------------------------------------------------------------#"NR" (dlt)";
if (vbose==1)
for (idx=0; idx< i4;idx++ ) printf("%-20s(%04d-%04d)=%s.\n",xl_name[idx],xl_beg[idx],xl_beg[idx]+xl_len[idx]-1,substr($0,xl_beg[idx],xl_len[idx]))
else
if (debug==1)
for (idx=0; idx< i4;idx++ ){if (trim(substr($0,xl_beg[idx],xl_len[idx]))!="") printf("%d:%-20s(%04d-%04d)=%s.\n",NR, xl_name[idx],xl_beg[idx],xl_beg[idx]+xl_len[idx]-1,substr($0,xl_beg[idx],xl_len[idx]))}
else
for (idx=0; idx< i4;idx++ ) if (trim(substr($0,xl_beg[idx],xl_len[idx]))!="") printf("%-20s=%s.\n",xl_name[idx],substr($0,xl_beg[idx],xl_len[idx]));
c4++;
}
if ( hdr==0 && match(substr($1,1,3),"00V"))
{
print "------------------------------------------------------------------------------#"NR" (vndr)";
if (vbose==1)
for (idx=0; idx< i5;idx++ ) printf("%-20s(%04d-%04d)=%s.\n",vn_name[idx],vn_beg[idx],vn_beg[idx]+vn_len[idx]-1,substr($0,vn_beg[idx],vn_len[idx]))
else
if (debug==1)
for (idx=0; idx< i5;idx++ ){if (trim(substr($0,vn_beg[idx],vn_len[idx]))!="") printf("%d:%-20s(%04d-%04d)=%s.\n",NR, vn_name[idx],vn_beg[idx],vn_beg[idx]+vn_len[idx]-1,substr($0,vn_beg[idx],vn_len[idx]))}
else
for (idx=0; idx< i5;idx++ ) if (trim(substr($0,vn_beg[idx],vn_len[idx]))!="") printf("%-20s=%s.\n",vn_name[idx],substr($0,vn_beg[idx],vn_len[idx]));
c5++;
};

}

END{
print "==============================================================================";
print "Hdr=",c1;
print "Line=",c2;
print "Dist=",c3;
if (c5>0) print "Vndr=",c4;
if (c4>0) print "Xdlt=",c4;
print "total=" c1+c2+c3+c4+c5;
}

' $input_file

}

function chkEDI
{
if [ $# -ne 1 ]; then
echo "Error: $0 missing EDI file name"
return 1
fi

chk_file=$1
echo "File: $chk_file (EC Map='$map_id)"

# check non-Ascii
echo "$indent Checking Non-Ascii..."

grep -qP "[\x80-\xFF]" $chk_file

if [ $? -eq 0 ]; then

# store out AWK results in var
read cnt1 <<< $(grep -nP "[\x80-\xFF]" $chk_file | awk -F\: '{printf "#%d.", $1} END{print x" "}'| tee /dev/tty)

echo "$indent $indent Error non-ascii found on above line(s). Total: "$(echo $cnt1 | grep -o \# |wc -l)
return 1
fi

# check ROWID 000-003
echo "$indent Checking ROWID..."

# get valid EC ID from map spool & create expression for grep command

cut -c1-3 $chk_file | grep -v $(cut -c1-3 $spool|sort|uniq|sed 's/^/-e /' | tr '\n' ' ') | sort |uniq -c > $0.$$

if [ -s $0.$$ ]; then

(( cnt1 = 0));

while read cnt lvl; do
echo -n "$indent $lvl($cnt): "
grep -n ^"$lvl" $chk_file | awk -F\: '{printf "#%d.", $1} END{print " "}'
(( cnt1 = cnt1 + 1 ))
done < $0.$$

echo "$indent $indent Error: invalid ROWID found on above line(s). Total Count: $cnt1"
return 1
fi

# check dups
echo "$indent Checking Dups (vid/vln/dln)..."

# check dup vid
grep ^000 $chk_file | cut -c9-16 | sort | uniq -c | grep -v "^ \{5\} 1 " > $0.$$

if [ -s $0.$$ ]; then

(( cnt1 = 0));

while read cnt vid; do
echo -n "$indent $vid($cnt): "
grep -n ^000.....$vid $chk_file | awk -F\: -v vid=$vid '{printf "#%d.", $1} END{print " "}'
(( cnt1 = cnt1 + 1 ))
done < $0.$$

echo "$indent Error: duplicate VID(s) found on above line(s). Total Count: $cnt1"
return 1

fi

# check dup vchr ln
grep ^001 $chk_file | cut -c9-16,17-21 --output-delimiter=" " | sort | uniq -c | grep -v "^ \{5\} 1 " > $0.$$

if [ -s $0.$$ ]; then

(( cnt1 = 0));

while read cnt vid vln; do
echo -n "$indent $indent $vid $vln ($cnt): "
grep -n ^001....."$vid$vln" $chk_file | awk -F\: -v vid=$vid -v vln=$vln '{printf "#%d.", $1} END{print " "}'
(( cnt1 = cnt1 + 1 ))
done < $0.$$

echo "$indent Error: duplicate Vchr Line(s) found on above line(s). Total Count: $cnt1"
return 1
fi

# check dup distrib ln
grep ^002 $chk_file | cut -c9-16,17-21,22-26 --output-delimiter=" " | sort | uniq -c | grep -v "^ \{5\} 1 " > $0.$$

if [ -s $0.$$ ]; then

(( cnt1 = 0));

while read cnt vid vln dst; do
echo -n "$indent $indent $vid $vln $dst ($cnt): "
grep -n ^002....."$vid$vln$dst" $chk_file | awk -F\: -v vid=$vid -v vln=$vln '{printf "#%d.", $1} END{print " "}'
(( cnt1 = cnt1 + 1 ))
done < $0.$$

echo "$indent Error: duplicate Distrib Line(s) found on above line(s). Total Count: $cnt1"
return 1
fi

#check dates
echo "$indent Checking dates..."

parseEDI $chk_file | grep -e '_DATE ' -e '_DT ' | tr '[:=.]' ' ' >$0.$$

dateErr=0
(( cnt1 = 0));

while read recno fld pos dt; do

bad=1

if [[ $dt = 20{2}([0-9])/[0-1][0-9]/[0-3][0-9] ]] && [[ $(date -d "$dt" >/dev/null 2>&1; echo $?) -eq 0 ]]; then
bad=0
fi

if [ $bad -eq 1 ]; then
echo "$indent $indent #"$recno" "$fld" "$pos" "$dt
dateErr=1
(( cnt1 = cnt1 + 1 ))
fi

done < $0.$$

if [ $dateErr -eq 1 ]; then
echo "$indent $indent Error: invalid dates found on above line(s). Count: $cnt1"
return 1
fi

#check EOL
echo "$indent Checking EOL..."

cnt1=$(tail -1 $chk_file | wc -l)

if [ $cnt1 -ne 1 ]; then
echo "$indent $indent Error: EOL missing"
return 1
fi
}
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# usage - display usage info
#
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
function usage {

cat <<EOF
EDI file parser
Usage: $scr [-r run_control ] [-u user ] [-v] edi_file
-a: check combined file
-d: check file
-h: header only
-m: Map ID
-r: PS Run Control
-u: PS User
-v: verbose mode
EOF

exit 1
}

####################################################################
map_id=''
typeset -i vbose=0
typeset -u user='BATCH'
typeset -i debug=0
typeset -i hdr=0;
typeset -i all_files=0
runcntl=""
indent=" ";

while getopts :dhm:r:u:v args
do
case $args in

d) debug=1
;;

h) hdr=1
;;

m) map_id="$OPTARG"
;;

r) runcntl="$OPTARG"
;;

u) user="$OPTARG"
;;

v) vbose="1"
;;

?) echo 'Invalid option: '$OPTARG
usage
;;

esac
done

shift $(($OPTIND - 1))

# must specify edi file name or a run control
if [ ${runcntl:=x} = "x" ] && [ $# = 0 ]; then
usage
fi

# get user id & pw
usr=$(ora_user)
pwd=$(ora_pwd)

edi_file=$1

if [ ${runcntl:=x} != "x" ]; then
# get file path & map id
sql="select trim(ECONEFILEPATH)||trim(ECONEFILENAME)||','||trim(ECFORCETRANS) from ps_ECRUNCNTL where oprid='"$user"' and RUN_CNTL_ID='"$runcntl"'"
execSQL

if [ ! -s $spool ]; then
echo 'Cannot find EC Run Control for PS User '$user' Run Control '$runcntl
exit 1
fi

edi_file=`awk -F, '{print $1}' $spool`

if [ ! -s $edi_file ]; then
echo 'EDI file '$edi_file' not found'
exit 1
fi

sql="select trim(ECMAPID) from ps_ECMAPDEFN where ECTRANSID=trim('"`awk -F, '{print $2}' $spool`"')"
execSQL

map_id=`awk '{print $1}' $spool`

else
if [ ! -f $edi_file ]; then
echo 'EDI file '$edi_file' not found'
exit 1
fi
fi

if [ ${map_id:-x} = 'x' ]; then

tag=`head -1 $edi_file | cut -c1-3`

if [ $tag = '999' ]; then

sql="select trim(ECMAPID) from ps_ECMAPDEFN where ECTRANSID=trim('"`head -1 $edi_file | cut -c4-18`"')"
execSQL

map_id=`awk '{print $1}' $spool`

else
if [ $tag = '000' ]; then

# vchr type offset & length
sql="select ECSTARTPOS||'-'|| (ECSTARTPOS+ LENGTH-1) from ps_ECINMAPFLD where ECMAPID = 'AVCHRDEFN' and ECFILEROWID='000' and FIELDNAME='TX_VOUCHER_TYPE'"
execSQL

vt_pos=$(cat $spool)

vchr_type=`head -1 $edi_file | cut -c$vt_pos`

if [ "$vchr_type" = 'SPV ' ]; then
map_id='ASPVDEFN'
else
if [ "$vchr_type" = 'SVV ' -o "$vchr_type" = 'MVV ' ] ; then
map_id='AVCHRDEFN'
fi
fi
fi
fi
fi

if [ ${map_id:-x} = 'x' ]; then
echo 'Cannot get EC Map ID'
exit 1
fi

if [ ${runcntl:=x} != "x" ]; then
echo 'EC Map='$map_id' (file='$edi_file')'
#else
#echo "EDI file: $edi_file (EC Map='$map_id)"
fi

# get EC Map in spool file
#sql="select (select ECROWIDVALUE from ps_ECINMAPFLD where ECMAPID=m1.ECMAPID and ECFILEROWID=m1.ECFILEROWID and seqno=1) ECFILEROWID ,seqno,FIELDNAME, ECSTARTPOS,length,ECSTARTPOS+length-1 from ps_ECINMAPFLD m1 where ECMAPID='"11$map_id"' order by 1,2"
sql="select ECFILEROWID ,seqno,FIELDNAME, ECSTARTPOS,length,ECSTARTPOS+length-1 from ps_ECINMAPFLD m1 where ECMAPID='"$map_id"' order by 1,2"
execSQL

# parse or check option
if [ $debug -eq 1 ]; then

#disable hdr mode - check all levels
hdr=0;
list="";
err=0;

# check combined file or individual file?

if [ $2'x' != 'x' ]; then
list="$2*"

for file in $list; do
chkEDI $file

if [ $? -ne 0 ]; then
err=1
fi
done

if [ $err -eq 0 ]; then

echo 'File: '$edi_file' (combined)'
echo "$indent Checking dups ..."

# get vchr id count
grep ^000 $edi_file | cut -c9-16 | sort | uniq -c | grep -v "^ \{5\} 1 " > $0.$$

if [ -s $0.$$ ]; then
(( cnt1 = 0));

while read cnt vid; do
echo -n "$indent "$vid": "

# find vid in original file
grep -n ^000.....$vid $list | awk -F":" '{if (NR >1) printf ", "; printf "#"$2" ("$1")"}'
echo " "

(( cnt1 = cnt1 + 1 ))

done < $0.$$

echo "$indent Error: duplicate VID(s) found on above line(s). Count: $cnt1"
fi
fi
else
chkEDI $edi_file
fi
else
echo EC Map=$map_id
parseEDI $edi_file
fi

Thursday, June 9, 2011

Network Monitoring, packet analysis tool

http://www.wireshark.org/download.html

Tuesday, May 24, 2011

Oracle tools for SQL Optimizer and Execution Plans

Oracle SQL Optimizer and Execution Plan details:

SQL Trace 10046

SQL*Plus "set autotrace"

Extended execution plan statistics (v$sql_workarea)

The gather_plan_statistics hint (or statistics_level=all)

Select /*+ gather_plan_statistics */
* From xxx where....

Select * From v$sql Order By last_active_time desc

Select * From Table(dbms_xplan.display_cursor('77w1cgpppqu79','0','allstats +peeked_binds'))

Select * From Table(dbms_xplan.display_cursor(Null, Null, 'ALLSTATS LAST'));

Oracle Ref: http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams044.htm#REFRN10028

alter session set events 'immediate trace name flush_cache';

```
alter system flush buffer_cache;
```
```
alter system flush shared_pool;
```
```
ALTER TABLESPACE  OFFLINE;
```
```
ALTER TABLESPACE  ONLINE;
```
v$sql_optimizer_env - cursor in the SGA
v$ses_optimizer_env - session
v$sys_optimizer_env - system

select level n from dual connect by level <= 10000;

Great explanation about Consistent Gets & DB Block Gets: http://mwidlake.wordpress.com/2009/06/02/what-are-consistent-gets/

Friday, January 7, 2011

Joomla

Using the technique described below you can add a Super Administrator if you have access to Joomla DB:

http://www.instantphp.com/news/37-tips-and-tricks/76-recover-super-administrator-password-of-joomla-10x-and-15x.html

Blue Spur - my online notepad

Pages