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

Need to generate aa .csv file from store procedure and  email to users using Oracle data,  the data is contained in store procdure/cursor and the email recipients will be pulled from a store procedure data
amanda43Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
you don't prefix the variables with the cursor name,  you only do the prefixing if you have cursor for loop and then you prefix with the for loop variable.
Like in my example,  the for loop had a variable "x", so you would use "x.column_name"

You're not using a cursor for loop though, so you just use the variables you fetch into

Also you were missing a semicolon after the variables, had an extra "end;"  and left out an underscore in "a__name" in the concatenation.
I think I've fixed all of the other syntax errors.

You do need to specify your mailhost/smtp server as well as a mime boundary , the boundary as shown in the article can be almost anything.


CREATE OR REPLACE PROCEDURE trackekr(cursor1 IN OUT SYS_REFCURSOR)
AS
    v_connection  UTL_SMTP.connection;
    v_clob        CLOB := EMPTY_CLOB();
    v_len         INTEGER;
    v_index       INTEGER;
 c_mime_boundary CONSTANT VARCHAR2(256) := 'the boundary can be almost anything';

    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  (your query here);

    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
            || rec
            || ','
            || d_id
            || ','
            || customer
            || ','
            || wife
            || ','
            || date_rec
            || ','
            || special_h
            || ','
            || g_amount
            || ','
            || credit_amount
            || ','
            || a_number
            || ','
            || a__name
            || UTL_TCP.crlf;
    END LOOP;

    -- UTL

    v_connection := UTL_SMTP.open_connection(mailhost, 25);   -- you need to fill in your SMTP server name or ip address
    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;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Creating the CSV is easy.  There are many string aggregation examples out there.

I prefer the XML trick shown here:
http://www.experts-exchange.com/Database/Oracle/Q_24914739.html#a25864822

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

Part one is here:
http://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.
0
 
amanda43Author Commented:
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;
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
Here is a previous question where I created a CSV from a complicated query:
http://www.experts-exchange.com/Database/Oracle/Q_26810723.html#a34865014

Once you get the CLOB, then all you need is the SMTP code from the articles I mentioned to send the email.
0
 
sdstuberCommented:
Check the third code example in this article.


http://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
1
 
amanda43Author Commented:
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?
0
 
sdstuberCommented:
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;

Open 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
    );

Open 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.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
amanda43Author Commented:
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!
0
 
sdstuberCommented:
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);
0
 
amanda43Author Commented:
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!
0
 
sdstuberCommented:
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;

Open 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;

Open in new window

0
 
amanda43Author Commented:
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;
0
 
amanda43Author Commented:
Thank you for your support, very much appreciated.
0
 
sdstuberCommented:
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.
0
 
amanda43Author Commented:
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
0
 
sdstuberCommented:
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
0
 
amanda43Author Commented:
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
0
 
sdstuberCommented:
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;

Open 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.
0
 
amanda43Author Commented:
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.
0
 
sdstuberCommented:
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.
0
 
amanda43Author Commented:
Using Oracle sql Developer, How do I see the contents of the clob or the cursor, is there a way?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.