sims 2 nightlife download free full versionposer 7 download free cracksolveigmm video splitter crack free downloadpl sql developer download windows 64 bit
Oracle announces beta accessibility of Oracle Database 12 c, Release 2 providing more agility and scalability for cloud deployments.
Welcome towards the most comprehensive portfolio of cloud solutions for the business, IT infrastructure, and development needs.
What key performance indicators matter within the Digital Age, and exactly how do you measure their value on the business? New global research by CIMA shows the way to build a next-generation finance function capable to capture the worthiness being developed by digital technologies.
Market-leading vertical software running your core business.
Recruit, develop, and retain top talent having a socially oriented, data-rich cloud software suite.
Oracle announces breakthrough processor and systems design with SPARC M7, featuring Security in Silicon, SQL in Silicon for unparalleled database efficiency, and record-setting enterprise performance.
New applications, platforms, and tools broaden Oracles complete enterprise cloud.
Everything an enterprise manager should know.
Oracle President Thomas Kurian references the benefits of Oracle Database within the Oracle Cloud.
Transform your organization with Oracle Cloud.
Oracle Marketing Cloud creates ideal customers.
Learn that this Oracle Cloud Platform can accelerate your organization.
Platform to be a service permits you to rapidly develop scalable apps.
Oracle Cloud Platform to be a Service is designed for developers.
IDC Research weighs in on Platform to be a Service.
Discover tips on how to drive innovation.
Cloud would be the only way for companies to obtain the speed and innovation they really need.
Finding the best library.
Under memo of understanding with Tencent Cloud, Oracles portfolio of cloud services will likely be offered in China.
In-depth articles on Oracle technology now on the smartphone and tablet.
Data Capital can be a foundation with the future.
Oracles Configure, Price, Quote tool is transforming selling practices.
See what CERN European Laboratory for Particle Physics discovered.
Oracle DIVArchive Suite helps MLB Network manage 7 petabytes of video content annually.
Hitachi moved 650 subsidiaries to centralized recruiting.
When DX Marketing were forced to scale up and lower your expenses, there seemed to be only one destination to turn.
Four Conferences: Modern CX, Finance, HCM, and Supply Chain.
Oracle Industry Connect: Orlando, April 2016
Thought leadership as well as practices.
Oracle CloudWorld: Seoul, January 19
Featuring keynotes for Oracle executives and industry visionaries.
Five Conferences: Modern CX, Finance, HCM, Supply Chain, and IT.
Modern Supply Chain Experience: Jan 25 27, 2016
Come to San Jose for optimum practices and new solutions on the top supply chain innovators.
Simplify processes and accelerate decisions.
Align processes and remain compliant.
Support growth and innovation and exploit enabling technologies.
Leverage insight, empower teams and reach your goals.
Connect with mobile and resolve issues quickly.
Phone: 1.800.633.0738 Oracle announces beta use of Oracle Database 12 c, Release 2 providing more agility and scalability for cloud deployments.
Welcome to your most comprehensive portfolio of cloud solutions on your business, IT infrastructure, and development needs.
What key performance indicators matter inside the Digital Age, and just how do you measure their value towards the business? New global research by CIMA shows ways to build a next-generation finance function capable of capture the additional value being produced by digital technologies.
Market-leading vertical software to operate your core business.
Recruit, develop, and retain top talent having a socially oriented, data-rich cloud software suite.
Oracle announces breakthrough processor and systems design with SPARC M7, featuring Security in Silicon, SQL in Silicon for unparalleled database efficiency, and record-setting enterprise performance.
New applications, platforms, and tools broaden Oracle s complete enterprise cloud.
Everything a company manager would need to know.
Oracle President Thomas Kurian speaks about the benefits of Oracle Database from the Oracle Cloud.
Transform your organization with Oracle Cloud.
Oracle Marketing Cloud creates ideal customers.
Learn the way the Oracle Cloud Platform can accelerate your organization.
Platform being a service permits you to rapidly develop scalable apps.
Oracle Cloud Platform being a Service is generated for developers.
IDC Research weighs in on Platform being a Service.
Discover the best way to drive innovation.
Cloud could be the only way for companies to discover the speed and innovation they really need.
Hunting down hard-to-find errors.
Under memo of understanding with Tencent Cloud, Oracle s portfolio of cloud services will likely be offered in China.
In-depth articles on Oracle technology now on the smartphone and tablet.
Data Capital is usually a foundation to the future.
Oracle SuperCluster and Oracle ZFS Storage Appliance have boosted performance 17x with the cycling innovator.
Oracle ERP, EPM, and Sales Cloud are raising the Silicon Valley YMCA boost its membership.
Oracle DIVArchive Suite helps MLB Network manage 7 petabytes of video content annually.
Oracle Exalytics and Oracle Business Intelligence are enabling start up company opportunities.
When DX Marketing needed to scale up and lower your expenses, there was clearly only one location to turn.
Four Conferences: Modern CX, Finance, HCM, and Supply Chain.
Oracle Industry Connect: Orlando, April 2016
Thought leadership and finest practices.
Oracle CloudWorld: Seoul, January 19
Featuring keynotes for Oracle executives and industry visionaries.
Five Conferences: Modern CX, Finance, HCM, Supply Chain, and IT.
Modern Supply Chain Experience: Jan 25 27, 2016
Come to San Jose for the very best practices and new solutions in the top supply chain innovators.
Simplify processes and accelerate decisions.
Align processes and turn into compliant.
Support growth and innovation and exploit enabling technologies.
Leverage insight, empower teams and reach your goals.
Connect with mobile and resolve issues quickly.
Phone: 1.800.633.0738 HTTP/1.0 302 Found Location: /pls/asktom/f?p100:11:0::::P11QUESTIONID:255615160805 Server: BigIP Connection: Keep-Alive Content-Length: 0
Sending e-mail! - - Oracle 8i specific response
Thanks for your 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 utilizing the information trapped in our Database Oracle 8i automatically?
This can be a sample chapter from my book Expert One on One Oracle - it describes tips on how to send email from Oracle versions 816 and better the java only approach described about halfway through works in 815 and UTLSMTP and sending mail UTLSMTP, introduced with the first time in Oracle 8.1.6, is surely an interface on the Simple Mail Transfer Protocol. It requires you have an SMTP server within your network somewhere? most sites I have been to get at least one SMTP server running which is the most popular solution to sending mail. The UTLSMTP package is best suited for sending small, text only emails in the database. While its API props up sending of attachments and any devices? it's left for your requirements to actually encode the multi-part document? by way of example turning binary attachments into mime-encoded documents. Weve already seen the best way to use UTLSMTP inside the DBMSJOB section where we managed to make it apparently execute faster by executing it 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 that can offer somewhat considerably more functionality? like the ability to simply send attachments while using email. Since SMTP is really a very low level protocol, well reuse existing public domain code to obtain an SMTP interface at a lot higher level? and well obtain it with almost no code. UTLSMTP? an increased example In the DBMSJOB section, we explored tips on how to make sending email using UTLSMTP manage to execute faster. We also made email transactional as the name indicated in that section? in the event you rollback the email doesn't sent, should you commit? out it is going. I strongly suggest the use of DBMSJOB as being a layer with your emails routines thereby. 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 incredibly limited in the wild. It sends email to exactly one recipient, you simply can't CC or BCC anyone, you can not setup a topic - - the email always arrives that has a?blank? subject line. We would like to aid more options using this type of package. A full discussion from all of the possibilities with UTLSMTP would require thorough knowledge with the SMTP protocol itself? something which is away from scope in this book. Readers considering all from the opportunities provided with SMTP should review RFC812? which may be the description of SMTP. This is available on the web at
Below, I will simply present the best way to send a contact 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 proportions o A subject line o A descriptive?from? line rather than showing precisely the email address because?from? within the email client A specification for just a PLSQL package that supports this could look like these. In here, we define an assortment type to allow to get a caller to only send a listing of recipients together with provide the external specification in the PLSQL routine we will likely be 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 with this implementation is actually comparatively straightforward? if understand just enough with the SMTP protocol and what a message looks like how email clients find the From, To, CC and many others. Before we look with the code, we?ll have a look at what a message might actually appear to be. Consider the subsequent 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 would like That is what you'd probably transmit since the body with the email using UTLSMTP to possess the email client set the From, Subject, and so forth. There are no SMTP commands for doing that magic, rather, this header info is placed right inside the body in the email itself? separated through the text from the email with a blank line. Once we recognize that? sending an e-mail with all the options we'd like is pretty easy. The only thing we end up needing to understand beyond that may be that in order to send out the email to more and another recipient, we call more then once? with various names. That?s the many information we end up needing to know then for you an email. So, here's the package body. We start which has a couple of constants and globals. You will naturally need to affect the gmailhost to become the name of a server you need, Aria is my machine in Oracle? you'll not be capable of access 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 send out an email to numerous recipients? it essentially addresses the email. At a similar time, it builds the?To:? or?Cc:? lines that people?ll eventually send as part on the email itself and returns that formatted string. It was implemented as being a separate function since we want to make this happen 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 individuals published function? normally the one people will actually call to deliver mail. It starts with an interior procedure writeData which is used to simplify the sending in 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 just isn't null 45 then 46 data gmailconn, ptext gcrlf ; 47 end if; 48 end; Now were ready to truly send the mai. This part is just not very different from your very simple routine we started with. It begins in precisely the same fashion? by connecting towards 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 than calling once? it uses are addressemail function to think of 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 determine that list! 55 ltolist: addressemail To:, pto ; 56 lcclist: addressemail Cc:, pcc ; 57 lbcclist: addressemail Bcc:, pbcc ; 58 Now, we utilize OPENDATA call to start out sending the body from the email. The code on lines 61 through 68 generates the header part of data. Line 69 sends the body from the email the contents in the email and line 70 terminates the email for individuals. 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 such as 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 is what generated the ASCII text: Date: 13 May 01 12:33:22 From: Oracle Database Account Subject: This is usually a subject To:, Cc: Hello Tom, here is the mail you'll need We saw above? that may be what got delivered to all of those recipients? including, although we simply cannot see that recipient mainly because it was bcc?ed. That covers most from the typical uses from the UTLSMTP supplied package. Above I did say it's capable of sending email with attachments etc but that could require an inordinate level of effort on our part. We would need to: 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 like uuencoding, binhex, and many others 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 makes use of the already written and incredibly robust JavaMail API as described below. Loading and utilizing the JavaMail API In order to utilize the UTLSMTP package, you will need to already possess a Java enabled database in Oracle8i. This is because UTLSMTP depends on UTLTCP and UTLTCP subsequently is built on Java functions. Remember, in case you dont employ a Java enabled database you need to use UTLHTTP as described above to deliver simple emails. So, should you are competent to use 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 impart us with the ability to deliver much more complicated emails on the database; including attachments. The following is determined by work performed with a coworker of mine, Mark Piermarini who helps me out with plenty of my Java issues. If you go to
youll be competent to download their JavaMail API. The download you have will consist of a number of hundred files; one of which we are enthusiastic about. After you download the JavaMail API? ensure also to have their the JavaBeansTM Activation Framework extension or JAF. This is needed to own the JavaMail API package. After you have downloaded the two of these sets of files? you will should extract in the JavaMail APIdownload and from your JAF download. This is whatever you will need out of this? twenty-four hours a day read with the documentation, there can be a lot of functionality in there we're not using, were just while using the send an e-mail part from the API. The API includes functions for receiving mail too from IMAP, POP, along with other sources. We will ought to load the and in the database using loadjava but before we are able to do we must repackage them. These jar files are compressed within a format that is certainly not understood with the database byte code interpreter. You must unjar and rejar them without compression or utilize a tool for instance WinZip to rejar them to a zip file. What I did on Windows 2000 was: 1. Used WinZip to extract the valuables in into my c:tempmail directory 2. Used WinZip to make a new archive 3. Put the valuables in c:tempmail. including subdirectories into this new archive I did the same for? only replacing mail with activation within the above steps. Now we're ready to load these zip or jar files, anything you named them in the database. These should be loaded utilizing the SYS user 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: could be the userid and password to your SYS account. As stated previously, some in the packages are protected and need 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 at the same time but youll have to modify the command to accomplish this o - r: is short for?resolve. This will resolve all external references from the loaded classes making an effort to verify which the loaded java classes will be in a position 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 the process. o - f: is short for?force. This isnt necessary around the first load but is OK make use of. If you try a loadjava thus hitting an error, you are able to correct it, and reload? then you'll either ought to use the dropjava command to go the jar file in the database or use?force. Using?force just makes it easier for individuals. o - noverify: won't attempt to verify the bytecode. You has to be granted Verifier to carry out this option. In addition, this choice must be used together with - r. SYS has this privilege. This is needed since the bytecode verifier will flag some issues with all the file this also works around that issue. o - synonym: creates public synonyms of those classes. Since we won't install the mail java code we write as SYS, this permits us to discover the SYS loaded java classes. o - g public: grants execute on these loaded classes to PUBLIC. If this will not be desirable, affect the?g to be only the user you intend to create the send mail routines in, one example is - g UTILITYACCT. You can find out more to do with loadjava along with the above options inside Oracle8I Java Developers Guide. After these packages are loaded, we have been ready to develop a Java stored procedure to truly send the mail. This procedure will act being a thin layer on top with the JavaMail API and definately 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 capacity to use CCs and BCCs and send an attachment. It is left as a possible exercise to the reader to implement passing arrays of BLOBs or overloading this to back up CLOB or BFILE types for attachments likewise. The Java stored procedure we'll create follows. It uses the essential functionality from the JavaMail API class and is pretty straightforward. Again, were not going into every one of the uses with the JavaMail API that is a book alone, the basics here. The?mail? class below carries a single method?send?. This will be the method we're going to use to deliver a message. As it really is implemented, it returns the very if it really is successful in sending the meial plus a 0 otherwise. This implementation is quite basic? it might be far more sophisticated, providing support for most attachment types CLOBS, BFILES, LONGS and the like. It could be also modified to report back for the caller the error received from SMTP for example?invalid recipient, no transport, and many others. 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 with all the SQL call specification we outlined above? the arguments are mainly self explanatory. The two which may need some clarification include the attachmentType along with the attachmentFileName. The attachmentType really should be a MIME Multi-purpose Internet Mail Extensions type? perhaps you may be familiar with from HTML documents. The MIME style of a GIF image one example is is?image/gif?, the mime sort of a plain text document could be?text/plain?, an HTML attachment could be?text/html? etc. The attachmentFileName in this particular example is NOT the an existing OS file that might be attached but the filename on the attachment from the email itself? exactly what the recipient in this email will discover the name from the attachment as. The actual attachment is the that may be sent for this routine. Now, on top of the body with the code. We begin by setting the session? on the name on the SMTP host the caller delivered to us? the JavaMail API reads this value when deciding what SMTP server to attach 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 what it's all about if from, who to deliver it to, who to transmit a?carbon copy? cc or?blind carbon copy? bcc, exactly what the subject with the email is and what date ought 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 for you an email. If the attachmentData argument is just not null, then we are going to MIME encode the email? an ordinary that props up the sending of attachments along with other multi-part documents. We do that by putting together multiple MIME areas of the body? on this case a couple of them, one to the body with the email the text plus the other with the attachment itself. Lines 76 through 78 need some additional explanation. They are how you can send a communication via a BLOB. The JavaMail API doesn?t view the type natively it really is after all a plain API. In order for you the BLOB attachment, we should provide a method for that JavaMail API to get for the BLOB data. We make it happen by creating our personal DataHandler? a class through an interface which the JavaMail API understands tips on how to call in order for getting 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 will not have an attachment? setting the body from the email is accomplished very simply through 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 normal interface with the JavaMail API gain access to our type. It is incredibly straightforward in the 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 accomplish 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 any of us have the Java class designed for PL/SQL to bind to, we require to create that binding routine to map the PL/SQL types with their Java Types and bind the PL/SQL routine to the present Java class. That is actually: 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, ab muscles last thing we have to do before by using this is to ensure our user the owner from the above mail class and send stored procedure has sufficient privileges to carry out the routine. Those would be the next: 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 from the grant on, I used a wildcard inside permissionname. This allows TKYTE for connecting to and resolve ANY host. Technically, we will put in there precisely the name on the SMTP sever we is going to be using. That could be the minimal grant we needed. This is needed so as to resolve the hostname of our own SMTP host after which connect into it. The other permission, , is needed so that you can set the in your sessions properties. Now we have been ready to test. I reused some code on the DBMSLOB section where we a routine loadafile. I modified that as well as the DEMO table to own a BLOB column rather than a CLOB and loaded the file we loaded in like a class into this demo table. Now I can use this PL/SQL block for you it to myself just as one attachment in a email in 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 for you 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 deliver ; 21 end if; 22 end loop; 23 end; 24Successfully sent PL/SQL procedure successfully completed. You definitely desire to set serverouput on and call the OUTPUT routine on when testing this. This is as the exception has printed through the Java stored procedure to through default that could go in to a trace file around the server. If you want to determine any errors inside your SQLPlus session, you
How must i send the mails thru oracle 7.1 database as ourt dataBase will not be Oracle 8. can u plz send me ur precisely the same solution help Oracle 7.1???
Reviewer: Bijay R. Tuladhar from Hayward, CA
This is one from the most useful solutions. Thank you Tom for helping all of us!
Excelent, but sending mails may be discussed, what about receiving mails.
Reviewer: Shawn from Toronto, ON, Canada
I attempted to un-jar them and re-jar the files. while using the loadjava I am getting a exception. Any suggestions? Or are you able to send me the re-jared files and figure out where to have 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 mightn't have been initialized. mail:39: Variable msg might possibly not have been initialized. mail:41: Variable msg may possibly not have been initialized. mail:44: Undefined variable or class name: Transport Info: 6 errors loadjava: 8 errors Can anyone assist me to out? Thanks ahead of time.
Reviewer: Rajesh Jaswal from Hoshiarpur, Punjab
Reviewer: Akthar amp; Kamalanathan - from Singapore
Neatly through with correct step, We would want to commentchecklist something on positive sense, 1. First to begin with Check whether JVM for Oracle is installed, else run add classpath the steps 1, 2, 3, 4 given from the TOMs response with just have to change on 4 you ought to 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 think that that within the sentence loadjava parameter - s synonym for and elso have to be present?
December 28, 2001 - 10:11 am UTC
It is great but the way to 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 aid 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 may possibly 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 the. The answer above is currently an extract from my book which includes better step by Follow those.
January 27, 2002 - 3:08 am UTC
HI all While loading Jar files I am getting this 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 in a:1149 at :1021 at :193 at :49
January 28, 2002 - 11:07 pm UTC
I tried to transmit email from D2K but it really failed. I used TYPE per your writings earlier, nonetheless it shows error, because OBJTYPE isn't a procedure. How it are going to be solved? Would you please produce a solution?
January 31, 2002 - 4:57 am UTC
hi, I tried the very first example, it won't seem to figure. Any Idea? thanks upfront 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
took action now not make use of a valid mail host with your sendmail routine. You are getting a blunder back in the smtp server which isnt an smtp server saying im out of stock you did change: 5 as 6 lmailhost VARCHAR2255: ; 7 lmailconn ; 8 BEGIN to obtain a hostname that may be valid available for you right?
February 01, 2002 - 6:40 am UTC
Hi, appreciate your locating the error to me. Indeed, there is an error from the address. sorry for almost any inconveniences. ; For sure, this will aid me proceed inside my implementations and research. Best Regards Yogeeraj
February 18, 2002 - 9:33 pm UTC
Tom this mail routine were using it since previous couple of months and is particularly running and mailing well. thank u a whole lot. I have made some changes to the present routine to send out multiple files by not passing blob array.as would not know I to accomplish this.!!! but by reading files in java program.! I would like to send out u the foundation. Mean I would also love to know another thing what decides total attachment size to become sent in one mail. In one database sch I can send attachments of 1MB whereas in other Im tied to 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 to deliver email. But I facing a peculiar problem. Once there was clearly a problem from the SMTP server and it also stopped responding, now at the time I fired a mail through the database. The result was that my sesion got hung. Is there in whatever way of trapping the big mistake.
February 25, 2002 - 12:46 am UTC
Hi Check the trace file generated in on your own server u may have 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 understand it working, it are going to be a HUGE help and solution for needs. I am attempting to follow the instructions outlined here, and did everything they said to accomplish, 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 end referenced objects. Im getting ORA-29534, plus the result is 45 unresolved object references as soon as the load. The loading from the worked great, no problems in any respect, but had difficulties with SOME in the objects in Here is one example with the errors that I am getting: skipping: com/sun/mail/imap/RightsRight has already been resolved skipping: javax/mail/IllegalWriteException has already been resolved skipping: com/sun/mail/iap/Argument is definitely resolved resolving: com/sun/mail/smtp/SMTPMessage errors: com/sun/mail/smtp/SMTPMessage ORA-29534: referenced object /mail/internet/MimeMessage cannot 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 can't be found resolving: javax/mail/FolderClosedException errors: javax/mail/FolderClosedException ORA-29534: referenced object /mail/Folder couldn't be resolved Any idea whats wrong, and/or how I can make it better?? - ::YEX::-
show me a cut and paste of the loadjava command.
February 26, 2002 - 1:24 pm UTC
Reviewer: Bob Yexley from Dayton, OH USA
Can you reload the activation and mail ZIP files using another user? This will facilitate debugging and we can easily use it to look for the resolver for virtually every problems. Also, add - debug in 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 still have 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'll need ; 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 would need to be done!!!???
try to find ORA-29540 on this web site.
February 26, 2002 - 3:29 pm UTC
Reviewer: Raza from Toronto, Canada
this mustn't be done above the network - - the file that you are loading must be one on the server anyway dont make use of the network.
February 26, 2002 - 4:03 pm UTC
Do we'd like to do exactly the same things for Oracle9i because you described with this detailed instruction? If it really is different, is it possible you explain what's more, it in detail? Thanks lots! Harvey
should work with 9i, havent loaded this myself established but the steps would basically be a similar you dont ought to use sys and in all likelihood wont be capable to use sys normally in 9i, that will be different yes, you should need to make this happen in 9i in case you wanted to deliver email with attachments.
February 26, 2002 - 5:43 pm UTC
Thx for the 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 obtain it work! I have two instances running about the same machine, I have opened the telnet session on box where my database is, and wanting to load by issuing these 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 that is set to rcisdvl in case I issue loadjava without rcisdvl RCISDEV loadjava - user sys/changeoninstall Then I understand this error!! SQL Error while connecting with oci8 driver to default database: ORA-01034: ORAC LE out of stock ORA-27101: shared memory realm won't exist IBM AIX RISC System/6000 Error: 2: No such file or directory could hardly open connection loadjava: 2 errors What really should be done!!! you advice Thanks beforehand 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 you'll be able to then: sqlplus sys/changeoninstall then run loadjava.
Very nice! But do you think get more details about UTLSMTP.
Tom, In your pl/sql solution if bcc list will not be written out with writeData, could you explain the way will work? Thanks, Chris
because this: 15 gmailconn, precipientsi ; does the specific sending, the writing on the CC and TO list is merely for display - - the decision to writedata your doesnt affect WHO the email is actually deliver to - - just what are the email client displays.
I have followed every one of the instructions. And they were successfull also. But when I type these command SQL desc send ERROR: ORA-04043: object send doesn't exist I obtain the above said error. Can anybody assist me to. The mail8i and activation8i, are proper. But if anybody incorporates a working version please send on
Tom I downloaded the javamail package according to your note. However, inside archive there's no file. Any idea how I will get this?. I downloaded the 1.2 version. Also, many thanks explaining how bcc list works. Rgds, Chris
It is separate, goto and appearance for activation.
tom, i run these 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 could you tell me why? i understand this error which ever the email address contact info i give inside recpt please help
contact your email guys/network guys. the server you happen to be attempting to use isn't set up to relay, this really is NOT an oracle issue, not only a UTLSMTP issue, it is usually a SMTP configuration issue. You are probably while using WRONG mailhost since its not setup to relay anti-spamming defense
tom, it is possible to limit for the email body size that you mentioned as part of your answer as 32k only. please reply. thanks
Only as I are coded. I am passing the body as being a single plsql varchar2 variable. If you passed a short-term clob and wrote out 32k at the time, you might make becoming large because you wanted. There is no limit well, 4gig
If i have to transmit multiple emails, like typicallly. i use a cursor that returns emails and other detials. i have to loop and email the opposite details for the email address can it be only the next 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 often a Reminder for any 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 ones above. this becomes: begin for rec in loop ; end loop; end;Thats what procedures are only concerned with.
Can you please indicate what sort of code have to be changed to e-mail a bfile just as one attachment I must e-mail compressed files in a OS filesystem. I am just beginning to learn Java and dont even know where to focus on above mentioned problem although I expect a minimum of the BLOBDataSource nested class to alter or a similar class for bfile to get added. By the way - which resources can you suggest for learning Java, received 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 a bfile in java would be the same as being a blob. You might consider:
it has some nifty utilities inside. It is written for that PLSQL developer that wants for getting their feet wet with many java.
Reviewer: Randy Richardson from St. Louis, MO
Tom, Is there a method to validate the TO list before sending? Or will you have to just send and allow bad emails 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 from your database for the server The address book was in exchange or around the If it is possible to ask MS tips on how to access the address book via java, we could certainly accomplish that. hmm, isnt this how email viruses get going? Im doubtful, all sorts of security difficulties with accessing the address book you need to trust the oracle account and I have no idea if Java can access that or you cannot. We could use C too if they support that - - nevertheless the security issues abound.
Reviewer: srividhya from bangalore, India
Hi Tom, Thanks a lot with the is really great and had installed around the development database oracle 8.1.6 along no difficulties with it. However, when we attemptedto deploy on our live database oracle 8.1.6, were facing a great deal of installation problems. We realized which the java option will never be set if your database was setup1.5yrs database dimensions are do we set the java option?We are not capable to set the java option for an incremental do we accomplish that. Thanks beforehand. Please !!. We are just delayed in with it.
tom, will it be possible to track the failure of an e-mail. think that i am sending email using utlsmtp with TO list has a single person and CC list has 10 persons. now email was delivered to all the CC people but not for the TO list person. in this particular case i need for you an email to a ADMIN saying email compared to that TO body's failed will it be possible?
the email will bounce tot he REPLY-TO address. set the reply-to to admin. I believe there is undoubtedly an error-to or something like that also. you should have to look up
tom, would you give me just how do set the reply-to address to admin. because once i tried from the following way mail will not be sent to your admin. nevertheless the bounced mail come towards 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 can be a Reminder for any 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 incredibly informative. I tried it, doenloaded perfectly but I am facing condition 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 answer to Yexley problem Forward it for me.
drop the usa. through the email address as should are already indicated inside the bounced email, weve stopped while using the country specific domain name
Reviewer: Binh Ta from Maryland, USA
Hi Tom, Very helpful indeed. But I is it possible to answer my follow-up question too please: How can one use PL/SQL to produce Appointments in Exchange? Ive searched practically the entire internet but found nothing. Would really appreciate in case you can shedsome light. Many thanks. Binh Ta.
No idea, I know nothing about exchange. We is capable of doing com automatation calls from plsql on NT with all the com cartridge.
Reviewer: Munz from Reston, USA
Tom: Do I realize that if I want to deliver an email but having a subject line and a note pulled out in the database I can undertake it with PL/SQL. I only should use jAVAMAIL API when I need t osend attachments. THank you,
You never need javamail, you can use utlsmtp 100%. It is only much much easier with javamail to send out attachments, it can do the benefit you. UTLSMTP will do for sending text based email
Reviewer: Munz from Reston, USA
Tom: If you do not want to work with named parameters and never cc or bcc anyone in email how will you pass parameters. I am getting a mistake when I do: begin , null, , ,, This is usually a subject, Hello Tom, this can be a mail you would like ; lineMessage sent on the user successfully; end;Cant you pass null values to a selection. Thanks,
why dont you want to work with named parameters? these are better then positional, you might be not calling this from SQL so that may be not a concern. Otherwise, arrange the parameters to match your needs but wait, thatll break your other code unless it uses named parameters I would recommend you work with a named parameter, it really is more readable, less ambigous and when someone changes your order on you later - - you're PROTECTED. You can make this happen: begin sender email, from, to, , , subj, body ; end;also. You are not passing jail array - you passed a wide range with some blank strings - - completely different. Just pass empty arrays like I have them defaulted to
TCPConnection class will not be loaded in database I want for you mail from, nevertheless the classes are loaded in another db behind the firewall that I can access by using a db link. I have tried building a synonym for UTLSMTP with all the db link. Should this work? I also tried writing a package that accepts exactly the same inputs send mail does, but calls send mail through the db link while using same arguments it absolutely was called with. This doesnt work either. I get wrong number or type arguments. Can you show how you should call UTLSMTP across a db link from the db that doesnt possess the java classes installed?
utlsmtp relies on a record. you happen to be probably not defining your connection record applying 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 along with the error message cut and pasted from sqlplus, its hard to tell
Reviewer: Sven Bleckwedel from Santos, Brazil
Hi TOM, Your explanation was handy to assist me to to implement this resource in the database that we administer. When using another character set inside my case, WE8ISO8859P1 some problems appeared, but i
Welcome on the worlds largest community of developers,
admins, and architects using industry-standard technologies
in conjunction with Oracle products.
Java Magazine : What Will You Build Today?
Phone: 1.800.633.0738 Welcome towards the worlds largest community of developers,
admins, and architects using industry-standard technologies
in in conjunction with Oracle products.
DBAs, Developers, SysAdmins, Architects - Something for all! FREE OTN Virtual Tech Summit Replay Library. Watch now!
Java Magazine : What Will You Build Today?
Free, Interactive Tech Info Delivered to Your Desk
Download : Oracle Database Migration Assistant for Unicode 2.1
Phone: 1.800.633.0738 Oracle 11g Free Download Latest Version setup for Windows. It is full offline installer standalone setup of Oracle 11g for Windows 32 bit 64 bit PC.
Oracle 11g is surely an object relational databasebased management system which s considered because most robust database software from the market. Oracle 11g can also be the leading database management software all above the world. This software product is the standard inside the management of data so you don t should worry about the size from the data it may manage them easily. You can also Download Oracle 10g for Windows.
Before starting details of what Oracle 11g includes new to the users we ought to go through the essential concept of Oracle s data management procedure. Oracle manages databases inside a relational manner. Software committed to database management were while using hierarchical concept where data was kept in tree like structures. Oracle uses relational based model where information is stored logically in table and that is composed of column who have different attributes. Now you wonder where this g comes from inside the title of Oracle s version Oracle 11g. Well Oracle is usually a company containing always come program some creative strategies to marketing. Like within the 90s internet was the greatest entity and everyone was only crazy about having internet. Oracle in those times upgraded their database management version Oracle 8 and named it Oracle 8i and i also here stands fir internet. This would be a very striking move plus it hit the nail within the head. Just in this way now a days grid computation has become the next popular trend for enterprises for architecture management hence the letter g is roofed which means grid. You can also Download Oracle 9i which has been another great release.
In Oracle 11g there are lots of components which can be installed like Oracle Application Express, Oracle Database Vault, Oracle Configuration Manager and Oracle SQL Developer. Then 11g has Automatic Maintenance Task Management which will give you a comprehensive and efficient treating scheduling and allocation of resources like CPU time. Automatic Diagnostic Respiratory is yet another very helpful feature for Oracle 11g. It is often a new management system for storing and organizing larger than fifteen diagnostic data and trace files.
Below are a few noticeable features that you just ll experience after Oracle 11g free download.
Object relational database management system.
Leading database management software all on the world.
Numerous new components ready to become installed.
Automatic Maintenance Task Management for scheduling of resources.
Automatic Diagnostic Respiratory included.
Software Full Name: Oracle 11g v11.2.0.1.0
Before you begin Oracle 11g free download, ensure your PC meets minimum system requirements.
Memory RAM: 1GB of RAM required.
Hard Disk Space: 500MB of free space required.
Click on below button to start out Oracle 11g Free Download. This is complete offline installer and standalone setup for Oracle 11g. This could well be compatible with both 32 bit and 64 bit windows.
Copyright 2013-2015 All Rights Reserved.
Visit each of the Toad communities for usage of resources, best practices as well as the product teams.
Easily automate maintenance, ensure optimal performance and minimize risks from changes
Toad DBA Suite for Oracle is often a comprehensive list of Oracle DBA tools that enable you to you have to be proactive. These Oracle administration tools automate maintenance, ensure optimal performance, and mitigate potential risk of change. The suite allows you to resolve troubles before they impact production. And, since routine effort is automated, you re assured of accuracy as nothing you've seen prior. You can also help reduce the risks connected with performance bottlenecks, database vulnerability, patches, upgrades, and even more.
Assures you of proper maintenance, performance, and change
Enables you to definitely maintain and manage schemas, including compare and sync
Enables someone to administer databases, including users, roles, infrastructure and utilities
Facilitates management, synchronization, and deployment of schema changes
Ensures you find the best-possible application performance by proactively identifying issues straight from running SQL statements, PL/SQL, or any other source code and automating the SQL optimization process
Includes eight hours of free, web-based training to assist you take full advantage of Toad s powerful functionality
1 GHz Processor Spotlight requires 1.2 GHz processor
924 MB RAM required, 1 GB recommended
Windows XP, 2003, Vista, 2008 and Windows 7 32-bit and 64-bit
When running with a 64-bit os, you must utilize the 32-bit Oracle Client.
In Windows Vista, you have to use a logon using the Admin privilege or when you are logged on being a normal user, you need to have the Universal Access Control UAC turned OFF.
Toad for Data Analysts: Does not support Windows 2000, Windows 2003 64-bit, DB2 connections on Windows XP 64-bit only
Oracle 10 Client Net 10 or Instant Client 10.
Oracle 11 Client Net 11 or Instant Client 11.1.0.7.
Oracle versions 8.0.6; 8.1.7; 9i; 9iR2; 10gR1; 10gR2; 11gR1, 11gR2
Quest SQL Optimizer for Oracle: Requires Oracle 8.1.7 or later
Spotlight on Oracle: Requires Oracle 9.2.0.4 or later and SQLNet 2.3.4 or later
VGA monitor, 1024x768 resolution, small fonts Spotlight requires 1280x1024 or larger
Application: Citrix support on Windows Server 2003 Service Pack 2 while using MetaFrame Presentation Server 4.0 and Citrix Presentation Server Clients 10.2
Desktop VDI: Quest vWorkspace 7.0 on Windows
XP Professional 32-bit Service Pack 2 Server: VMWare ESX Server 3.5 and VMWare
Server Console 1.0.10 on Windows Server 2003 Server Pack 2
business needs. Contact Now
Postgres Plus Enterprise Edition may be the best database and tools package from the PostgreSQL ecosystem for enterprise database compatibility, performance, scalability, high availability, monitoring, management, disaster recovery and database development.
Postgres Plus Standard Edition provides technical support and also the fundamental tools needed for almost any mission critical PostgreSQL installation.
You are paying too much on your database. Lower your database TCO now together with the power of open source and database compatibility from EnterpriseDB!
PostgreSQL, the worlds sophisticated open source database.
Postgres Plus Cloud Database PPCD adds scalability, convenience, and affordable to PostgreSQL or Postgres Plus Advanced Server databases.
Designed specifically for that cloud, PPCD features automated scale-rid of its cluster architecture, built-in failover for high availability, private instances for security and consistent performance, and take care of administrative access.
Monthly billing, yearly subscriptions, and hourly pay-as-you-go payment plans can be purchased.
EDB tools focus on large scale mission-critical enterprise deployments providing abdominal muscles best in Postgres database management, monitoring, performance, replication, high availability, backup, scalability, security, and disaster recovery.
Yearly calendar with scheduled workout sessions. EnterpriseDBs On-demand/Self-paced courses may be taken anytime and everywhere allowing that you strengthen your Postgres skills when its most convenient for you personally. There are no travel expenses to improve costs no time away from work with case you might be needed. You can start, stop and commence again without notice. EntepriseDB has certification tracks for both PostgreSQL and Postgres Plus Advanced Server. Holding a certification from EnterpriseDB affirms a database professionals Postgres skills, and employers trust certifications as industry acknowledgement of proficiency and the capability to perform effectively. Our certification program sets the international standard for Postgres professionals, and people certified under this system fill an evergrowing and critical requirement of Postgres knowledge in enterprise environments.
Frequently asked questions regarding our Training. Among our customers, youll see as relevant use cases in particular compelling ROI accounts of success. Learn how companies are creatively sufficient reason for minimal disruption for their business reducing their database TCO. Product documentation for PostgreSQL, Postgres Plus Advanced Server, Installation, JDBCODBCECPG Connectors, xDB Replication Server, Migration Guide, Database Compatibility Developers Guide, Advanced Server features Guide, and Postgres Plus Cloud Database. EnterpriseDB is deeply associated with and committed for the PostgreSQL community with all the common goal of constantly improving and building upon the software along with promoting and facilitating the adoption of PostgreSQL and related products worldwide.
EnterpriseDB is proud to sponsor and work with all the best and brightest from the PostgreSQL and general database communities in particular. It is using expertise and deep information about Postgres we are competent to make significant contributions to your community version of PostgreSQL together with EntepriseDBs Postgres Plus brand of products. In addition, their knowledge can also help fine tune our services offerings including software subscriptions, training, tech support team and consulting services. EnterpriseDB would be the leading worldwide provider of Postgres software and services that enable enterprises to lessen their reliance on costly proprietary solutions and slash their database spend by eighty percent or more. With powerful performance and security enhancements for PostgreSQL, sophisticated management tools for global deployments and database compatibility, EnterpriseDB software supports both mission and non-mission critical enterprise applications. More than 2, 500 enterprises, governments as well as other organizations worldwide use EnterpriseDB software, support, training and professional services to integrate open source software inside their existing data infrastructures.
Based in Bedford, MA, EnterpriseDB is backed by strategic keep on investing.
Meet EnterpriseDBs executive team, consisting of seasoned entrepreneurs and business leaders with diverse backgrounds. Their knowledge and expertise from the technology space allows them to forcefully plan and execute the Companys long-term growth strategy, and keep a keen eye on operational excellence. An experienced team of veteran Postgres and database skilled professionals and business specialists execute the vision and strategy presented by EDB-s executive team. They oversee teams of developers, engineers, consultants, marketing and sales professionals and operations specialists worldwide. They ensure EDB industry is supported with world-class services and products, and company programs are in destination for a deliver on strategic growth initiatives.
Ed Boyajian, President and Chief Executive Officer, EnterpriseDB
Greg Case, Co-founder and Managing Partner, PeakEquity Partners
Paul Winn, Co-founder and Managing Partner, PeakEquity Partners
Eric Jensen, NewSpring Capital
Ric Anderson, Partner, Milestone Partners
Jim Bramante, Independent
EnterpriseDB would be the leading worldwide provider of Postgres software and services that enable enterprises to lower their reliance on costly proprietary solutions and slash their database spend by eighty percent or more. With powerful performance and security enhancements for PostgreSQL, sophisticated management tools for global deployments and database compatibility, EnterpriseDB software supports both mission and non-mission critical enterprise applications. More than 2, 800 enterprises, governments as well as other organizations worldwide use EnterpriseDB software, support, training and professional services to integrate and optimize open source software into their existing data infrastructures.
Based in Bedford, MA, EnterpriseDB is backed by strategic many.
EnterpriseDB is revolutionizing the enterprise database market together with the power of open source software. Terrific opportunities can be purchased to qualified candidates who're bright, industrious, and interested in excellence. As part from the EnterpriseDB team, youll work in the fast-paced and dynamic environment to build up, support, market, and selling our award-winning enterprise-class database products and services. We offer competitive compensation packages that are included with stock options and benefits, and that we enjoy a challenging, collegial work place that spans the modern world.
Enterprise Edition: Download the latest release of Postgres Plus Advanced Server below.
Standard Edition: Download the present release of PostgreSQL below.
Older releases may be accessed throughout the Customer Portal downloads section open to customers only.
Please Note: Cookies needs to be enabled with the download process to work correctly.
The installers for Postgres Plus Advanced Server will initially download and configure the 9.4 or 9.3 release. Please ensure that you update your components towards the latest patchpoint releases using StackBuilder Plus just after installation is complete.
If you happen to be looking for Advanced Server RPMs for PowerLinux, please Contact Sales to request the place and credentials for that EDB YUM repository.
Postgres Plus Advanced Server 9.4
Postgres Plus Advanced Server 9.3
Postgres Plus Advanced Server provides each of the compatibility features and tools liked by Oracle users, enabling customers to operate their Oracle applications virtually unchanged.
Please ensure you change your components to your latest patchpoint releases using StackBuilder soon after installation is complete.
Recommended: Try EnterpriseDB tools by running Stackbuilder after installing PostgreSQL, expanding Trial Products and selecting Components under EnterpriseDB
Recommended: Try EnterpriseDB tools by running Stackbuilder after installing PostgreSQL, expanding Trial Products and selecting Components under EnterpriseDB
Recommended: Try EnterpriseDB tools by running Stackbuilder after installing PostgreSQL, expanding Trial Products and selecting Components under EnterpriseDB
Recommended: Try EnterpriseDB tools by running Stackbuilder after installing PostgreSQL, expanding Trial Products and selecting Components under EnterpriseDB
PostgreSQL is one with the largest and the majority mature open source projects today, having produced a superb database famous for its transaction processing capabilities as well as across a variety of query intensive and mixed load applications.
xDB Replication Server 5.1
xDB Replication Server 5.1
Note : xDB Replication Server is often a single merchandise that allows that you set either multi-master or single-master settings within your configuration.
From the Required Components table be sure you download each with the 3 components: SERVER, AGENT and CLIENT.
Be certain to select the correct computer for each component. For instance, when you want to operate the Client on Windows, the Server on Linux and also your Agents on Solaris, make sure you pick the suitable downloads.
The SQL Profiler is undoubtedly an optional component, that in case used must match the operating environment with the Agent the place you plan to profile queries.
After your downloads are complete, copy each installer to its target machines.
Postgres Enterprise Manager SERVER for PostgreSQL v9.x 1 included in any other case on target system
Postgres Enterprise Manager AGENT for Postgres Plus Advanced server 9.1 and PostgreSQL 9.0
At-a-glance views for I/O, storage, memory, user and session activity, and wait statistics
The SQL Profiler is installed together with Postgres Plus Advanced Server and simply requires configuration.
Postgres Enterprise Manager gives DBAs the visual tools to remotely monitor, manage and tune large-scale PostgreSQL and Postgres Plus database servers.
EDB Failover Manager creates fault tolerant database clusters to lower downtime whenever a master database fails by preserve data online in High Availability configurations.
EDB Backup and Recovery Tool 1.1
EDB Backup and Recovery Tool EDB BART replaces error-prone scripting and manual tasks with simple commands as well as a system-wide catalog. Remote and local Postgres database backup and disaster recovery becomes simple and easy trouble-free.
PostGIS 2.1.0 for Advanced Server 9.3
PostGIS 2.0.1 for Advanced Server 9.2
PostGIS 1.5.3 for PostgreSQL 9.1
PostGIS 1.5.3 for Advanced Server 9.1
PostGIS 1.5.2 for Advanced Server 9.0
Lorem Ipsum is dummy text on the printing and typesetting Lorem Ipsum is actually dummy text in the printing and Ipsum is actually dummy text in the printing and typesettingLorem Ipsum is merely dummy text in the printing and typesetting.
2015 EnterpriseDB Corporation. All rights reserved.
HTTP/1.0 302 Found Location: /pls/asktom/f?p100:11:0::::P11QUESTIONID:728625409049 Server: BigIP Connection: close Content-Length: 0
Thanks with the question, Muthuraj.
Asked: September 10, 2000 - 2:30 am UTC
Answered by: Tom Kyte Last updated: February 11, 2013 - 9:48 am UTC
Hello Sir, My dilemma is thru stored procedure or pl/sql annonyms block how you can write the records into Excell sheet in Oracle collique does thru We desire to achieve thru oracle ex, I am getting all employee records from employee table and as an alternative to spooling right into a text file I wish to write into excell sheet. You were asked the running environment Windows NT Workstation Oracle 8i Server Located in same workstation There have no cartridges and app server or web server even not from forms. Our Achievement has for being done thru pl/sql procedure. For extreem case in any other case possible only in database explain the way to do in Oracle Web server 3. Thank You
We is capable of doing this using a straight plsql procedure. There are more the other format we could use to post an excel file - - from CSV to SYLK. I will demonstrate the SYLK format as I curently have the code plus it offers the power to do much fancier stuff like fonts, headings, formulas etc. We uses UTLFILE start to see the supplied packages guide for setup information about that package. You need an parameter set due to this to work correctly. UTLFILE allows us to post a file within the server and also since your workstation server, this would work nicely for you personally. Here may be the code with the example. It should ensure you get going: Rem Rem Id Rem Rem Copyright c 1991, 1996, 1997 by Oracle Corporation Rem NAME Rem - Dump to Spreadsheet with formatting Rem DESCRIPTION Rem This package offers API to create a file inside the Rem SYLK formatting. This permit formatting in the Rem spreadsheet with merely a ascii text file. This version Rem of owasylk is specific to Oracle8. Rem NOTES Rem Rem MODIFIED MM/DD/YY Rem clbeck 04/08/98 - Created. Rem tkyte 09/10/00 - Made it use UTLFILE. Rem Rem create or replace package owasylk as - - type owaSylkArray is table of varchar22000; - - procedure show pfile in type, pquery in varchar2, pparmnames in owaSylkArray default owaSylkArray, pparmvalues in owaSylkArray default owaSylkArray, psumcolumn in owaSylkArray default owaSylkArray, pmaxrows in number default 10000, pshownullas in varchar2 default null, pshowgrid in varchar2 default YES, pshowcolheaders in varchar2 default YES, pfontname in varchar2 default Courier New, pwidths in owaSylkArray default owaSylkArray, ptitles in owaSylkArray default owaSylkArray, pstriphtml in varchar2 default YES ; - - procedure show pfile in type, pcursor in integer, psumcolumn in owaSylkArray default owaSylkArray, pmaxrows in number default 10000, pshownullas in varchar2 default null, pshowgrid in varchar2 default YES, pshowcolheaders in varchar2 default YES, pfontname in varchar2 default Courier New, pwidths in owaSylkArray default owaSylkArray, ptitles in owaSylkArray default owaSylkArray, pstriphtml in varchar2 default YES ; - - end owasylk;show error create or replace package body owasylk as - - gcvalue varchar232767; gdesct tab; type vcarr is table of varchar22000 index by binaryinteger; glengths vcarr; gsums vcarr; - - - - gfile type; procedure p pstr in varchar2 is begin line gfile, pstr ; end; function buildcursor q in varchar2, n in owaSylkArray, v in owaSylkArray return integer is c integer: cursor; i number: 1; begin c, q, ; loop variable c, ni, vi ; i: i 1; end loop; return c; end buildcursor; - - - - function strhtml line in varchar2 return varchar2 is x varchar232767: null; inhtml boolean: FALSE; s varchar21; begin if line is null then return line; end if; for i in 1. length line loop s: substr line, i, 1 ; if inhtml then if s then inhtml: FALSE; end if; else if s then inhtml: TRUE; end if; end if; or else inhtml and s! then x: x s; end if; end loop; return x; end strhtml; - - function ite b boolean, t varchar2, f varchar2 return varchar2 is begin if b then return t; else return f; end if; end ite; - - procedure printcomment pcomment varchar2 is begin return; p ; chr10 ; pcomment chr10 ; ; end printcomment; - - procedure printheading font in varchar2, grid in varchar2, colheading in varchar2, titles in owaSylkArray is ltitle varchar22000; begin p ID; ORACLE ; printcomment Fonts ; p P;F font ;M200 ; p P;F font ;M200;SB ; p P;F font ;M200;SUB ; - - printcomment Global Formatting ; p F;C1;FG0R;SM1 ite uppergridYES,, ;G ite uppercolheadingYES,, ;H ; for i in 1. loop p F;C tochari1 ;FG0R;SM0 ; end loop; - - printcomment Title Row ; p F;R1;FG0C;SM2 ; for i in 1. loop glengthsi: namelen; gsumsi: 0; begin ltitle: titlesi; exception when others then ltitle: name; end; plainly 1 then p C;Y1;X2;K ltitle ; else p C;X tochari1 ;K ltitle ; end if; end loop; end printheading; - - function printrows c in integer, maxrows in number, sumcolumns in owaSylkArray, shownullas in varchar2, striphtml in varchar2 return number is rowcnt number: 0; line varchar232767: null; n number; begin loop exit when rowcnt maxrows or rows c 0 ; rowcnt: rowcnt 1; printcomment Row rowcnt ; - - p C;Y tocharrowcnt2 ; for i in 1. loop value c, i, gcvalue ; gcvalue: translate gcvalue, chr10chr9;, ; gcvalue: ite upper striphtml YES, strhtml gcvalue, gcvalue ; glengthsi: greatest nvllengthgcvalue, nvllengthshownullas, 0, glengthsi ; line: C;X tochari1; line: line ;K; begin n: tonumber gcvalue ; if upper sumcolumnsi Y then gsumsi: gsumsi nvln, 0; end if; exception when others then n: null; end; line: line ite n is null, ite gcvalue is null, shownullas, gcvalue, n ; p line ; end loop; - - end loop; return rowcnt; end printrows; - - procedure printsums sumcolumns in owaSylkArray, rowcnt in number is begin if 0 then return; end if; - - printcomment Totals Row ; p C;Y tocharrowcnt 4 ; p C;X1;KTotals: ; - - for i in 1. loop begin if uppersumcolumnsi Y then p C;X tochari1 ;ESUMR3C:R tocharrowcnt2 C ; end if; end; end loop; end printsums; - - procedure printwidths widths owaSylkArray is begin printcomment Format Column Widths ; p F;W1 1 7 ; for i in 1. loop begin p F;W tochari1 tochari1 tochartonumberwidthsi ; exception when others then p F;W tochari1 tochari1 greatest glengthsi, length gsumsi ; end; end loop; p E ; end printwidths; - - procedure show pfile in type, pcursor in integer, psumcolumn in owaSylkArray default owaSylkArray, pmaxrows in number default 10000, pshownullas in varchar2 default null, pshowgrid in varchar2 default YES, pshowcolheaders in varchar2 default YES, pfontname in varchar2 default Courier New, pwidths in owaSylkArray default owaSylkArray, ptitles in owaSylkArray default owaSylkArray, pstriphtml in varchar2 default YES is - - lrowcnt number; lcolcnt number; lstatus number; begin gfile: pfile; columns pcursor, lcolcnt, gdesct ; - - for i in 1. loop column pcursor, i, gcvalue, 32765; end loop; - - printheading pfontname, pshowgrid, pshowcolheaders, ptitles ; lstatus: pcursor ; lrowcnt: printrows pcursor, pmaxrows, psumcolumn, pshownullas, pstriphtml ; printsums psumcolumn, lrowcnt ; printwidths pwidths ; end show; - - procedure show pfile in type, pquery in varchar2, pparmnames in owaSylkArray default owaSylkArray, pparmvalues in owaSylkArray default owaSylkArray, psumcolumn in owaSylkArray default owaSylkArray, pmaxrows in number default 10000, pshownullas in varchar2 default null, pshowgrid in varchar2 default YES, pshowcolheaders in varchar2 default YES, pfontname in varchar2 default Courier New, pwidths in owaSylkArray default owaSylkArray, ptitles in owaSylkArray default owaSylkArray, pstriphtml in varchar2 default YES is begin show pfile pfile, pcursor buildcursor pquery, pparmnames, pparmvalues, psumcolumn psumcolumn, pmaxrows pmaxrows, pshownullas pshownullas, pshowgrid pshowgrid, pshowcolheaders pshowcolheaders, pfontname pfontname, pwidths pwidths, ptitles ptitles, pstriphtml pstriphtml ; end show; - - end owasylk;show error
December 12, 2001 - 6:20 am UTC
My real question is that I have 3 tables, 2 that contain many records. I need to write a codeplsql which will select all matching records and insert them into your 3rd real dilemma is that table Anot table C must contain records in Excel or Access file format which can be saved using a diskette. Thus in addition to selecting records from table B to become inserted into table C, I should also discover a way of reading or selecting matching recordsin table A saved for the floppy diskette. Can this be achievable in oracle7.3.2? Can you provide me a simple code to achieve that? NB. Table Astudnumber, subject, level Table Bstudnumber, dept, year Table Cstudnumber, subject, level, dept, year. Thanks a good deal. Please bear with me at night.
not learning the difference here. I showed you the best way to populate table C through an insert into select. I showed you the best way to dump any query to data using UTLFILE, whether it's against table A, table C, table foobar.
January 23, 2002 - 3:18 pm UTC
hi Tom. I wish to give visiting excel report inside spreadsheet? e.g REPORT FOR SALES DEPT. then data Can you aid me?
the SYLK file format is surely an open, documented standard. You can seek out references for it, they're able to show you the best way to format a sylk file for the reason that fashion. then you certainly just need to modify the
January 24, 2002 - 12:47 am UTC
This can be quite useful for use Is this method can also be applicable for Lotus 1-2-3 spreadsheets. Secondly will we import data from Lotus 1-2-3 sheets into oracle
if lotus can understand sylk files, ' think it could, at the very least the last time I first viewed it about 8 years back it could, didnt even know it turned out still around. You would EXPORT the info from lotus in to a delimited file and make use of sqlldr to load it.
January 24, 2002 - 9:12 am UTC
Tom i search internet site for SYLK utility. and that i found sylk FILE FORMAT. I change you program for report heading. ThnakS a great deal YOU ARE THE BEST
January 24, 2002 - 10:04 am UTC
Hi: I am using Oralce 8.1.7 on SUN. I have Fired the code give by U. After Creating Package, When i try to carry out the code where we employed to call package its giving me foll error. ERROR at line 1: ORA-06510: PL/SQL : unhandled user-defined exception ORA-06512: at FILE, line 101 ORA-06512: at FILE, line 192 ORA-06512: at line 4 Please let me out.
you are yet to setup the mandatory parameters for utl-file. Please refer towards the supplied packages guide, you may need to setup the utlfiledir parameter.
January 24, 2002 - 11:31 am UTC
You understand error if: 1 You never have specified utlfiledir init parameter as Tom warned 2 You have specified it, but like a specific directory c:temp Solution to 2 is: - Specify it as being utlfiledir - Or alter the directory to c:temp from c:temp in Toms code I tested on Windows.
January 25, 2002 - 5:06 am UTC
Reviewer: Reddy from Bangalore, India
Hi tom I copied and pasted in your database the package and package body continues to be created. After When I run the follwing code FILE line 98 error is comming. I am comming on the server using Administrator login. User ReddyOracle815 SELECT STATUS, OBJECTNAME FROM USEROBJECTS WHERE 2 OBJECTNAME LIKE OWASYLK% AND 3 OBJECTTYPE IN PACKAGE, PACKAGE BODYSTATUS OBJECTNAME - ----------------- VALID OWASYLK VALID OWASYLK ReddyOracle815 declare output type; begin output: E:ORADEV, , w, 32000 ; pfile output, pquery select empno id, ename employee, sal Salary, comm commission in which job :JOB and sal :SAL, pparmnames JOB, SAL, pparmvalues MANAGER, 2000, psumcolumn N, N, Y, Y, pshowgrid NO ; output ; end;declare ERROR at line 1: ORA-06510: PL/SQL : unhandled user-defined exception ORA-06512: at FILE, line 98 ORA-06512: at FILE, line 192 ORA-06512: at line 4 What could are the reason. Pls clarify by incorporating small example how this error is comming and ways in which to eliminate.
January 27, 2002 - 6:08 am UTC
What will be the suggestions in order to get this implementing 7.3.4.4 rather then 8i?
Youll must recode it to not make use of the new collection type syntax provided with 8.0 or more - - use plsql index by table types instead. So, thats the only real changes youll should make. Its not as effortless to initialize plsql index by tables since it is collection types - - so, rather than the caller coding: declare output type; begin output: c:temp, , w, 32000 ; pparmnames JOB, pshowgrid NO ; output ; end; theyll must code as soon as you rewrite the owasylk package declare output type; lparmnames ; begin output: c:temp, , w, 32000 ; lparmnames1: JOB; lparmnames2: SAL; pparmnames pshowgrid NO ; output ; end;
January 28, 2002 - 5:14 am UTC
Reviewer: Tony Reed from Oslo, Norway
At present I create reports as txt files which might be opened as delimited in Excel. This is far better! No requirement of formatting etc. Thanks.
Tom, One question. I recognize that Owasylk passes within a table of bind name and also a table of bind values as varchar2 data, although columns being bound could be numbersdates. Am I misunderstanding how value works, or will this not cause each variable to get bound like a string, thus and thus indexes will likely be ignored on predicates the spot that the datatype is just not a string.
you should use tonumber and todate where appropriate, eg: choose from emp where empno tonumber:x choose between emp where hiredate todate:x choose between emp where ename :x would really be appropriate as an example.
September 12, 2002 - 11:31 am UTC
Hi, Tom, I need to write the file into client machine by TEXTIO. You told us we could change from UTLFILE to TEXTIO? And I make the package owasylk within the program unit within form, and as well change the many UTLFILE to TEXTIO type. But when I compiled it, there is undoubtedly an error:, native - - error. How can I solve that? Thanks
Followup September 12, 2002 - 3:44 pm UTC
in forms, you almost certainly want to make use of EXECSQL, you should use DBMSSQL but you are unable to in some releases of forms reference a packaged variable like this. Just utilize number - - CONSTANTS - - v6 constant integer: 0; native constant integer: 1; v7 constant integer: 2; - -
September 12, 2002 - 12:12 pm UTC
Hi, Tom I solved that problem around the client side. So, please ignore that question. Thanks and use a nice day.
September 12, 2002 - 12:26 pm UTC
Hi, Tom, Thanks available for you excellent works. My real question is: we ended up data write in the excel file, but this is really in general cell format. What i want to accomplish is generate the information in TEXT format. In you printrow codes, will we implement that? Could you shed an easy on this lease? Thanks,
Followup September 12, 2002 - 4:11 pm UTC
Youll need to dig up the SYLK file format for the web - - google it, it is usually a documented standard.
September 15, 2002 - 11:43 pm UTC
Hi, Tom, Appreciate for your solution. There are basically 3 varieties of data inside the database: VARCHAR2, NUMBER and Date. I desire to dynamically write them into excel datasheet. So I must dynamically receive the data kind of each column and wirte them to the columnrow depending on the derived data type. Could you shed some light for this topic please? Also with the Number, just how can I know it truly is just NUMBER or NUMBER6, 2? Are there any difference to procedure that? At last, we want to make use of JAVA implement exactly the same function write data into excel, for instance, SQLJ or JDBC. Could you compare your solution and JAVA solution from the aspect of performance and of use along with security please? Thanks a million
Followup September 16, 2002 - 7:17 am UTC
No you dont. A number might be fetched to a varchar2. A date may be fetched to a varchar2. When you print - - they will all be converted into varchar2 regardless. Hence, I just fetch them into varchar2. This utility handles dates/numbers as they are. A comparision of the to java o it is written, the java one is just not o this really is easy to code, performant, and may never throw a Null pointer exception o you would like to setup utlfiledir to create this plsql version operate in releases before 9iR2, in 9iR2 you need to use a directory object instead. You should use permission to generate this be employed in java - - more or less the identical. Java is not going to make this any benefit, count on me on that certain. Why does one care what language something is coded in.
September 16, 2002 - 10:57 am UTC
Hi, Tom, Please bear beside me, Im kinda insist my. Because what I want to complete is pre-format each column when write them for the excelsheet. The person who receives the excelsheet probably doesnt are aware of the datatype for every columnbeacuse it's always GENERAL format when he/she opens the columncell format property. So from this pre-format, each column will be from the right format in the event the guy open the columncell format and know just what the original format in the data. That means we're not only write data into excel but in addition prepare the fileformat column for that person with no/little DB knowledge. We are alert to we can do that by using PRO/C or JAVA because we could write them into excel by correct format- %s, %i, %d plus it is inside right format and ready to get checked by opening the format property inside excelsheet. I remember there are a couple of lines of codes which handle finding datatypes with the outcome from the query dynamically, but I are unable to find it, would you supply that pointer please? Thanks a million
Followup September 16, 2002 - 7:33 pm UTC
you can replace the code, which uses dbmssql, to call the describe routine in dbmssql to uncover the datatype, scale and precision and do whatever formatting output you prefer.
September 17, 2002 - 5:04 pm UTC
Hi, Tom, I already solved the problemdatatype on my own. One more problem; How can I obtain the DATE value including time? I make an effort to extract the date valueincluding some time to manipulate it together with the other times and write the effect to excel. I are not able to use gcvalue given it dont allow me to one example is: SELECT todategcvalue, DD-MON-YYYY HH24:MI:SS - todateotherdate, DD-MON-YYYY HH24:MI:SS FROM DUAL; It can only do thiswith YY, however it doesnt give time portion: SELECT todategcvalue, DD-MOC-YY HH24:MI:SS - todateotherdate, DD-MON-YY HH24:MI:SS I experimented with define another variablefor date: gdvalue DATE and rehearse: value c, i, gdvalue if it can be a date type, nonetheless it gives me errornot match col type. Again, I tried to try and do: column pcursor, i, gdvalue, 32765 brfore getting date value nevertheless it give me another error. So, Im used up gas, and might you let me know how can I get exectly date valueincluding some time and write them into excel by correct formatI can accomplish this part? Thanks a million
seems like you are trying to discover the difference between two dates right? if you do, see:
September 17, 2002 - 11:41 pm UTC
Nop, SIr, The issue that I can not find the time portion out in the variable gcvalueonly date portion with two digits of the year show up regardless of format I specify, e.g even I use DD-MON-YYYY HH24:MI:SS to explicitly covert it and display towards the output. I can handle the diff between two dates. Is VARCHAR2 variable including gcvalue competent at getting the worth from binded column including the type is date? Thanks,
Followup September 18, 2002 - 7:22 am UTC
in the event you select tochar gcvalue, dd-mon-yyyy hh24:mi:ss you may most certainly have that string with TIME within it. You would really really must provide a complete YET very concise example of your respective issue in order to me to comment, because I just dont realize what your problem may be. CONCISE as an important word because sentence.
September 18, 2002 - 9:44 am UTC
Hi, TOm, Thanks for ones time. Ok, this is actually the problem: if I call the package by passing a query that features a column having to start dating ? type, then this time portion will not be show up and simply comes with two digits year format even I specified explicitly the date format which i want. For example: lcursor, decide upon emp, ; pfile output, pcursor lcursor, pshowgrid YES ; inside above format, there is really a hiredate date type field which cannot be correctly displayed in excel after generating the file. But if I do: lcursor, select empno, ename, tocharcredate, DD-MM-YYYY HH24:MI:SS hd from emp, ; it could possibly displayed the date correctly in excel nevertheless it is from the format of TEXT as it belongs to VARCHAR2. So, my concern is: I still are not able to extract enough time portion for the date type column, might you test the code by simply passing query decide upon emp, write hiredate to be a time formatcoltype12, and lastly you will obtain the hiredate column isn't correctly displayed should you open the excel sheet. Can the variable gcvalue be familiar with get the significance of date formati mean including TIME portion and become any format only specified the date format explicitly if we understand the type with the column is 12coltype12? Thanks
Followup September 18, 2002 - 3:03 pm UTC
pass it a query that formats the date while you like eg: dont use, use empno, ename, tocharhiredate, dd-mon-yyyy hh24:mi:ss OR issue: execute immediate alter session set nlsdateformat dd-mon-yyyy hh24:mi:ss ; before calling owasylk to customize the default date format. If you want to try this in owasylk you will need to a check for your datatype are you might be b ensure you bind to start dating datatype - - not only a character string c format it however you prefer after getting it Ill not of any more use with this one create a you find the time component you said so - - when I use b it can be how you will need to output it for excel that you'll need to discover c I don't realize squat about excel ;
September 18, 2002 - 10:21 am UTC
Hi Tom, How can I create multiple worksheets in Excel file? Seems sylk only supports single sheet. Thanks, John
Followup September 18, 2002 - 3:11 pm UTC
sylk is sylk. worksheets are MSs thing. At this point, you'd have to ask MS - - what would be the standard flat formatting I can write that lets me use all of one's features.
September 18, 2002 - 1:03 pm UTC
Hi, We can create separate worksheet by gaining knowledge through sample files. All we lack will be the current standard format file of SYLK held by MICROSOFT, the lastest one I can find is for EXCEL 2.0 in published in 1985-1986. But as I said we CAN create separate worksheet. Thanks
September 18, 2002 - 3:21 pm UTC
Hi, TOm, I taught me to be of not showing time portion by gcvalue, because it truly is VARHCAR2 typegcvalue-maybe it can be a bug. So I bind the date format by declare another date variable gdvalue to be able to match them when i have the column value. Thanks anyway to your useful codes. BTW, we can easily do most with the thing to excel format by by using this code, but you need to to replace the code in your own needs.
September 18, 2002 - 5:05 pm UTC
Hi, Tom, What could be the largest quantity of records we are able to write into excel by making use of this code? I have aimed to write about 450, 000 recordsfrom a table, pure query into excelSYLK by this technique, but I only got 10001 reocrds showup after I open the SYLK file under MS excel. Please clarify. THanks
Followup September 18, 2002 - 6:55 pm UTC
the real question is HOW MANY RECORDS can excel handle and that may be 64k 450k - - absolutely no way, they dont do this
October 04, 2002 - 3:26 pm UTC
Hi, Tom, Appreciate you code. Ine thing I need to ask is how the speed of code execution is quite slow in the event the table is reasonably largeboth of columns and also records are incredibly large, e.g: million records and 100 columns. it will take at the least 520 minutes to complete the job. Would mind tell us tips on how to increase the speed please? Thanks
You could recode to make use of array You could work with a faster method including C hunt for arrayflat on this website, code already exists