asked on

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?

Shaju Kumbalath

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.

Shaju Kumbalath

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;

shru_0409

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

sns-emea

ASKER

Dear Shru 0409,

Both procedures were created with compilation errors.

Shaju Kumbalath

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 )

shru_0409

CREATE OR REPLACE PROCEDURE SEND_EMAIL (
l_msg in varchar2 )

make the bracket ...... and try it

sns-emea

ASKER

Hi Shru,

Yes, the update solved the SEND_EMAIL compilation issue but still TEST procedure is giving issues upon creation. Any suggestions?

shru_0409

can u explain the error....

shru_0409

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;

sns-emea

ASKER

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.

shru_0409

catch the error in loop statemnet .. thers is nothing wrong in the syntax.....

sns-emea

ASKER

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;

ASKER CERTIFIED SOLUTION

shru_0409

membership

This solution is only available to members.

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

Start Free Trial