[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 717
  • Last Modified:

Oracle 9i Email Select results throught a procedure

Gents,

I have a tested stored procedure which allows me to send email through my exchange server.

CREATE OR REPLACE PROCEDURE SEND_EMAIL IS
BEGIN
  declare
        vEmailAddress           VARCHAR2(255):= 'my@domain.com';
        conn                    UTL_SMTP.CONNECTION;
                crlf                    VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
        vUser                   VARCHAR2(255) := USER;
        mesg                    VARCHAR2(500);
        vSqlErrm                VARCHAR2(255);
            

BEGIN
    /* Connect to the mail server */
    conn := utl_smtp.open_connection( 'server', 97 );
   utl_smtp.helo( conn, 'server' );
    utl_smtp.mail( conn, 'my@domain.com' );

   /* Set up all recipients and don't call utl_smtp if the e-mail variable is null */
    utl_smtp.rcpt( conn, vEmailAddress );

/* Create the message */
    mesg:= 'Date: ' || TO_CHAR( sysdate, 'dd Mon yy hh24:mi:ss' ) || crlf ||
           'From: sender@domain.com' || crlf ||
           'Subject: Virus Alert' || crlf ||
           'To: ' || vEmailAddress ||', '|| crlf ||
           '' || crlf ||
           'Email text';


/* Send message and close connection */
    utl_smtp.DATA( conn, mesg );
    utl_smtp.quit( conn );

EXCEPTION
      WHEN OTHERS
      THEN
           vSqlErrm := SQLERRM;

END;
END;

I want to use the above procedure to e mail me the result of another procedure shown below:

create or replace procedure TEST
is
begin
     for x in (select s.storerkey STORER,s.sku SKU,s.lot LOT,s.qty SQTY,l.qty LQTY
from lot s, lli_lot l
where s.storerkey=l.storerkey
and s.sku=l.sku
and s.lot=l.lot
and s.qty<>l.qty) loop
          dbms_output.put_line(x.STORER || ' ' || x.SKU || ' ' || x.LOT || ' ' || x.SQTY || ' ' || x.LQTY);
     end loop;
end;
/

How can i integrate the result of the TEST procedure so it could be sent via e mail through the send_email procedure?
0
sns-emea
Asked:
sns-emea
  • 6
  • 4
  • 3
1 Solution
 
Shaju KumbalathCommented:
U should modify the sendmail procedure in a such way that it should accept the message as an input argumet and instead of calling dbms_output , u can pass the message as an argument.
0
 
Shaju KumbalathCommented:
ie
create or replace procedure TEST
is
begin
     for x in (select s.storerkey STORER,s.sku SKU,s.lot LOT,s.qty SQTY,l.qty LQTY
from lot s, lli_lot l
where s.storerkey=l.storerkey
and s.sku=l.sku
and s.lot=l.lot
and s.qty<>l.qty) loop
          send_mail(x.STORER || ' ' || x.SKU || ' ' || x.LOT || ' ' || x.SQTY || ' ' || x.LQTY);
     end loop;
end;
 
0
 
shru_0409Commented:
CREATE OR REPLACE PROCEDURE SEND_EMAIL
l_msg  in varchar2
IS
BEGIN
  declare
        vEmailAddress           VARCHAR2(255):= 'my@domain.com';
        conn                    UTL_SMTP.CONNECTION;
                crlf                    VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
        vUser                   VARCHAR2(255) := USER;
        mesg                    VARCHAR2(500);
        vSqlErrm                VARCHAR2(255);
           

BEGIN
    /* Connect to the mail server */
    conn := utl_smtp.open_connection( 'server', 97 );
   utl_smtp.helo( conn, 'server' );
    utl_smtp.mail( conn, 'my@domain.com' );

   /* Set up all recipients and don't call utl_smtp if the e-mail variable is null */
    utl_smtp.rcpt( conn, vEmailAddress );

/* Create the message */
    mesg:= 'Date: ' || TO_CHAR( sysdate, 'dd Mon yy hh24:mi:ss' ) || crlf ||
           'From: sender@domain.com' || crlf ||
           'Subject: Virus Alert' || crlf ||
           'To: ' || vEmailAddress ||', '|| crlf ||
           '' || crlf ||
           'Email text'||l_msg;


/* Send message and close connection */
    utl_smtp.DATA( conn, mesg );
    utl_smtp.quit( conn );

EXCEPTION
      WHEN OTHERS
      THEN
           vSqlErrm := SQLERRM;

END;
END;


---------------

create or replace procedure TEST
is
l_msg varchar2(1000);

begin
     for x in (select s.storerkey STORER,s.sku SKU,s.lot LOT,s.qty SQTY,l.qty LQTY
from lot s, lli_lot l
where s.storerkey=l.storerkey
and s.sku=l.sku
and s.lot=l.lot
and s.qty<>l.qty) loop
l_msg := x.STORER || ' ' || x.SKU || ' ' || x.LOT || ' ' || x.SQTY || ' ' || x.LQTY;
SEND_EMAIL(l_msg);
          dbms_output.put_line(x.STORER || ' ' || x.SKU || ' ' || x.LOT || ' ' || x.SQTY || ' ' || x.LQTY);
     end loop;
end;


try this
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
sns-emeaAuthor Commented:
Dear Shru 0409,

Both procedures were created with compilation errors.
0
 
Shaju KumbalathCommented:
i think he has missed out brackets while defining the procedure
CREATE OR REPLACE PROCEDURE SEND_EMAIL
l_msg  in varchar2

should be
CREATE OR REPLACE PROCEDURE SEND_EMAIL
(l_msg  in varchar2 )  
0
 
shru_0409Commented:
CREATE OR REPLACE PROCEDURE SEND_EMAIL (
l_msg  in varchar2 )

make the bracket ...... and try it
0
 
sns-emeaAuthor Commented:
Hi Shru,

Yes, the update solved the SEND_EMAIL compilation issue but still TEST procedure is giving issues upon creation. Any suggestions?
0
 
shru_0409Commented:
can u explain the error....
0
 
shru_0409Commented:
or u can try this

CREATE OR REPLACE PROCEDURE test
IS
   l_msg   VARCHAR2(1000);

   CURSOR c1
   IS
      SELECT s.storerkey storer, s.sku sku, s.lot lot, s.qty sqty,
             l.qty lqty
        FROM lot s, lli_lot l
       WHERE s.storerkey = l.storerkey
         AND s.sku = l.sku
         AND s.lot = l.lot
         AND s.qty <> l.qty;
BEGIN
   FOR x IN c1
   LOOP
      l_msg :=  x.storer || ' ' || x.sku || ' ' || x.lot || ' ' || x.sqty || ' '|| x.lqty;
      send_email(l_msg);
      DBMS_OUTPUT.put_line(   x.storer
                           || ' '
                           || x.sku
                           || ' '
                           || x.lot
                           || ' '
                           || x.sqty
                           || ' '
                           || x.lqty
                          );
   END LOOP;
END;
0
 
sns-emeaAuthor Commented:
If i exec send_email (1), i get an e mail where as if i execute test procedure, i don t get an e mail.
0
 
shru_0409Commented:
catch the error in loop statemnet .. thers is nothing wrong in the syntax.....
0
 
sns-emeaAuthor Commented:
Hi Shru,

True, noticed that the select had no result, so i updated it to select from another table :)
I got an e mail but the issue is that i got in the e mail only the first value

SQL> exec hehe
1 ahmad
2 anj

Email:

Email text1 ahmad

Is there any issue in the loop?

create or replace procedure hehe
is
l_msg varchar2(1000);

begin
     for x in (select a,b
from t) loop
l_msg := x.a || ' ' || x.b;
SEND_EMAIL(l_msg);
          dbms_output.put_line(x.a || ' ' || x.b);
     end loop;
end;

0
 
shru_0409Commented:
catch the  x.a and  x.b vaule in variable and pass it .. i think null value make trouble.....
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now