Solved

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

Posted on 2013-11-07
22
2,844 Views
Last Modified: 2013-11-26
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
0
Comment
Question by:amanda43
  • 10
  • 9
  • 2
  • +1
22 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39632076
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
 

Author Comment

by:amanda43
ID: 39632171
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39632177
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39632180
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
0
 

Author Comment

by:amanda43
ID: 39634141
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39634198
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39634419
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
 

Author Comment

by:amanda43
ID: 39645290
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39645645
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
 

Author Comment

by:amanda43
ID: 39645671
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39645716
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:amanda43
ID: 39648585
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39648865
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
 

Author Closing Comment

by:amanda43
ID: 39649274
Thank you for your support, very much appreciated.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39649325
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
 

Author Comment

by:amanda43
ID: 39649328
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39649347
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
 

Author Comment

by:amanda43
ID: 39663397
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39663441
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
 

Author Comment

by:amanda43
ID: 39663588
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39663626
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
 

Author Comment

by:amanda43
ID: 39679657
Using Oracle sql Developer, How do I see the contents of the clob or the cursor, is there a way?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now