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