Oracle Applications Technical Blog - By Anand Shankar: UNIX

Showing posts with label UNIX. Show all posts

Tuesday, August 20, 2019

UNIX - Send E-Mail with HTML Body and Multiple Attachments

Saw this question on several forums so thought to explain how to send e-mail from UNIX using sendmail with a HTML body and multiple attachments so below is the script for you to modify and use

font_text=`echo ""`
echo "To: me@domain.com" >> mime.txt
echo "From: someone@domain.com" >> mime.txt
echo "Subject: Your subject goes here" >> mime.txt
echo "MIME-Version: 1.0" >> mime.txt
echo "Content-Type: multipart/mixed; boundary=\"XXXXboundary text\"" >> mime.txt
echo "" >> mime.txt
echo "This is a multipart message in MIME format." >> mime.txt
echo "" >> mime.txt
echo "--XXXXboundary text" >> mime.txt
echo "Content-Type: text/html" >> mime.txt
echo "" >> mime.txt
echo "<html>" >> mime.txt
echo "<body>" >> mime.txt
echo "" >> mime.txt
echo "" >> mime.txt
echo "Dear User, " >> mime.txt
echo "Put your HTML body over here" >> mime.txt
echo "" >> mime.txt

#Below section you can remove if you do not have a table to send
echo "<table border=\"3\" cellpadding=\"10\">" >> mime.txt
echo "<tr bgcolor=\"cce6ff\">" >> mime.txt
echo "<td>${font_text}Column 1</td>" >> mime.txt
echo "<td>${font_text}Column 2</td>" >> mime.txt
echo "</tr>" >> mime.txt
echo "<tr>" >> mime.txt
echo "<td>${font_text}First column text</td>" >> mime.txt
echo "<td>${font_text}Second column text</td>" >> mime.txt
echo "</tr>" >> mime.txt
echo "</table>" >> mime.txt
#End of the table section

#Below is the signature or ending of the mail
echo "" >> mime.txt
echo "" >> mime.txt
echo "Thank You, " >> mime.txt
echo "UNIX Box " >> mime.txt
echo "NOTE: This is an automated e-mail from UNIX. Please do not respond to this message" >> mime.txt
echo "" >> mime.txt

echo "</body>" >> mime.txt
echo "</html>" >> mime.txt

#Put first boundary here between HTML body and first attachment
echo "--XXXXboundary text" >> mime.txt
echo "Content-Type: application/zip;name=test.zip; Content-Transfer-Encoding: base64;Content-Disposition: attachment" >> mime.txt
echo "" >> mime.txt
uuencode test.zip test.zip >> mime.txt
echo "" >> mime.txt
#Put second boundary here between first attachment and second attachment
echo "--XXXXboundary text" >> mime.txt
echo "Content-Type: application/zip;name=test2.zip; Content-Transfer-Encoding: base64;Content-Disposition: attachment" >> mime.txt
echo "" >> mime.txt
uuencode test2.zip test2.zip >> mime.txt
echo "" >> mime.txt

#If you have more attachments add the same as above from the boundary till the uuencode

#Put final boundary
echo "--XXXXboundary text--" >> mime.txt

cat mime.txt | sendmail me@domain.com

Tuesday, February 10, 2015

SFTP Batch Mode - Providing Password using EXPECT command

Dear All

Many may have come across requirement to connect to a remote server and SFTP a file from there to your Oracle server for further processing

We can do this in 2 ways as below

-----------------------------------------------------------------------------

Traditional Approach : Using Private-Public Key Pair setups

Steps to perform on Oracle server
1) Login to the app tier of the Oracle box as the applmgr (or user which is used to run HOST programs) user
2) In the home directory of the applmgr user, check to see if there is a directory .ssh (you need to use ls -al to see hidden directories)
3) If not, then create one and grant 755 permission to it
4) Execute the command ssh-keygen -t rsa
5) This will ask you a series of inputs, so just keep hitting enter until back to the prompt (do not provide any passphrase etc.)
6) In the .ssh directory you will now see 2 files id_rsa (your private key) and the id_rsa.pub (your public key)

Steps to perform on Remote server
1) Provide the id_rsa.pub file which was generated above to the remote server admin
2) Ask them to login using the user which will be used for actually performing the SFTP operation
3) Go to the home directory and check to see if a .ssh directory exists
4) If not, create it with 755 permissions
5) Inside the .ssh directory create a file called authorized_keys and provide it with 700 permissions
6) Copy the contents of the id_rsa.pub into the file (if already existing then append to it)

Now, back on the Oracle server try to do sftp username@hostname ... do this from the applmgr user and it should not prompt you for a password and hence batch SFTP connection is established

-----------------------------------------------------------------------------

Alternate Approach: Using EXPECT command to provide password hence not needing key files

The below command can be used as a base to write your script. The text in blue are all parameters for the user name, server, password etc

expect<<EOD
spawn /usr/bin/sftp $SFTP_USERNAME@$REMOTE_SERVER
expect "Enter password:"
send "$SFTP_PASSWORD\r"
expect "sftp>"
send "cd /out\r"
expect "sftp>"
send "get $FILE_NAME_PATTERN\r"
expect "sftp>"
send "quit\r"
EOD

In this approach you need not copy the public key to the remote server to enable batch mode SFTP

-----------------------------------------------------------------------------

Hope this helps you all sometime

Cheers

Friday, September 19, 2014

Upload XML Publisher files from File System

Dear Readers

I am sure when many of us have migrated XML publisher files (such as RTF files of templates, XML files of Data Templates, Bursting control files etc.), we have had to provide detailed document with screenshots so that person migrating the same knows how to upload these files to the database

Now, there is no need to do so. We can use the Oracle standard XDOLoader command to load the files from file system straight into the database without even opening the application

Below is an example of this for uploading a RTF template file. The same can be extended to other types of files and just we need to change the value of the parameter LOB_TYPE to any of the valid values mentioned below (from lookup XDO_LOB_TYPE)

Command
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD $apps_passwd -JDBC_CONNECTION $jdbc_conn_string
-LOB_TYPE TEMPLATE_SOURCE
-APPS_SHORT_NAME XXCUST
-LOB_CODE XXCUSTBIP1
-LANGUAGE en
-TERRITORY 00
-XDO_FILE_TYPE RTF
-FILE_CONTENT_TYPE application/rtf
-FILE_NAME /tmp/XXCUSTBIP_TEMPL1.rtf
-NLS_LANG $NLS_LANG
-CUSTOM_MODE FORCE

Valid Values for LOB_TYPE

LOB_TYPE_CODE	DESCRIPTION
BURSTING_FILE	Bursting Control File
DATA_TEMPLATE	Data Template
TEMPLATE	Template
TEMPLATE_SCHEMA	Template Schema
TEMPLATE_SOURCE	Template Source
XML_SAMPLE	XML Sample Data
XML_SCHEMA	XML Schema

How to find what to pass for XDO_FILE_TYPE and FILE_CONTENT_TYPE

If you are wondering how to determine the values to pass for XDO_FILE_TYPE and FILE_CONTENT_TYPE for files other than RTF templates, then all you need to do is query your already created record in XDO_LOBS in the instance where you manually loaded the file and it will show you the correct values

Hope this helps you all

Cheers
A

FNDLOAD De-mystified

Dear Readers

Many of us have always used FNDLOAD for various purposes

Here in this post, I have collected all the FNDLOAD commands that can be used for various objects

The DOWNLOAD commands are provided here-in and the UPLOAD is basically the same except the command ends with the ldt file name and no other parameters are needed for it

Concurrent Program
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ldt_file_name.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XXCUSTPROGRAM"

Profile
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct ldt_file_name.ldt PROFILE PROFILE_NAME="XXPROFILE" option APPLICATION_SHORT_NAME="XXCUST"

AOL Form
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ldt_file_name.ldt FORM FORM_APP_SHORT_NAME="XXCUST" FORM_NAME="XXCUSTFORM"

AOL Function
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ldt_file_name.ldt FUNCTION FORM_APP_SHORT_NAME="XXCUST" FUNCTION_NAME="XXCUSTFUNC"

OAF Page Function
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ldt_file_name.ldt FUNCTION FUNCTION_NAME="XXOAFFUNC"

XMLP Data Definition
FNDLOAD apps/$apps_password O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct ldt_file_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME="XXCUST" DATA_SOURCE_CODE="XXBIPUBTEST"

Lookup
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct ldt_file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ="XXCUST" LOOKUP_TYPE="XXCUSTLOOKUP"

Form Personalization
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct ldt_file_name.ldt FND_FORM_CUSTOM_RULES function_name="OEXOEORD" #Form FUNCTION ON which personalization IS made

Value Set
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ldt_file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="XXCUSTVALSET"

Responsibility
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ldt_file_name.ldt FND_RESPONSIBILITY RESP_KEY="XXCUSTRESP"

Request Set
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct ldt_file_name.ldt REQ_SET_LINKS REQUEST_SET_NAME="XXCUSTREQSET"

AOL Message
FNDLOAD apps/$apps_password O Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct ldt_file_name.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="XXCUSTMESSAGE"

WebADI Integrator
FNDLOAD apps/$apps_password O Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct ldt_file_name.ldt BNE_INTEGRATORS INTEGRATOR_ASN="XXCUST" INTEGRATOR_CODE="XXCUSTWEBADI"

Alert
FNDLOAD apps/$apps_password O Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct ldt_file_name.ldt ALR_ALERTS APPLICATION_SHORT_NAME="XXCUST" ALERT_NAME="XXCUSTOM Oracle Alert"

Hope this helps you all

Cheers
A

Wednesday, September 17, 2014

Using Oracle SRS Delivery Options to SFTP output

Dear Reader

I am sure many of us have faced the requirement where we need to SFTP the output generated by a program to a specific server and location

Maybe we have the requirement to generate a CSV file in specific location with specific naming conventions and then SFTP this to a remote server

Most of the times, we would go with the approach of using a custom Host program to perform the SFTP operation which will pick the CSV file we wrote in specific directory and send this to the remote server

However, when we use the custom program approach especially for SFTP, we need to create the private-public key combination on the 2 servers which may not be accepted by the remote server

Now, in R12, Oracle have given a way to do this using seeded functionality via Delivery options window on the SRS submission itself as shown below

We just need to enter the destination details here along with the password in this screen

Now, though this is fine if you want to SFTP the output file from $APPLCSF/$APPLOUT directory with the name as o<request_id>.out ... what if you want to SFTP a file from another directory with specific name such as SFTPTestingDelOpt20140917.csv ???

Then, this seeded approach alone will not work. So for this we need to add below update statement at the end of the program to update the outfile name in FCR table so that the delivery options will know the file to be transferred

lv_request_id is fnd_global.conc_request_id
lv_file_dir is the directory in which you created the file
lv_file_name is the file with name as SFTPTestingDelOpt20140917.csv

UPDATE fnd_concurrent_requests

SET outfile_name = lv_file_dir || '/' || lv_file_name

WHERE request_id = lv_request_id;

Now, many user may also complain that they do not want to enter the delivery options details each time when they run the program and rather they would have the same done automatically.

This can also be done by creating a row in FND_CONC_PP_ACTIONS as shown below. Here, the assumption is we pass the server name, user name, password and destination directory as hidden parameters to our program and it will create the delivery options internally and user need not to enter it ... simple isn't it :-)

SELECT processor_id
INTO lv_processor_id
FROM fnd_conc_pp_actions
WHERE concurrent_request_id = lv_request_id
AND ROWNUM < 2;

INSERT INTO fnd_conc_pp_actions

(

concurrent_request_id,

action_type,

status_s_flag,

status_w_flag,

status_f_flag,

last_update_date,

last_updated_by,

creation_date,

created_by,

last_update_login,

completed,

SEQUENCE,

argument1,

argument2,

argument3,

argument4,

argument5,

argument6,

ops_instance,

processor_id

)

SELECT lv_request_id,

'Y',

'N',

SYSDATE,

lv_user_id,--fnd_global.user_id

SYSDATE,

lv_user_id,--fnd_global.user_id

lv_login_id,--fnd_global.login_id

'N',

'S',--secure flag

p_server, --hidden parameter for SFTP server

p_username, --hidden parameter for SFTP username

p_password, --hidden parameter for SFTP password

p_dest_dir, --hidden parameter for SFTP destination dir

'22',--constant

-1,

lv_processor_id --derived above

FROM DUAL;

COMMIT;

Hope this helps you all

Cheers
A