UTL_TCP 2

[prosenjit]

Dear All,
This is my Oracle client configuration

<<
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.3.0.0 - Production
TNS for Solaris: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
>>

I connect to a Oracle 8i server in a separate machine.

I need to send mails from the Oracle database itself. My aim is to write a procedure which shall pick up e-mail ids and the subject and message from a table and send it automatically. Using DBMS_JOB.SUBMIT I shall execute this procedure at some specified interval. The procedure I have written for this is as under after connecting to a specific application username .

<<
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('mailserver', 25); -- open the SMTP port
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(-20000,
'Unable to send e-mail message from pl/sql');
END;
/
>>

Assumed that &quot;mailserver&quot; is the mailserver we use for sending our normal mails using Eudora/Outlook Express etc from the machine where my Oracle Client also resides.

The errors while creating the procedure are :
<<
SQL> show errors

Errors for PROCEDURE SEND_MAIL:

LINE/COL ERROR
-------- -------------------------------------------------------------
7/6 PLS-00201: identifier 'UTL_TCP.CONNECTION' must be declared
7/6 PL/SQL: Item ignored
10/3 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

10/3 PL/SQL: Statement ignored
11/3 PL/SQL: Statement ignored
11/24 PLS-00201: identifier 'UTL_TCP.GET_LINE' must be declared
12/3 PL/SQL: Statement ignored
12/9 PLS-00201: identifier 'UTL_TCP.WRITE_LINE' must be declared
13/3 PL/SQL: Statement ignored
13/24 PLS-00201: identifier 'UTL_TCP.GET_LINE' must be declared
14/3 PL/SQL: Statement ignored
14/9 PLS-00201: identifier 'UTL_TCP.WRITE_LINE' must be declared
15/3 PL/SQL: Statement ignored
15/24 PLS-00201: identifier 'UTL_TCP.GET_LINE' must be declared
16/3 PL/SQL: Statement ignored
16/9 PLS-00201: identifier 'UTL_TCP.WRITE_LINE' must be declared
17/3 PL/SQL: Statement ignored
17/24 PLS-00201: identifier 'UTL_TCP.GET_LINE' must be declared
18/3 PL/SQL: Statement ignored

>>

What I feel is that the package UTL_TCP is missing. If I happen to find this package do I create it in the database in my application user schema or do I need to log in as sys or system user. Also learnt that I need to have the JServer option installed. What does that mean?? Does it mean I shall have to run the Oracke Installer once again and install thsi option ?? If yes then at the server end or at the Client end??

I would appreciate an answer after somebody has actually done and succeded in what I intend to do.

Regards
Prosenjit

 

[karluk]

Oracle's web site indicates that utl_tcp was introduced in 8.1.6. Can anyone confirm this? I don't have 8.1.5 myself, so I can't check. If so, your send_mail proc won't work until you upgrade.

Even in the absence of utl_tcp there most likely is an implementation that will work with Oracle 8.1.5. I seem to recall posts on similar topics. Suggestions anyone?

 

[MikeLacey]

there is a FAQ, but it just covers sending mail once the packages are installed....

prosenjit - have you searched all of the *.sql files under $ORACLE_HOME for any that contain UTL_TCP?
Mike
michael.j.lacey@ntlworld.com

 

[prosenjit]

Mike/Karluk
I have not found any such .sql where this package is being created. May be what karluk says makes sense. But still don't you feel that if I manage to get the script and create in this databse itsef(8.1.5.0.0) it should work.

If you have it I request you send it to me . I shall create it in the database after having a look at it. May be it might work.

Regards
Prosenjit

 

[RandyD]

There is a tool called ezdbmail that does this for you and is Oracle version independent.

check out

http://www.ezdbmail.com

there is a free version to try.

hth
randy

 

[prosenjit]

RandyD,
I appreciate your response but the mails I need to send has to be from the database itsef not from an application.

That is may be my application(D2k,Powerbuilder or whatever...) where may be I have to press a button to send the mails is not running for 2/3 days. Still the mails should go to the mail ids picking from tables and message from a table also. I hope I make sense and has to be possible

Refer to the problem once again for clarification.

Please help me ...my boss really after me buddy!!!!

 

[carp]

Try looking for a package called UTL_SMTP. I am not familiar with UTL_TCP, but I have sent email from within the database using UTL_SMTP.

 

[prosenjit]

Carp,
Is this package version specific? See my first message where I have specified the Version details.

This package is there in my database. Is it possible to create it in the sys user and use it? If yes request it to send me the entire stuff in an attachment may be ...

Regards
prosenjit_banerjee@hotmail.com

 

[carp]

I checked on Metalink and found the following thread:

I've heard from the presentation of World Oracle User Group conference last month about that UTL_SMTP, UTL_TCP, UTL_HTTP packages are supplied with Oracle 8.1.5 (Oracle 8i). However, I can only find documents about UTL_HTTP, but cannot find any for UTL_SMTP, UTL_TCP.

Are these two packages not released yet? If they are released with Oracle 8.1.5, could you tell me where I can install these two handy packages?
============
Subject: Re : UTL_SMTP, UTL_TCP

These are new features to be included with release 8.1.6. I don't have a firm release date but I understand it will be early 2000.

So you will not be able to use these on an 8.1.5 install. Even if you get the scripts, there are java types underlying the functionality, so you would need to also create these java types in your database. I think you will have to upgrade to 8.1.6 before you can use these packages.

 

[prosenjit]

I think what Karluk said at the first place was right.

I think upgradation is the only means. UTL_HTTP is any way there. God knows !!!

Thanks to all any way!!!

 

[RustyMason]

I was able to get UTL_TCP to work just fine in 8.1.6, but not 8.1.5. BTW, the example in the package documentation slightly off. It shows calling utl_tcp.write_line as a procedure, but it's really a function.

<<
CREATE OR REPLACE PROCEDURE SEND_MAIL (
-- Example taken from:
-- thread186-29324 msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )

IS
c utl_tcp.connection;
rc integer;

BEGIN

c := utl_tcp.open_connection('63.79.228.2', 25); -- open the SMTP port
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection

EXCEPTION

when others then
raise_application_error(-20000,
'Unable to send e-mail message from pl/sql');
END;
/

>>


Good Luck,
Rusty

 

[Guest_imported]

Dear
This will not work with 8.1.5.

For 8.1.5, you need to create a text file from your procedure using UTL_FILE package to the operating system.

After creating the file, you need to write a unix shell script or your operting system script using 'SENDMAIL' or 'MAILX' function of unix or the required command line function of your OS. This script picks up the file based on the set enironment path variables and then delete the file. Then schedule this script using your OS scheduler(like register in crontab in case of unix) after every 2-3-5 minutes to fire and send the mail.

It is definitely a long procedure but the proved one.

Bye...Vinay