Oracle Pro Tips, Trends & Technology eXTRA Pinnacle Publishing http://www.pinnaclepublishing.com Issue 3.18 November 12, 2002 TABLE OF CONTENTS 1) Pop Quiz (answer at the end) 2) Oracle News 3) IT News 4) Book Bag: Oracle PL/SQL Programming 3rd Edition 5) Oracle OpenWorld 2003 6) Answer to the Pop Quiz -------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you write the content of a BLOB column to a file? --------------------------------------------------------- 2) ORACLE NEWS --------------------------------------------------------- Oracle is providing a series of free Web-based seminars on how to include Oracle9i database and Oracle9i Application Server (Oracle9iAS) in your application. These seminars are intended for existing Oracle partners, and you may need to provide a partner ID when you register. The following are some of the topics covered in the seminars: * How to Increase Sales By Embedding Oracle9i Technology * How To Embed The Oracle9i Database * Benefits of Embedded Oracle9i Application Server To sign up for the online seminar, go to: http://www.oracle.com/start/9iaspartnerform/introeblast.html?src=1060444&Act=53 --------------------------------------------------------- 3) IT NEWS --------------------------------------------------------- While companies are slashing IT spending and announcing layoffs in North America, the technology boom is India is continuing its brisk pace. Companies such as Sun Microsystems, Oracle, and others are expanding their research and development centers in India. In fact, some of the products developed at these centers are already in production, such as Oracle Student System. Find out more at: http://yahoo.businessweek.com/magazine/content/02_45/b3807151.htm --------------------------------------------------------- 4) BOOK BAG: Oracle PL/SQL Programming 3rd Edition --------------------------------------------------------- Steven Feuerstein has set up an opportunity for you to buy a signed copy of his newly released 3rd edition of Oracle PL/SQL Programming and at the same time benefit the work of the Crossroads Fund, a public foundation that provides grants to community organizations working on issues of social and economic justice in the Chicago metropolitan area. If this is of interest to you, please visit http://www.crossroadsfund.org and click on http://store.yahoo.com/crossroadsfund. You won't get the lowest price around, but you will get an autograph and you'll do a good deed. Not a bad deal, all around. --------------------------------------------------------- 5) ORACLE OPENWORLD 2003 --------------------------------------------------------- Oracle OpenWorld will be held in San Francisco, California November 10-14, 2002. For those fortunate few (who had the money to cover the admission fees), you'll be able to watch Larry Ellison -- live! For the rest of us, don't despair. You can still participate remotely, via the Web: http://www.oracle.com/oracleworld/online/sanfrancisco/ --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- In our last issue, we raised the question of how to write a BLOB column to a file. We had provided an overview of the solution. Here's the detailed version. I want to thank my colleague at Procase, Lev Moltyaner (mailto:lmoltyaner@procaseconsulting.com) for providing the above example. Also, Tomas Albinsson (mailto:tomas.albinsson@bliva.com) from Sweden had developed a similar routine using Java. Please contact him directly if you're interested in finding out more. Note that this information is also on Oracle MetLink. The document reference is (DocID=150104.1). The UTL_LOB package can be used to write out BLOBs (note that CLOBs can be written using the UTL_FILE package). Configuration --------------------------------------------------------- 1. Configure the listener.ora and tnsnames.ora files for external procedures and start the external procedure listener. a. Edit listener.ora and add in the top address list: (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCmavto)) At the bottom add this definition for the path where the library will reside: (SID_DESC = (ENVS=LD_LIBRARY_PATH=/u1/app/oracle/product/817/lib:/u1/app/oracle/product/817/ctx/lib:/u1/ app/oracle/local) (SID_NAME = PLSExtProc1) (ORACLE_HOME = /u1/app/oracle/product/817) (PROGRAM = extproc) ) b. Edit tnsnames.ora Add definition with KEY matching the KEY in listener: EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = ipc)(KEY = EXTPROCmavto)) ) (CONNECT_DATA = (SID = PLSExtProc1) (PRESENTATION = RO) ) ) c. Restart the listener and test the listener: lsnrctl stop lsnrctl start tnsping extproc_connection_data 2. Compile the external procedure shared library as follows: $ORACLE_HOME/rdbms/demo should have the .mk file and lob2file.c files and the make command will generate the .sl file. make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_with_context \ SHARED_LIBNAME=lob2file.sl OBJS=lob2file81.o This should compile and link the lob2file.sl shared library in your current working directory. Move this library to the location defined in the listener: /u1/app/oracle/local 3. Create the LIBRARY database object and the package. connect user_name/password start lob2file.sql The script will prompt for the path for the library. Specify the path and the library name from step 2 (no file extension): /u1/app/oracle/local/lob2file 4. Edit this sample test script to test: declare a_blob BLOB; status NUMBER; begin select school_logo into a_blob from school where rownum <= 1; -- specify full path for output files utl_lob.SetLogging(TRUE, '/tmp/x.log'); utl_lob.UnloadToFile(a_blob, '/tmp/x.jpg', status); dbms_output.put_line('Exit status = ' || status); end; UTL_LOB Documentation -------------------------------------------------------- create or replace package utl_lob is procedure SetLogging(which BOOLEAN, a_log VARCHAR2); procedure UnloadToFile(a_lob BLOB, a_file VARCHAR2, status OUT NUMBER); end utl_lob; Procedure SetLogging: Used to enable/disable logging of progress/error messages to a log file. Enable logging if you're having problems to obtain detailed information about where and why the UnloadToFile procedure might be failing. Procedure UnloadToFile: Used to unload the contents of a BLOB from the database and write the contents to a binary OS file in the specified location. --------------------------------------------------------- Well, that's it for this issue. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2002 http://www.pinnaclepublishing.com All rights reserved.