asked on

utl_mail - Sending query as an attachment

Hi,

I have a requirement to send query results in an email from an oracle database. I have begun the procedure but really need help with the following:
1.I need to modify the query to search for ENTRY DATEs created in the past two days.
2.I will have a table with email addresses and only want to send specific reps the query don't know how to put this in the cursor

Any help will be greatly appreciated. I'm new to Oracle

CREATE OR REPLACE PROCEDURE SALESMAIL AS 
 
DECLARE
  lv_sender VARCHAR2(200) :='First.Last@Company.com';
  lv_recipients VARCHAR2(200) :='First.Last@Company.com';
  lv_subject VARCHAR(200) :='Test Small Quotes';
  lv_priority PLS_INTEGER := NULL;
  lv_last     BOOLEAN := FALSE;
  lv_count NUMBER := 0;
  lv_message VARCHAR2(32000);
  lv_conn  utl_smtp.connection;
 
  CURSOR cur_query
  IS
15.  SELECT ("CQ_HEADER"."BILL_NAME", "CQ_HEADER"."CQ_NUMBER", "CQ_HEADER"."TOTAL_PRICE", "CQ_STATUS"."STATUS_CODE", "SALESPERSON"."SALESPERSON_NAME", "CQ_HEADER"."ENTRY_DATE", "CQ_HEADER"."EMAIL_ADDRESS", "CQ_HEADER"."PHONE_NUMBER", "CQ_HEADER"."ATTENTION", "CQ_HEADER"."NOTES")a
  FROM   ("QCTL"."CQ_HEADER" "CQ_HEADER" LEFT OUTER JOIN "QCTL"."CQ_STATUS" "CQ_STATUS" ON "CQ_HEADER"."CQS_AUTO_KEY"="CQ_STATUS"."CQS_AUTO_KEY") LEFT OUTER JOIN "QCTL"."SALESPERSON" "SALESPERSON" ON "CQ_HEADER"."SPN_AUTO_KEY"="SALESPERSON"."SPN_AUTO_KEY"
  WHERE  "CQ_HEADER"."TOTAL_PRICE">=2000 AND "CQ_STATUS"."STATUS_CODE"='TRACKING' AND ("CQ_HEADER"."ENTRY_DATE">TO_DATE ('21-09-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "CQ_HEADER"."ENTRY_DATE"<=TO_DATE ('04-11-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
  ORDER BY "CQ_HEADER"."BILL_NAME", "CQ_HEADER"."ENTRY_DATE", "CQ_HEADER"."TOTAL_PRICE" DESC
  connect by level <=1000;

  BEGIN
    lv_conn:= SALESMAIL.begin_mail
                  ( lv_sender
                    , lv_recipients
                    , lv_subject
                    , SALESMAIL.multipart_mime_type
                    , lv_priority
                  );
 
    SALESMAIL.begin_attachment
          ( lv_conn
            ,'text/html"
            , FALSE
            ,'test.csv"
           ,'7 bit'
            );
 
    FOR rec IN cur_query
    LOOP
     lv_message := lv_message||rec.a||chr(13);
      SALESMAIL.write_text(lv_conn, lv_message);
    END LOOP;
 
    SALESMAIL.end_attachment
       ( lv_conn
           ,TRUE
         );
 
         SALESMAIL.end_mail(lv_conn);
        END;
 
BEGIN
  NULL;
END SALESMAIL;

Open in new window

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Annette Wilson, MSIS

ASKER

Thank your for responding sdstuber,

Just not sure how to correctly hardcode test email recipients but,

I revised the following and an getting two errors:

Error(3,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

Error(11,47): PLS-00103: Encountered the symbol "OUTER" when expecting one of the following: ) , for group having intersect minus order start union where connect

CREATE OR REPLACE PROCEDURE SMQUOTESMAIL AS

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


BEGIN
FOR x IN (SELECT "CQ_HEADER"."BILL_NAME", "CQ_HEADER"."CQ_NUMBER", "CQ_HEADER"."TOTAL_PRICE", "CQ_STATUS"."STATUS_CODE", "SALESPERSON"."SALESPERSON_NAME", "CQ_HEADER"."ENTRY_DATE", "CQ_HEADER"."EMAIL_ADDRESS", "CQ_HEADER"."PHONE_NUMBER", "CQ_HEADER"."ATTENTION", "CQ_HEADER"."NOTES"
  FROM   "QCTL"."CQ_HEADER", "CQ_HEADER" LEFT OUTER JOIN "QCTL"."CQ_STATUS", "CQ_STATUS" ON "CQ_HEADER"."CQS_AUTO_KEY"="CQ_STATUS"."CQS_AUTO_KEY") LEFT OUTER JOIN "QCTL"."SALESPERSON", "SALESPERSON" ON "CQ_HEADER"."SPN_AUTO_KEY"="SALESPERSON"."SPN_AUTO_KEY")
  WHERE  "CQ_HEADER"."TOTAL_PRICE"<=2000 AND "CQ_STATUS"."STATUS_CODE"='TRACKING' AND ("CQ_HEADER"."ENTRY_DATE">TO_DATE ('21-11-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "CQ_HEADER"."ENTRY_DATE"<=TO_DATE ('04-12-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
  ORDER BY "CQ_HEADER"."BILL_NAME", "CQ_HEADER"."ENTRY_DATE", "CQ_HEADER"."TOTAL_PRICE" DESC
  
  LOOP
    v_clob :=
    v_clob
    || x
    || ','
    || TO_CHAR(x.created, 'yyyy-mm-dd hh24:mi:ss')
    || UTL_TCP.crlf;
    END LOOP;
    
    v_connection := UTL_SMTP.open_connection(:p_smtp_server);
    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: ' || :'First.Last@ontic.com' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Subject: test subject'  ||  UtL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    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;
       
       UTL_SMTP.wirte_data(v_connection, UTL_TCP.crlf);
       
       UTL_SMTP.close_data(v_connection);
       UTL_SMTP.quit(v_connection);
       EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END SMQUOTESMAIL;

Open in new window

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Annette Wilson, MSIS

ASKER

Thank you for following up! I really appreciate this. I am not familiar with Oracle syntax for sending email.

I'm getting the following errors now.

Error(10,3): PL/SQL: SQL Statement ignored
Error(15,3): PL/SQL: ORA-00933: SQL command not properly ended
Error(21,9): PLS-00113: END identifier 'LOOP' must match 'SMQUOTESMAIL' at line 1, column 11
Error(23,5): PLS-00103: Encountered the symbol "V_CONNECTION"
Error(23,46): PLS-00103: Encountered the symbol ":" when expecting one of the following: ( ) - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> table continue avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall merge year month day hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timestamp interval date <a string literal with character set specification>

CREATE OR REPLACE PROCEDURE SMQUOTESMAIL AS

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


BEGIN
  SELECT "CQ_HEADER"."BILL_NAME", "CQ_HEADER"."CQ_NUMBER", "CQ_HEADER"."TOTAL_PRICE", "CQ_STATUS"."STATUS_CODE", "SALESPERSON"."SALESPERSON_NAME", "CQ_HEADER"."ENTRY_DATE", "CQ_HEADER"."EMAIL_ADDRESS", "CQ_HEADER"."PHONE_NUMBER", "CQ_HEADER"."ATTENTION", "CQ_HEADER"."NOTES"
  FROM   "QCTL"."CQ_HEADER" LEFT OUTER JOIN "QCTL"."CQ_STATUS" ON "CQ_HEADER"."CQS_AUTO_KEY"="CQ_STATUS"."CQS_AUTO_KEY" LEFT OUTER JOIN "QCTL"."SALESPERSON" ON "CQ_HEADER"."SPN_AUTO_KEY"="SALESPERSON"."SPN_AUTO_KEY"
  WHERE  "CQ_HEADER"."TOTAL_PRICE"<=2000 AND "CQ_STATUS"."STATUS_CODE"='TRACKING' AND "CQ_HEADER"."ENTRY_DATE">TO_DATE ('21-11-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "CQ_HEADER"."ENTRY_DATE"<=TO_DATE ('04-12-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
  ORDER BY "CQ_HEADER"."BILL_NAME", "CQ_HEADER"."ENTRY_DATE", "CQ_HEADER"."TOTAL_PRICE" DESC
  
  LOOP
    v_clob :=
    v_clob
    || ','
    || TO_CHAR(x.created, 'yyyy-mm-dd hh24:mi:ss')
    || UTL_TCP.crlf;
    END LOOP;
    
    v_connection := UTL_SMTP.open_connection(:'MYconnection');
    UTL_SMTP.helo(v_connection, :'mydomain');
    UTL_SMTP.mail(v_connection, :'my.name@company.com');
    UTL_SMTP.rcpt(v_connection, :'manager.name@company.com');
    UTL_SMTP.open_data(v_connection);
    
    UTL_SMTP.write_data(v_connection, 'From: ' || 'my.name@company.com'|| UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'To: ' || 'my.name@company.com'|| UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Subject: COMPANY TEST'  ||  UtL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    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;
       
       UTL_SMTP.wirte_data(v_connection, UTL_TCP.crlf);
       
       UTL_SMTP.close_data(v_connection);
       UTL_SMTP.quit(v_connection);
       EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END SMQUOTESMAIL;

Open in new window

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Annette Wilson, MSIS

ASKER

Thank you. I really appreciate your help.

I get the following errors.

1. How do I correctly declare in Oracle
2. I will also have to declare a variable for email addresses. The email will be sent to various individuals based on data in a table. I will select the email address with the quote data and send it to each individual.

Procedure QCTL.SMQUOTESMAIL@maxqprod
Error(6,18): PLS-00201: identifier 'INTERGER' must be declared
Error(6,18): PL/SQL: Item ignored
Error(37,5): PL/SQL: Statement ignored
Error(40,18): PLS-00302: component 'CREATED' must be declared
Error(55,5): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(55,5): PL/SQL: Statement ignored
Error(57,5): PL/SQL: Statement ignored
Error(57,11): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(63,8): PL/SQL: Statement ignored
Error(63,17): PLS-00302: component 'WIRTE_DATA' must be declared

CREATE OR REPLACE PROCEDURE SMQUOTESMAIL AS

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


BEGIN
  FOR x
        IN (SELECT "CQ_HEADER"."BILL_NAME",
                   "CQ_HEADER"."CQ_NUMBER",
                   "CQ_HEADER"."TOTAL_PRICE",
                   "CQ_STATUS"."STATUS_CODE",
                   "SALESPERSON"."SALESPERSON_NAME",
                   "CQ_HEADER"."ENTRY_DATE",
                   "CQ_HEADER"."EMAIL_ADDRESS",
                   "CQ_HEADER"."PHONE_NUMBER",
                   "CQ_HEADER"."ATTENTION",
                   "CQ_HEADER"."NOTES"
              FROM "QCTL"."CQ_HEADER"
                   LEFT OUTER JOIN "QCTL"."CQ_STATUS"
                       ON "CQ_HEADER"."CQS_AUTO_KEY" = "CQ_STATUS"."CQS_AUTO_KEY"
                   LEFT OUTER JOIN "QCTL"."SALESPERSON"
                       ON "CQ_HEADER"."SPN_AUTO_KEY" = "SALESPERSON"."SPN_AUTO_KEY"
             WHERE "CQ_HEADER"."TOTAL_PRICE" <= 2000
               AND "CQ_STATUS"."STATUS_CODE" = 'TRACKING'
               AND "CQ_HEADER"."ENTRY_DATE" >
                       TO_DATE('21-11-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
               AND "CQ_HEADER"."ENTRY_DATE" <=
                       TO_DATE('04-12-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
            ORDER BY "CQ_HEADER"."BILL_NAME",
                     "CQ_HEADER"."ENTRY_DATE",
                     "CQ_HEADER"."TOTAL_PRICE" DESC) 
  
  LOOP
    v_clob :=
    v_clob
    || ','
    || TO_CHAR(x.created, 'yyyy-mm-dd hh24:mi:ss')
    || UTL_TCP.crlf;
    END LOOP;
    
    v_connection := UTL_SMTP.open_connection('mysmpt_connection');
    UTL_SMTP.helo(v_connection, 'mydomain');
    UTL_SMTP.mail(v_connection, 'myname@company.com');
    UTL_SMTP.rcpt(v_connection, 'manager.name@company.com');
    UTL_SMTP.open_data(v_connection);
    
    UTL_SMTP.write_data(v_connection, 'From: ' || 'myname@company.com' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'To: ' || 'my.name@company.com' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Subject: TEST'  ||  UtL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    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;
       
       UTL_SMTP.wirte_data(v_connection, UTL_TCP.crlf);
       
       UTL_SMTP.close_data(v_connection);
       UTL_SMTP.quit(v_connection);
       EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END SMQUOTESMAIL;

Open in new window

Sean Stuber

The errors are fairly self explanatory.

Error(6,18): PLS-00201: identifier 'INTERGER' must be declared

Open in new window

there is no such data type as "INTERGER"

Looks like you misspelled "INTEGER"

Error(40,18): PLS-00302: component 'CREATED' must be declared

Open in new window

you are trying use x.created in your code but your for loop "x" doesn't return any columns named "CREATED" from the sql.

Looks likethat's a line you copied from my article but didn't change it to reflect what you are doing. In that article, I queried "created" in my sql. You must use the columns in your sql.

Error(63,17): PLS-00302: component 'WIRTE_DATA' must be declared

Open in new window

You are trying to call a procedure called "WIRTE_DATA" but no such procedure exists.

Looks like you misspelled "write_data"

Annette Wilson, MSIS

ASKER

Thank you. I know that I need to read up on writing oracle procedures and to watch my spelling.

I still get the following regarding the loop:

Error(37,5): PL/SQL: Statement ignored
Error(40,8): PLS-00306: wrong number or types of arguments in call to 'TO_CHAR'

Just don't understand how this loop works and how I need to include the data in it.

LOOP
v_clob :=
v_clob
|| ','
|| TO_CHAR(x, 'yyyy-mm-dd hh24:mi:ss')
|| UTL_TCP.crlf;
END LOOP;

When I remove the TO_CHAR statement, it compiles well.

In SQL I normally create a stored procedure and I can set up a job to execute it and I can send email. do you have a good book reference on Oracle functionality?

I'm getting the following error:

Connecting to the database databasename.
ORA-24247: network access denied by access control list (ACL)
Process exited.
Disconnecting from the database databasename.

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Annette Wilson, MSIS

ASKER

Thank you for your help. Our DBA will install the appropriate libraries tonight.

I just need help on one more item.

In the data set, I will include an email address for each sales person.
I want an email to go to each sales person with his own data set in the email.

How do I create a variable that will enable me to loop through and send the appropriate email to the salesperson?

Sean Stuber

I think that'll need to be a new question with sample data to work with along with expected results.