Forum Stats

  • 3,838,658 Users
  • 2,262,390 Discussions
  • 7,900,727 Comments

Discussions

UTL_MAIL setup, thoughts please!

User_Z9RR4
User_Z9RR4 Member Posts: 4 Green Ribbon

Hello forum peeps!


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.

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 in advance,

Frank