SFTP Process & SUDO command in APPS Shell Code

sftp process :

Create batch file which is executed in remote machine

Batch name : batch_file contents are

        lcd .
        $lv_pre_option
        $lv_ftp_cmd ${lv_file_name}.${timestamp_val}.gpg

3.Execute the batch

         sftp batch_file “remoteHostUser@remotHostName”

sudo command :

                     In Oracle Apps some of the special software(gpg encryption,decryption,sftp process) installed in specific user. So if you want to use those software we need to add software installed user as sudo user.

 sudo  -u “username” –H “barchName”

barchName – this batch is executed in other system. which holds some of the command to be executed in sudo user

WITHOUT USING PASSWORD WE CAN RUN THE UNIX COMMAND IN OTHER USER SYSTEM.

PDF Related UNIX Commands

Removing particular days before created files here the day is consider as 30

find "$from_path/" -maxdepth 1 -name "$FILENAME_PREFIX*" -mtime +30 -exec rm -rf {} \;

Merging multiple pdf into single pdf

gs -dNOPAUSE -sDEVICE=pdfwrite -sOUTPUTFILE=finalcombinedpdf.pdf -dBATCH <file1.pdf> <file2.pdf>

Decrypt the password protected pdf file

pdftk "FILENAME" input_pw "DOCPASSWORD" output "NewDoc.pdf"

Print the pdf in printer

acroread -toPostScript -shrink -size a4 -landscape |lp -d "PRINTERNAME" "NewDoc.pdf“

Sending EMAIL & MAIL Attachment from Shell Script/Unix Command

Sending mail without attachment

echo "Success Mail content" | mailx -s "Test mail" tgkq36@gmail.com

Sending mail with single attachment

uuencode <file.pdf>  <file.pdf> | mailx –s “Test Mail” tgkq36@gmail.com  <  body_content_file

Sending mail with multiple  attachment

uuencode <file.pdf > <sample1.pdf>    > data
uuencode <file2.pdf>  <sample2.pdf >  >> data
echo “Success “ >> data

Change the sender/from mail address

mailx –s “Test Mail” tgkq36@gmail.com -- -r “sendearmailaddress” ~c “ccaddres” < data

Mutt options :

mutt -s "Test mail" -a <NewDoc.pdf>  tgkq36@gmail.com < body_content_file

b address  (bcc)
c address (cc)
Note : multiple mail address separated by comma

SPOOL Process in Shell Script

spoolFunc()
{
        sqlplus -s /nolog <<!!|grep -v 'Connected'
        connect ${FCP_LOGIN};
        set colsep "|"
        set term off
        set feedback off
        set HEADING off
        set pagesize 0
        set verify off
        SET echo off
        SPOOL $MSIAP_TOP/bin/success.csv;
        SELECT 'ROLLNO|'||'NAME|'||'MARKS' from dual;
        select * from student;
        Spool OFF;
        Exit;
/!!
 }
spoolFunc

TELNET Process in Shell Script

 export FSS_DEST='host_name'
 export FTP_USER='username'
 export FTP_PWD='password‘
 (  echo open $FSS_DEST
    sleep 5
    echo $FTP_USER
    sleep 3
    echo $FTP_PWD
    sleep 3
    echo 'cd $directory_name‘
    echo "chmod 777 filename“
    echo 'some more output, etc.'
    echo 'exit'
) | telnet > /dev/null

FTP Process in Shell Script

 echo -e "user $user_id $password
      ascii
      prompt
      $source_dir
      lcd ${DATA_PATH}
      $ftp_command $process_file
      bye“ > ftpfile
  ftp -n "$remote_server" < ftpfile 2>&1 1> $FTPLOG
  if (grep "^Login incorrect." $FTPLOG)
     then
        echo "ERROR LOGIN INCORRECT"
  fi
  if (grep "^Not connected." $FTPLOG)
     then
        echo "ERROR FTP CONNECT"
  fi
  if (grep "No such file" $FTPLOG)
     then
        echo "ERROR FTP NO SUCH FILE"
  fi
  if (grep "access denied" $FTPLOG )
  then
        echo "ERROR FTP ACCESS DENIED"
  fi
  if (grep "^Please login" $FTPLOG )
  then
        echo "ERROR FTP LOGIN"
  fi

SQL Loader program in Shell Script

1. Check the control file is present
2. Check the data file present
3. Check the data file size is zero or not
4. Put dos2unix <filename> is used to remove newline character and control character , this is optional
5. Load the data into custom table using sql loader

Syntax :
sqlldr “db_user/db_passwd” control="controlfile path".ctl log="logfilepath".log bad="badfile path".bad  data="data file path“
Example :

sqlldr ${FCP_LOGIN} control="controlfile.ctl" log="logfile.log" bad="badfile.bad"  data="datafile“

Note : bad file is created only data file having invalid data content

Call sql files in Shell Script

Need to check the parameter (if parameter is passing to sql file)

Syntax

 sqlplus ${FCP_LOGIN} @’SQLFILE_NAME’ p1 p2

Check the return code status

sql file must end with exit statement

Example :

sqlplus ${FCP_LOGIN} @/sql/sample.sql

If [ “$?” = 0 ] ; then
   echo –e “sql statement is successfully executed”
fi

Get the values from Value Set in Shell Script in R12

getPathInfo()
{
                sqlplus -s /nolog <<!!|grep -v 'Connected'
                connect ${FCP_LOGIN};
                SET HEADING OFF;
                SET FEEDBACK OFF;
                SET LINESIZE 3000;
                SET SERVEROUTPUT ON SIZE 1000000;
                BEGIN
                DECLARE
                         value1                VARCHAR2(250) := '';
                         value2                VARCHAR2(250) := '';
                         BEGIN
                             BEGIN
                                   SELECT '10','20'
                                   INTO    value1 ,value2
                                   FROM dual;
                             EXCEPTION
                                  WHEN NO_DATA_FOUND  THEN
                                        DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
                                  WHEN OTHERS THEN
                                        DBMS_OUTPUT.PUT_LINE('ERROR');
                             END;

                              DBMS_OUTPUT.PUT_LINE(value1||'#'||value2);
                     END;
                END;
/
!!
}
value=`getPathInfo`
x=`echo "$value"|cut -d# -f1`
y=`echo "$value"|cut -d# -f2`
echo "x : $x“
echo "y : $y“ 

Output :

x : 10
y : 20

Note : change the select query to fetch the valueset values

Validate Directories and Files- Check file size is zero or not