Sign in or 
Location: The Official Oracle Wiki
Discussion: php and mail using orace
Category:
Discussion Forum /
General Discussion
Share this
Showing 3 posts
noumian |
php and mail using orace
Dec 17 2007, 5:08 PM EST
i have a database in oracle. with a site in php in my orace http server.what is the best way for me post a mail to the some users in my database every day at the same time? 0 out of 7 found this valuable. Do you?
Keyword tags:
content management
ecm
ibr
idc
middleware
oracle
oracle applications
site studio
stellent
ucm
|
|
smellinger |
1. RE: php and mail using oracle
Mar 13 2008, 12:19 PM EDT
What I would do is create an email package within the database and then either run a cron job or dbms_job at a specific time. Here is a mail package I have written.create or replace procedure sendmail (send_to IN VARCHAR2, send_from IN VARCHAR2, SUBJECT IN VARCHAR2, TEXT IN VARCHAR2) IS mailhost VARCHAR2(30) := '127.0.0.1'; -- use the local server mailconn utl_smtp.connection; message VARCHAR2(2000); rtn utl_smtp.reply; start_loc number := 1; end_loc number := 0; seperator varchar2(1) := ','; send_also varchar2(100); v_send_to varchar2(100); BEGIN v_send_to := send_to; ----------------------------------------------------------------- -- return address must be a valid address for email to be sent -- ----------------------------------------------------------------- mailconn := utl_smtp.open_connection(mailhost, 25); rtn := utl_smtp.helo(mailconn, mailhost); dbms_output.put_line ('Helo: '||to_char(rtn.code)||' '||rtn.text); rtn := utl_smtp.mail(mailconn, send_from); dbms_output.put_line ('Sender: '||to_char(rtn.code)||' '||rtn.text); -- allow semicolons but translate them to commas v_send_to := translate(v_send_to, ';',','); -- find the first occurance end_loc := instr(v_send_to, seperator, 1); ------------------------------------------------- -- there is only one "SEND TO" person ------------------------------------------------- if end_loc = 0 then rtn := utl_smtp.rcpt(mailconn, v_send_to); dbms_output.put_line ('To: '||to_char(rtn.code)||' '||rtn.text); 1 out of 3 found this valuable. Do you? |
|
smellinger |
2. RE: php and mail using orace
Mar 13 2008, 12:19 PM EDT
Part 2(due to 2000 char limit)rtn := utl_smtp.data(mailconn, 'Subject: '||SUBJECT||utl_tcp.crlf||TEXT); dbms_output.put_line ('DATA: '||to_char(rtn.code)||' '||rtn.text); end if; ------------------------------------------------- -- there is more than one so break up the list ------------------------------------------------- WHILE end_loc > 0 LOOP end_loc := instr(v_send_to, seperator, start_loc); dbms_output.put_line ('start_loc : '||to_char(start_loc)); dbms_output.put_line ('end_loc : '||to_char(end_loc)); if end_loc != 0 THEN send_also := substr(v_send_to, start_loc, end_loc-start_loc); else -- we are at the end of the string send_also := substr(v_send_to, start_loc); END IF; dbms_output.put_line ('To (1): '||send_also); rtn := utl_smtp.mail(mailconn, send_from); rtn := utl_smtp.rcpt(mailconn, send_also); dbms_output.put_line ('To (2): '||to_char(rtn.code)||' '||rtn.text); rtn := utl_smtp.data(mailconn, 'Subject: '||SUBJECT||utl_tcp.crlf||TEXT); dbms_output.put_line ('DATA: '||to_char(rtn.code)||' '||rtn.text); start_loc := end_loc + 1; end loop; rtn := utl_smtp.quit(mailconn); dbms_output.put_line ('QUIT: '||to_char(rtn.code)||' '||rtn.text); EXCEPTION WHEN others THEN dbms_output.put_line('oohh.. big error'); END; / show errors 3 out of 5 found this valuable. Do you? |
