Appendix B: Getting Real Work Done with Oracle Appendix B: Getting Real Work Done with Oracle by Philip Greenspun, part of SQL for Web Nerds Consider John Q. Nerd with his bona fide SQL expertise and MIT PhD. How much useful work can John get out of Oracle? None. John Q. Nerd only knows how to drive Oracle from SQL*Plus. This appendix covers the little details such as getting data into or out of Oracle. It is organized as an FAQ. How do I get data into Oracle? Answer 1: Start up SQL*Plus. Type really fast. Answer 2: If you're an AOLserver Achiever, format your data into a comma-separated values (CSV) file. Park the file underneath the Web server's page root. Request the file from a Web browser and watch as a beautiful user interface pops up inviting you to have AOLserver import the data into the RDBMS table of your choice. Answer 3: If you need to load 1000+ rows per second, a fairly common requirement in the data warehousing world, read the Oracle8 Server Utilities, SQL*Loader section. Answer 4: If SQL*Loader makes you feel like ripping out what's left of your hair, go with Perl DBD/DBI (a module available from http://www.cpan.org; explanation available in Advanced Perl Programming (Srinivasan 1997; O'Reilly). Answer 5: If your data are imprisoned in another RDBMS, consider using a fancy GUI tool such as Data Junction (http://www.datajunction.com). These are PC-based applications that open simultaneous connections to your Oracle database and some other RDBMS. They can generally show you what's available in the other database and drag whatever you think necessary into Oracle. Answer 6: If your data are in another Oracle database, read up in the Oracle8 Server Utilities manual about exp and imp. Answer 7: If your data are coming in from an email message, program your mailer to fork a Perl DBD/DBI script that will then parse out the content from the headers and other such crud, open up Oracle, and then send an insert statement. Due to the fork and the opening of Oracle, this is 1/100th the efficiency of an AOLserver Tcl script. However, you probably won't be getting 40 email messages per second so it doesn't really matter. Perl is also better than Tcl for this application because Perl has more powerful regular expression operators and a better library of freeware modules. Tcl 8.1 redresses these issues to some extent by incorporating Perl-style regexp. How do I get data out of Oracle? If you want to publish data on the Web, look at the approaches articulated in Philip and Alex's Guide to Web Publishing, Chapter 13 (http://photo.net/wtr/thebook/databases-interfacing.html). If you are sending data to another Oracle installation, use exp, documented in the Oracle8 Server Utilities manual, exp and imp section. [e-mail]'s Comments Note regarding answer 2, CSV support in AOL server was dropped starting with Version 3. This alternative no longer works. -- Chuck Ehrlich, February 14, 2000 Answer 1b: Load your dataset into Emacs and format it into INSERT statements, and pull that into your SQL shell (SQL*Plus). Re: Answer 5: This may seem like a trivial thing to do using SELECT statements or things like Access. Until you get to the dates and times. At that point it can start becoming a pain. Another source of annoyance is imprecise representations, e.g., 1.66666666666666 coming out as 1.666667, stuff like that. NULLs can also be problematic. -- Thomas Hundt, May 30, 2000 Add a comment | Add a link