Blue Spur - my online notepad

2012-01-19T08:56:00.000-08:00

Files related....

fuser: Identifies processes using a file or file structure
whodo: Lists the jobs being performed by users on the system.
procfiles:list symbols from object files

ar: list library contents (.a)
nm: list symbols from object files (.a, .so, .o)
ldd: Lists dynamic dependencies (XCOFF) ($LD_LIBRARY_PATH)
what: Displays identifying information in files. (.a, .so, .o, XCOFF), work with GET/SCCS

Find out web server port number

2011-12-02T10:01:00.000-08:00

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.

Regular Expressions

2011-09-29T08:45:00.001-07:00

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.

Change Firefox icon

2011-09-28T15:13:00.000-07:00

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."

Change Yahoo Mail links 2

2011-08-27T13:33:00.000-07:00

Another round of YM changes -

EDI File Parser

2011-07-20T08:41:00.000-07:00

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

Network Monitoring, packet analysis tool

2011-06-09T09:38:00.000-07:00

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

Oracle tools for SQL Optimizer and Execution Plans

2011-05-24T13:55:00.000-07:00

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/

Joomla

2011-01-07T20:52:00.000-08:00

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

Change Yahoo Mail links

2010-12-16T10:13:00.000-08:00

Recently Yahoo Mail replaced the My Yahoo link with Games on its home screen:

This is a headache because I dont play any web Games & had a lot of links under My Yahoo. Using Greasemonkey on my Firefox to tweak it back and put up a host of the most oft-used links there. Thanks YM for the idea - now the Mail screen is my time-saving launch pad:

Code:
// ********************************** 12/16/10 - changed Games link back to My Yahoo
var xx=document.getElementById("_test_my_yahoo");
if (xx != null)
{
xx.setAttribute("href","http://my.yahoo.com/");
xx.innerHTML="My Yahoo!";
}

// ********************************** 12/16/10 - added extra links
var xx=document.getElementById("networklinkscx");
if (xx != null)
{
xx.innerHTML=xx.innerHTML +
' | Gmail' +
' | News' +
' | STAT' +
' | Fidelity' +
' | Spurs' +
' | A101' +
' | CR' +
' | LogMeIn ' +
' | Blog ' ;

}

Update Object Property

2010-10-28T15:04:00.000-07:00

The General tab of object property can be used to store description, change documentations, etc. Often times I find myself not doing this until the end... It can take a while to go back and open each object to add the description. The following script automates the process by using the Project info for all objects in the project.

Tools v8.49.12.

--Update DESCR and DESCRLONG of Project Objects.
-- 1. set LASTUPDDTTM, LASTUPDOPRID To The same As Project
-- 2. append DESCRLONG
-- 3. set DESCR To Project If blank
---------------------------------------------------------------------------------

PS has 80+ object types, not all of them are listed here. Look for 'Unknown Object Type: ' and add a section for the object. Trace to see what objects PSIDE is accessing.
A nice & quick tracer app: Statement Tracer for Oracle

Peoplesoft Project XML Parser

2010-06-30T13:31:00.001-07:00

Parse Peoplesoft Project XML file.

Find Portal Menu Navigation Path

2010-05-27T07:37:00.000-07:00

PORTAL_OBJNAME is the key -

If a few page heading text are known, whether at Folder or Content level, use this sql to get PORTAL_OBJNAME:

Select * From psprsmdefn Where portal_name ='???????' And upper(DESCR254) Like upper('%fuzzy header%') -- find correct PORTAL_OBJNAME

Use this sql with the PORTAL_OBJNAME:

With psnav As
(Select RTRIM(Reverse(sys_connect_by_path(Reverse(portal_label), ' >----- ')), ' >----- ') navpath, Level lvl
From psprsmdefn A
Where portal_name = '????????'
START With PORTAL_OBJNAME = 'MY_PORTAL_OBJNAME'
CONNECT By PORTAL_OBJNAME = Prior PORTAL_PRNTOBJNAME)
Select navpath From psnav Where lvl=(Select max(lvl) From psnav)

This traverses from the Portal root down to the queried object. To go from queried object down:

Select LTRIM((sys_connect_by_path(portal_label, ' =====> ')),'=====> ' ) path, Level lvl
From psprsmdefn A
Where portal_name = '???????'
START With PORTAL_OBJNAME = 'EPGL_IMPORT_JOURNALS'
CONNECT By PORTAL_PRNTOBJNAME = Prior PORTAL_OBJNAME

If RECNAME is known:

Select * From ps_XREF_PANEL_VW Where recname='MY_RECNAME' -- get PNLNAME

Select * From ps_PNLGROUP_VW Where PNLNAME='MY_PNLNAME' -- get PNLGRPNAME

Select * From psprsmdefn Where PORTAL_NAME = '???????' And PORTAL_URI_SEG2 = 'MY_PNLGRPNAME' -- find PORTAL_OBJNAME

Use above sqls to find navigation up and down from the PORTAL_OBJNAME.

If a few field label texts are known:

Select * From pspnlfield Where upper(lbltext) Like upper('%??????%') --- find page name

use above sqls to find navigation up and down from the PORTAL_OBJNAME.

My Quick PL/SQL

2010-05-21T13:16:00.001-07:00

Read a File:

Create Directory TMP_DIR As 'xxxxxxxx';

Declare
input_file utl_file.file_type;
input_buffer varchar2(98);
pos NUMBER := 0;

Begin
input_file := utl_file.fopen ('TMP_DIR','aaaa.bbb', 'R');

If utl_file.is_open(input_file) Then
Loop
Begin
utl_file.get_line(input_file, input_buffer);

If input_buffer Is Null Then
EXIT;
End If;

Select instr(input_buffer, 'NUMBER OF ROWS FETCHED') Into pos From dual;

If pos > 0
Then
Select to_number(substr(input_buffer, 92,7)) Into inrcgl_cnt From dual;
dbms_output.put_line('INRCGL:'|| inrcgl_cnt);
End If;

Exception
When NO_DATA_FOUND Then
EXIT;
End;
End Loop;

End If;

utl_file.fclose(input_file);*/

Display external jobs in PS Process Monitor

2010-01-21T15:15:00.000-08:00

Many task schedulers exist to provide complex and powerful features that PS' own Process Scheduler cannot match. For example, IBM's Tivoli Workload Scheduler allows complex definition of jobs (=PS' Process) and Job Streams (= PS' Job), server and workstation setup, sophisticated dependency structure, cross platform support, scheduling calendar and beyond, in a GUI environment. You can define complex dependency in its graphical to drag and link jobs with ease. A drawback of this is these jobs are not displayed in Process Monitor. The following are the steps I took to display them in PS:

Quick PS Util - Get Record Description

2010-01-15T14:59:00.000-08:00

This function displays Record Definition under Property for a PS record, the function can be expanded to display LONG field values for any PS record.

Create Or replace Function  gl( rec In varchar2, long_fld In varchar2, key_fld In varchar2 )
Return varchar2
As
  l_cursor    integer Default dbms_sql.open_cursor;
  l_n         number;
  l_long_val  varchar2(4000);
  l_long_len  number;
  l_buflen    number := 4000;
  l_curpos    number := 0;
Begin

  dbms_sql.parse( l_cursor, 'select ' || long_fld || ' from ' || rec ||' where recname=upper(:x)', dbms_sql.native );
  dbms_sql.bind_variable( l_cursor, ':x', key_fld );

  dbms_sql.define_column_long(l_cursor, 1);
  l_n := dbms_sql.execute(l_cursor);

  If (dbms_sql.fetch_rows(l_cursor)>0)
  Then
     dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,l_long_val, l_long_len );
 End If;

 dbms_sql.close_cursor(l_cursor);

 Return l_long_val;

End gl;
/

EX:
Select gl('psrecdefn','descrlong','vendor') From dual

**** Nice utility to parse PS SQL Trace files : http://devwfb.blogspot.com/2009/11/script-analyzing-tracesql-file-and.html

Get View Definition Text: -----------------------------------------------------------------------
select * from user_views where view_name=upper('')

select DBMS_METADATA.GET_DDL('VIEW',upper('___')) from dual

select * from PSSQLTEXTDEFN where sqlid=upper('___')

Korn Shell Snippets

2010-01-07T15:52:00.001-08:00

Numeric Test --

1.
if [[ -z $(echo $2 | sed 's/[0-9]//g') ]]
then
echo "integer only"
fi

if [[ -z $(echo $2 | awk '/^[0-9]+$/') ]]
then
echo "integer only"
fi

if [[ ! -z $(awk -v x=$a 'END{if(x==x+0) print "integer"}' /dev/null) ]]
then
echo "integer only"
fi

Select * From table_name Where Regexp_Like(column_name, '^[0-9]+
Display Shell line number --

typeset -x PS4='[$LINENO] '
ksh -x script)

Alias to display File access/mod/create time --
alias ft='_(){ echo "modify time: \c";ls -l $1; echo "create time: \c"; ls -lc $1; echo "access time: \c";ls -lu $1; }; _'

Display Date Time at prompt:
export SECONDS="$(date '+3600*%H+60*%M+%S')";typeset -Z2 _h; typeset -Z2 _m ; typeset -Z2 _s;_hh="(SECONDS/3600)%24";_mm="(SECONDS/60)%60";_ss="(SECONDS)%60";_time='${_x[(_m=_mm)==(_h=_hh)==(_s=_ss)]}$_h:$_m:$_s';
export PS1=$(echo "${_time}")':$PWD>';

Date math & TZ (AIX): /etc/environment (https://www-304.ibm.com/support/docview.wss?uid=isg3T1000252)

TZ=CST6CDT,M3.2.0/2:00:00,M11.1.0/2:00:00 (DST enabled)

CST6CDT is the time zone you are in;
M3 is the third month;
.2 is the second occurrence of the day in the month;
.0 is Sunday;
/2:00:00 is the time.

# get current TZ value
tz=`echo $TZ | tr -s '[:upper:]' '[\0*]' | cut -d, -f"1"`

# 2 hrs back
((tz_2hr_back=$tz+2))

# date string format = MMDDHHMIYY
TZ=GMT+$tz_2hr_back date +%m%d%H%M%y;

ksh93 Associative Array:
unsert color
set -A color
color=([Application Engine]=1 \
[COBOL SQL]=2 \
[Crystal]=3 \
[SQR Process]=4 \
[SQR Report]=5)

${color[@]} = all elements of the array
${!color[@]} = subscripts of an array
${#color[@]} = # of elements within the array.
${color[@]:offset:length} = Elements within a numeric subscript range

echo ${color[@]}
echo ${!color[@]}
echo ${#color[@]}
echo ${color[@]:1:length}

idx=0
while [ $idx -lt ${#color[@]} ]
do
echo ${color[@]:$idx:1}
((idx=idx+1))
done

resolve bind variable values in PS trace/log files

2009-11-30T09:05:00.000-08:00

Peoplesoft SQL and AE trace files display bind variable values along with the sql statements. Here is a script that quickly merges them so the sql statements can be rerun for testing.

A Dynamic Prompt Table based on Specific Columns

2009-07-17T09:26:00.000-07:00

Some Peoplesoft Financials tables include a number of columns that are coming from different modules such as PO, AP, REQ. Commonly these transaction data are stored in modules' own specific tables, and a certain functions, such as Commitment Control, capture these transaction into a single table. In such case, the Commitment Control transaction table is defined with a column assigned for each type of the modules, such as VOUCHER_ID, PO_ID, REQ_ID, JOURNAL_ID etc.

The Search Key for such tables is defined by another auto-incrementing numeric column. This renders the key column meaningless. On a deliverered search page, user has to know beforehand what the unique key is in order to bring up the transaction.

Quick PS Util - Display Table data in order of Column Name

2009-06-23T14:35:00.000-07:00

When using Toad or other tools that displays results in a grid that can be scrolled from left to right, and there are many columns, I usually have to to go back and forth a few times to find a column. Some of the solutions to this --

1. creataing a function returns a sql that displays fields in the order of filed name:

CREATE OR REPLACE function  fo(table_in IN varchar2)RETURN varchar2
IS
  sql1      varchar2(6000);
  cnt       integer;
  rec_name  varchar2(30);

  cursor c1 is
     select column_name as fieldname from all_tab_columns where TABLE_NAME=rec_name order by column_name;

BEGIN

 cnt      :=0; 
 sql1     :='select ';
 rec_name :='PS_'||upper(table_in);

 FOR zz IN c1 LOOP
    sql1:=sql1 || zz.fieldname||',';
    cnt:=cnt+1;
 END LOOP;    

 if cnt = 0 then

    rec_name := upper(table_in);

    FOR zz IN c1 LOOP
        sql1:=sql1 || zz.fieldname||',';
        cnt:=cnt+1;
    END LOOP;    
 end if;

 if cnt = 0  then
    return table_in ||' columns not found';
 end if;

 sql1 := sql1 || '''---'' from ' || rec_name ;

 RETURN sql1 ;

EXCEPTION
 WHEN OTHERS THEN
    raise_application_error(-20001,'Errro - '||SQLCODE||' -ERROR- '||SQLERRM);

END;
/

show error function fo

Ex:

select fo('installation') from dual -- default to 'PS_'
select fo('psrecdefn') from dual
select fo('all_objects') from dual

Change Peoplesoft FTP to SFTP - Part 4

2009-06-17T21:03:00.000-07:00

A sample script that ties up things using the information discussed.
1. minimize code changes to delivered script & call custom code in separate script

:::::

cd ${PS_HOME}/bin

# source my sftp script
. my_sftp_script

ftp_cmd_file=$1
::::::
::::::
echo "about to submit ftp -vin $1"
#$FTP_CMD -vin < $1    
my_sftp_function

break
;;

esac

::::::::

2. my_sftp_script:

#####################################################
#
# My SFTP function
#
#####################################################
function my_sftp_function
{

#singal handler
trap "cleanup" 0
trap "echo Cancelled; cleanup;" 1 2 3 15

err=0
feedback=1

SFTP_CMD="/usr/bin/sftp"
sftp_cmd="h_sftp_cmd."$$
sftp_tmp="h_sftp_tmp."$$
sftp_log="h_sftp_log."$$
dev=me@some.place.com
user=/somehost/somedir/user_list

##### sftp messages           --change text as needed
SFTP_LOGIN_ERR="Permission denied (publickey,password,keyboard-interactive)"
SFTP_NO_FILE="No such file or directory"

##### ftp messages & codes    --change text as needed
FTP_NO_FILE="A file or directory in the path name does not exist."
FTP_LOGIN_ERR="Login incorrect."
FTP_MAX_SIZE="Exceeded storage allocation"

FTP_DEL_NOFILE_CD=532
FTP_LOGIN_ERR_CD=530
FTP_MAX_SIZE_CD=552
FTP_GET_NOFILE_CD=550

FTP_PUT_OK="226 Transfer complete."
FTP_GET_OK="226 Transfer complete."
FTP_DEL_OK="250 DELE command successful."

###############
# get ftp xfer command, remote dir, remote file name

cmd=''
r_file=`grep ^put $ftp_cmd_file | awk '{print $3}' | tr '\"' '\0'`

if [ ${r_file:-none} == 'none' ]; then

r_file=`grep ^get $ftp_cmd_file | awk '{print $2}' | tr '\"' '\0'`

if [ ${r_file:-none} == 'none' ]; then

r_file=`grep ^delete $ftp_cmd_file | awk '{print $2}' | tr '\"' '\0'`

if [ ${r_file:-none} != 'none' ]; then
  cmd=delete
fi
else
cmd=get
fi
else
cmd=put
fi

r_dir=`grep ^cd $ftp_cmd_file | awk 'BEGIN{var=""} {if ($2!="/") var=var"/"$2; else var=var$2; } END{print var}' | sed 's/\/\//\//g'`

#echo "!sleep 500" > $sftp_cmd

# convert ftp to sftp commands
grep -e ^lcd -e ^cd -e ^put -e ^delete -e ^get -e ^quit $ftp_cmd_file | sed s/^delete/rm/ | awk '!/^rm/ {print $0} /^rm/ {print $0"\""}'  > $sftp_cmd


echo 'sftp commands---------------'                  > $sftp_log
cat $sftp_cmd                                       >> $sftp_log

echo '\nsftp log--------------------'               >> $sftp_log

# run sftp command batch file
$SFTP_CMD -b $sftp_cmd `grep ^user $ftp_cmd_file|awk '{print $2}'`@`grep ^open $ftp_cmd_file|awk '{print $2}'` 1>>$sftp_log 2>&1

err=$?

if [ $err -ne 0 ]; then

grep -qi $SFTP_LOGIN_ERR $sftp_log

if [ $? -eq 0 ]; then
echo $FTP_LOGIN_ERR_CD" "$FTP_LOGIN_ERR
else
grep -qi  $SFTP_NO_FILE $sftp_log

if [ $? -eq 0 ]; then

   if [ $cmd = 'delete' ]; then
       echo $FTP_DEL_NOFILE_CD" "$FTP_NO_FILE
   else
       echo $FTP_GET_NOFILE_CD" "$FTP_NO_FILE
   fi
else

   grep -qi  $FTP_MAX_SIZE $sftp_log

   if [ $? -eq 0 ]; then

       echo $FTP_MAX_SIZE_CD" "$FTP_MAX_SIZE

   fi
fi
fi

else
# check sftp results

echo '\nverifying results------------'            >>$sftp_log

chk_results

err=$?

if [ $err -eq 0 ]; then

if [ $cmd = 'get' ]; then
   echo $FTP_GET_OK
else
   if [ $cmd = 'put' ]; then
       echo $FTP_PUT_OK
   else
       echo $FTP_DEL_OK
   fi
fi
fi
fi

# send feedback
if [ $err -ne 0 ] && [ $feedback -eq 1 ]; then

echo $cmd' failed.'                                >> $sftp_log                     
                                      
echo '\nftpunx log------------------'              >> $sftp_log
echo ${ftp_cmd_file%.*}".log:"                     >> $sftp_log
cat `echo ${ftp_cmd_file%.*}".log"`                >> $sftp_log
                                      
echo '\nEnd: '`date`                               >> $sftp_log

if [ -f  $user ]; then
mailx -s"My SFTP failed" `cat $user`             < $sftp_log     else                                                          mailx -s"My SFTP failed" $dev                    < $sftp_log    fi  fi  return $err  }  ################################################ # # check SFTP results # ################################################ function chk_results {  case $cmd in        put | get)         # compare file sizes                  echo "         `grep ^cd $ftp_cmd_file`         ls -l "$r_file"          quit         " > $sftp_cmd

$SFTP_CMD -b $sftp_cmd `grep ^user $ftp_cmd_file|awk '{print $2}'`@`grep ^open $ftp_cmd_file|awk '{print $2}'` 1>$sftp_tmp 2>&1

if [ $? -ne 0 ]; then
   return 1
fi

# get temp local file name

if [ $cmd = 'put' ]; then
  l_file=`grep ^lcd $ftp_cmd_file | awk 'BEGIN{var=""} {if ($2!="/") var=var"/"$2; else var=var$2; } END{print var}' | sed 's/\/\//\//g'`/`grep ^put $ftp_cmd_file | awk '{print $2}' | tr '\"' '\0'`
else
  l_file=`grep ^lcd $ftp_cmd_file | awk 'BEGIN{var=""} {if ($2!="/") var=var"/"$2; else var=var$2; } END{print var}' | sed 's/\/\//\//g'`/`grep ^get $ftp_cmd_file | awk '{print $3}' | tr '\"' '\0'`
fi

l_file_size=`ls -l $l_file | awk '{print $5}'`
r_file_size=`grep -v ^sftp $sftp_tmp | grep $r_file | awk '{print $5}'`

if [ $l_file_size -ne $r_file_size ]; then

   echo 'file size different:'                                 >> $sftp_log
   echo 'local  file size='$l_file_size' ('$l_file')'          >> $sftp_log  
   echo 'remote file size='$r_file_size' ('$r_dir'/'$r_file')' >> $sftp_log

   return 1
fi

break
;;

delete)

# DELETE - verify file does not exist; use subshell so sftp does not error off

(
echo "
`grep ^cd $ftp_cmd_file`
ls -l "$r_file"
quit
"
) | $SFTP_CMD `grep ^user $ftp_cmd_file|awk '{print $2}'`@`grep ^open $ftp_cmd_file|awk '{print $2}'` 1>$sftp_tmp 2>&1

if [ $? -ne 0 ]; then
   return 1
fi


grep -q "$2 not found" $sftp_tmp

if [ $? -ne 0 ]; then
   cat $sftp_tmp                                               >> $sftp_log

   return 1
fi

break
;;
esac

return 0
}


function cleanup
{
rm $sftp_tmp 2>/dev/null
rm $sftp_cmd 2>/dev/null
rm $sftp_log 2>/dev/null

}

Navigation to related pages:

Page Used to Define an FTP Server for File Attachments

Page Name	Object Name	Navigation	Usage
URL Maintenance	URL_TABLE	PeopleTools, Utilities, Administration, URLs	Specify an FTP server for storing file

Page used to Set Up File Attachment Servers

Page Name	Object Name	Navigation	Usage
Maintain File Att Servers	PV_ATT_SRV_ADMIN	Set Up Financials/Supply Chain, Utilities, Administer File Attachments, Administer Attachment Servers	Used to set up the FTP server location for PDF and XML file attachments which are included with notifications.

Pages Used to Set Up Attachments for Transactions

Page Name	Object Name	Navigation	Usage
Maintain File Att Servers	PV_ATT_SRV_ADMIN	Set Up Financials/Supply Chain, Utilities, Administer File Attachments, Administer Attachment Servers	Identify servers on which to store attachments.
Maintain Component Subdirs	PV_ATT_SRV_ADMIN	Set Up Financials/Supply Chain, Utilities, Administer File Attachments, Administer Attachment Servers, Maintain Component Subdirs	Define component names and paths where the attachments are stored.
Vendor Address	VNDR_ADDRESS	eProcurement, Procurement Application Admin, Maintain Vendors, Vendor Profile, Address	Define vendor email addresses for sending attachments.
URL Maintenance	URL_TABLE	eProcurement, Procurement Application Admin, Maintain Overall System Options, Maintain FTP Server Location	Requires end users to enter the file location manually for attachments (not the recommended method). Set up a location for storing attachments. This is usually an FTP server. Add a subfolder named PV to the root directory. Give read and write privileges to anonymous users or any users that are specified in the FTP string. Create a URL identifier that is named PV for the FTP location for PeopleSoft eProcurement attachments, and specify the FTP server address. The FTP root location must contain a subdirectory that is named PV.

Change Peoplesoft FTP to SFTP - Part3

Change Peoplesoft FTP to SFTP - Part 3

2009-06-09T09:04:00.000-07:00

Because FTP is like any 3rd party application to PS, and it generally passes a return code of zero in the non-interactive session, it is not useful in determining a Success or Fail status. So PS is using the session message that is captured in the log file and filtering the FTP status code. A list of status code I can find that is used by PS for this :

226 - Transfer complete.
250 - DELE command successful.
532 - A file or directory in the path name does not exist
550 - A file or directory in the path name does not exist
552 - Exceeded storage allocation

The following considerations should be taken in regards to the wide range of systems and implementation of SFTP executables:

The only way I found that can consistently returns a error status code is the "-b" batch option. If you use HERE document or subshell-echo it returns zero even if there are errors.
The FTP status code should be standard, but the message text can vary per system. SFTP does not have a standard status code (on our system), and message text can also vary by systems.

So analyze and thoroughly test the code, look for variations in message text and make necessary adjustments.

The Peoplecode statement for adding/loading a file is like:

The function (AddAttachment) calls ExecutePutAttachment API which examines the log file (ftp????_?????.log) for FTP status code then sets RETCODE as follows:

System Variable for &RETCODE	Value	FTP Code	Comments
%Attachment_Success	0
%Attachment_Failed	1
%Attachment_Cancelled	2		user hit “CANCEL” instead of “Upload”
%Attachment_FileTransferFailed	3
%Attachment_NoDiskSpaceAppServ	4		N/A for FTP
%Attachment_NoDiskSpaceWebServ	5		N/A for FTP
%Attachment_FileExceedsMaxSize	6	552
%Attachment_DestSystNotFound	7
%Attachment_DestSysFailedLogin	8	530	sftp cannot authenticate
%Attachment_FileNotFound	9	550	user “Upload” a 0-byte file; “View” file does not exist
%Attachment_DeleteFailed	10	552	user hit “delete” but file does not exist
%Attachment_NoFileName	11		user hit “Upload” without selecting a file
%Attachment_FileNameTooLong	12

You can see not all status codes are related to FTP. This is due to the the fact only a portion of the transfer process utilizes ftp. Between Browser and Web Server, Web Server & App Server, App Server & DB Server FTP is not involved. I tried to test as many FTP codes as I can, these are the ones I see PS reports off the log file. For ex. if you set &MESSAGE_LVL (in FILE_ATTACH_WRK peoplecode) to a value of 1 or 2, you will see a detailed message box after each operation. This is the delivered code:

&RETCODE = AddAttachment(&URL_ID, &ATTACHSYSFILENAME, &FILEEXTENSION, &ATTACHUSERFILE, &FILESIZE);
:::::::::::::::::::::::::::::::::::::
If &MESSAGE_LVL = 1 Or
&MESSAGE_LVL = 2 Then
If (&RETCODE = %Attachment_Failed) Then
MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 2, "AddAttachment failed");
End-If;
:::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::
If (&RETCODE = %Attachment_DestSysFailedLogin) Then
MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 9, "AddAttachment failed: Unable to login into destination system for ftp");
End-If;
If (&RETCODE = %Attachment_FileNotFound) Then
MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 29, "The file was not found so the operation could not be completed.");
End-If;
/* Error Message for Invalid file or No file name */
If (&RETCODE = %Attachment_NoFileName) Then
MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 38, "AddAttachment failed: No File Name Specified.");
End-If;
::::::::::::::::::::::::::::::

The API AddAttachment will return zero if it sees this FTP session message in the log file:

226 Transfer complete.

so the SFTP will simply run:

echo "226 Transfer complete"

after it verifies a PUT operation is successful. Similarly, DeleteAttachment API is looking for:

250 DELE command successful

Next is a script that ties everything together.

Change Peoplesoft FTP to SFTP - Part 2 | Change Peoplesoft FTP to SFTP - Part4

Change Peoplesoft FTP to SFTP - Part 2

2009-06-08T15:03:00.000-07:00

In the PS generated script, ftp?????_????.txt, it stores the host, login, cd, lcd, get/put/delete & quit commands. A single file is processed each time. Prior to doing sftp, the correct public/private keys need to be generated, known_host and authorized_keys must be set up for the ftp use account. Converting the script to run sftp is straightforward, in 2 steps:

1. grep -e ^lcd -e ^cd -e ^put -e ^delete -e ^get -e ^quit $ftp_cmd_file | sed s/^delete/rm/ | awk '!/^rm/ {print $0} /^rm/ {print $0"\""}' > $sftp_cmd

This extracts all ftp commands, excluding host and login info, and stores them into a temp sftp script. The ftp "delete" command is replaced by sftp "rm" command. In addition, for DELETE operation, PS leaves off a closing ", so it looks like this

delete "XXXXXX.YYY

FTP does not complain it, but SFTP is more strict in syntax and will cause an error. The command changes it to

rm "XXXXXX.YYY"

For PUT operation, PS inserted a "mkdir" command. This will cause SFTP to error in batch mode. So this command is not picked up by the script, just verify the directory exists beforehand.

2. $SFTP_CMD -b $sftp_cmd `grep ^user $ftp_cmd_file|awk '{print $2}'`@`grep ^open $ftp_cmd_file|awk '{print $2}'` 1>$sftp_log 2>&1
($SFTP_CMD = sftp executable)

This executes SFTP in batch mode, with host name and login info from the original ftp script. Password is not needed, as it will be authenticated by Public Key.

I embedded a sleep command in SFTP to get a view at the process tree for this

24781020 - An App Server instance handles the PS connection and peforms

15798308 - App Server instances spwans a shell & executes ftpunx script

19480650 - ftpunx script runs sftp command

14291166 - sftp run ssh to handle file transfer

2396354 - ssh exeutes a OS command

This is all it takes to transfer a file using secured FTP with sftp. But this is just the beginning, to fully integrate with PS, the results must be "relayed" back to PS . This is done through examination of the SFTP log file. The technique I used is detailed in the next part.

Change Peoplesoft FTP to SFTP - Part 1 | Change Peoplesoft FTP to SFTP - Part 3

Peoplesoft Notify Address

2009-06-03T11:46:00.000-07:00

Application Package Peoplecode:
PT_WF_NOTIFICATION.Notification.OnExecute

method Send

/* SendMail(0, &strEmailCC, "", &strEmailBCC, &Subject, &Message, &strFileNames, &strFileTitles, &tempEmailReplyTo, ";", &ContentType);*/
SendMail(0, &strEmailTo, &strEmailCC, &strEmailBCC, &Subject, &Message, "/path/path/filename.ext", "xxx.txt", "From_Notify@rver.xx.yy.zz", ";", &ContentType, &tempEmailReplyTo, "sender@srvr1.xx.yy.zz");

Further reading:

SMTP
Internet Messaging protocols (SMTP,POP3 and IMAP4)
SPAM Filtering

Change Peoplesoft FTP to SFTP - Part 1

2009-05-31T17:39:00.001-07:00

Many workplaces are implementing a higher level of secured data transfer requirements due to increased security concerns. The PS delivered file transfer process in v8.49 currently does not support SFTP however. These are the steps I employed to make the switch. This article applies to the file transfer from a client desktop to a FTP server using the delivered ftpunx shell script.

Firstly a note on the flow of Peoplesoft's File Attachment process. In a 4-tier environment, a file transfer request initiates from the client browser. This file data goes to the Web server using HTTP or HTTPS protocal. Web Server then sends it to App Sever through Jolt. It is in the final leg of the relay, from App Server to destination FTP Sever, that FTP/SFTP is employeed. When viewing a file, the above steps are reversed.

The Peoplecode function that transfers a file is AddAttachment. There are multiple pages that uses multiple ways to call this function, some directly and some can go through layers of Application Package code, but they ultimately call the same function/

A look into the behind the scene actions when adding an file attachment -
1. generating a unique temp file name, such as:

&UniqueName = .OPRID | "_" | String(Month(%Date)) | "_" | String(Day(%Date)) | "_" | String(Year(%Date)) | "_" | String(Hour(%Time)) | "_" | String(Minute(%Time)) | "_" | String(Second(%Time));

The name can vary slightly among different load pages/processes. They are in the general format of Date Time stamp and OPRID.

2. When user hit Attach button, an entry is inserted into PSFILE_ATTDET:

This row will stay in the table as a tag. FILE_SEQ is 0, FILE_SIZE is 5 bytes, indicating the FILE_DATA size. (value is HEX 00 64 00 75 00 on my system)

ATTACHSYSFILENAME is a unique file name that will be used to store file data as it loads in chunks of 28K indexed by FILE_SEQ. If no file is selected to load, this row is not removed.

All the rows (one per chunk) used during file load are deleted after successfully reassembled on App Server, and before actual FTP runs.

3. The Upload box displays after the above steps:

4. When user selects a file and hit "Upload", internally PS run a FileAttachService API that performs these tasks:

the tag row in PSFILE_ATTDET is cleared
get MAXCHUNKSIZE from PSOPTIONS. Default is 28K.
breaking down file data into chunks of MAXCHUNKSIZE and insert each segment, identified by FILE_SEQ, into PSFILE_ATTDET

(the last segment size is smaller because it contains only the remainder of file. so the file size = MAX(FILE_SEQ) - 1 * MAXCHUNKSIZE + FILE_SIZE of final segment)

note: the API is not directly accessible from Peoplecode. PS runs them at appropriate junction to perform special purpose internal tasks.

Now that the data has been saved onto a table on Database server. Next PS will put the data back into a file on App Server.
5. The function AddAttachment is called after the above has completed. PS runs internal APIs ExecuteGetAttachmentDB & ExecuteDeleteAttachmentDB:

reassembles the data from table back into a file, in the order of FILE_SEQ, the location for the temp file is /tmp/PSFTP/_?????/????
delete data from PSFILE_ATTDET

Now the file is on App Server, ready for FTP to its destination. When viewing a file, the above steps are reversed. The file is FTP'ed to App Server, then Jolt'ed to Web Server, then HTTP'ed to client browser.

6. Still in AddAttachment, the API ExecutePutAttachment performs the most important tasks of the process:

parse FTP URL (ftp://UID:PWD@FTP_Dest_Server/xxx/xxx/xxx/_
_?????.ext) to build a series of FTP commands (open, user, cd, lcd, get/put/delete...) into a temp script in /tmp/ftp???_????.txt
verify the FTP script exists
run FTP using the FTP script
check FTP results in /tmp/ftp?????_?????_?????.log
delete FTP script, preserve log if needed

This is a very critical step because of the sensitive data involved. The ftp???.txt contains all FTP commands needed to perform the transfer. PS runs(spawn()?) the FTP wrapper, $PS_HOME/bin/ftpunx, to send the file. The output from the ftp is stored in ftp???.log & used to verify results. This is the process as being run:

/bin/sh $PS_HOME/bin/ftpunx /tmp/ftp????_????_?????.txt

A look at ftpunx:

2 things to note: (1) $FTP_CMD is the ftp executable on App server and $1 is the ftp????.txt script (2) because of the location of "result=$?", it's going to always = 0. As a result we will always see "File Transfer Successful" in the log, even in failed situations. This is misleading but not damaging, because PS does not rely on this check.

If all goes well, the file is now supposed to be FTP'ed to it's destination. How does PS verify this? It checks the resulting session message file. Before getting into this, take a look at the changes in the script to run SFTP.

Change Peoplesoft FTP to SFTP - Part 2

Cool Gadgets

2009-05-31T10:05:00.000-07:00

Enlarge CSR Description and Resolution Box

2009-05-30T23:01:00.000-07:00

The web version of Stat has some nice feature over the client desktop version. But the text editing area for Description and Resolution is way too small.

Using a simple greasemonkey script I enlarge both to allow for easy editing:

Development Tools -

2009-05-29T22:14:00.000-07:00

As a programmer I have used numerous programming tools for development on DOS, Windows, and Unix. The programming languages include 8086 PC assembly, c, c++, cobol, php, VB, VB script, java script, ASP. Applications that run standalone on PC, Windows, or Client-Server and Server side. I found a development tool can add to one's coding skill and system knowledge greatly in that it can save large amount of time and facilitates coding, compiling, testing and debugging. The primary factor for me is the capability to customize Menu, Toolbar and even Context Menu, create Macros, remap Keyboards. The deafult settings usually have a lot of items I dont need, they just take up screen space. In addition its annoying to see these unused items. Some functions are set too "dangerously" close. Like "Commit' and "Rollback", the 2 tiny bitmap button next to each other is a potential trap.

I am waiting for the day when voice commands becomes a reality for programming task. For now as little typing as possible.

text editor - Ultraedit by IDM Computer Solutions. Check out the programmer(&founder)'s story.
terminal emulator - SecureCRT by VanDyke Software
SQL development - Toad by Quest Software

My bin

2009-05-29T20:48:00.000-07:00

dd -

#!/bin/csh -f
#-------------------------------------------------------------------------------
set parm = 'ls -l'

while (1)
if ($1 == '/od') then
if ($?bysize) then
echo 'Error: cannot sort by both date & size'
exit(1)
else
set bydate = '-t -r'
endif
else
if ($1 == '/os') then
if ($?bydate) then
echo 'Error: cannot sort by both date & size'
exit(1)
else
set bysize = 'sort -n -k 5'
endif
else
if ($1 == '/r') then
set revse = '-r'
else
if ($1 == '/a') then
set hidden = '-a'
else
break
endif
endif
endif
endif
shift
end

#---------------------------------------------------------------------------
if ($?hidden) then
set parm = "$parm $hidden"
endif

if ($?bydate) then
set parm = "$parm $bydate"
endif

if ($?bysize) then
$parm $* | $bysize | more -e
else
$parm $* | more -e
endif

exit(0);

gr -

#!/bin/ksh

# this script greps Process

if [ $1'x' = 'x' ]; then
echo 'Nothing to grep'
exit 1
fi

flag=ef

while getopts :c:l args
do
case $args in

c) clear
shift
;;

l) flag=fL
shift
;;

esac
done

if [ $flag == "ef" ]; then
echo "tracing process by name >>>> ps -ef | grep $* "

ps -ef | grep $* | grep -v ~djen/bin/gr | grep -v grep
else

echo "tracing single process tree >>>> ps -fL "

ps -fL $*
fi

path -

#!/bin/ksh
#-------------------------------------------------------------------------------

if [ $# -eq 0 ]; then

echo $PATH | tr ':' '\n'

else

set -A arPATH `echo $PATH | tr ':' ' '`

idx=0
while [ $idx -lt ${#arPATH[*]} ]
do
if [ -f ${arPATH[idx]}/$1 ]; then
ls -l ${arPATH[idx]}/$1
break
else
((idx=idx+1))
fi
done

fi

My Oracle SQLs

2009-05-29T20:40:00.001-07:00

-------------------------------------------------------------------------------------------------------SQL history
http://www.dbasupport.com/oracle/ora10g/session_wait.shtml

select distinct sql_id from v$active_session_history where SESSION_ID=

select distinct sql_id from DBA_HIST_ACTIVE_SESS_HISTORY where SESSION_ID=

-------------------------------------------------------------------------------------------------------DB Objects
--http://www.techonthenet.com/oracle/sys_tables/
all_views
ALL_CATALOG

-------------------------------------------------------------------------------------------------------get Session Id name
Select Sid From V$SESSION Where audsid = SYS_CONTEXT('userenv','sessionid')

-------------------------------------------------------------------------------------------------------get User name
Select '*** User = '||USER||' ***' From dual

-------------------------------------------------------------------------------------------------------get db name
Select Name, CREATED, LOG_MODE
From V$DATABASE;

-------------------------------------------------------------------------------------------------------get DB links
Select db_link From user_db_links;

Select A.OWNER, A.HOST, A.DB_LINK, A.USERNAME, A.CREATED,
DECODE (B.FLAG, 0, 'NO', 1, 'YES') "Dec", B.AUTHUSR, C.STATUS
From DBA_DB_LINKS A, SYS.USER$ U, SYS.LINK$ B, DBA_OBJECTS C
Where A.DB_LINK = B.Name And
A.OWNER = U.Name And
B.OWNER# = U.USER# And
A.DB_LINK = C.OBJECT_NAME And
A.OWNER = C.OWNER And
C.OBJECT_TYPE = 'DATABASE LINK'
Order By 1, 2, 3;

------------------------------------------------------------------------------------------------------SYS_CONTEXT: http://www.psoug.org/reference/sys_context.html
Select SYS_CONTEXT('userenv','DB_NAME') From dual

DB_NAME = Database
SERVER_HOST = Name Of server Where DB resides
HOST = Name Of client
SESSION_ID = Session Id
OS_USER = O.S user
SERVICE_NAME = Name Of service conneted To A Session (db Name)
SESSION_USER = Name Of user login Id
SESSION_USERID = dentifier Of The Database user Name By which The Current user Is Authenticated.
Sid =

-------------------------------------------------------------------------------------------------------who is running what
Select s.Program, s.schemaname, s.Sid, s.status, s.process, s.osuser, A.sql_text, P.Program
From v$session s, v$sqlarea A, v$process P
Where s.sql_hash_value=A.hash_value And s.sql_address=A.address And s.paddr=P.addr
--and s.schemaname='&1'
And s.status='ACTIVE'

-------------------------------------------------------------------------------------Oracle Parameters (init.ora)
Select Name,
DECODE (Type, 1, 'Boolean', 2, 'String', 3, 'INTEGER', 4, 'FILE', 5, 'RESERVED', 6, 'BIG INTEGER') Type,
VALUE, DESCRIPTION, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISDEFAULT,
ISMODIFIED, ISADJUSTED
From V$PARAMETER
Order By Name;

--RBS
Select
r.segment_name segment_name,
r.owner owner,
r.tablespace_name tablespace_name,
r.status status,
ROUND(r.initial_extent/1024/1024) initial_extent,
ROUND(r.next_extent/1024/1024) next_extent,
s.Extents,0 Extents,
ROUND(s.rssize/1024/1024) rssize,
s.xacts active_trans
From
dba_rollback_segs r,
v$rollname n,
v$rollstat s
Where r.segment_name = n.Name
And n.usn = s.usn;

Select 'Database Version = '||banner From v$version Where Rownum = 1;

Select 'Database Block Size = '||value From v$parameter Where Name = 'db_block_size';

----------------------------------------------------------------------------------------------------Date Time difference
Sql> Create Or REPLACE Function datediff( p_what In VARCHAR2,
2 p_d1 In DATE,
3 p_d2 In DATE ) Return NUMBER
4 As
5 l_result NUMBER;
6 Begin
7 Select (p_d2-p_d1) *
8 DECODE( UPPER(p_what),
9 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, Null )
10 Into l_result From dual;
11
11 Return l_result;
12 End;
13 /
Function created

---------------------------------------------------------------------------------------------------who is using rbs?
Select SUBSTR(A.os_user_name,1,8) "OS User"
, SUBSTR(b.object_name,1,30) "Object Name"
, SUBSTR(b.object_type,1,8) "Type"
, SUBSTR(c.segment_name,1,10) "RBS"
, E.process "PROCESS"
, SUBSTR(d.used_urec,1,8) "# Of Records"
, d.used_ublk
From v$locked_object A
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session E
Where A.object_id = b.object_id
And A.xidusn = c.segment_id
And A.xidusn = d.xidusn
And A.xidslot = d.xidslot
And d.addr = E.taddr

----------------------------------------------------------------------------------------------------Re-Connect
Begin sys.dbms_application_info.set_module('T.O.A.D.', Null); End;

-----------------------------------------------------------------------------------------------------Client Info
SET serveroutput On
Declare
XXX VARCHAR(20);

Begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('TOAD1+1');
DBMS_APPLICATION_INFO.READ_CLIENT_INFO (XXX );
DBMS_OUTPUT.PUT_LINE(XXX);
End;

Select P.spid,
substr(s.osuser,1,10) osuser,
substr(s.username,1,8) username,
substr(s.Program,1,24) Program,
substr(s.client_info,1,60) ClientInfo
From v$session s, v$process P
Where s.paddr=P.addr
And s.osuser Is Not Null
--and spid=4767916
Order By s.osuser

-------------------------------------------------------------------------------------------------SET_SESSION_LONGOPS
Select * From V$SESSION_LONGOPS Where sql_address='070000004185C7E0'

Select *-- sql_id
From v$session
Where Sid =24

Select operation
, options
, object_name
, object_alias
, object_type

Select *
From v$sql_plan --WHERE object_name='XXXX'
Where address = --'070000004185C7E0'
( Select sql_address
From v$session
Where Sid =24)

---------------------------------------------------------------------------------------------------------CBO
Select *--index_name,table_name, blevel, avg_data_blocks_per_key, avg_leaf_blocks_per_key, clustering_factor
From user_indexes Where table_name In ('XXXX','YYYY')

Select *--table_name, blocks
From user_tables Where table_name In (''XXXX','YYYY')

EXEC DBMS_STATS.gather_table_stats('orauser', ''XXXX');
EXEC DBMS_STATS.gather_table_stats('orauser', ''XXXX', estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats('orauser', ''XXXX');
EXEC DBMS_STATS.gather_index_stats('orauser', ''XXXX', estimate_percent => 15);

--more on Statistics
http://www.oracle-base.com/articles/8i/CostBasedOptimizerAndDatabaseStatistics.php

Select table_name, num_rows From dba_tables Where owner='orauser'
Order By 2 desc

Select * From user_tab_cols

col c1 heading 'Average Waits|forFull| Scan Read I/O' format 9999.999
col c2 heading 'Average Waits|for Index|Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999

Select
A.average_wait ,
b.average_wait ,
A.total_waits /(A.total_waits + b.total_waits) ,
b.total_waits /(A.total_waits + b.total_waits) ,
b.average_wait , A.average_wait
From
v$system_event A,
v$system_event b
Where
A.event = 'db file scattered read'
And
b.event = 'db file sequential read'

Select * From v$system_event

-----------------------------------------------------------------------------------------------------File Status

Select SEGMENT_NAME, STATUS From dba_rollback_segs

Select * From dba_data_files Order By file_id

Select FILE#,status,Error From v$datafile_header

Select Name , status From v$datafile ;
4
/u04/oradata/f752x539/o1_mf_psindex_2pxzbghr_.dbf

-----------------------------------------------------------------------------------------------------AutoNumber in trigger
Create Or REPLACE Trigger TableName_ID_assign
Before Insert On TableName For Each Row
Begin
Select TableName_IDs.Nextval Into :New.Id From dual;
End;
/

Create Procedure TableName_C( p_ID Out TableName.Id%Type ...) Is
v_NewID TableName.Id%Type;
Begin
Select TableName_IDs.Nextval Into v_NewID From dual;
Insert Into TableName (Id ...) Values (v_NewID ...);
p_ID = v_NewID;
End TableName_C;
/

---------------------------------------------------------------------------------------------------Tables/Index in a TableSpace
Select A.Name,c.Name Tablespace,b.analyzetime analyzed,b.rowcnt ,d.bytes, d.bytes/POWER(2,20) MB,d.bytes/POWER(2,30) GB
From sys.obj$ A, sys.tab$ b, sys.ts$ C,DBA_SEGMENTS d
Where A.Name Like 'XXXX%'
And A.obj#=b.obj#
And b.ts#=c.ts#
And A.Name=d.segment_name
And d.segment_type='TABLE'
And d.tablespace_name=c.Name

Select A.Name,c.Name Tablespace,b.analyzetime analyzed,b.rowcnt ,d.bytes, d.bytes/POWER(2,20) MB,d.bytes/POWER(2,30) GB
From sys.obj$ A, sys.ind$ b, sys.ts$ C,DBA_SEGMENTS d
Where A.obj#=b.obj#
And b.ts#=c.ts#
And A.Name=d.segment_name
And d.segment_type='INDEX'
And d.tablespace_name=c.Name
And A.Name Like ''XXXX%'
Order By Name

Select segment_name,segment_type, bytes/POWER(2,20) MB,bytes/POWER(2,30) GB
From DBA_SEGMENTS Where segment_name Like 'XXXX%'

Drop Table XXXX

Select * From sys.tab$

Select * From DBA_SEGMENTS Where segment_name Like 'XXXX%'

-- total bytes
Select SUM(BYTES) From USER_SEGMENTS Where SEGMENT_TYPE='TABLE' And segment_name=UPPER(''XXXX')

--how many MB -GB
Select 1073741824/POWER(2,20) MB, 1073741824/POWER(2,30) GB From dual

--move to a different tablespace
Alter Table 'XXXX Move Tablespace new_table_space

----------------------------------------------------------------------------------------------bind variable VALUES IN 10g
http://halisway.blogspot.com/search/Label/oracle

First have A look In v$sql To find The Sql Query you are looking For,Join The sql_id To v$sql_bind_capture And To View The bind variable Values For that Query.
v$session.sql_hash_value = Current Cursor Sql, v$session.prev_hash_value = prev Sql. Both can Use To look up Sql text (HASH_VALUE(

Select sql_id,sql_text From v$sql Where users_executing > 0

Select Name, position, DATATYPE_STRING,WAS_CAPTURED,LAST_CAPTURED,VALUE_STRING
From v$sql_bind_capture Where sql_id='5chvypbvbxtau'

Select sql_text From v$sqlarea Where users_executing > 0

--from SID
Select SQL_TEXT,sql_id From V$SQLTEXT_WITH_NEWLINES Where HASH_VALUE=TO_NUMBER((Select sql_hash_value From v$session Where Sid =459 And Type <> 'BACKGROUND')) Order By PIECE

---------------------------------------------------------------------------------------------------Oracle select Index
-->> USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.

Select * From USER_TAB_COLUMNS Where table_name='XXXXX' And column_name='BUSINESS_UNIT'

--USER_TABLES.NUM_ROWS - IF NUM_DISTINCT = NUM_ROWS THEN USING an INDEX would be preferable TO doing a FULL TABLE SCAN.
--AS the NUM_DISTINCT decreases, the cost OF USING an INDEX increase thereby making the INDEX LESS desirable.

Select * From USER_TABLES Where table_name='XXXXX'

Select Clustering_Factor From USER_INDEXES Where index_name='XXXXX'

# USER_INDEXES.Clustering_Factor - This defines how Ordered The Rows are In The Index. If Clustering_Factor approaches The NUMBER Of Blocks In The Table, The Rows are Ordered. If it approaches The NUMBER Of Rows In The Table, The Rows are randomly Ordered. In such A Case, it Is unlikely that Index entries In The same leaf Block will Point To Rows In The same Data Blocks.

# Decrease The INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make The Cost Of A Full Table Scan cheaper.

----------------------------------------------------------------------------------------------- Descending index
Select owner, index_name, uniqueness, status
,INDEX_TYPE, Temporary, PARTITIONED, Logging, Degree, funcidx_status, join_index
From sys.ALL_INDEXES
Where table_owner = 'orauser'
And table_name = 'XXXXX'
Order By index_name

Select * From sys.ALL_IND_COLUMNS Where index_owner = 'orauser' And index_name = 'XXXXXX' Order By column_position

Select column_expression From sys.ALL_IND_EXPRESSIONS Where index_owner = 'orauser'And index_name = 'XXXXX'And column_position =3

Select
INDEX_NAME "Index Name"
,INDEX_TYPE "Index Type"
,UNIQUENESS "Uniqueness"
,STATUS "Status"
,TABLE_OWNER || '.' || TABLE_NAME "Table"
,TABLE_TYPE "Table Type"
,TABLESPACE_NAME "Tablespace"
,Buffer_Pool "Buffer Pool"
,initcap(partitioned) "Partitioned"
,decode(Temporary, 'N', 'No', 'Yes') "Temporary"
,INI_TRANS "Initial Transactions"
,MAX_TRANS "Max Transactions"
,INITIAL_EXTENT "Initial Extent Size"
,NEXT_EXTENT "Next Extent Size"
,MIN_EXTENTS "Minimum Extents"
,MAX_EXTENTS "Maximum Extents"
,PCT_INCREASE "Percent Increase"
,PCT_FREE "Percent Free"
,Freelists "Freelists"
,FREELIST_GROUPS "Freelist Groups"
,Degree "Degree"
,Instances "Instances"
,LAST_ANALYZED "Last Analyzed"
,BLEVEL "BLevel"
,LEAF_BLOCKS "Leaf Blocks"
,DISTINCT_KEYS "Distinct Keys"
,AVG_LEAF_BLOCKS_PER_KEY "Avg Leaf Blocks Per Key"
,AVG_DATA_BLOCKS_PER_KEY "Avg Data Blocks Per Key"
,Clustering_Factor "Clustering Factor"
,NUM_ROWS "Num Rows"
,SAMPLE_SIZE "Sample Size"
,Generated "Generated"
,decode(JOIN_INDEX, 'NO', 'No', 'Yes') "Join Index"
From SYS.USER_INDEXES
Where INDEX_NAME Like 'XXXXX'

--------------------------------------------------------------------------------------------Session Info
--::::::::::Sql statements still In The Cache that have done A great deal Of Disk I/O And Buffer gets. (Buffer gets show approximately The amount Of CPU Resource used.)
Select * From V$SQLAREA --WHERE module LIKE 'T.O.A.D%'

--::::::::: Shows active (in progress) transactions
Select Sid, serial#,s.status,username, terminal, osuser,
T.start_time, r.Name, (T.used_ublk*8192)/1024 USED_kb, T.used_ublk "ROLLB BLKS",
DECODE(T.Space, 'YES', 'SPACE TX',
DECODE(T.recursive, 'YES', 'RECURSIVE TX',
DECODE(T.noundo, 'YES', 'NO UNDO TX', T.status)
)) status
From sys.v_$transaction T, sys.v_$rollname r, sys.v_$session s
Where T.xidusn = r.usn
And T.ses_addr = s.saddr

Select
substr(s.username,1,18) username,
substr(s.Program,1,15) Program,
decode(s.command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
s.command||': Other') command
From
v$session s,
v$process P,
v$transaction T,
v$rollstat r,
v$rollname n
Where s.paddr = P.addr
And s.taddr = T.addr (+)
And T.xidusn = r.usn (+)
And r.usn = n.usn (+)
Order By 1
;

----------------------------------------------------------------------------------------------------- reset PW
Select username, Password
From DBA_USERS Where username='XXXX'

Alter USER XXXX Identified By XXXXX

------------------------------------------------------------------------------------------------- how many users in DB, CPU usage
Select rpad(c.Name||':',11)||chr(9)||chr(9)|| rpad(' Current logons='||(to_number(b.sessions_current)-1),20)||chr(10)|| 'cumulative logons='||rpad(substr(A.value,1,12),12)||chr(9)|| 'highwater mark='||b.sessions_highwater||chr(9)
From v$sysstat A, v$license b, v$database c
Where A.Name = 'logons cumulative'

Select substr(sn.Name,1,30) parameter, ss.username ||' ('|| se.Sid ||') ' user_process, se.value, ss.osuser,ss.process,ss.Program,ss.logon_time,ss.client_info,ss.last_call_et
From v$session ss, v$sesstat se, v$statname sn
Where se. statistic# = sn.statistic#
And sn.Name Like '%CPU used by this session%'
And se.Sid=ss.Sid
Order By sn.Name, se.value DESC

---------------------------------------------------------------------------------------------- Installed Packages
Select *--owner, object_type
From dba_objects Where object_name = 'UTL_FILE'

Select * From dba_objects Where OBJECT_TYPE='PACKAGE' Order By 2

Select *--owner, object_type
From dba_objects Where owner='PUBLIC' And OBJECT_TYPE='SYNONYM'
And OBJECT_name In (Select OBJECT_name From dba_objects Where owner='SYS' And OBJECT_TYPE='PACKAGE')

---------------------------------------------------------------------------------------------- Random 1/10 records select
select a.* from table1 a
where dbms_utility.get_hash_value(dump(key_field),0,100)<10

---- create 10000 random strings
select dbms_random.string('x', 10),
   case when level <= 5000 then 1 else 0 end,
   level
from dual
connect by level <= 10000

---------------------------------------------------------------------------------------------- Displaying multiple column values per row
http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm

my quick ksh

2009-05-29T13:22:00.000-07:00

Numeric test -

1.
nonums="$(echo $answer | sed 's/[0-9]//g')"

if [ ! -z "$nonums" ] ; then
echo "Not an integer value."
fi

2.
echo -n "Pick a number between 1 and 20: " read answer

nonums="$(echo $answer | sed 's/[0-9]//g')"

if [ ! -z "$nonums" ] ; then
echo "Not an integer value." exit 0
fi

if [ $answer -lt 10 ] ; then
echo "Your answer is less than ten"
else
echo "Your answer is not less than ten"
fi

3.
case xxx in
+([0-9]))
echo 1
;;
* )
echo 0
;;
esac

Parse file Name & Ext -

file=xxxx.yyy

nam=`echo ${file%.*}`
ext=`echo ${file#*.}`

remove CR from EOF -

tr '\012' ' '; echo

awk '{printf "%s ", $0} END { print "" }'

Ksh Version -

at a ksh prompt - print xxx

$ set|egrep -i ver

$ what /bin/ksh | egrep Version

Email -

simple text body:

cat file1 | mailx -s "subject" user@host.com
mailx -s "subject" user@host.com <>

attachments:

uuencode file1 file1 | mailx -s "subject" user@host.com
(uuencode file1 file1; uuencode file2 file2) | mailx -s "subject" user@host.com

both:

(cat file1; uuencode file1 file1;uuencode file2 file2 ) | mailx -s "subject" user@host.com

save attachment in mbox then uuencode to retrieve the file.

sendmail:

#!/bin/ksh
set -xv
# sendmail header function
fn_sendmail()
{
# Build sendmail header.
echo "From: user1@test.com"
echo "To: user2@test2.com"
echo "Reply-to: user3@test3.com, user4@user4"
echo "Subject: ${SUBJECT}"
echo "X-MSMail-Priority: High"
echo "importance: 1"
#echo "Mime-Version: 1.0"
#echo "Content-Type: text/plain"
#echo "Content-Type: multipart/mixed"
echo "

# Run the data function.
Hi XXX,

This is a test email

YYY
"
# uuencode the attachments, add checks if required file extension,
for k in file1 file2
do
uuencode $k $k
done

}

SUBJECT=test_subject

# Call sendmail header and pipe to sendmail.
(fn_sendmail)| sendmail -t -oi
##

My quick unix

2009-05-29T13:06:00.000-07:00

AIX v5.3
find -

modified > 3 days : find . -mtime +3
modified <>2 & < >

find only in current dir, exclude sub-dir: find . $ ! -name . -prune $ $ -name "xyz.dat*" -o -name "abc*" $

find in all sub-dirs: find . -type d -print

find only in 1st level sub-dirs: find . $ ! -name . -prune $ -type d -print

find only in 1st level sub-dirs, folder names matching: find . $ ! -name . -prune $ $ -type d -a \( -name "5AA00" -o -name "5X[0789]00" $ \)

more - http://www.unix.com/answers-frequently-asked-questions/13863-advanced-complex-uses-find-command.html

Grab file records by line #

awk '{if (NR>=11895 && NR<=11905) print $0}' xxx > yyy
awk 'NR==11895, NR==11905' xxx > yyy

awk '{if (NR==11895 || NR==11905) print $0}' xxx > yyy

groups - find user groups

find groups a user belongs to: groups user1 user2...
find users in a group: lsgroup -f group
(file: /etc/group)

IFS - change field delimiter

see current value:

$echo "$IFS" | od -b
0000000 040 011 012 012
0000004

$echo "$IFS" | od -c
0000000 \t \n \n
0000004

changing IFS to ","

IFS_SAV=$IFS
IFS=,
line='a,,b,c,d'
for x in $line
do
echo $x
done

ls - get file date time stamp

ls -l = modification time
ls -lu = access time
ls -l = creation time

port -

AIX - This file shows the usage of ports - /etc/services

/opt/freeware/sbin

lsof -i :port#

proctree -

proctree (-a) PID

procfiles -

procfiles (-n) PID

ps -
for user xxx
>> ps -fuxxx
>> ps -fl -uxxx

all jobs by xxx sort by cpu
>> ps -fuxxx | sort +3

all jobs related to PID ??????
>> ps -fL ??????

display environment of another process:

ps eww PID | tr ' ' '\n' | grep ORACLE_SID

check process priority:

ps -eo pid,state,nice,args | less -S

ps -lef

change priority:
nice/renice

read -
(break fields on a record/line into variables)

>>move 1st field of each line to end of line (xx=1st field, yy=rest of fields)
while read -r xx yy
do
print printf "%s %s/n" $yy $xx
done <>> To read a line and split it into fields, and use "enter ur name:" as a prompt
read word1?"enter ur name: " word2 word3 word4....

>> read more than 1 values into a variable (reak up the list by using for)
$ read x y z; echo $x; echo $y; echo $z
one two three four
$one
$two
$three four

>> show the oldest file
: ls -tr| read xxx; echo $xxx
: ls -tr |&
read -p $xxx
echo $xxx

>> read from a file into variables
exec 3<>

sed -

print a line with a pattern & the next line

search string = APPSTVCH.BI120-3

sed -n '
/APPSTVCH.BI120-3/ {
N
/\n.*1/ p
}' APPSTVCH_703295.log

sort -
>> sort by column 4-14 of 1st field:

sort -k 1.4,1.14

>> sort by numeric, 3rd field, delimited by ':'
sort -n -t ':' +2 /etc/passwd

>> second field as the sort key
sort -k 2,2 infile # new style
sort +1 -2 infile # old style

>> reverse sort, contents of infile1 and infile2, placing the output in outfile and using the second character of the second field as the sort key (assuming that the first character of the second field is the field separator):

sort -r -o outfile -k 2.2,2.2 infile1 infile2 # new style key definition used
sort -r -o outfile +1.1 -1.2 infile1 infile2 # old style key definition used

>> sorts the contents of infile1 and infile2 using the second non-blank character of the second field as the sort key:

sort -k 2.2b,2.2b infile1 infile2
sort +1.1b -1.2b infile1 infile2

>> prints the passwd(4) file (user database) sorted by the numeric user ID (the third colon-separated field):

sort -t ':' -k 3,3n /etc/passwd
sort -t ':' +2 -3n /etc/passwd
sort -n -t ':' -k 3,3 /etc/passwd

>> imitate uniq using sort: either of the following commands prints the lines of the already sorted file infile, suppressing all but one occurrence of lines having the same third field:

sort -um -k 3.1,3.0 infile
sort -um +2.0 -3.0 infile

The -n option informs sort to compare the specified field as numbers, not ASCII characters. The r option reverses the order of the sort.

sort -t: +5 -6 +0 -1 /etc/passwd # The output is now sorted by field 6, then by field 1 if necessary.

using TAB as delimiter, sort on 5th field: sort -t "`/bin/echo '\t'" +6 xx

touch -

- change DTTM stamp

03:04:55 a.m. on Jan 2, 1985
>>touch -t 198501020304.55 program.c

use the time stamp of another file
>>touch -r file1 program.c

avoid creating a new file, enter
>>touch -c program

Using Oracle Function Based Index for dynamic Search Key Column

2009-05-12T09:34:00.000-07:00

What the Report Says => What It Means

2009-02-02T13:07:00.000-08:00

http://rudy.ca/what-the-report-means.html

Peoplecode ODBC Function

2002-05-02T08:27:00.000-07:00

Open Database Connectivity (ODBC) allows an application to access data stored in variety of DBMSs. Commonly within an organization departmental data may be kept in their own specific repository. For example, you may need to look up a county code that's in DB2, a Commodity Code in SQL Server, or even your HR data in the same DBMS as your Peoplesoft on another server. Will you try to bring them into PS through a bunch of download and upload processes? What about keeping them in sync? This article will demonstrate a simple alternative by using ODBC interface APIs to achieve the goal.

To use ODBC in peoplecode, ODBC needs to be installed and configured properly for the target DBMS. Go to Control Panel and double click the ODBC Administrator. You should see something like this:

The Drivers tab lists all the drivers available for various DBMS. You also need to set up at least one DSN for the target DBMS. Here's a quick rundown on how the interface works:

1. Application (peoplecode) calls ODBC manager(odbc32.dll) with a series of requests
2. ODBC manager relays the requests to appropriate drivers as defined in DSN
3. platform specific ODBC driver performs DB query/update and send results back

Here's an example using the above information to read a Ship Order name from a push button on a panel:

When user clicks the Access button, the following data is read from Access into PS and displayed by Winmessage().

Let's now create a simple peoplecode function that will do the above, reading Access DB and retrieving a string, number and date field. The code uses v3 of 32-bit of ODBC driver. Create a User DSN as follows for the sample Northwind database included in Access:

(You can also set up System DSN which is available to all uses on client pc, add default UID and password. If you leave them blank, a prompt will be displayed)

Copy the following code into a push button FieldChange event:

Download Code Sample 1.

This section of code opens an ODBC connection with DSN we just set up in Read-Only mode. It then selects 3 fields from orders table and displays their values in a message box. The code demonstrates steps in establishing various connection handles, executing SQL statements, retrieving result columns, and disconnection steps.

This code example barely skims the surface of ODBC programming. It's wide range of function calls enables you to code what's best suited for your needs. For example, if a SQL is executed only once, you may use SQLExecuteDirect(). But you may use SQLPrepare() and SQLExecute() if the SQL will be run multiple times. The same applies to retrieval of data. It's a subject worth deeper exploration. Here's some points to consider:

The best place to use this code is in a lookup function. A push button brings in the data from a non-PS DBMS based on a PS field value. PS discourages the use of external function calls from inside a scroll and in all Save events, for obvious integrity and performance reasons, although I have gone as far as inserting rows of MS Access data into PS in a loop (by opening a 2nd connection into PS, thus bypassing all peoplecode Save restrictions). It may work for you if you examine your data thoroughly and explore appropriate ODBC functions.

Make sure your DSN is set up correctly before using it in peoplecode. I included a simple utility to help test your DSN.

If the DSN cannot connect, it will not work in peoplecode function.

Execute you SQL statements in native DBMS before using them in peoplecode. Each DBMS may use a specific syntax. For example, I have to use ‘#' to denote a date field in Access:

select orders.orderid from orders where orders.orderdate = #04/24/2002#

If the SQL does not work in it's native DBMS, it will not work in peoplecode function.

I find it easier to read all data in as Char, and then convert them into other data types rather than reading them in as the original data type.

The state of a table can effect the result of a query. For example, when the table is in the process of being altered or modified, or an Access table is opened by someone in Design mode, you may not get the correct results back.

After each function call, we are supposed to check the return code (&RC). If non-zero, it could indicate an error (SQL_ERROR), or success with warning (SQL_SUCCESS_WITH_INFO). Generally I found this to be true, but at times it's not consistent. Not sure why, it might have something to do with the call translation. As you can see, the check is omitted in the sample ( just rely on the result column values to be present.) You can certainly add them to the function.

A good practice is to set up User DSN with correct login information. Otherwise, a prompt will be displayed for user to login. This would require users to have knowledge about UID and password. Each ODBC driver has it's own parameters. The following is an ODBC setup for a secured Access database:

An alternative is to embed this info in your peoplecode SQLConnect string. But this also exposes the login information. The balance between maintenance and security are to be determined at your organization.

A good source of information is Microsoft's own ODBC page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/dasdkodbcoverview.asp

(this article was originally published on 05/02/02 at www.slerp.com. PSTools version 5,6,7)

Peoplecode OLE Automation

2002-03-14T08:34:00.000-08:00

OLE (Object Linking and Embedding) enables you to exchange data into/from any application that has registered previously in your system. Frequently used to populate data into Word, Access, or Outlook. Here I’ll skip the background information about OLE, and go right into demonstrating how to export data in a Grid into an Excel spreadsheet. There are 4 peoplecode OLE functions we’ll use to implement this: CreateObject, ObjectDoMethod, ObjectGetProperty, ObjectSetProperty.

Consider the Budget Inquiry panel in PS Financials:

When user clicks the Excel button, the Grid data is transferred into the spreadsheet:

Peoplecode behind the Excel button:

     Local object &WORKAPP;
Declare Function SendClip PeopleCode D_WIN32_FUNCLIB.D_CLIP_IO FieldFormula;
&WORKAPP = CreateObject("Excel.Application");
ObjectSetProperty(&WORKAPP, "UserControl", "True");
&WORKBOOKS = ObjectGetProperty(&WORKAPP, "Workbooks");
&WORKBOOK = ObjectDoMethod(&WORKBOOKS, "Add");
&WORKSHEETS = ObjectGetProperty(&WORKBOOK, "Worksheets");
&WORKSHEET = ObjectGetProperty(&WORKSHEETS, "Item", 1);
ObjectSetProperty(&WORKSHEET, "Name", "MyOLEtest");
&RANGE = ObjectGetProperty(&WORKSHEET, "Range", "A1:E1");
&FONT = ObjectGetProperty(&RANGE, "Font");
ObjectSetProperty(&FONT, "Bold", "True");
&HEADING = "Select" | Char(9) | "Budget Period" | Char(9) |
"Begin Date" | Char(9) | "End Date" | Char(9) | "Description";&DTL_LINES = "";
For &IDX = 1 To ActiveRowCount(RECORD.CTL_CRIT_SEG) Step 1;
&SELECT = FetchValue(CTL_CRIT_SEG.SELECT_BDGT_PERIOD, &IDX);
&BP = FetchValue(CTL_CRIT_SEG.BUDGET_PERIOD, &IDX);
&BEG_DT = FetchValue(CTL_CRIT_SEG.BEGIN_DT, &IDX);
&END_DT = FetchValue(CTL_CRIT_SEG.END_DT, &IDX);
&DESCR = FetchValue(CTL_CRIT_SEG.DESCR, &IDX);
&LINE = &SELECT | Char(9) | &BP | Char(9) | &BEG_DT | Char(9) |
&END_DT | Char(9) | &DESCR;
&DTL_LINES = &DTL_LINES | &LINE | Char(10);
End-For;
&XX = SendClip(&HEADING);
ObjectDoMethod(&RANGE, "Select");
ObjectDoMethod(&WORKSHEET, "Paste");
&XX = SendClip(&DTL_LINES);
&CELL = "E" | &IDX;
&RANGE2 = ObjectGetProperty(&WORKSHEET, "Range", "A2:" | &CELL);
ObjectDoMethod(&RANGE2, "Select");
ObjectDoMethod(&WORKSHEET, "Paste");
&IDX = &IDX + 1;
&CELL = ObjectGetProperty(&WORKSHEET, "Range", "F" | &IDX);
ObjectDoMethod(&CELL, "Select");
ObjectSetProperty(&WORKAPP, "Visible", True);
ObjectDoMethod(&WORKBOOK, "SaveAs", "c:\temp\oletest.xls");

Code Dissection

Local object &WORKAPP;

**Must declare the Object before instantiating it properly

Declare Function djSendClip PeopleCode
D_WIN32_FUNCLIB.D_CLIP_IO FieldFormula;

**Use a clipboard function posted at this site earlier, this allows us to copy data into clipboard and paste to spreadsheet-same way we manually paste data. Not the only way to do it, but it simplifies the job. You can copy in the code here or in a FUNCLIB as above

&WORKAPP = CreateObject("Excel.Application");
::::::::::::::::
&WORKSHEETS = ObjectGetProperty(&WORKBOOK, "Worksheets");

**Instantiate anExcelspreadsheet,hierarchy:Application->Workbooks->Workbook->Worksheet, allow user to control it after we are done

&WORKSHEET = ObjectGetProperty(&WORKSHEETS, "Item", 1);
ObjectSetProperty(&WORKSHEET, "Name", "MyOLEtest");

**Name sheet #1 MyOLETest

&RANGE = ObjectGetProperty(&WORKSHEET, "Range", "A1:E1");
:::::::::::::::::::::
ObjectDoMethod(&WORKSHEET, "Paste");

**Set title text of the 5 columns in Bold

&DTL_LINES = "";
For &IDX = 1 To ActiveRowCount(RECORD.CTL_CRIT_SEG) Step 1;
:::::::::::::::::::::::
End-For;
&XX = SendClip(&DTL_LINES);

**Prepares the grid data & copy to clipboard

&CELL = "E" | &IDX;
&RANGE2 = ObjectGetProperty(&WORKSHEET, "Range", "A2:" | &CELL);
ObjectDoMethod(&RANGE2, "Select");
ObjectDoMethod(&WORKSHEET, "Paste");

**the data will occupy cells A2 (Row 1 is title) through column E and whatever number of rows as the value &IDX

&IDX = &IDX + 1;
&CELL = ObjectGetProperty(&WORKSHEET, "Range", "F" | &IDX);
ObjectDoMethod(&CELL, "Select");

**move cursor to a new cell

ObjectSetProperty(&WORKAPP, "Visible", True);
ObjectDoMethod(&WORKBOOK, "SaveAs", "c:\temp\oletest.xls");
**display the spreadsheet and save it

You can further define formula, subtotal, sort cells, or set up a template that includes these formulas, subtotals etc. When you populate the cells, this additional information will be readily present. This method is a simple and efficient alternative to nVision reports. With your knowledge about specific application, you can invoke different methods to achieve the same results as we did here.

(this article was originally published on 03/14/02 at www.slerp.com. PSTools version 5,6,7)

Peoplecode FTP function

2002-02-14T08:38:00.000-08:00

Many processes are executed on the server for performance reasons. Their output and log files, such as process logs and *.spf files from SQR, are usually handled in the following ways:

loaded into a table to be viewed in a on-line panel

saved to another location and picked up through 3-party reporting tools

moved to another server by using a server script

Here's a simple alternative to the above methods - FTP to client directly and use the rich set of tools on the client side to view or manipulate it.

The function accepts 5 parameters:

FTP site

Peoplecode 3-tier Detection

2002-02-07T08:39:00.000-08:00

The Status Bar is a control that is at the bottom of a panel that displays information about the application status. It is divided into individual panes. Each pane displays specific aspects of the process/function the user is in. 5 panes are present in v.7 and 6 panes in v.8. This article will demonstrate a platform independent method to detect 3-tier connection by accessing the information stored on status bar, provided the database sever and application server have different names.

Take a look at a sample status bar:

The 1st pane does not have a border, and is used for panel loading. We are interested in the 3rd pane. It displays the server name, GF75DMO. In PS8, there’s another pane for Time, but server name is still on 3rd pane.

If server name is not displayed, turn it on in Configuration Manager Display tab:

Copy the function declaration:

Declare Function GetActiveWindow Library "user32"
() Returns long As number;
Declare Function GetWindow Library "user32"
(long Value As number, long Value As number) Returns long As number;
Declare Function GetClassNameA Library "user32"
(long Value As number, string Ref As string, integer Value As number) Returns long As number;
Declare Function SendMsg2 Library "user32" Alias "SendMessageA"
(long Value As number, integer Value As number, integer Value As number, string Ref As string)
Returns long As number;Function GetSB(&PANETEXT As string) Returns boolean;
&STATBAR = "msctls_statusbar32";
&HWND = GetActiveWindow();
&GW_CHILD = 5;
&GW_HWNDNEXT = 2;

&MAXLEN = 128;   &CLSNAME = Rept(" ", &MAXLEN);
&CWND = GetWindow(&HWND, &GW_CHILD);
While &CWND > 0
&CLSLEN = GetClassNameA(&CWND, &CLSNAME, &MAXLEN);
If &CLSNAME = &STATBAR Then
  Break;
End-If;
&CWND = GetWindow(&CWND, &GW_HWNDNEXT);
End-While;
If &CWND > 0 Then
&NPARTS = 0;
&CORD = 0;
&SB_GETTEXTA = 1026;
&RC = SendMsg2(&CWND, &SB_GETTEXTA, 2, &PANETEXT);
If &RC > 0 Then
  Return True;
End-If;
End-If;
Return False;
End-Function;

Test the function in a FieldChange event:

&XX = GetSB(&PANETEXT);
If &XX Then
If &PANETEXT = "MyAppSvr" Then
WinMessage("3-tier");
Else
WinMessage("2-tier");
End-If;
Else
WinMessage("fail");
End-If;

The server name is returned in &PANETEXT. Compare it to a list of known App Server names. If a match is found, then the user has logged into the App server.

As with all external Windows function APIs, if your app server is not NT, set the execution location of this code to client:

You can also read or write into this or other panes on the status bar. But there are some considerations when writing text to it. Please email the author if you’d like to see how to do it.

(this article was originally published on 02/07/02 at www.slerp.com. PSTools version 5,6,7)

Peoplecode Windows Registry Access

2002-01-11T08:44:00.000-08:00

Windows Registry stores various settings about the Windows system and other applications. To be able to access these information provides greater capability of your Peoplecode applications. For example, only a small number of Peoplesoft’s own settings (through Configuration Manager) are available as system variables. But they are all accessible in registry. The following are typical registry settings for 7.5 PSTools:

This Peoplecode function ReadReg() will let you glance at all the ASCII registry data of installed software under HKEY_CURRENT_USER, which contains the sub-key Software that describes the current user's software settings and contains program-specific information previously stored in the Win.ini or private initialization files in Windows 3.x.

To use the function, set &SUBKEY to the path in registry, as shown on the status bar above, and set &VALNAME to the Value Name in question. The Value Data will be returned in &KEY_VAL. For example, to read the value of PS_HOME, use subkey “Software\PeopleSoft\PeopleTools\Release7.5\Process Scheduler” and Value Name “PS_HOME”. Be sure to include spaces where needed. If the data string is longer than 128 bytes, you need to increase the value of &KEY_LEN.

Copy the function declaration:

Local number &H_REG_KEY;
Declare Function RegOpenKeyExA Library "advapi32" (long Value As number,
string Value As string, long Value As number, long Value As number,
long Ref As number) Returns long As number;
Declare Function RegQueryValueExA Library "advapi32" (long Value As
number, string Value As string, long Value As number, long Ref As
number, string Ref As string, long Ref As number)
Returns long As number;
Declare Function RegCloseKey Library "advapi32" (long Value As
number) Returns long As number;
Function ReadReg(&SUBKEY As string, &VALNAME As string,
&KEY_VAL As string) Returns boolean;
&HKEY_CURRENT_USER = - 2147483647;
&KEY_QUERY_VALUE = 1;
   &RC = RegOpenKeyExA(&HKEY_CURRENT_USER, &SUBKEY, 0,
&KEY_QUERY_VALUE, &H_REG_KEY);
If &RC <>  0 Then
MessageBox(48, "ReadReg", 0, 0, "Cannot open Registry");
Return False;
End-If;
/*key value type is null-terminated string */
&REG_SZ = 1;
/* initialize string to 128 bytes */
&KEY_LEN = 129;
&KEY_VAL = Rept(" ", &KEY_LEN);
&RC = RegQueryValueExA(&H_REG_KEY, &VALNAME, 0, &REG_SZ,
&KEY_VAL, &KEY_LEN);
If &RC <>  0 Then
MessageBox(48, "ReadReg", 0, 0, "Cannot query Registry");
Return False;
End-If;
&RC = RegCloseKey(&H_REG_KEY);
Return True;
End-Function;
Copy the following function call test:&SUBKEY = "Software\PeopleSoft\PeopleTools\release7.5\startup";
&VALNAME = "dbtype";
&KEY_VAL = "";
&XX = ReadReg(&SUBKEY, &VALNAME, &KEY_VAL);
If &XX Then
MessageBox(48, "ReadReg", 0, 0, &SUBKEY | "\" | &VALNAME | "=" | &KEY_VAL | ".");
Else
WinMessage("Fail");
End-If;

note: this article was originally published on 1/11/02 at www.slerp.com for PSTools version 6,7.  it uses ms windows system dll which is not accessible in PIA unless for a pure NT shop. still it provides an example on how to interface to OS library functions &  the peoplecode parameters passing.

PeopleCode Clipboard Function

2001-12-14T08:41:00.000-08:00

This function allows access to Clipboard where data can be pasted into a wide range of applications. My client uses it to output data into email to be sent off to other departments. For output that does not require extensive formatting, it's a quick & easy alternative to existing options, such as Crystal or nVision. For example, in a grid/scroll, where datacannot be seen on one screen, you can now select only columns (or other criteria) that are of interest and copy them to clipboard. This allows data to be selectively extracted/copied from a grid.

The following code declares the Clipboard function:

Declare Function OpenClipboard Library "USER32"
(long Value As number) Returns boolean;
Declare Function EmptyClipboard Library "USER32"
() Returns boolean;
Declare Function SetClipboardData Library "USER32"
(long Value As number, long Value As number) Returns long As number;
Declare Function CloseClipboard Library "USER32"
() Returns boolean;
Declare Function GlobalAlloc Library "kernel32"
(integer Value As number, long Value As number) Returns long As number;
Declare Function GlobalFree Library "kernel32"
(long Value As number) Returns long As number;
Declare Function lstrcpy Library "kernel32"
(long Value As number, string Ref As string) Returns long As number;
Declare Function GlobalLock Library "kernel32"
(long Value As number) Returns long As number;
Declare Function GlobalUnlock Library "kernel32"
(long Value As number) Returns long As number;Function djSendClip(&STR) Returns boolean;
&CLIPOK = False;
/* Clipboard format is text only */
&CF_TEXT = 1;
/* memory type is global fixed*/
&GMEM_FIXED = 0;
/* get 1 byte more than the text length */
&LEN = Len(&STR) + 1;
/* get a memory block from global heap , return global Handle*/
&H_GMEM = GlobalAlloc(&GMEM_FIXED, &LEN);
If &H_GMEM <> 0 Then
/*  lock memory address, retrun pointer to memory (same as H_GEMM for GMEM_FIXED) */
&LP_GMEM = GlobalLock(&H_GMEM);
If &LP_GMEM <> 0 Then
   /* copy string to global memory area, retrun long pointer to global string */
   &LPSTR = lstrcpy(&LP_GMEM, &STR);
   If &LPSTR <> 0 Then
      &B_RC = OpenClipboard(0);
      If &B_RC = True Then
         &B_RC = EmptyClipboard();
         If &B_RC = True Then
            &H_RC = SetClipboardData(&CF_TEXT, &LP_GMEM);
            If &H_RC <> 0 Then
               &CLIPOK = True;
            End-If;
         End-If;
         &B_RC = CloseClipboard();
      End-If;
   End-If;
   &RC = GlobalUnlock(&LP_GMEM);
End-If;
&RC = GlobalFree(&H_GMEM);
End-If;
If &CLIPOK = False Then
MessageBox(48, "SendClip", 0, 0, "Failed to copy to clipboard");
End-If;
Return &CLIPOK;
End-Function;

The following example uses the function above to copy a string to clipboard:

&XX = djSendClip("copy data to clipboard");If &XX Then
WinMessage("Success");
Else
WinMessage("Fail");
End-If;
(this article was originally published on 12/14/01 at www.slerp.com. PSTools version 6,7)