Creating a .csv file from store procedure to email to users using Oracle

Creating the CSV is easy.  There are many string aggregation examples out there.

I prefer the XML trick shown here:
https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html?anchorAnswerId=25864822#a25864822

An Expert here has created several articles on using SMTP to create and send emails.

Part one is here:
https://www.experts-exchange.com/Database/Oracle/A_5915-Extending-Oracle%27s-Email-functionality-with-PL-SQL-Authentication.html

All that said, if you can provide sample data and expected results we can come closer to a plug and play solution.

Here is the select in the store procedure and the cursor, all I need is to output this data as .csv file which will be emailed to users pulled from a oracle store procedure

create or replace
PROCEDURE             tracker
(
  Cursor1 IN OUT SYS_REFCURSOR
  )

AS

-
 BEGIN
  OPEN Cursor1 FOR
 
  Select distinct column1
  ,a,column2
  ,b.column3
  ,b,colum1
  b,column5
  a,column4
  a,column6  
  , (select subcolum1 from table3 where id2=id1) as column
  from tablea a, tableb b
  where a.id1 = a.id2
 union all
....
....
....
union all
....
....
....
   ;
  end;

Here is a previous question where I created a CSV from a complicated query:
https://www.experts-exchange.com/questions/26810723/Constructing-a-complicated-query.html?anchorAnswerId=34865014#a34865014

Once you get the CLOB, then all you need is the SMTP code from the articles I mentioned to send the email.

Check the third code example in this article.


https://www.experts-exchange.com/Database/Oracle/A_7749-How-to-send-email-attachments-with-Oracle.html


It shows how to embed a query, turn the query into csv and then email that csv as an attachment.


alternatively you could create the package in that article
create the clob csv as shown in that exampe

then send the clob with the send_attach_clob procedure

Hello,

Thank you for your post but I do not see the  part where the .csv file is created,  I have the query working and the cursor has the data, just how do I create the .csv file?

from the article...
First you build a CLOB with your csv data .
You would use this same loop but with your query and use your own columns in the concatenation

 FOR x IN (SELECT *
                FROM all_objects
               WHERE ROWNUM < 20)
    LOOP
        v_clob :=
               v_clob
            || x.owner
            || ','
            || x.object_name
            || ','
            || x.object_type
            || ','
            || TO_CHAR(x.created, 'yyyy-mm-dd hh24:mi:ss')
            || UTL_TCP.crlf;
    END LOOP;

Select allOpen in new window

Then, when you write the clob into the email body,  it is written with the MIME boundaries so it can be read as an attachment.

 -- Set up attachment header
    UTL_SMTP.write_data(
        v_connection,
           'Content-Disposition: attachment; filename="'
        || 'your_file_name.csv'
        || '"'
        || UTL_TCP.crlf
    );

Select allOpen in new window

You never create a physical file locally, the csv contents exist only in the clob and the email

If you need to send the data but also keep a copy of it around then use utl_file to write the file somewhere.
I'd do the file writing at the same time I did the clob construction.   No need to loop through the content twice.

If you do need to create a physical *.csv file, remember that Oracle stored procedures work in the Oracle database, so any files that stored procedures create are in directories that are on (or available to) the database server.  The files are *NOT* created on a client PC.  That means the email then also has to be sent from the database server O/S (or from the database via the server O/S), not from a client PC, unless your client PC has access to the server directory where the *.csv files gets created (or copied to).  

I usually have PL\SQL create the file on the database server, then at the end of the procedure, copy this file to a network location that is shared with Windows clients.  This allows me to do the e-mail from a Windows PC.  But, this assumes that a Windows user will manually find and send the e-mail.  That may or may not be what you want.

Thank you for your post but I am still lost, the example from SDSTRUDER, do I combine the 2 pieces of code? if the data is already in a cursor, can I assign the cursor to a CLOB type? and where do I set the connection information or the email information.
In regards of MARKGEER post,  I want to creat a file in the database server and the copy to a network location.  I just do not understand how to set this properly/
Please excuse my ignorance and I hope  you can help me.

Thank you!

no, you can't assign a cursor to a clob.

you could iterate through a cursor though and append the elements as shown above.

each of the colon-prefixed variables is called a bind variable.  Put your values there.



 UTL_SMTP.helo(v_connection, :p_domain);
    UTL_SMTP.mail(v_connection, :p_from);
    UTL_SMTP.rcpt(v_connection, :p_to);
    UTL_SMTP.open_data(v_connection);

    UTL_SMTP.write_data(v_connection, 'From: ' || :p_from || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'To: ' || :p_to || UTL_TCP.crlf);

Could you please help on how to effectively build the procedure based on this example:
create or replace
PROCEDURE             tracker
(
  Cursor1 IN OUT SYS_REFCURSOR
  )

AS

-
 BEGIN
  OPEN Cursor1 FOR
 
  Select distinct column1
  ,a,column2
  ,b.column3
  ,b,colum1
  b,column5
  a,column4
  a,column6  
  , (select subcolum1 from table3 where id2=id1) as column
  from tablea a, tableb b
  where a.id1 = a.id2
 union all
....
....
....
union all
....

Thank you so much!

in the declare section of your procedure you need these...

    v_connection UTL_SMTP.connection;
    v_clob       CLOB := EMPTY_CLOB();
    v_len        INTEGER;
    v_index      INTEGER;

Select allOpen in new window

after you open your cursor loop through it and build the string like shown above, then using the same code as in the article example, send the clob.
Just fill in the variables and change the subject and filename to something meaningful

loop
     fetch cursor1 into val1,val2,val3,val4,val5,val6,val7;    ---- you need to declare these variables
     exit when cursor1%notfound;

      v_clob :=
               v_clob
            || val1
            || ','
            || val2
            || ','
            || val3
            || ','
            || val4
            || ','
            || val5
            || ','
            || val6
            || ','
            || val7
            || UTL_TCP.crlf;
end loop;

    v_connection := UTL_SMTP.open_connection(:p_smtp_server);    --- you need to fill in this value
    UTL_SMTP.helo(v_connection, :p_domain);    --- you need to fill in this value
    UTL_SMTP.mail(v_connection, :p_from);    --- you need to fill in this value
    UTL_SMTP.rcpt(v_connection, :p_to);    --- you need to fill in this value
    UTL_SMTP.open_data(v_connection);

    UTL_SMTP.write_data(v_connection, 'From: ' || :p_from || UTL_TCP.crlf);    --- you need to fill in this value
    UTL_SMTP.write_data(v_connection, 'To: ' || :p_to || UTL_TCP.crlf);    --- you need to fill in this value
    UTL_SMTP.write_data(v_connection, 'Subject: test subject' || UTL_TCP.crlf);    -- change the subject to something meaningful
    UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);

    UTL_SMTP.write_data(
        v_connection,
        'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf
    );
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data(
        v_connection,
        'This is a multi-part message in MIME format.' || UTL_TCP.crlf
    );

    UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);

    -- Set up attachment header
    UTL_SMTP.write_data(
        v_connection,
           'Content-Disposition: attachment; filename="'
        || 'your_file_name.csv'       -----  change the filename to something meaningful
        || '"'
        || UTL_TCP.crlf
    );
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

    -- Write attachment contents

    v_len := DBMS_LOB.getlength(v_clob);
    v_index := 1;

    WHILE v_index <= v_len
    LOOP
        UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
        v_index := v_index + 32000;
    END LOOP;

    --
    -- End attachment
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);

    UTL_SMTP.close_data(v_connection);
    UTL_SMTP.quit(v_connection);
END;

Select allOpen in new window

This is my code but I keep on having this complile problems:

PLS-00487 (135: 11): PLS-00487: Invalid reference to variable 'Cursor1'
PLS-00201 (175: 43): PLS-00201: identifier 'MAILHOST' must be declared
PLS-00201 (176: 32): PLS-00201: identifier 'MAIL.EXCHANGE' must be declared
PLS-00201 (188: 56): PLS-00201: identifier 'C_MIME_BOUNDARY' must be declared
PLS-00201 (196: 47): PLS-00201: identifier 'C_MIME_BOUNDARY' must be declared
PLS-00201 (222: 47): PLS-00201: identifier 'C_MIME_BOUNDARY' must be declared

Please help


** CODE
create or replace
PROCEDURE trackekr
(
  CURSOR1 IN OUT SYS_CURSOR
 
  )
 
AS
 v_connection UTL_SMTP.connection;
    v_clob       CLOB := EMPTY_CLOB();
    v_len        INTEGER;
    v_index      INTEGER;

Rec    number(10,0) := 0 ;
D_ID          number(10,0) := 0 ;
Customer             varchar2(20);
wife            varchar2(20);
Date_rec          date;
Special_H  varchar2(20);
G_Amount          number(10,0) := 0 ;
Credit_Amount     number(10,0) := 0 ;
A_number varchar2(20);
A__Name   varchar2(20);


 BEGIN
 
  OPEN CURSOR1 FOR
 
 SELECT rec, D_ID, Customer, wife, Date_rec, Special H,
g_amount from table rec...

 ;
 

 
  WHILE CURSOR1%NOTFOUND
    LOOP
    FETCH  CURSOR1 INTO  Rec, D_ID, Customer,wife,
      Date_rec,Special_H, G_Amount , Credit_Amount,A_number , A__Name


    v_clob :=
     v_clob
||  CURSOR1.Rec
|| ","
||  CURSOR1.D_ID
|| ","
||  CURSOR1.Customer
|| ","
||  CURSOR1.wife
|| ","
||  CURSOR1.Date_rec
|| ","
||  CURSOR1.Special_H
|| ","
||  CURSOR1.G_Amount
|| ","
||  CURSOR1.Credit_Amount
|| ","
||  CURSOR1.A_number
|| ","
||  CURSOR1.A_Name
|| UTL_TCP.crlf;

end loop;
-- UTL

 v_connection := UTL_SMTP.open_connection(mailhost, 25);  
    UTL_SMTP.helo(v_connection,mail.Exchange.mydomain.com);  
    UTL_SMTP.mail(v_connection, 'mylogin.Exchange.mydomain.com');  
    UTL_SMTP.rcpt(v_connection,'mylogin.Exchange.mydomain.com');    --- you need to fill in this value
    UTL_SMTP.open_data(v_connection);

    UTL_SMTP.write_data(v_connection, 'From: ' ||'mylogin.Exchange.mydomain.com' || UTL_TCP.crlf);    
    UTL_SMTP.write_data(v_connection, 'To: ' || 'mylogin.Exchange.mydomain.com' || UTL_TCP.crlf);  
    UTL_SMTP.write_data(v_connection, 'Subject: test subject' || UTL_TCP.crlf);    
    UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);

    UTL_SMTP.write_data(
        v_connection,
        'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf
    );
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data(
        v_connection,
        'This is a multi-part message in MIME format.' || UTL_TCP.crlf
    );

    UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);

    -- Set up attachment header
    UTL_SMTP.write_data(
        v_connection,
           'Content-Disposition: attachment; filename="'
        || 'FIRSTFILE.csv'      
        || '"'
        || UTL_TCP.crlf
    );
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

    -- Write attachment contents

    v_len := DBMS_LOB.getlength(v_clob);
    v_index := 1;

    WHILE v_index <= v_len
    LOOP
        UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
        v_index := v_index + 32000;
    END LOOP;

    -- End attachment
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);

    UTL_SMTP.close_data(v_connection);
    UTL_SMTP.quit(v_connection);
END;
END;

Thank you for your support, very much appreciated.

also note..

you pass in your cursor variable but it's not associated with anything yet.  That's fine, but then you open it, read through it and exhaust all rows leaving nothing of value as an out parameter.

I suggest removing the parameter completely and create a cursor for loop like in the article.
Simpler syntax, same effect.

Please do not take it as abuse but is there a way to include formating options or instead of create a .csv, can I create an .XLS with formating.

Thank you

formatting options - certainly, it's just text, apply whatever formatting functions you want upper/lower/initcap/rpad/lpad/trim/date/number formats, etc.

xls - probably not without using some 3rd party libraries

xlsx - much easier, it's just xml - but definitely outside the scope of this question

Hello,

The file is sent but the data is not in the file, could you  please point to me where is where the CLOB is being assign to the  .csv file?

Thank you

the clob isn't "assigned" to a csv.

It's simply written into the body of the email with the proper MIME boundaries.  This is the part that does that ...

 -- Write attachment contents

    v_len := DBMS_LOB.getlength(v_clob);
    v_index := 1;

    WHILE v_index <= v_len
    LOOP
        UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
        v_index := v_index + 32000;
    END LOOP;

Select allOpen in new window

In a sense, there is no such thing as email attachments.  Read the articles above for more information, but here's an abbreviated form.
What modern email clients do is recognize delimited sections of text as begin special.

Basically email with attachements looks like this...


blah, blah, blah,
other random text
-- SOMETHING SPECIAL HERE
1,2,3
a,b,c
-- END OF SOMETHING SPECIAL
blah, blah, blah
even more random text


when your email client receives the message it doesn't see text and files it sees nothing but text.
However, it will parse through it looking for the "special" tags.  Anything in between them is considered an attachment.
How exactly that attachment block will be rendered is up to your client.

Thank you for your prompt response, the problem I have is that it does send the email but the file is empty, no headers, no data, I am thinking that maybe the Creation of the CLOB is not working.

you have lots of options there.  

instead of sending the email, change all of the write calls
to dbms_output.put_line

see if there is data in it

put a counter in your loop that fetches the rows.
check the count.

put some debug messages inside the fetch loop.


these really aren't related to this question though.
these are just basic debugging.  good luck.

Using Oracle sql Developer, How do I see the contents of the clob or the cursor, is there a way?