Unix Tips for Oracle Apps Developers

Just thought of sharing few Unix tips.

Mailing without an attachment:

email_ids=”sysadmin@testemail.com analyst@testemail.com”
echo “GL Daily Interface Completed.” | mailx -s “GL Daily Interface” $email_ids

You can also use the email id directly like below:

echo “GL Daily Interface Completed.” | mailx -s “GL Daily Interface” sysadmin@testemail.com

Mailing with an attachment:

email_ids=”sysadmin@testemail.com analyst@testemail.com”
echo “GL Daily Interface – Output” | mailx -s “GL Daily Interface” -a GL_Daily_Interface_$fileDate.txt -a GL_Journal_Import_$fileDate.txt $email_ids

-s = parameter used to identify the Subject of the mail
-a = parameter used to identify the file to be attached
echo = used to print a string to the log file or in the above case the string will be printed as part of the Email Body

Formatting System Date

echo $(date +%m%d%y%H%M%S)  // The date will be printed as MMDDYYHH24MMSI format.

echo $(date +%m%d%Y%H%M%S) // The date will be printed as MMDDYYYYHH24MMSI format

echo $(date +%F%H%M%S) // The date will be printed as YYYY-MM-DDHH24MISS format

Appending System Date as part of the File Name

This is a very common requirement. Once you process a file and want to archive it, it is a good practice to append the date with the processed file.
You can also generate an outbound file and want to append the date at the end of the file name. I have already mentioned how to format the date in the topic before this one.

Let us say you want the file name to be like “FileName_MMDDYYHH24MISS.txt” format.

in_folder=”/data/interfaces/in/GL”
archive_folder=”/data/interfaces/archive/GL”
fileDate=$(date +%m%d%y%H%M%S)
mv $in_folder/GL_INTERFACE.txt $archive_folder/GL_INTERFACE_$fileDate.txt

The “mv” command will move the file GL_INTERFACE.txt from source directory to the target directory. We are using GL_INTERFACE_$fileDate.txt for the target file name.
In the target folder, the file name will be appended with the Date. “mv” also removes the file from the source directory.

Connect to SQL From Unix

If you have a host concurrent program, it is very likely that you will have to connect to the Database to retrieve values or run some queries.
To connect to SQL from Unix (if you are using a host concurrent program), please use the below syntax. The below example connects to the database and retrieves the output file name of GL Journal Import Program.

file_name=$(sqlplus -s $1 <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
SELECT outfile_name
FROM fnd_concurrent_programs fcp, fnd_concurrent_requests fcr
WHERE concurrent_program_name =’GLLEZL’
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND TRUNC(request_date) = TRUNC(SYSDATE)
AND rownum = 1;

exit;
EOF
)

$1 in the above example will pass the Oracle Username and password to connect to the database.
file_name is the variable used to hold the query output.

I will add more when I get time. For now, I hope this helps.

If you have questions or need help, please feel free to post your questions and I will try my best to help!!