ITEC NEWSLETTER

Vol. 8, No. 7
July 21, 2000

Table of Contents
1. Remedy Upgrade Coming Soon
2. OpenVMS and Oracle Multi-Threaded Server
3. Sending E-Mail from Oracle

 

1. Remedy Upgrade Coming Soon

We are in the final stages of testing an upgrade of the USER portion of theRemedysystem. This upgrade incorporates many of the patches for the previous releaseand shouldaddress some of the slow connection issues we have been experiencing. It willalso includethe Windows 2000 patch.

Once the testing is complete, a new zip file will be created along withupdateddocumentation. Additionally, for our current users, there will be an executablefile thatwill upgrade only the user portion of your system, leaving the HelpDeskapplicationsintact.

We will let you know when it is in place. Please address any concerns and/orissuesthat you may have to me at Valerie.Anastasi@itec.mail.suny.edu.

Submitted by Valerie Anastasi (Valerie.Anastasi@itec.mail.suny.edu)

 

2. OpenVMS and Oracle Multi-ThreadedServer

Plattsburgh recently installed Oracle 8.0.5.1.0 on Alpha Open VMS. When theyconfiguredMTS the performance of the database took a turn for the worse. A call to Oraclerevealedthat this is still a problem with all versions of Oracle greater than 7.3.3.6on theOpenVMS platform.

Oracle is aware of the problem and they working on getting it resolvedalthough no datewas given as to when a patch would be available. The analyst said that thereare a lot ofpeople that want to use MTS so it is quite a high priority.

Submitted by Joe Lofft (Joe.Lofft@itec.mail.suny.edu)

 

3. Sending E-Mail fromOracle

Beginning with Oracle 8.1.6 is it possible to send email from PL/SQLprocedures ortriggers without having to call external programs. This is accomplished throughthe use ofthe UTL_SMTP package which contains procedures and functions that can send theproper SMTPcommands to the server of your choice. I am assuming here that the reader isfamiliar withhow to issue SMTP commands to a mail server.

The procedure below can be called directly from the SQL*Plus prompt or fromanotherPL/SQL procedure. While in SQL*Plus, if issue a SET SERVEROUTPUT ON command,you will beable to see the informational messages sent back from the mail server via theDBMS_OUTPUT.PUT_LINE calls.

There is no error checking in this procedure, I'll leave that up to you. TheUTL_SMTPpackage has a lot of pre-defined error conditions that you can use.

procedure SEND_MAIL
    (
    FROM_USER in varchar2 default null,
    TO_USER in varchar2 default null,   
    MESSAGE_SUBJECT in varchar2 default '[No subjectspecified]',
    MESSAGE_BODY in varchar2 default '[No messagespecified]'
    )
is
    SMTP_CONNECTION UTL_SMTP.CONNECTION;
    SMTP_REPLY UTL_SMTP.REPLY;
    CRLF varchar2(2) := CHR(13)||CHR(10);
    MESSAGE varchar2(4000);
begin

MESSAGE :=
    'From: '|| FROM_USER || CRLF ||
    'To: '|| TO_USER || CRLF ||
    'Subject: '|| MESSAGE_SUBJECT || CRLF ||
    '' || CRLF ||
    MESSAGE_BODY || CRLF;

    SMTP_REPLY :=
utl_smtp.open_connection(host=>'mailserver.doamin.com',port=>25,c=>SMTP_CONN
ECTION);
    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);
    dbms_output.put_line('Connection host:'||SMTP_CONNECTION.host);
    dbms_output.put_line('Connection port:'||SMTP_CONNECTION.port);

    SMTP_REPLY :=utl_smtp.command(c=>SMTP_CONNECTION,cmd=>'HELO',arg=>null);
    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);

    SMTP_REPLY :=utl_smtp.command(c=>SMTP_CONNECTION,cmd=>'MAIL
FROM:',arg=>FROM_USER);
    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);

    SMTP_REPLY :=utl_smtp.command(c=>SMTP_CONNECTION,cmd=>'RCPT
TO:',arg=>TO_USER);
    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);

    SMTP_REPLY :=utl_smtp.open_data(c=>SMTP_CONNECTION);
    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);

   utl_smtp.write_data(c=>SMTP_CONNECTION,data=>MESSAGE);

    SMTP_REPLY :=utl_smtp.close_data(c=>SMTP_CONNECTION);
    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);

    SMTP_REPLY :=utl_smtp.command(c=>SMTP_CONNECTION,cmd=>'QUIT',arg=>null);
    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);

end SEND_MAIL;

Submitted by Joe Lofft (Joe.Lofft@itec.mail.suny.edu)

 

 

Editor: Barbara A. Boquard Barb.Boquard@itec.mail.suny.edu
ITEC's General Office 716/878-ITEC (4832)
Operations 716/878-5122
FAX 716/878-4235
Web Page http://www.itec.suny.edu


ITEC Home