Recently i have been challanged with the question as where i would recieve mail using postfix and insert this email in my oracle Database 10g. There are many ways of handling this question as for me , i choose to use PHP scripting to do the trick.
The PHP script has the ability to filter out the information by To, From, Subject and Message. After all parts are filtered out, we can start to create our insert statement.
The PHP language is powerfull as also the extensions for supporting Oracle databases. Mainly there are 2 ways of handling Oracle connection from PHP. The OCI protocol which stands for Oracle Call-Interface and the ORA protocol which use the Normal Oracle function.
As for now i choose to use the OCI protocol as it seems to give me a more stable connection to my Oracle database.
Here i will show you the code i use to recieve an Email through postfix. ( add this code to your aliases file for recieving mail on reciever@YOUR-DOMAIN.COM , remember to run newaliases to update your aliases.db !)
##########################################################
# PIPE Mail naar Oracle DB Poster
##########################################################
reciever: "|/etc/postfix/post_mail_reciever.php"
After recieving an email , the email is passed into our script where it will be filtered and inserted into the database. The script i use to do this looks like this : ( copy paste this as /etc/postfix/post_mail_reciever.php ) Make sure you provide enough rights for the script to be executed by user “nobody”.
#!/usr/local/bin/php
< ?php
###############################################
# Read from stdin
###############################################
$fd = fopen("php://stdin", "r");
$email = "";
while (!feof($fd)) {
$email .= fread($fd, 1024);
}
fclose($fd);
###############################################
# Handle email
###############################################
$lines = explode("\n", $email);
###############################################
# Empty vars
###############################################
$from = "";
$subject = "";
$headers = "";
$message = "";
$splittingheaders = true;
###############################################
# Email in vars
###############################################
for ($i=0; $i < count($lines); $i++) {
if ($splittingheaders) {
// this is a header
$headers .= $lines[$i]."\n";
// look out for special headers
if (preg_match("/^Subject: (.*)/", $lines[$i], $matches)) {
$subject = $matches[1];
}
if (preg_match("/^From: (.*)/", $lines[$i], $matches)) {
$from = $matches[1];
}
}
else
{
// not a header, but message
$message .= $lines[$i]."\n";
}
if (trim($lines[$i])=="") {
// empty line, header section has ended
$splittingheaders = false;
}
}
###############################################
# Replace Chars in String
###############################################
$message = str_replace("'" , "\"" , $message);
################## TEMP ################
# Debug mail , for storing recieved mail
########################################
# ECHO "----------HEADERS---------------------/n";
# ECHO $headers;
# ECHO "----------FROM------------------------/n";
# ECHO $from;
# ECHO "----------Subject---------------------/n";
# ECHO $subject;
# ECHO "----------MEssage----------------------/n";
# ECHO $message;
# ECHO "-------------------------------------";
# $save_path='/tmp';
#
# $date = date("H:i:s,d-m-Y");
# $temp='file.tmp';
# $dest= "MAIL_".$date;
#
#
# $fp = fopen($save_path.'/'.$temp, "w", 0);
# fputs($fp, $email);
# fclose($fp);
#
# rename($save_path.'/'.$temp,$save_path.'/'.$dest);
#
#######################################
###############################################
# Enviroment
###############################################
PutEnv("ORACLE_SID=ORCL");
PutEnv("ORACLE_HOME=/u00/oracle/product/10.2.0/db");
PutEnv("TNS_ADMIN=/u00/oracle/product/10.2.0/db/network/admin");
###############################################
# Mail Functions
###############################################
function send_mail($status , $errcode , $errmsg , $erroff , $errsql) {
$mail = "error_mail@YOUR_DOMAIN.COM";
##############################
# Create Error mail attachment
##############################
$save_path='/etc/postfix';
$temp='file.tmp';
$dest='error.txt';
$date = date("H:i:s d-m-Y");
$fp = fopen($save_path.'/'.$temp, "w", 0);
fputs($fp, "[".$date."] Error Code : ORA-".$errcode."\n\n");
fputs($fp, "[".$date."] Error MSG : ".$errmsg."\n");
fputs($fp, "[".$date."] Error Pos : ".$erroff."\n\n");
fputs($fp, "[".$date."] Error SQL : ".$errsql."\n\n");
fclose($fp);
rename($save_path.'/'.$temp,$save_path.'/'.$dest);
##############################
$command = "mail -s $status $mail < /etc/postfix/error.txt";
shell_exec($command);
}
###############################################
# Main
###############################################
$error = "";
$db = "ORCL";
$connect = OCILogOn("scott", "tiger", $db);
$query="begin scott.general.p_accept_mail_from_postfix('$from',sysdate,'$subject','$message'); end;";
if ($connect)
{
## Parse Error catch
$parse = ociparse($connect, $query);
if (!$parse) {
$error = OCIError($connect);
send_mail("MAIL_PARSE_ERROR(ORCL)", $error['code'] ,$error['message'] , $error['offset'] , $error['sqltext']);
}
ociexecute($parse);
## Execute Error catch
$error = OCIError($parse);
if($error){
send_mail("MAIL_EXECUTE_ERROR(ORCL)",$error['code'] ,$error['message'] , $error['offset'] , $error['sqltext']);
}
$committed = ocicommit($connect);
## Commit Error catch
if (!$committed) {
$error = OCIError($committed);
send_mail("MAIL_COMMIT_ERROR(ORCL)",$error['code'] ,$error['message'] , $error['offset'] , $error['sqltext']);
}
# We can turn this on when we want to also monitor every successful processed Mail
#else
#{
# send_mail("EM-Mail-Processed","OK" , "OK" , "OK" , "OK");
#}
ocilogoff($connect);
}
else
{
$error = OCIError($connect);
send_mail("URGENT-MAIL_DBCONNECT_ERROR(ORCL)", $error['code'] ,$error['message'] , $error['offset'] , $error['sqltext'] ); }
###### END ########
? >
As you can see there are alot of error control checks in there just to be sure an email is processed correctly. When ever there is a problem while parsing , executing or connecting against the Oracle database. A rich error report is send to an email adres of your choice for debug purposes.
Hope you will find benefit for using this script in your implementations.
