Home » RDBMS Server » Server Administration » Did I set up UTL_MAIL correctly and completely? (12c on Windows)
Did I set up UTL_MAIL correctly and completely? [message #682518] Tue, 27 October 2020 07:19 Go to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
Hi all!

I previously developed against Oracle 11 but never administered my own instance. Currently I am writing a small piece of software that aims to send an email notification whenever it detects infrastructure failures. One of the configurable options will be to use an Oracle instance and to leverage its built-in SMTP functionality, in order to allow the user to avoid opening extra ports on the firewall for SMTP if they already have one for Oracle. I have already done it against DB2 UDB, using the module named the same as Oracle's, but in that case I did not have to set it up: it came with DB2 OOB.

Now, I am only under the impression that I gather from Oracle online documentation that UTL_MAIL is the way to go. I found a thread on this very forum in which Bill B mentioned that UTL_MAIL is nothing more than a wrapper around DBMS_SMTP. I am learning as I am typing this, essentially.

I installed my own local copy of Oracle 12c on the same Windows machine that I am using for development and typing this from.

In SqlDeveloper, I checked available packages and found neither DBMS_SMTP nor UTL_MAIL. I had gone to https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_mail.htm and found that the latter has to be installed. I tried the commands from that page:

sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
but both of them failed to find both files. I then simply opened each of the files in Sql Developer and ran them. They created the package for me. I can see the body of the package in Sql Developer but it does not have anywhere nearly as many procedures as mentioned in https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/u_smtp.htm I assume that I will need at least some of them, for instance AUTH, because my SMTP server requires user name and password authentication.

Still unsure if this was it, I found this blog post: https://dbaoracletips.blogspot.com/2011/09/how-to-install-jserver-java-virtual.html and ran the command from it:

@?/javavm/install/initjvm
It output a lot of text that I did not have a chance to capture because the SQL Plus window closed all by itself. I went back and ran the 2nd command:

select comp_name,status from dba_registry;
In the output I can see:

JServer JAVA Virtual Machine
VALID
I still do not have DBMS_SMTP package and could not find information on how to install it.

Could anyone please point me in the right direction as to the next steps that I should take before I can proceed to write the C# code that will invoke the UTL_MAIL.SEND_ATTACH_VARCHAR2 procedure?

Thank you!
Al
Re: Did I set up UTL_MAIL correctly and completely? [message #682519 is a reply to message #682518] Tue, 27 October 2020 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I still do not have DBMS_SMTP package and could not find information on how to install it.

The standard Oracle package is UTL_SMTP not DBMS_SMTP.
UTL_MAIL is indeed based on UTL_SMTP which itself is built upon UTL_TCP.
If you have not UTL_SMTP, you have to execute (as SYSDBA):
@<your oracle home directory>/rdbms/admin/utltcp.sql
@<your oracle home directory>/rdbms/admin/prvttcp.plb
@<your oracle home directory>/rdbms/admin/utlsmtp.sql
@<your oracle home directory>/rdbms/admin/prvtsmtp.plb 
You also have to set "smtp_out_server" instance parameter.
You also have to set ACL, see https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=55904#175938 for an example.

I don't know if you can use UTL_MAIL with SMTP server authentication; in this case you may have to use UTL_SMTP as explained in https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:12526941604660#51890874357845

For your posts, better use SQL*Plus and show us (copying and pasting) what you do and get instead of trying to tell us, maybe you missed something important in your description.

[Edit: missing word]

[Updated on: Wed, 28 October 2020 03:57]

Report message to a moderator

Re: Did I set up UTL_MAIL correctly and completely? [message #682520 is a reply to message #682519] Tue, 27 October 2020 09:39 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
Grand merci, Michel!

Now that the excitement from a new challenge subsided, I can soberly review everything again and see that UTL_SMTP exists OOB, UTL_MAIL has been created by me running the 2x scripts, and that everything about the packages seems to be in order. My out-server has been set up. Since I have not hit the error in the linked thread, I was not aware of the ACLs. Now I've ran the linked script and the ACLs are hopefully set up to.

I took this snippet from https://oracle-base.com/articles/10g/utl_mail-send-email-from-the-oracle-database

BEGIN
  UTL_MAIL.send_attach_varchar2 (
    sender       => 'info@localdomain.lan',
    recipients   => 'info@localdomain.lan',
    cc           => 'info@localdomain.lan',
    bcc          => 'info@localdomain.lan',
    subject      => 'UTL_MAIL Test',
    message      => 'If you get this message it worked!',
    attachment   => 'This is the contents of the attachment.',
    att_filename => 'my_attachment.txt'    
  );
END;
But it throws this error:

Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 441
ORA-06512: at "SYS.UTL_MAIL", line 713
ORA-06512: at line 2
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.
I also tried:

call UTL_MAIL.SEND ('info@localdomain.lan', 'info@localdomain.lan', null, null, 'UTL_MAIL Test', 'Message', 'text/plain; charset=us-ascii', null)
And got the same:

Error report -
SQL Error: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 441
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.
Re: Did I set up UTL_MAIL correctly and completely? [message #682523 is a reply to message #682520] Tue, 27 October 2020 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The ACLs were introduced in 11gR2 to improve the security and your link is from 10g.

You have systematically this error whatever you do with utl_mail when smtp_out_server is not set.
Post the result of:
show parameter smtp_out_server
Re: Did I set up UTL_MAIL correctly and completely? [message #682525 is a reply to message #682523] Tue, 27 October 2020 11:22 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
SQL> connect
Enter user-name: sys as sysdba
Enter password:
Connected.
SQL> show parameter smtp_out_server

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server                      string
SQL> ALTER SYSTEM SET smtp_out_server='localdomain.lan' SCOPE=SPFILE;

System altered.

SQL> show parameter smtp_out_server

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server                      string
SQL>
Re: Did I set up UTL_MAIL correctly and completely? [message #682527 is a reply to message #682525] Tue, 27 October 2020 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you just put it in the spfile you have to restart the database so it is taken into account.

Hopefully this is a dynamic parameter so you can set it in the current instance executing:
ALTER SYSTEM SET smtp_out_server='localdomain.lan' SCOPE=MEMORY;
Re: Did I set up UTL_MAIL correctly and completely? [message #682528 is a reply to message #682527] Tue, 27 October 2020 11:47 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
Grand merci!

Got it working by simply rebooting the box, since I forgot the shutdown immediate commands that had to be issued after changing the setting. Lack of my experience with Oracle showing!
After it came back up, the email went out. Hurray!

I knew this forum could help!
Re: Did I set up UTL_MAIL correctly and completely? [message #682533 is a reply to message #682528] Tue, 27 October 2020 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I'm glad you make it work in the end.
Have fun with Oracle.

Why this silly error "PL/SQL: numeric or value error" when you do not set "smtp_out_server"?
It comes from this line in the code:
FOR I IN SMTP_SERVERS.FIRST .. SMTP_SERVERS.LAST LOOP
Oracle does not test if the array is empty before using it!
I hope some day some one will add this test before the loop and raise a meaningful error message.


Re: Did I set up UTL_MAIL correctly and completely? [message #682554 is a reply to message #682533] Wed, 28 October 2020 14:13 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
Not so fast! It still kicks like a mule.

I can send emails when logged in as SYS as SYSDBA.
But when I log in as the intended application's regular user, it throws the following error:

Error starting at line : 1 in command -
call  UTL_MAIL.send(sender     => 'info@localdomain.lan',
                recipients => 'info@localdomain.lan',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!')
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
I am likely missing some permissioning. When I look at the user permissions in SqlDeveloper, it shows that EXECUTE ANY PROCEDURE is granted. I granted EXECUTE ANY PROGRAM to no avail. Does it need anything else?
Re: Did I set up UTL_MAIL correctly and completely? [message #682556 is a reply to message #682554] Wed, 28 October 2020 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Never grant "EXECUTE ANY PROCEDURE" to an account, this allows it to do everything in the database.
2/ Always first try what you want to do in a program from SQL*Plus, this will allow you to know if the problem comes from the statement or from the program.
3/ You have to set ACL to grant the user as per the example in the link above.

Also read SYS/SYSDBA is special.

Re: Did I set up UTL_MAIL correctly and completely? [message #682557 is a reply to message #682556] Wed, 28 October 2020 15:15 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
Now I realize that there was an error in my ACL script. I forgot to replace the placeholder for the actual value of the host.

  dbms_network_acl_admin.assign_acl (
    acl  => 'utl_mail.xml',
    host => '<your smtp server host name or address>'
  );
How can I fix this misconfiguration?
Re: Did I set up UTL_MAIL correctly and completely? [message #682558 is a reply to message #682557] Wed, 28 October 2020 16:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use DBMS_NETWORK_ACL_ADMIN.DROP_ACL to drop the ACL and recreate it.

Note that DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL allows to specify a range of ports if you want to improve the security specifying the actual port used by your SMTP server.

[Updated on: Wed, 28 October 2020 16:11]

Report message to a moderator

Re: Did I set up UTL_MAIL correctly and completely? [message #682559 is a reply to message #682558] Wed, 28 October 2020 16:44 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
Dropped the ACL, re-created it with the correct SMTP host name, but still getting the same error under the regular user:

Error starting at line : 1 in command -
call  UTL_MAIL.send(sender     => 'info@localdomain.lan',
                recipients => 'info@localdomain.lan',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!')
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

[Updated on: Wed, 28 October 2020 16:46]

Report message to a moderator

Re: Did I set up UTL_MAIL correctly and completely? [message #682561 is a reply to message #682559] Wed, 28 October 2020 17:23 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
Try running below SYS AS SYSDBA

sqlplus sys/<pwd> AS SYSDBA
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
Re: Did I set up UTL_MAIL correctly and completely? [message #682562 is a reply to message #682561] Wed, 28 October 2020 18:31 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
I have already run both files, and it works fine for SYS as SYSDBA. If I run your script, I get this, like I wrote in the OP:

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 28 19:19:35 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SP2-0310: unable to open file "$ORACLE_HOME/rdbms/admin/utlmail.sql"
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
SP2-0310: unable to open file "$ORACLE_HOME/rdbms/admin/prvtmail.plb"
SQL>
If I open files in SqlDeveloper instead, then I get the following by running utlmail.sql:

Package UTL_MAIL compiled

Public synonym UTL_MAIL created.
And this is what I see from prvtmail.plb:

Package UTL_MAIL_INTERNAL compiled


Package body UTL_MAIL_INTERNAL compiled
Grant succeeded.


Package body UTL_MAIL compiled

No errors.
But after running both, I am still getting the same error as above when attempting to send an email from the regular user account and it still works fine for SYS. So we are back at the square 1.
Re: Did I set up UTL_MAIL correctly and completely? [message #682565 is a reply to message #682559] Thu, 29 October 2020 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From the user schema, with SQL*Plus, post the result of:
SELECT owner, object_type 
FROM ALL_OBJECTS 
WHERE object_name = 'UTL_MAIL'
ORDER BY 1, 2
/
then, still with SQL*Plus:
begin
  UTL_MAIL.send(sender     => 'info@localdomain.lan',
                recipients => 'info@localdomain.lan',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!');
end;
/
Re: Did I set up UTL_MAIL correctly and completely? [message #682570 is a reply to message #682565] Thu, 29 October 2020 07:35 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
SQL> SELECT owner, object_type
  2  FROM ALL_OBJECTS
  3  WHERE object_name = 'UTL_MAIL'
  4  ORDER BY 1, 2
  5  /

OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
PUBLIC
SYNONYM


SQL>
Re: Did I set up UTL_MAIL correctly and completely? [message #682572 is a reply to message #682570] Thu, 29 October 2020 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your user has not the privilege to call UTL_MAIL, so execute, as SYSDBA:
grant execute on UTL_MAIL to <your user>;
Re: Did I set up UTL_MAIL correctly and completely? [message #682573 is a reply to message #682572] Thu, 29 October 2020 10:22 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
Worked perfectly! Merci beaucoup!

But what does "PUBLIC" mean in the output above? Is not it supposed to mean that everyone can access? Or is my user not a member of the PUBLIC? If it is not then should it be added to the PUBLIC?
Re: Did I set up UTL_MAIL correctly and completely? [message #682576 is a reply to message #682573] Thu, 29 October 2020 10:59 Go to previous messageGo to next message
EdStevens
Messages: 1282
Registered: September 2013
Senior Member
Darth Waiter wrote on Thu, 29 October 2020 10:22
Worked perfectly! Merci beaucoup!

But what does "PUBLIC" mean in the output above? Is not it supposed to mean that everyone can access? Or is my user not a member of the PUBLIC? If it is not then should it be added to the PUBLIC?
Look again at the query:

SQL> SELECT owner, object_type
  2  FROM ALL_OBJECTS
  3  WHERE object_name = 'UTL_MAIL'
  4  ORDER BY 1, 2
  5  /

OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
PUBLIC
SYNONYM


SQL>
It has nothing to do with showing access privileges. It simply shows the OWNER and TYPE of an object. In this case, a synonym that is owned by public. Users would still need necessary privileges on the object that is referenced by the public synonym.

[Updated on: Thu, 29 October 2020 11:01]

Report message to a moderator

Re: Did I set up UTL_MAIL correctly and completely? [message #682577 is a reply to message #682576] Thu, 29 October 2020 11:15 Go to previous messageGo to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
So "PUBLIC" is not a group that everyone belongs to, is it?

[Updated on: Thu, 29 October 2020 11:15]

Report message to a moderator

Re: Did I set up UTL_MAIL correctly and completely? [message #682579 is a reply to message #682573] Thu, 29 October 2020 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Darth Waiter wrote on Thu, 29 October 2020 16:22
Worked perfectly! Merci beaucoup!


But what does "PUBLIC" mean in the output above? Is not it supposed to mean that everyone can access? Or
is my user not a member of the PUBLIC? If it is not then should it be added to the PUBLIC?
In this case, it shows a PUBLIC SYNONYM.
All accounts are member of PUBLIC group (the only group).
So all user has access to this synonym.
To have access to a synonym does not mean you have access to the original object.
In the opposite, if you have access to the original object then you have access to it through a public synonym.

The view ALL_OBJECTS shows all objects the current user has access to.
If you execute now the query, you'll see 2 objects: the synonym and the package.

[Updated on: Thu, 29 October 2020 12:06]

Report message to a moderator

Re: Did I set up UTL_MAIL correctly and completely? [message #682581 is a reply to message #682579] Thu, 29 October 2020 13:03 Go to previous message
Darth Waiter
Messages: 29
Registered: October 2020
Junior Member
Michel Cadot wrote on Thu, 29 October 2020 12:02

To have access to a synonym does not mean you have access to the original object.
I will write this down, since this explains everything.

Thank you!
Al
Previous Topic: How can I safely limit the amount of memory used by Oracle 12c?
Next Topic: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE
Goto Forum:
  


Current Time: Sun Nov 29 19:50:57 CST 2020