Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121

    Unanswered: Attaching query results to an email.

    I've got a stored procedure that sends emails. It works just fine. It has provisions for file attachements. What I need to do is attach the results of a simple query to the email. I would like to avoid getting in to the input/output directories, writing a file to the filesystem, etc if at all possible. Is it possible to run a query within the storted procedure and have it attach the results in a text file all within the stored procedure and not use file system IO?
    Last edited by DBA-ONE; 10-17-05 at 12:16.

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Yes - we use the utl_tcp package to accomplish this - basically you can create an attachment on the fly, and it does not write a file to the file.

    http://www.oracle.com/technology/sam...ildemo_sql.txt

    You would do something like this - I am going to reference the procedure/function names in the example - If you cannot understand, let me know and I'll try to give you a full example.

    1) Call begin_mail procedure
    2) Call begin_attachment procedure with inline set to TRUE to write body of email
    3) Call write_text to write the actual body (each write text is one line, so call it multiple times for multiple lines)
    4) Call end_attachment with inline set to FALSE (ends body)
    5) Call being_attachment (now with inline set to FALSE to indicate start of attachment)
    6) Loop through a cursor or start writing text with write_text
    7) Call end_attachment with inline set to TRUE to indicate end of attachment
    8) Call end_mail

    These are the steps I have used before and it works nicely - there may be an easier way, but this definitely does work.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Thanks for the info. However, I'm not going to use this as it is far too complicated. I'm just going to write some seperate proc to kick out the results to a file and then use the send mail procedure which already has attachment capability. This is where SQL Server is far superior. In order to do all this it is about ten lines of code and that is puting the params on second lines!

    Sometime getting data out of Oracle is like getting blood from a rock.

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Quote Originally Posted by DBA-ONE
    Thanks for the info. However, I'm not going to use this as it is far too complicated. I'm just going to write some seperate proc to kick out the results to a file and then use the send mail procedure which already has attachment capability. This is where SQL Server is far superior. In order to do all this it is about ten lines of code and that is puting the params on second lines!

    Sometime getting data out of Oracle is like getting blood from a rock.
    There are also java classes available that can be used as well if you do not like this method. I believe javamail is one of these.
    Oracle OCPI (Certified Practicing Idiot)

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Its really not that many more steps than creating a traditional file - In utl_file, you have to declare your filehandle, open it for writing, loop through a cursor and put_line each line, and at the end close your filehandle. To attach this, you would then also need to include a call to the mail package to attach that file. So in reality, you are not saving that much code by dumping it to the server as you think. Its no more complicated than utl_file is, but to each their own.
    Oracle OCPI (Certified Practicing Idiot)

  6. #6
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    True but I can control the file creation process in a single procedure while not depending on a insane number of procedures and functions. I do not wish to maintain this package for such a simple need.

Posting Permissions

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