Display line number in VI editor in Unix

Following command can be used to display line numbers

:set number 

Following command can be used to hide the line numbers

:set nonumber 

FNDLOAD - Menu Driven

Hi friends, before I proceed thanks to everyone reading and supporting the blog.

Here is another new post on FNDLOAD which provides a menu to select the option and execute FNDLOAD Command.
Below are the screenshots of how screen looks
1)Accepts the apps password. Note that the password is hidden and not displayed on the screen.

2)Displays a menu to select an option and Downloads/Uploads a .ldt file

Below is the script

#The script that Displays a Menu and calls FNDLOAD command accordingly. 
#Created by       Date            Version
#Suresh Vaishya   29-Jul-09       1.0
#http://sureshvaishya.blogspot.com

#Suresh Vaishya. Reading apps password outside loop to avoid entering same thing again and again.
echo "Enter APPS Password: "
stty -echo             #Turns echo off
read appspwd
stty echo              #Turns echo on

sel='123456789'
while true
do  
if [ $sel -ne '123456789' ]
then
echo "Press ENTER key to continue"
read key
fi

tput clear
echo "1.  Download Concurrent Program"
echo "2.  Upload   Concurrent Program"
echo "3.  Download Request Group for a program"
echo "4.  Upload   Request Group for  a program"
echo "5.  Download Value Set"
echo "6.  Upload   Value Set"
echo "7.  Download Menu"
echo "8.  Upload   Menu"
echo "9.  Download Descriptive Flexfield Definition"
echo "10. Upload   Descriptive Flexfield Definition"
echo "11.  Download Descriptive Flexfield Definition"
echo "12. Upload   Descriptive Flexfield Definition"
echo "13. Download Lookup Definition and Values"
echo "14. Upload   Lookup Definition and Values"
echo "15. Download Forms Personalization"
echo "16. Upload   Forms Personalization"
echo "17. Download Responsibility"
echo "18. Upload   Responsibility"
echo "q or Q. Quit"

echo "Enter your selection  "
read sel

#echo "You entered $sel"
if [ "$sel" = 'q' ] || [ "$sel" = 'Q' ]
then
exit
fi

if [ $sel -gt 0 ] && [ $sel -lt 19 ]
then
if [ `printf "%d\n" "'$sel"` -gt 57 ] || [ `printf "%d\n" "'$sel"` -lt 49 ] # Suresh Vaishya. Using the ascii value to check valid values
then
echo "You entered $sel"
echo "Invalid selection. Valid value are from 1 to 18."
else
echo "Enter .ldt Name: "
read ldtname

#Suresh Vaishya   Download Concurrent program
if [ $sel -eq 1 ]
then
echo "Enter Application Short Name: "
read applname

echo "Enter Concurrent Program Short Name: "
read cpname

FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $ldtname PROGRAM APPLICATION_SHORT_NAME="$applname" CONCURRENT_PROGRAM_NAME="$cpname"

echo "LDT File $ldtname created"
#Suresh Vaishya Upload Concurrent program
elif [ $sel -eq 2 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $ldtname - CUSTOM_MODE=FORCE
echo "LDT File $ldtname uploaded"
#Suresh Vaisha Download Request Group
elif [ $sel -eq 3 ]
then
echo "Enter Request Group Application Short Name"
read applname

echo "Enter Request Group Name"
read rgname

echo "Enter Program Short Name"
read cpname

FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct $ldtname REQUEST_GROUP REQUEST_GROUP_NAME="$rgname" APPLICATION_SHORT_NAME="$applname" REQUEST_GROUP_UNIT UNIT_NAME="$cpname"
#Suresh Vaishya Upload Request Group
elif [ $sel -eq 4 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct $ldtname
echo "LDT File $ldtname uploaded"
#Suresh Vaishya   download Value Set
elif [ $sel -eq 5 ]
then
echo "Enter Value set Short Name"
read vsname

FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname VALUE_SET FLEX_VALUE_SET_NAME="$vsname"
echo "LDT File $ldtname created"
#Suresh Vaishya Upload Value Set
elif [ $sel -eq 6 ]
then
FNDLOAD apps/$appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname
echo "LDT File $ldtname uploaded"
#Suresh  Vaishya Download Menu 
elif [ $sel -eq 7 ]
then
echo "Enter Menu Name"
read mname
FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct $ldtname MENU MENU_NAME="$mname"
#Suresh Vaishya Upload Menu 
elif [ $sel -eq 8 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $ldtname
#Suresh    Vaishya Download DFF
elif [ $sel -eq 9 ]
then
echo "Enter Application Name"
read applname
echo "Enter Descriptive Flexfield Name"
read dff
FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname DESC_FLEX APPLICATION_SHORT_NAME="$applname" DESCRIPTIVE_FLEXFIELD_NAME="$dff"
#Suresh  Vaishya Upload DFF
elif [ $sel -eq 10 ]
then
FNDLOAD apps/$appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname
#Suresh Vaishya Download KFF
elif [ $sel -eq 11 ]
then
echo "Enter Application Name"
read applname
echo "Enter Key Flexfield Name"
read dff
FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname KEY_FLEX APPLICATION_SHORT_NAME="$applname" DESCRIPTIVE_FLEXFIELD_NAME="$dff"
#Suresh  Vaishya Upload KFF
elif [ $sel -eq 12 ]
then
FNDLOAD apps/$appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname

#Suresh Vaishay Download Lookup type
elif [ $sel -eq 13 ]
then
echo "Enter Application Name"
read applname
echo "Enter Lookup Type Name"
read lname
FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct $ldtname FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="$applname" LOOKUP_TYPE="$lname"
#Suresh Vaishya Upload Lookup Type
elif [ $sel -eq 14 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct $ldtname
#Suresh Vaishya Download Forms Personalization
elif [ $sel -eq 15 ]
then
echo "Enter Function Name for the form"
read fname
FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct $ldtname FND_FORM_CUSTOM_RULES FUNCTION_NAME="$fname"
#http://sureshvaishya.blogspot.com Upload forms personalization
elif [ $sel -eq 16 ]
then
FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct $ldtname
#Suresh Download Responsibility
elif [ $sel -eq 17 ]
then
echo "Enter Responsibility Key"
read rname
FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct $ldtname FND_RESPONSIBILITY RESP_KEY="$rname"
#Suersh Upload Responsibility
elif [ $sel -eq 18 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct $ldtname
fi

fi # ascii if
else
echo "You entered $sel"
echo "Invalid selection. Valid value are from 1 to 18."
fi # number check if
done
# End of Script. http://sureshvaishya.blogspot.com

How to complete Host(Unix Shell Script) Concurrent Program with Warning

If we have a concurrent program as of type host(Unix Shell Script), then the program by default either completes Normal or in Error.
If we return from shell script using text exit 0, program completes in normal and if we use any value other value e.g. exit 1, exit 2, exit 10 etc then the program completes in error.
Below is the code that can be used if requirement is to complete it in warning.

sqlplus -s $p_usr_pwd <<-EOF
  set feedback off
  declare
    l_stat Boolean;
    l_session_id Number;
  begin
    fnd_global.initialize(session_id => l_session_id
                        , user_id => fnd_global.user_id
                        , resp_id => fnd_global.resp_id
                        , resp_appl_id => fnd_global.resp_appl_id
                        , security_group_id => null
                        , site_id => null
                        , login_id => null
                        , conc_login_id => null
                        , prog_appl_id => null
                        , conc_program_id => null
                        , conc_request_id => $p_req_id
                        , conc_priority_request => null);
    l_stat := fnd_concurrent.set_completion_status('WARNING','Completed in Warning. Review log for details.');
    commit;
  end;
/
  exit;
EOF

In the example above $p_usr_pwd stores database username/password, $p_req_id stores the request ID for concurrent request that needs to complete in warning.

Related Post:
Concurrent program parameter in Unix Shell Script
Calling SQLPLUS from unix shell script

In a directory in Unix convert Tab Delimeted file to Comma delimeted

On Request from one of our reader here is a post to convert excel files into a comma delimeted file.

create shell script using following code. The script below will fetch all files with extension xls and convert tabs into comma and create a .csv file.

cd /applmgr/custom/inbound/data
for i in *.xls
do
echo $i
newfile=$i.csv
awk 'BEGIN {
        FS = "\t"
        OFS = ","
     }
     {
        $1 = $1
        for (i = 1; i <= NF; i++) {
           if ($i == "") {
              $i = "null"
           }
        }
        print $0
     }' $i > $newfile
done

SQLLDR - Shell script to load Multiple files in table

Based on request from one of our reader, here is the post to read file from a directory and then call sqlloader command to load data file into a table.
Assumptions:
1) There has to be some way/standard to recognize the file name. In my case its a .dat file starting with sample. If you don't know data file name then create a directory specific to the load and select all data files from that directory.
2) The file format has to be same so that same .ctl file can be used to read the file and load the table.
3) Once loaded the file is then archived.

Control file code

LOAD DATA
insert into table sv_temp
fields terminated by '|' optionally enclosed by '"'
(first_col
,second_col
)

Shell Script Code

cd $CUSTOM_TOP/data
for file in sample*.dat
do
sqlldr userid=$login control=$CUSTOM_TOP/bin/sv_test.ctl data=$CUSTOM_TOP/data/$file
mv $CUSTOM_TOP/data/$file $CUSTOM_TOP/archive/$file
done

3 files sample1.dat, sample2.dat, sample3.dat was copied in CUSTOM_TOP/data directory.
Sample Output is

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 8
Commit point reached - logical record count 9

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 5

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 7

SQL Loader (SQLLDR) in Oracle Applications

One of the executable type in Oracle applications is SQL*Loader. In this post we will discuss more about how to define, create and use this type of Execution Method.

Execution Method SQL*Loader is used to load data into database table using SQL*Loader. Below are the steps that needs to completed
1) Create Control File and copy it in the TOP/bin directory. E.g. if your custom application top directory is $XX_TOP, then the file should be copied in $XX_TOP/bin directory

OPTIONS (SKIP=1)
LOAD DATA infile '%1'
APPEND INTO TABLE sv_test_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
(
   item_number     "trim(:item_number)"
 , description     "trim(:description)"
 , attribute1      "trim(:attribute1)"
 , process_flag    Constant 'UNPROCESSED'
)

2) Create an executable with execution method as SQL*Loader and use the control file Name as Execution file Name. The Application should same as where the file is copied.

3) Create a concurrent program definition and use the executable created above.
It is optional to have any parameters to the program, but in this case the data file name should be same as control file name except the extension for the data file being .dat
If you notice in the control file definition above I have used %1 as the infile name. This is how parameters are referenced in control file.
So now we can create conc. program with a file name as a parameter(Full file name with path should be entered and this is case sensitive.)

4) Assign this program to the request group and it is ready to be used.

SQL Loader limit number of rows

Based on a query from one of our reader here is an example to illustrate how to limit number of rows to be loaded in SQL Loader. This can be done using option LOAD

OPTIONS (SKIP=1, LOAD=10, ERRORS=5)
LOAD DATA infile c:/sv_test.dat
REPLACE INTO TABLE sv_test_sql_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
    item_number     "trim(:item_number)"
  , vendor_name "trim(:vendor_name)"
  , vendor_site_name "trim(:vendor_site_name)"
  , supplier_item   "trim(:supplier_item)"
  , process_flag  Constant 'UNPROCESSED'
)

In the example above the total records to be loaded is limited to 10, error records is 5 and 1 record is skipped.

These options can also be given with sqlldr command as follows

sqlldr control='sv_test.ctl' data='sv_test.dat' load=10 errors=5  skip=1

Keywords: SQL*LOADER, ERRORS, SKIP, LOAD

Command/Code to get ASCII value in UNIX

Found a method to ASCII to character and vice versa in unix and thought of sharing it.
Below is the code.

## Character to ASCII
printf "%d\n" "'A"

## ASCII number to character
awk -v char=65 'BEGIN { printf "%c\n", char; exit }'

Set use of VI Commands at the UNIX prompt

VI commands can be used at the UNIX prompt to retrieve history commands or modify already typed command using VI editor keys. This can be done by executing following command

set -o vi

One of the option is to enter and save this command in .profile file, so that everytime we login in UNIX this command is executed.

Preserve Timestamp, mode, owner while using copy command in Unix

In Unix, Sometimes when we need to take a backup of the file we generally add timestamp along with the file and create a backup, however the new file created has the creation date as system date.
This can be avoided using preserve option in unix.
For e.g. I will copy ARXSGPO.rdf from $AR_TOP/reports/US directory. The file signature looks like

-rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO.rdf

Now the following files are created using preserve and no preserve option

 cp ARXSGPO.rdf ARXSGPO_np.rdf   # without preserve option
 cp -p ARXSGPO.rdf ARXSGPO_p.rdf # with preserve option

This is how the signature of file looks like

 -rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO.rdf
 -rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO_p.rdf
 -rw-r--r--  1 applmgr oracle 647168 May  3 23:43 ARXSGPO_np.rdf

It can be noted that ARXSGPO_np.rdf has timestamp as May 3, whereas file ARXSGPO_p.rdf created using preserve mode has timestamp as Apr 21. So using preserve option we can save the original information of the file from where the file is copied.

Store SQLPLUS output to a variable in Unix Shell Script

In response to one of the question asked, here is the solution of how to call sqlplus and store the output in a variable.
In the script below I am connecting to sqlplus through unix and querying fnd_lookup_values table and storing the output of query in a unix variable. This variable can then be later referred in the code using $.

#Suresh Vaishya:The script connects to sqlplus and stores output of a query into a unix variable.
#The output of query is seperated by '~' to identify different record values.

login='apps/apps'
code=`
sqlplus -s $login <set heading off
set feedback off
SELECT lookup_code||'~' FROM fnd_lookup_values WHERE lookup_type ='AGREEMENT LINES' and enabled_flag = 'Y' and Nvl(end_Date_ac
tive,sysdate+1) > sysdate and language='US';
exit
EOF`
echo 'Output of SQL Query is '
echo $code
echo 'End of Script'

The script output is

Output of SQL Query is
BLANKET~ ITEM~ MAIN~ NOTES~ PRICE~ QUOTATION~
End of Script

Related Post
Calling SQLPLUS from unix

Basic VI Editor Commands

Cursor Movements

h, j, k, l	Move left, Down, Up, Right
0	Move to First Character of the Line
$	Move to end of Line
w	Move to next word
CTRL + D	Move Page Down
CTRL + U	Move Page Up
G	Move to the End of File
1G or :1	Move to the Top of File
nG	Move to Line number n. (replace n is any integer value)
CTRL + G	Display current Line Number

 

Find a Text

/text	Find a text in Forward Direction
?text	Find a text in Reverse Direction
n	Repeat the previous Search

Note: text needs to be replaced by the string that needs tobe searched.

 

Editing, Inserting orDeleting a Text

a	Append after Cursor
A	Append to the end of Line
i	Insert before cursor
I	Insert from the Beginning of the line
o	Insert a new Line in the edit mode
cw	Change word
cc	Change whole line
C	Change text to end of line
dd	Delete Current Line
5dd	Delete current line and next 4
D	Delete from Current Position to the end of line
u	Undo last change
U	Restore Current Line

 

Copy(yanking) and Paste

yy	Yank a copy of Current Line
p	Insert previously yanked Line

 

Saving and Exiting

ZZ or :wq	Save and exit file
:q!	Exit without Saving
:w filename1	Save edited file as filename1. Similar to Save as.

Calling SqlPlus From Unix Shell Script

In this post we will discuss how we can call sqlplus from within a unix shell script. Here is a post on how to read concurrent program parameters from shell script and this is a continuation to that.
The syntax to call a SQLPLUS from shell script is

sqlplus -s $login <<-EOF
set feedback off
set serveroutput on
declare
--  variable declarations
....
....
begin
--sql statements..
....
....
end;
/
exit
EOF

In the syntax above,
$login is the unix variable which stores username/password.
EOF is the identifier to indicate start and end of sql statements.
UNIX variables can be referred inside sqlplus by using $ sign. Any dbms_output.put_line statements are printed in the log file if script is run through concurrent program.

Read Concurrent Parameters in UNIX shell script

Found this question in one of the forum and thought of creating a blog for same.
Often when concurrent program is created on a shell script there is a need to extract parameters passed from concurrent program in the shell script.
In Shell Script, $1 is the variable that stores following information
- Concurrent Program Short Name
- Request ID
- Login Information(APPS username and password)
- User Id
- User Name
- Printer Name
- Save output Flag
- Print number of Copies
- List of concurrent program parameters

Following code can be used to extract different values

requestid=`(echo $1 | cut -f2 -d' ' | cut -f2 -d= | tr -d '"' )`   #request_id
login_usr_pwd=`(echo $1 | cut -f3 -d' ' | cut -f2 -d= | tr -d '"' )` #database username/password
conc_user_id=`(echo $1 | cut -f4 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #userid
conc_user_name=`(echo $1 | cut -f5 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #username
prog_param1=`(echo $1 | cut -f9 -d' ' | tr -d '"' )`  #parameter 1 
prog_param2=`(echo $1 | cut -f10 -d' ' | tr -d '"' )`  #parameter 2 
prog_param3=`(echo $1 | cut -f11 -d' ' | tr -d '"' )`  #parameter 3 
prog_param4=`(echo $1 | cut -f12 -d' ' | tr -d '"' )`  #parameter 4 
prog_param5=`(echo $1 | cut -f13 -d' ' | tr -d '"' )`           #parameter 5 
prog_param6=`(echo $1 | cut -f14 -d' ' | tr -d '"' )`           #parameter 6

When connecting to SQLPLUS in shell script, it is always advisable to extract database username and password from parameter($login in our example above) and not hard-code the value in program.

Remove Control M (^M) Characters From Unix File

If you see control M characters(^M) in a file in UNIX, don't worry there is a very simple way to convert the file in UNIX format.
The command is

dos2unix filename [newfilename]

e.g. remove control characters from file abc.txt

dos2unix abc.txt abc.txt  #this will replace abc.txt and remove control characters

dos2unix abc.txt xyz.txt  #a new file xyz.txt will be created after removing control characters

So isn't that simple lovely command took off all the worries.

Tips and Tricks with SQL Loader

Following are some of the tips and tricks that can be used with SQL Loader

1) Load text for a column which is having more than 4000 bytes.
Use following syntax

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)
)

2) How to use oracle functions with SQL Loader

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)   --- first 40000 characters
, column1  "trim(:column1)"       --- Trims and loads
, column2  "replace(:column2,'\n',chr(10))"  --- replace \n with new line
, column3  DATE "DD-MON-YYYY"   --- defining date style
, column4  "upper(:column4)"      --- changing to upper case
, column5  "lower(:column5)"      --- changing to lower case
, column6  constant "FIXED"      --- assigning a constant value
, column7  "sv_sequence.nextval"  --- defaulting a value from sequence
)

Thats it for today .. will update with more commands later.

Securing FTP in shell scripts using .netrc

Often we use FTP in the shell scripts and for security reason it is advisable not to store username and password in the shell scripts.
Here I discuss how can we secure the FTP process and restrict sharing of username and passwords.

File .netrc in the $HOME directory allows file transfers in batch mode. This file stores the machine name, login and passwords. The FTP commands gets information from the file and connects to the FTP server.
Each record has the format:

machine machine_name login login_name password passwd
where machine_name, login_name, passwd refer to a system name with the login and password for that account on the machine
e.g machine xyz.server.com login anonymous password xyz123

There are following 2 ways to execute the FTP commands
1) Create a command file and store all the FTP commands in the file.
e.g. File command_ftp is created and saved. The file has following content

bin
cd /inbound
get abcd.txt
bye

Write following code in the shell script

ftp xyz.server.com < command_ftp

2) The FTP commands can also be stored in .netrc file as a macro and the commands will be executed with the FTP command.
The .netrc file content would be something like this

machine xyz.server.com login anonymous password xyz123
macdef bin
cd /inbound
get abcd.txt
bye

For this case the shell script will have following ftp command
e.g.
ftp xyz.server.com

The other way of securing FTP is by using the sftp(secure FTP) commands which is a network protocol that provides file transfer and transfers file in a secure way.