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  |
 |
Darth Waiter
Messages: 78
Registered: October 2020
|
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
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
select comp_name,status from dba_registry;
JServer JAVA Virtual Machine
VALID
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 #682520 is a reply to message #682519] |
Tue, 27 October 2020 09:39  |
|
Darth Waiter
Messages: 78
Registered: October 2020
|
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;
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.
call UTL_MAIL.SEND ('info@localdomain.lan', 'info@localdomain.lan', null, null, 'UTL_MAIL Test', 'Message', 'text/plain; charset=us-ascii', null)
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 #682559 is a reply to message #682558] |
Wed, 28 October 2020 16:44 |
|
Darth Waiter
Messages: 78
Registered: October 2020
|
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 #682565 is a reply to message #682559] |
Thu, 29 October 2020 01:21 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
/
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 #682576 is a reply to message #682573] |
Thu, 29 October 2020 10:59 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Darth Waiter wrote on Thu, 29 October 2020 10:22Worked 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>
[Updated on: Thu, 29 October 2020 11:01] 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 |
|
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Darth Waiter wrote on Thu, 29 October 2020 16:22Worked 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 |
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 02 18:23:27 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
