TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Oracle Professional 3) HTML PL/SQL Tip 4) IOUG Collaborate06 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is CTXSYS.DEFAULT_STOPLIST used for? --------------------------------------------------------- 2) Oracle Professional --------------------------------------------------------- In the January issue of Oracle Professional, Steven Feuerstein begins a discussion on Conditional Compilation along with other new features added to the Oracle 10g PL/SQL compiler. Brijesh Patel and Peter Steinheuser introduce us to Oracle Internet Directory and how to use this feature along with a custom shell script and PL/SQL package to maintain your various database services. And, Roy Gomes explains how we can use Oracle Text technology to perform powerful and high quality text searches on documents using SQL. Here’s how you can access Oracle Professional: http://www.oracleprofessionalnewsletter.com --------------------------------------------------------- 3) HTML PL/SQL Tip --------------------------------------------------------- This piece of code was sent to me from a colleague, Thomas Al-Hamad, who sourced it from a Tom Kyte Blog (http://tkyte.blogspot.com/). It’s quite useful for those of you who are using PL/SQL to generate HTML code. Here’s how it works. I’ll create a function in the database, then call the function from an anonymous pl/sql block and see what the corresponding HTML code is returned from the function. SQL>CREATE OR REPLACE FUNCTION funcRfCurHTML(rf SYS_REFCURSOR) RETURN CLOB IS lRetVal CLOB; lHTMLOutput XMLType; lXSL CLOB; lXMLData XMLType; lContext DBMS_XMLGEN.CTXHANDLE; BEGIN -- get a handle on the ref cursor -- lContext := DBMS_XMLGEN.NEWCONTEXT(rf); -- setNullHandling to 1 (or 2) to allow null columns to be displayed -- DBMS_XMLGEN.setNullHandling(lContext,1); -- create XML from ref cursor -- lXMLData := DBMS_XMLGEN.GETXMLTYPE(lContext,DBMS_XMLGEN.NONE); -- this is a generic XSL for Oracle's default XML row and rowset tags -- " " is a non-breaking space -- lXSL := lXSL || q'[]'; lXSL := lXSL || q'[]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[

]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[ ]'; lXSL := lXSL || q'[]'; -- XSL transformation to convert XML to HTML -- lHTMLOutput := lXMLData.transform(XMLType(lXSL)); -- convert XMLType to Clob -- lRetVal := lHTMLOutput.getClobVal(); -- close the context -- dbms_xmlgen.closecontext(lContext); RETURN lRetVal; END funcRfCurHTML; / Function created. Note, if you are not running Oracle 10g, then you’d need to remove the references to the quote character (q’) that was introduced in Oracle 10g. And, for you Oracle 9i users, this function will only compile in version 9.2.0.4 or higher. Now, here’s a small block to open a Ref Cursor for a sample query and call the function just created: SQL> declare l_cursor sys_refcursor; begin open l_cursor for select * from bb_temp where rownum < 3; :x := funcRfCurHTML ( l_cursor ); close l_cursor; end; / Now, we will display the contents of the variable, which we expect to contain our HTML code for the query. SQL> print x; X -----------------------------------------------

FUND_ID	NAME	START_DATE	SUBTYPE	PERCENT
17956	Lotus Canadian Equity	20000630	Communication and Media	11.4
17956	Lotus Canadian Equity	20000630	Gold and Precious Metals	6

And that’s it. Now, to make this reusable, I’d create a procedure that contained the call to the function instead of using an anonymous block, but you get the picture. Let me know if you have any comments on this topic. -------------------------------------------------------- 4) IOUG Collaborate06 -------------------------------------------------------- The Independent Oracle Users Group (IOUG), the Oracle Applications Users Group (OAUG) and Quest International Users Group (Quest) are joining to host COLLABORATE 06, a Technology and Applications Forum for the Oracle Community. The event will be held from April 23rd to April 27th, 2006 at the Gaylord Opryland Resort in Nashville, Tennessee. Collaborate06 offers Oracle professionals the best practices and user- driven training that each user group offers, as well as access to the greater Oracle community. You will have access to a wide range of sessions focusing on database administration, architecture and infrastructure, data warehousing and business intelligence, application server and web services, development, security, performance tuning, etc. If you register before March 21, you’ll save $200 of the full rate. For more information on how to register as well as complete session details, visit http://www.ioug.org/collaborate06. -------------------------------------------------------- 5) In the News -------------------------------------------------------- Microsoft and Apple have signed a 5 year software pact to develop versions of Office for Macs and announced plans to release a version of Office that will be compatible with Apple's new Intel-based computers. Apple’s senior VP, Phil Schiller, said that the company won't sell or support Windows itself, but also hasn't done anything to preclude people from loading Windows onto the machines themselves. This will allow Mac owners to run both Microsoft and Apple’s operating system on the same Apple computer. Microsoft’s Scott Erickson stated that they remain committed to offering a version of its Office business software for Macintosh computers. The last version was released in May of 2004 and usually releases every 2 to 3 years, though there’s no date set on the next release. * * * * Some of the top technical minds in Germany and France have been working on what they hope will be the world's most advanced multimedia search engine. The new engine is being called Quaero, which is Latin for “to search”, and is being billed as Europe’s answer to Google. But, going up against a proven company like Google won’t be easy as Google is already becoming more than just an Internet search engine. It has introduced an array of new software and is offering telecommunications services that move it well beyond its roots. And, Google has been aggressively seeking ways to import offline media, such as books and television shows, into its Internet search engine. Quaero isn’t the first attempt to develop an alternative to Google. Yahoo and Microsoft are now in the market, though have yet to erode Google’s dominance. But competition is good and almost always results in better tools and products for end-users. * * * * Oracle is offering $5.75 Billion in Investment Grade Notes, which it expects to close January 13th, 2006. It plans to use the proceeds of the offering to fund the purchase of Siebel Systems (expected to close on January 31, 2006 or shortly thereafter), for acquisition-related transaction costs and for general corporate purposes, including stock repurchases and other acquisitions. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- CTXSYS.DEFAULT_STOPLIST contains the list of stop words for your database’s language when using Oracle Text. If you’ve created a context index on a database column and you wish to do a search on that column, Oracle text removes stop words, which are common words from a default stop list or a stop list that an application developer can define. When doing a search, stop words are replaced so, for example, “bought the bird” and “bought a bird” will retrieve the same results because both “the” and “a” are stop words. As mentioned, developers can add to this default list if they wish by calling the ADD_STOPWORD and REMOVE_STOPWORD procedures in the package CTX_DDL. For more information about Oracle Text and how stop words are used, you can read Roy Gomes’ article in the January edition or Oracle Professional.