how to send email from pl/sql in 806?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: how to send email from pl/sql in 806?

  1. #1
    Join Date
    Apr 2000
    Location
    Charlotte, NC, USA
    Posts
    48

    Unhappy

    Hi,

    i am aware of UTL_SMTP in 817 to do this functionality(send email from pl/sql), but in 806 how do you accomplish this same task?

    thanx
    mocha

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    in 8.0.6 and earlier versions you will have to use an external program to send a mail .prferabbly a c programmme.you can do it using pl/sql & c prog.........

    I. sendMail C example
    ---------------------

    The files for the C example consist of the following:
    sendMail.c: File containing the actual C routine
    sendMailMain.c: C driver program which can be used to call sendMail()
    directly, without going through the database. This can
    be used to test sendMail.
    SendMail.sql: Contains the SQL commands necessary to create the library
    object and PL/SQL wrapper.
    ch10.mk: This is a sample Makefile (which may have to be modified for your
    system) that will compile the files.

    In order to run the sendMail program as an external routine, you need to do
    the following steps:

    1) Compile sendMail.c into a shared library. compile both sendMail.c and sendMailMain.c into an executable sendMail,
    and to create a shared library sendMail.so from sendMail.c. The
    executable can be used to test sendMail directly, without going through
    the database.

    2) Create the library using the CREATE LIBRARY command. See SendMail.sql
    for an example. You will need the CREATE LIBRARY system privilege GRANTed
    to you first.

    3) Create the SendMailC PL/SQL wrapper. See SendMail.sql for the example.

    4) Now you should be able to call sendMailC from an anonymous PL/SQL block
    to send your message, similar to the example in SendMail.sql. Be sure to
    change p_From and p_Recipient as neeed.

    heres the code


    pl/sql wrapper..........

    save this as sendmail.sql

    CREATE OR REPLACE PROCEDURE SendMailC (
    p_Subject IN VARCHAR2,
    p_Message IN VARCHAR2,
    p_From IN VARCHAR2,
    p_Recipient IN VARCHAR2)
    AS EXTERNAL
    LIBRARY SendMailLibrary
    NAME "sendMail"
    PARAMETERS (p_Subject STRING,
    p_Message STRING,
    p_From STRING,
    p_Recipient STRING);
    /

    -- Call SendMailC to send a message!
    BEGIN
    SendMailC('Test email from a C external routine',
    'This is the message!',
    'The_PL/SQL_Engine',
    'YOUR_EMAIL_ADDRESS_HERE');
    END;
    /
    /

    /*sendMail.c Prog*/
    #include

    /* Set TEMP_FILE to a filename in a directory writable by the
    * Oracle operating system user.
    */
    #define TEMP_FILE "/tmp/mail.out"

    /* Set MAIL_CMD to the full path of the system mail program. This
    * is often /bin/mail or /bin/rmail.
    */
    #define MAIL_CMD "/bin/mail"

    int sendMail(
    char *subject, /* Subject of the message */
    char *message, /* Message to be sent */
    char *from, /* Email id of the sender */
    char *recipient) /* Email id of the recipient */
    {
    FILE *tempFP;
    char systemCommand[500];

    /* We will send an email message using the Unix 'mail' command.
    * This requires us to put the message into a file first, then
    * we can use it as input to mail.
    */

    /* First write the subject and message to the file specified in
    * TEMP_FILE.
    */
    tempFP = fopen(TEMP_FILE, "w");
    fprintf(tempFP, "Subject: %s\n", subject);
    fprintf(tempFP, "From: %s\n", from);
    fprintf(tempFP, message);
    fclose(tempFP);

    /* Now build the mail command. */
    sprintf(systemCommand, "%s %s < %s\n",
    MAIL_CMD, recipient, TEMP_FILE);

    /* And execute it. */
    return system(systemCommand);
    }

    /*SendmailMain.c */
    #include

    extern int sendMail(char *, char *, char *, char *);

    int main()
    {
    char *subject = "Test email from a C routine";
    char *message = "This is a test message from C.\nHello!\n";
    char *from = "The_Operating_System";
    char *to = "YOUR_EMAIL_ADDRESS_HERE";

    printf("Sending C message to %s!\n", to);
    sendMail(subject, message, from, to);
    }
    regards
    hrishy

    P.s :you will have to use this make utility to compile sendmail and send mailmain into shared librarires.


    [Edited by hrishy on 05-23-2001 at 02:06 AM]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width