nicktoons winners cup racing download full versionnero burning rom download full versionpinnacle studio version 7 downloadopera mini 7 mobile web browser free download
Sending e-mail! - - Oracle 8i specific response
Thanks to the question, Leonardo.
Answered by: Tom Kyte Last updated: May 30, 2013 - 2:58 pm UTC
Category: Application Server Version: 8.1.5
How for you personalized email to clients registered during my portal while using information held in our Database Oracle 8i automatically?
This is usually a sample chapter from my book Expert One on One Oracle - it describes how for you email from Oracle versions 816 and the java only approach described about halfway through works in 815 far better UTLSMTP and sending mail UTLSMTP, introduced for that first time in Oracle 8.1.6, is definitely an interface for the Simple Mail Transfer Protocol. It requires you have an SMTP server as part of your network somewhere? most sites I have been to possess at least one SMTP server running since it is the most popular solution to sending mail. The UTLSMTP package is best suited for sending small, text only emails on the database. While its API sports ths sending of attachments and the rest? it's left to your account to actually encode the multi-part document? one example is turning binary attachments into mime-encoded documents. Weve already seen the best way to use UTLSMTP within the DBMSJOB section where we managed to get apparently execute faster by doing the work asynchronously. In this section we?ll revisit that example, build upon it? adding additional functionality. We will also have a look at an alternative to UTLSMTP providing you with somewhat far more functionality? such as the ability to simply send attachments while using email. Since SMTP is often a very low level protocol, well reuse existing public domain code for getting an SMTP interface at better level? and well understand with hardly any code. UTLSMTP? an increased example In the DBMSJOB section, we explored the best way to make sending email using UTLSMTP manage to execute faster. We also made email transactional naturally in that section? when you rollback the email doesn't sent, in case you commit? out it's going. I recommend the use of DBMSJOB to be a layer on the emails routines therefore. In that section, the example UTLSMTP routine we used was: tkyteTKYTE816 create or replace 2 PROCEDURE sendmail psender IN VARCHAR2, 3 precipient IN VARCHAR2, 4 pmessage IN VARCHAR2 5 as 6 lmailhost VARCHAR2255: ; 7 lmailconn ; 8 BEGIN 9 lmailconn: connectionlmailhost, 25; 10 lmailconn, lmailhost; 11 lmailconn, psender; 12 lmailconn, precipient; 13 datalmailconn ; 14 datalmailconn, pmessage; 15 datalmailconn ; 16 lmailconn; 17 end; 18Procedure created. tkyteTKYTE816 begin 2 sendmail, 3, 4 Hello Tom ; 5 end; 6PL/SQL procedure successfully completed. That works OK but is quite limited naturally. It sends email to exactly one recipient, you simply can't CC or BCC anyone, you can't setup a topic - - the email always arrives that has a?blank? subject line. We would like to compliment more options using this type of package. A full discussion of all the possibilities with UTLSMTP would require comprehensive knowledge in the SMTP protocol itself? an issue that is away from scope of the book. Readers serious about all on the opportunities provided by SMTP should review RFC812? which will be the description of SMTP. This is on the net at
Below, I will simply present how for you an email using UTLSMTP that supports: o Multiple?to? recipients o Multiple?cc? recipients o Multiple?bcc? recipients o A single body all the way to 32k in space o A subject line o A descriptive?from? line rather than showing precisely the email address since the?from? inside email client A specification for just a PLSQL package that supports this could look like this. In here, we define a wide range type to allow for just a caller to simply send a summary of recipients in addition to provide the external specification from the PLSQL routine as well as implementing: tkyteTKYTE816 create or replace package mailpkg 2 as 3 type array is table of varchar2255; 4 5 procedure send psenderemail in varchar2, 6 pfrom in varchar2, 7 pto in array default array, 8 pcc in array default array, 9 pbcc in array default array, 10 psubject in varchar2, 11 pbody in long ; 12 end; 13Package created. The package body because of this implementation is fairly straightforward? if understand just enough with the SMTP protocol and what an e-mail looks like how email clients obtain the From, To, CC etc. Before we take a look at the code, we?ll examine what a communication might actually appear like. Consider the next ASCII text: Date: 13 May 01 12:33:22 From: Oracle Database Account Subject: This is usually a subject To:, Cc: Hello Tom, this can be the mail you would like That is what you'd transmit because body from the email using UTLSMTP to obtain the email client set the From, Subject, etc. There are no SMTP commands for doing that magic, rather, this header facts are placed right within the body in the email itself? separated through the text on the email by way of a blank line. Once we know that? sending a communication with each of the options we require is pretty easy. The only thing we end up needing to understand beyond that's that in order to transmit the email to more the other recipient, we only call more then once? with various names. That?s all the information we require to know then for you an email. So, this is actually the package body. We start which has a couple of constants and globals. You will certainly need to alter the gmailhost to get the name of a server you need to get, Aria is my machine within Oracle? you do not be able to gain access to that: tkyteTKYTE816 create or replace package body mailpkg 2 as 3 4 gcrlf char2 default chr13chr10; 5 gmailconn ; 6 gmailhost varchar2255: ; 7 Next we have an inside unpublished function to transmit an email to several recipients? it essentially addresses the email. At a similar time, it builds the?To:? or?Cc:? lines that individuals?ll eventually send as part with the email itself and returns that formatted string. It was implemented being a separate function since we end up needing to accomplish this separately for your To, CC, and BCC lists: 8 function addressemail pstring in varchar2, 9 precipients in array return varchar2 10 is 11 lrecipients long; 12 begin 13 for i in 1. 14 loop 15 gmailconn, precipientsi ; 16 if lrecipients is null 17 then 18 lrecipients: pstring precipientsi ; 19 else 20 lrecipients: lrecipients, precipientsi; 21 end if; 22 end loop; 23 return lrecipients; 24 end; 25 26 Now we've got the implementation of the published function? usually the one people will actually call for you mail. It starts with an interior procedure writeData that's used to simplify the sending from the email headers the To:, From:, Subject: records. If the header record is NOT NULL, this routine make use of the appropriate UTLSMTP call for you it? along using the necessary end of line marker the carriage return/line feed: 27 procedure send psenderemail in varchar2, 28 pfrom in varchar2 default NULL, 29 pto in array default array, 30 pcc in array default array, 31 pbcc in array default array, 32 psubject in varchar2 default NULL, 33 pbody in long default NULL 34 is 35 ltolist long; 36 lcclist long; 37 lbcclist long; 38 ldate varchar2255 default 39 tochar SYSDATE, dd Mon yy hh24:mi:ss ; 40 41 procedure writeData ptext in varchar2 42 as 43 begin 44 if ptext will not be null 45 then 46 data gmailconn, ptext gcrlf ; 47 end if; 48 end; Now were ready to truly send the mai. This part just isn't very different from your very simple routine we started with. It begins in the identical fashion? by connecting on the SMTP server and starting a session: 49 begin 50 gmailconn: connectiongmailhost, 25; 51 52 gmailconn, gmailhost; 53 gmailconn, psenderemail; 54 Here is where it differs, rather then calling once? it uses are addressemail function to refer to it as potentially often, building the?To:? and?Cc:? list for individuals as well. It builds the BCC list but we won?t actually send that any of us don?t want the recipients to view that list! 55 ltolist: addressemail To:, pto ; 56 lcclist: addressemail Cc:, pcc ; 57 lbcclist: addressemail Bcc:, pbcc ; 58 Now, we utilize the OPENDATA call to start out sending the body in the email. The code on lines 61 through 68 generates the header a part of data. Line 69 sends the body with the email the contents in the email and line 70 terminates the email for many people. 59 datagmailconn ; 60 61 writeData Date: ldate ; 62 writeData From: nvl pfrom, psenderemail ; 63 writeData Subject: nvl psubject, no subject ; 64 65 writeData ltolist ; 66 writeData lcclist ; 67 68 data gmailconn, gcrlf ; 69 datagmailconn, pbody ; 70 datagmailconn ; 71 gmailconn; 72 end; 73 74 75 end; 76Package body created. And that?s it, now since I have numerous email addresses, , , I can test this API similar to this: tkyteTKYTE816 begin 2 3 psenderemail , 4 pfrom Oracle Database Account, 5 pto , , 6 pcc , 7 pbcc , 8 psubject This is really a subject, 9 pbody Hello Tom, here is the mail you would like ; 10 end; 11PL/SQL procedure successfully completed. And that call 's what generated the ASCII text: Date: 13 May 01 12:33:22 From: Oracle Database Account Subject: This can be a subject To:, Cc: Hello Tom, this is actually the mail you'll need We saw above? that is certainly what got delivered to all of those recipients? including, although we simply cannot see that recipient given it was bcc?ed. That covers most on the typical uses with the UTLSMTP supplied package. Above I did say it can be capable of sending email with attachments etc but that could require an inordinate level of effort on our part. We would must: o Learn tips on how to format a multi-part mime encoded document, no small feat o Base-64 encode binary data or use some equivalent encoding technique including uuencoding, binhex, and the like That can be conservatively a number of hundred, in any other case thousands of lines of PL/SQL code. Rather then accomplish that, I will report that you utilize the already written and extremely robust JavaMail API as described below. Loading and with all the JavaMail API In order to makes use of the UTLSMTP package, you will need to already employ a Java enabled database in Oracle8i. This is because UTLSMTP will depend on UTLTCP and UTLTCP subsequently is built on Java functions. Remember, in case you dont possess a Java enabled database you need to use UTLHTTP as described above to send out simple emails. So, when you are able to work with UTLSMTP, you will need to have a Java enabled database, we can easily go towards the Sun website and download their JavaMail API. This will provide us with the ability to send out much more complicated emails through the database; including attachments. The following is dependant on work performed using a coworker of mine, Mark Piermarini who helps me out with plenty of my Java issues. If you go to
youll have the ability to download their JavaMail API. The download you have will consist of several hundred files; one of which we are serious about. After you download the JavaMail API? ensure that also to acquire their the JavaBeansTM Activation Framework extension or JAF. This is had to run the JavaMail API package. After you have downloaded the two of these sets of files? you will have to extract from your JavaMail APIdownload and through the JAF download. This is all that you will need using this? go ahead and read through the documentation, there is often a lot of functionality in there we're not using, we're also just while using send a communication part with the API. The API includes functions for receiving mail too from IMAP, POP, and also other sources. We will must load the and to the database using loadjava but before we can easily do that any of us must repackage them. These jar files are compressed in a very format that is certainly not understood through the database byte code interpreter. You should unjar and rejar them without compression or employ a tool like WinZip to rejar them in to a zip file. What I did on Windows 2000 was: 1. Used WinZip to extract the items in into my c:tempmail directory 2. Used WinZip to produce a new archive 3. Put the valuables in c:tempmail. including subdirectories into this new archive I did the same principle for? only replacing mail with activation within the above steps. Now were ready to load these zip or jar files, whatever you decide to named them to the database. These should be loaded while using the SYS user simply because they have protected Java packages that regular users cannot upload. We makes use of the commands: loadjava - u sys/manager - o - r - v - f - noverify - synonym - g public loadjava - u sys/manager - o - r - v - f - noverify - synonym - g public Where: o - u sys/manager: may be the userid and password to your SYS account. As stated previously, some in the packages are protected and have to be loaded as SYS o - o: is shorthand for?oci8, I am utilizing the oci8 driver. You could makes use of the thin driver likewise but youll ought to modify the command for this o - r: is short for?resolve. This will resolve all external references from the loaded classes assisting to verify which the loaded java classes will manage to function as we load them o - v: is short for?verbose. This gives us something to try and do while loadjava is running. We can visualize it work through each step of that process. o - f: is short for?force. This isnt necessary within the first load but is OK to work with. If you try a loadjava thus hitting an error, you are able to correct it, and reload? then you'd either have to use the dropjava command to decrease the jar file on the database or use?force. Using?force just makes it easier for all of us. o - noverify: won't attempt to verify the bytecode. You has to be granted Verifier to complete this option. In addition, this program must be used together with - r. SYS has this privilege. This is needed considering that the bytecode verifier will flag some issues using the file and also this works around that issue. o - synonym: creates public synonyms because of these classes. Since on the internet install the mail java code we write as SYS, this will give us to discover the SYS loaded java classes. o - g public: grants execute on these loaded classes to PUBLIC. If this isn't desirable, modify the?g to be precisely the user you intend to create the send mail routines in, one example is - g UTILITYACCT. You can find out much more about loadjava plus the above options inside Oracle8I Java Developers Guide. After these packages are loaded, we have been ready to produce a Java stored procedure to essentially send the mail. This procedure will act to be a thin layer on top with the JavaMail API all of which will let us ultimately write a PL/SQL binding layer using the following spec: tkyteTKYTE816 desc send FUNCTION send RETURNS NUMBER Argument Name Type In/Out Default? - -------- - ------ - -- - -- PFROM VARCHAR2 IN PTO VARCHAR2 IN PCC VARCHAR2 IN PBCC VARCHAR2 IN PSUBJECT VARCHAR2 IN PBODY VARCHAR2 IN PSMTPHOST VARCHAR2 IN PATTACHMENTDATA BLOB IN PATTACHMENTTYPE VARCHAR2 IN PATTACHMENTFILENAME VARCHAR2 IN This function will impart us with the chance to use CCs and BCCs and send an attachment. It is left being an exercise for your reader to implement passing arrays of BLOBs or overloading this to guide CLOB or BFILE types for attachments at the same time. The Java stored procedure we shall create follows. It uses the fundamental functionality in the JavaMail API class and is actually comparatively straightforward. Again, were not going into each of the uses from the JavaMail API that is certainly a book alone, precisely the basics here. The?mail? class below includes a single method?send?. This may be the method we'll use for you a message. As it truly is implemented, it returns is there a if it really is successful in sending the meial plus a 0 otherwise. This implementation can be quite basic? it could be far more sophisticated, providing support for a lot of attachment types CLOBS, BFILES, LONGS and many others. It could even be modified to report back on the caller the precise error received from SMTP like?invalid recipient, no transport, and the like. tkyteTKYTE816 create or replace and compile 2 java source named mail 3 as 4 import ; 5 import ; 6 import ; 7 import ; 8 import ; 9 import ; 10 import ; 11 import ; 12 import ; 13 14 public class mail 15 16 static String dftMime application/octet-stream; 17 static String dftName ; 18 19 public static 20 sendString from, 21 String to, 22 String cc, 23 String bcc, 24 String subject, 25 String body, 26 String SMTPHost, 27 attachmentData, 28 String attachmentType, 29 String attachmentFileName The above argument list matches up together with the SQL call specification we outlined above? the arguments are mainly self explanatory. The two which may need some clarification will be the attachmentType as well as the attachmentFileName. The attachmentType needs to be a MIME Multi-purpose Internet Mail Extensions type? you may be familiar with from HTML documents. The MIME form of a GIF image by way of example is?image/gif?, the mime form of a plain text document could well be?text/plain?, an HTML attachment could be?text/html? and the like. The attachmentFileName with this example is NOT the domain name of an existing OS file that may be attached but alternatively the filename in the attachment from the email itself? what are the recipient of the email will discover the name with the attachment as. The actual attachment is the that is certainly sent to this particular routine. Now, on the body from the code. We begin by setting the session? for the name on the SMTP host the caller provided for us? the JavaMail API reads this value when deciding what SMTP server for connecting to: 30 31 int rc 0; 32 33 try 34 35 Properties props ; 36, SMTPHost; 37 Message msg 38 new props, null; 39 Next, we put in place the email headers. This part tells the JavaMail API who the content if from, who to send out it to, who to deliver a?carbon copy? cc or?blind carbon copy? bcc, the subject on the email is and what date needs to be associated together with the email: 40 new InternetAddressfrom; 41 42 if to! null 0 43, 44 to, false; 45 46 if cc! null 0 47, 48 cc, false; 49 50 if bcc! null 0 51, 52 bcc, false; 53 54 if subject! null 0 55 subject; 56 else no subject; 57 58 new Date; 59 Next, we use one of two methods to send out an email. If the attachmentData argument will not be null, then we are going to MIME encode the email? a typical that props up sending of attachments as well as other multi-part documents. We try this by establishing multiple MIME limbs? on this case a pair of them, one for your body on the email the text and also the other for that attachment itself. Lines 76 through 78 take some additional explanation. They are how we could send a communication via a BLOB. The JavaMail API doesn?t be aware of the type natively it truly is after all a plain API. In order to transmit the BLOB attachment, we need to provide a method to the JavaMail API to obtain at the BLOB data. We accomplish this by creating our very own DataHandler? a class by having an interface that this JavaMail API understands how you can call in order to obtain data to populate the attachment. This class BLOBDataHandler is implemented by us like a nested class below. 60 if attachmentData! null 61 62 MimeBodyPart mbp1 new MimeBodyPart; 63 body! null? body: ; 64 ; 65 66 MimeBodyPart mbp2 new MimeBodyPart; 67 String type 68 attachmentType! null? attachmentType: dftMime; 69 70 String fileName attachmentFileName! null? 71 attachmentFileName: dftName; 72 73 ; 74 fileName; 75 76 new 77 DataHandlernew BLOBDataSourceattachmentData, type 78 ; 79 80 MimeMultipart mp new MimeMultipart; 81 mbp1; 82 mbp2; 83 mp; 84 If the email isn't going to have an attachment? setting the body on the email is accomplished very simply through the single call to setText: 85 else 86 87 body! null? body: ; 88 89 msg; 90 rc 1; 91 catch Exception e 92 93 ; 94 rc 0; 95 finally 96 97 return new rc; 98 99 100 Now for nested class BLOBDataSource. It simply provides a plain interface for that JavaMail API to reach our type. It is quite straightforward in their implementation: 101//Nested class that implements a DataSource. 102 static class BLOBDataSource implements DataSource 103 104 private BLOB data; 105 private String type; 106 107 BLOBDataSourceBLOB data, String type 108 109 type; 110 data; 111 112 113 public InputStream getInputStream throws IOException 114 115 try 116 117 ifdata null 118 throw new IOExceptionNo data.; 119 120 return ; 121 catchSQLException e 122 123 throw new 124 IOExceptionCannot get binary input stream from BLOB.; 125 126 127 128 public OutputStream getOutputStream throws IOException 129 130 throw new IOExceptionCannot try this.; 131 132 133 public String getContentType 134 135 return type; 136 137 138 public String getName 139 140 return BLOBDataSource; 141 142 143 144Java created. Now that individuals have the Java class made for PL/SQL to bind to, we end up needing to create that binding routine to map the PL/SQL types for their Java Types and also to bind the PL/SQL routine to this particular Java class. That is simply: tkyteTKYTE816 create or replace function send 2 pfrom in varchar2, 3 pto in varchar2, 4 pcc in varchar2, 5 pbcc in varchar2, 6 psubject in varchar2, 7 pbody in varchar2, 8 psmtphost in varchar2, 9 pattachmentdata in blob, 10 pattachmenttype in varchar2, 11 pattachmentfilename in varchar2 return number 12 as 13 language java name , 14, 15, 16, 17, 18, 19, 20, 21, 22 23 return ; 24Function created. Now, the last thing we have to do before making use of this is to ensure our user the owner on the above mail class and send stored procedure has sufficient privileges to complete the routine. Those would be the subsequent: sysTKYTE816 begin 2 permission 3 grantee TKYTE, 4 permissiontype , 5 permissionname, 6 permissionaction read, write 7 ; 8 permission 9 grantee TKYTE, 10 permissiontype , 11 permissionname, 12 permissionaction connect, resolve 13 ; 14 end; 15PL/SQL procedure successfully completed. Note that within the grant on, I used a wildcard inside permissionname. This allows TKYTE to attach to and resolve ANY host. Technically, we can easily put in there precisely the name from the SMTP sever we are using. That will be the minimal grant we needed. This is needed so that you can resolve the hostname of our own SMTP host then connect to it. The other permission, , is needed so as to set the inside our sessions properties. Now we're ready to test. I reused some code in the DBMSLOB section where there were a routine loadafile. I modified that plus the DEMO table to get a BLOB column rather then a CLOB and loaded the file we loaded in being a class into this demo table. Now I can use the subsequent PL/SQL block for you it to myself just as one attachment in the email through the database: tkyteTKYTE816 set serveroutput on size 1000000 tkyteTKYTE816 exec output 1000000 tkyteTKYTE816 declare 2 retcode number; 3 begin 4 for i in select theBlob from demo 5 loop 6 retcode: send 7 pfrom , 8 pto , 9 pcc NULL, 10 pbcc NULL, 11 psubject Use the attached Zip file, 12 pbody to transmit email with, 13 psmtphost , 14 pattachmentdata , 15 pattachmenttype application/winzip, 16 pattachmentfilename ; 17 if retcode 1 then 18 line Successfully sent ; 19 else 20 line Failed to send out ; 21 end if; 22 end loop; 23 end; 24Successfully sent PL/SQL procedure successfully completed. You definitely need to set serverouput on and call the OUTPUT routine on when testing this. This is considering that the exception will be printed from the Java stored procedure to by default that could go in to a trace file for the server. If you want to view any errors with your SQLPlus session, you
How do you send the mails thru oracle 7.1 database as ourt dataBase just isn't Oracle 8. can u plz send me ur precisely the same solution work with Oracle 7.1???
Reviewer: Bijay R. Tuladhar from Hayward, CA
This is one on the most useful solutions. Thank you Tom for helping you!
Excelent, but sending mails continues to be discussed, why don't you consider receiving mails.
Reviewer: Shawn from Toronto, ON, Canada
I attempted to un-jar them and re-jar the files. when using loadjava I am getting a exception. Any suggestions? Or are you able to send me the re-jared files and figure out where to acquire them. Thanks,
The error message is: loadjava - u user/paswddb - o - r - f - v initialization complete loading: mail creating: mail resolver: resolving: mail errors: mail ORA-29535: source requires recompilation mail:31: Class Message not found. mail:31: Class MimeMessage not found. mail:37: Variable msg may possibly not have been initialized. mail:39: Variable msg may possibly not have been initialized. mail:41: Variable msg might not have been initialized. mail:44: Undefined variable or class name: Transport Info: 6 errors loadjava: 8 errors Can anyone assist me out? Thanks ahead of time.
Reviewer: Rajesh Jaswal from Hoshiarpur, Punjab
Reviewer: Akthar amp; Kamalanathan - from Singapore
Neatly carried out with correct step, We would choose to commentchecklist something on positive sense, 1. First to focus on Check whether JVM for Oracle is installed, else run add classpath the steps 1, 2, 3, 4 given inside the TOMs response with replace on step four you must desc send not desc mail. Step 5 make changes to match your SMTP HOST. 4. You are Wish u Happy Oracle Mailing on 8.1.5.
October 25, 2001 - 1:11 am UTC
Dont you believe that within the sentence loadjava parameter - s synonym for and elso has to be present?
December 28, 2001 - 10:11 am UTC
It is a great one but how you can attach file to my e-mail?
January 26, 2002 - 10:25 pm UTC
Dears, When running on 3 i got the subsequent errors. Can anyone assist me out of the usb ports? D:mail loadjava - u marchant/marchantitcmis - o - r - f - v arguments: - u marchant/marchantitcmis - o - r - f - v creating: source mail loading: source mail creating: mail resolving: source mail errors: source mail ORA-29535: source requires recompilation mail:31: Class Message not found. mail:31: Class MimeMessage not found. mail:37: Variable msg might not have been initialized. mail:39: Variable msg might not have been initialized. mail:41: Variable msg might possibly not have been initialized. mail:44: Undefined variable or class name: Transport Info: 6 errors The following operations failed source mail: resolution exiting: Failures occurred during processing Thanks
use NOVERIFY at the same time. On Januarary 27th, 2002 - - I totally rewrote the answer to this particular. The answer above is an extract from my book that has better step by Follow those.
January 27, 2002 - 3:08 am UTC
HI all While loading Jar files I am getting these error, can any body figure out why?? loadjava - u sys/changeoninstall - o - r - v - g public : oracle/jdbc/driver/OracleDriver at :526 at :442 at :93 1 for a:1149 at :1021 at :193 at :49
January 28, 2002 - 11:07 pm UTC
I tried to transmit email from D2K however it failed. I used TYPE per your writings earlier, nonetheless it shows error, because OBJTYPE isn't a procedure. How it will probably be solved? Would you please produce a solution?
January 31, 2002 - 4:57 am UTC
hi, I tried the very first example, it isn't going to seem to work. Any Idea? thanks beforehand Yogeeraj SQL begin sendmail, , Hello Deg ; end;2 3 4 5 6 begin ERROR at line 1: ORA-20001: 421 Service out of stock ORA-06512: at SMTP, line 83 ORA-06512: at SMTP, line 121 ORA-06512: at MAIL, line 8 ORA-06512: at line 2 SQL
learn about not utilize a valid mail host with your sendmail routine. You are getting an oversight back on the smtp server which isnt an smtp server saying im hard to get at you did change: 5 as 6 lmailhost VARCHAR2255: ; 7 lmailconn ; 8 BEGIN to get a hostname that will be valid to suit your needs right?
February 01, 2002 - 6:40 am UTC
Hi, many thanks locating the error in my opinion. Indeed, there is an error from the address. sorry for virtually any inconveniences. ; For sure, this helps me proceed within my implementations and research. Best Regards Yogeeraj
February 18, 2002 - 9:33 pm UTC
Tom this mail routine were using it since previous few months and it is running and mailing well. thank u a lot. I have made some changes to the present routine for you multiple files by not passing blob array.as failed to know I to achieve that.!!! but by reading files in java program.! I would like to transmit u the cause. Mean I would also love to know a very important factor what decides total attachment size to get sent in one mail. In one database sch I can send attachments of 1MB whereas in other Im limited by 6 m cannot find what's restricting me from sending bigger or oracle parameter as the two of these are on diff unix servers but SMTP is same. Once again Many thank you for ur continuous support.
February 21, 2002 - 10:01 am UTC
Hi Thomas, Your solutions are merely great. I have used your code for you email. But I facing a peculiar problem. Once there was clearly a problem inside SMTP server and it also stopped responding, now right then I fired a mail on the database. The result was that my sesion got hung. Is there by any means of trapping the big mistake.
February 25, 2002 - 12:46 am UTC
Hi Check the trace file generated in with your server u may obtain the exacty nature of problem there.
February 25, 2002 - 1:40 pm UTC
Reviewer: Bob Yexley from Dayton, OH USA
Thanks so much because of this solution. If I can have it working, it is going to be a HUGE help and solution for the needs. I am seeking to follow the instructions spelled out here, and did everything they said to perform, but am having problems with all the loadjava command for When I ran it, it loaded everything fine, but ran into problems when trying to settle referenced objects. Im getting ORA-29534, as well as the result is 45 unresolved object references following load. The loading on the worked great, no problems in any way, but had difficulties with SOME with the objects in Here is one example with the errors that I am getting: skipping: com/sun/mail/imap/RightsRight is resolved skipping: javax/mail/IllegalWriteException is definitely resolved skipping: com/sun/mail/iap/Argument is resolved resolving: com/sun/mail/smtp/SMTPMessage errors: com/sun/mail/smtp/SMTPMessage ORA-29534: referenced object /mail/internet/MimeMessage can't be resolved resolving: javax/mail/internet/MimeMessage errors: javax/mail/internet/MimeMessage ORA-29521: referenced name javax/activation/DataHandler can't be found ORA-29521: referenced name javax/activation/DataSource couldn't be found resolving: javax/mail/FolderClosedException errors: javax/mail/FolderClosedException ORA-29534: referenced object /mail/Folder could hardly be resolved Any idea whats wrong, and/or how I can remedy it?? - ::YEX::-
show me a cut and paste within your loadjava command.
February 26, 2002 - 1:24 pm UTC
Can you reload the two activation and mail ZIP files using another user? This will facilitate debugging and you can use it to look for the resolver for virtually every problems. Also, add - debug for your loadjava commands and send that output to
February 26, 2002 - 1:39 pm UTC
Reviewer: Raza from Toronto, Canada
I was trying toms example 1 and possess this error! can a single help me out begin psenderemail , pfrom Oracle Database Account, pto , , pcc , pbcc , psubject This is often a subject, pbody Hello Raza, here is the mail you would like ; end; ORA-29540: class oracle/plsql/net/TCPConnection doesn't exist ORA-06512: at TCP, line 678 ORA-06512: at TCP, line 247 ORA-06512: at SMTP, line 99 ORA-06512: at SMTP, line 121 ORA-06512: at PKG, line 49 ORA-06512: at line 2 and what needs being done!!!???
search for ORA-29540 on this web site.
February 26, 2002 - 3:29 pm UTC
this really should not be done in the network - - the file you're loading must be one on the server anyway dont make use of the network.
February 26, 2002 - 4:03 pm UTC
Do we end up needing to do the identical things for Oracle9i since you described within this detailed instruction? If it's different, can you explain in addition, it in detail? Thanks a lot! Harvey
should are employed in 9i, havent loaded this myself established but the steps would basically be the identical you dont must use sys and in all probability wont be capable of use sys typically in 9i, that will be different yes, you'd need to do that in 9i should you wanted to deliver email with attachments.
February 26, 2002 - 5:43 pm UTC
Thx to your Valuable ideas We have succefully implmented the SMTP mail with any attachments Including Binary in Pure oracle code i.e It Converts Binary to Base 64 format Thx Ashok
February 27, 2002 - 9:58 am UTC
I cant have it work! I have two instances running around the same machine, I have opened the telnet session on box where my database is, and wanting to load by issuing this command. RCISDEV loadjava - user sys/changeoninstallrcisdvl And getting this error. SQL Error while connecting with oci8 driver to rcisdvl: ORA-01031: insufficient privileges can't open connection loadjava: 2 errors I checked my ORACLE SID which can be set to rcisdvl and when I issue loadjava without rcisdvl RCISDEV loadjava - user sys/changeoninstall Then I understand error!! SQL Error while connecting with oci8 driver to default database: ORA-01034: ORAC LE out of stock ORA-27101: shared memory realm isn't going to exist IBM AIX RISC System/6000 Error: 2: No such file or directory cannot open connection loadjava: 2 errors What really should be done!!! you advice Thanks before hand Raza
Followup February 27, 2002 - 10:44 am UTC
You should log in as being the Oracle software owner. You should verify your oracle home and oracle sid You should verify that it is possible to then: sqlplus sys/changeoninstall then run loadjava.
Very nice! But how can i get more specifics of UTLSMTP.
Tom, In your pl/sql solution if bcc list is just not written out with writeData, are you able to explain the actual way it will work? Thanks, Chris
because this: 15 gmailconn, precipientsi ; does the exact sending, the writing with the CC and TO list is simply for display - - the phone call to writedata to be able doesnt affect WHO the email is actually shipped to - - just just what the email client displays.
I have followed the many instructions. And they were successfull also. But when I type these command SQL desc send ERROR: ORA-04043: object send doesn't exist I receive the above said error. Can anybody assist me to. The mail8i and activation8i, are proper. But if anybody features a working version please send on
Tom I downloaded the javamail package much like your note. However, from the archive there isn't any file. Any idea how I could get this?. I downloaded the 1.2 version. Also, thank you for explaining how bcc list works. Rgds, Chris
It is separate, goto and appearance for activation.
tom, i run the next snippet of code, i obtain the error declare lmailhost VARCHAR2255: our mailserver ip; lmailconn ; begin lmailconn: connectionlmailhost, 25; lmailconn, lmailhost; lmailconn, ; lmailconn, ; datalmailconn ; datalmailconn, pakka ma; datalmailconn ; lmailconn; end; ORA-20002: 550 5.7.1 Unable to relay for ORA-06512: at SMTP, line 86 ORA-06512: at SMTP, line 223 ORA-06512: at line 8 is it possible to tell me why? i understand error no matter what the email i give inside the recpt please help
contact your email guys/network guys. the server you might be attempting to use isn't set up to relay, this is NOT an oracle issue, not much of a UTLSMTP issue, it can be a SMTP configuration issue. You are probably with all the WRONG mailhost since it's not setup to relay anti-spamming defense
tom, what is the limit for the email body size as you mentioned within your answer as 32k only. please reply. thanks
Only as I get it coded. I am passing the body like a single plsql varchar2 variable. If you passed a brief clob and wrote against each other 32k with a time, you might make it as large when you wanted. There is no limit well, 4gig
If i have to transmit multiple emails, like typicallly. i use a cursor that returns contact information and other detials. i must loop and email additional details towards the email address would it be only the subsequent way or anyother simple way declare lmailhost VARCHAR2255: our mail server; lmailconn ; gcrlf char2 default chr13chr10; begin lmailconn: connectionlmailhost, 25; lmailconn, lmailhost; for rec in cemailcur loop lmailconn, ; lmailconn, address; datalmailconn ; data lmailconn, Date: sysdate gcrlf ; data lmailconn, Subject: Testing utlsmtp gcrlf ; datalmailconn, To: address gcrlf; datalmailconn, This is really a Reminder for just a Notification Received originally on: March 1, 2002. gcrlf; datalmailconn, gcrlf; datalmailconn, The Access Removal form attached requires your immediate attention. gcrlf; datalmailconn, gcrlf; datalmailconn, Access Removal Request For: gcrlf; datalmailconn, gcrlf; datalmailconn ; end loop; lmailconn; end; any suggestions? thanks
Yes, use modular code and factor out repeated processing Write a sendmail routine or just utilize the ones above. this becomes: begin for rec in loop ; end loop; end;Thats what procedures are only for.
Can you please indicate that this code have to be changed to e-mail a bfile as a possible attachment I need to e-mail compressed files in a OS filesystem. I am just beginning to learn Java and dont even know where to commence with above mentioned problem although I expect at the very least the BLOBDataSource nested class to alter or a similar class for bfile for being added. By the way - which resources could you suggest for learning Java, from an Oracle background?
Should be as elementary as changing pattachmenttype to BFILE and opening the BFILE in plsql before calling java - - the api to read by a bfile in java could be the same like a blob. You might consider:
it has some nifty utilities within it. It is written for your PLSQL developer that wants for getting their feet wet with a few java.
Reviewer: Randy Richardson from St. Louis, MO
Tom, Is there a solution to validate the TO list before sending? Or do you should just send and enable the bad contact information fail? We are using MS exchange 2000 mail server and would like to try and do validation contrary to the address book. Thanks.
Well, the email is coming on the database about the server The address book was in exchange or around the If you are able to ask MS the best way to access the address book via java, we could certainly make it happen. hmm, isnt this how email viruses start? Im doubtful, lots of security difficulty with accessing the address book you should trust the oracle account and I do not know if Java can access that you aren't. We could use C likewise if they support that - - though the security issues abound.
Reviewer: srividhya from bangalore, India
Hi Tom, Thanks a lot for that is really great and dealing had installed around the development database oracle 8.1.6 along no difficulty with it. However, when we experimented with deploy on our live database oracle 8.1.6, we're facing a great deal of installation problems. We realized which the java option will not be set if the database was setup1.several years database dimensions are do we set the java option?We are not in a position to set the java option as a possible incremental do we accomplish that. Thanks beforehand. Please !!. We are just delayed in making use of it.
tom, could it be possible to track the failure of a communication. believe i am sending email using utlsmtp with TO list has an individual and CC list has 10 persons. now email was shipped to all the CC people but not on the TO list person. with this case i need to transmit an email with an ADMIN saying email compared to that TO individual is failed could it be possible?
the email will bounce tot he REPLY-TO address. set the reply-to to admin. I believe there is surely an error-to or something like that too. you'll have to look it down
tom, could you give me just how can set the reply-to address to admin. because once i tried inside following way mail just isn't sent for the admin. however the bounced mail come for the from addressee only. declare lmailhost VARCHAR2255: our mail server; lmailconn ; ltoaddr varchar250: ; - - invalid email id lfromaddr varchar250; - - assign the from address here lreplyto varchar250; gcrlf char2 default chr13chr10; begin lmailconn: connectionlmailhost, 25; lmailconn, lmailhost; lmailconn, lfromaddr; lmailconn, ltoaddr; datalmailconn ; data lmailconn, Date: sysdate gcrlf ; data lmailconn, Subject: Testing utlsmtp gcrlf ; datalmailconn, To: ltoaddr gcrlf; datalmailconn, REPLY-TO: lreplyto gcrlf; datalmailconn, This is often a Reminder to get a Notification Received originally on: March 1, 2002. gcrlf; datalmailconn, gcrlf; datalmailconn, The Access Removal form attached requires your immediate attention. gcrlf; datalmailconn, gcrlf; datalmailconn, Access Removal Request For: gcrlf; datalmailconn, gcrlf; datalmailconn ; lmailconn; end;
Reviewer: shamim from New Delhi, India
Great Service done my you TOM Hi Tom Your answer and subsequent followup are extremely informative. I tried it, doenloaded perfectly but I am facing overuse injury in downloading of, It is showing error while recolving objects, inside same way as Yexley have. and send the debugged code at, BUt message comes until this id doed not exist. So please if he have given some means to fix Yexley problem Forward it in my opinion.
drop the usa. on the email address as should are actually indicated from the bounced email, weve stopped while using country specific domain name
Reviewer: Binh Ta from Maryland, USA
Hi Tom, Very helpful indeed. But I could you answer my follow-up question too please: How can one use PL/SQL to make Appointments in Exchange? Ive searched practically the entire internet but found nothing. Would really appreciate when you can shedsome light. Many thanks. Binh Ta.
No idea, I know nothing about exchange. We can do com automatation calls from plsql on NT with all the com cartridge.
Reviewer: Munz from Reston, USA
Tom: Do I recognize that if I want to transmit an email but with a subject matter line and a communication pulled out from the database I can get it done with PL/SQL. I only should use jAVAMAIL API when I need t osend attachments. THank you,
You never need javamail, you might use utlsmtp 100%. It is just much much easier with javamail to send out attachments, it lets you do the be employed by you. UTLSMTP is enough for sending text based email
Tom: If you do not want to utilize named parameters but not cc or bcc anyone in email how must you pass parameters. I am getting an oversight when I do: begin , null, , ,, This is really a subject, Hello Tom, this is actually the mail you will need ; lineMessage sent towards the user successfully; end;Cant you pass null values to a wide range. Thanks,
why dont you want make use of named parameters? they may be better then positional, that you are not calling this from SQL so that is certainly not a worry. Otherwise, arrange the parameters to match your needs but wait, thatll break your other code unless it uses named parameters I would recommend you utilize a named parameter, it can be more readable, less ambigous in case someone changes an order on you later - - you might be PROTECTED. You can accomplish this: begin sender email, from, to, , , subj, body ; end;too. You are not passing a vacant array - you passed an assortment with some blank strings - - completely different. Just pass empty arrays like I have them defaulted to
TCPConnection class isn't loaded in database I want to deliver mail rid of, even so the classes are loaded in another db behind the firewall that I can access by way of a db link. I have tried building a synonym for UTLSMTP while using the db link. Should this work? I also tried writing a package that accepts a similar inputs send mail does, but simply calls send mail throughout the db link together with the same arguments it had been called with. This doesnt work either. I get wrong number or type arguments. Can you show how you'll call UTLSMTP across a db link from your db that doesnt hold the java classes installed?
utlsmtp works on the record. that you are probably not defining your connection record making use of this remote utlsmtp package. tkyteTKYTE816 create or replace 2 PROCEDURE sendmail psender IN VARCHAR2, 3 precipient IN VARCHAR2, 4 pmessage IN VARCHAR2 5 as 6 lmailhost VARCHAR2255: ; 7 lmailconn ; but, sigh, lacking an example plus the error message cut and pasted from sqlplus, its very difficult to tell
Reviewer: Sven Bleckwedel from Santos, Brazil
Hi TOM, Your explanation was handy to let me to implement this resource in a very database which i administer. When using another character set inside my case, WE8ISO8859P1 some problems appeared, but i
Oracle8i On-Line Generic Documentation Release 3 8.1.7
Oracle8i Online Documentation CD-ROM Release 3 8.1.7 for Microsoft Windows
Oracle8i Client Online Documentation CD-ROM Release 3 8.1.7 for Microsoft Windows
Oracle interMedia Audio, Image, and Video Java Classes Users Guide and Reference, Release 8.1.7
Oracle8i Documentation Addendum, Release 8.1.7
Oracle Label Security Administrators Guide Release 8.1.7
Oracle Heterogeneous Services Release 8.1.7
Oracle8i 8.1.7 Installation Guide for Windows NT
Oracle8i 8.1.7 Client Installation Guide for Windows
Oracle8i 8.1.7 Release Notes for Windows NT
Legato Storage Manager Command Reference Guide Release 8.1.7 for Windows 2000 and Windows NT
Oracle Message Broker 2.0.1.0 Installation Guide for Windows NT
Oracle Applications InterConnect Installation Guide Release 4.0.0 for Windows NT and UNIX
Oracle8i Administrators Reference Release 3 8.1.7 for AIX-Based Systems