Did You Know? -- December 2000

By Hervé Deschamps, Oracle Corporation.



Intermedia - BLOB

It is often necessary to convert large text documents stored in Oracle from a Binary LOB format into varchar2(32767). Here is an example of function that does just that:

function get_file_text (p_file_name in varchar2) return varchar2 is
   v_blob_content blob;
   v_raw_content  raw(32767);
   v_blob_content := wwdoc_api.get_document_blob_content(p_name => p_file_name);
   v_raw_content := dbms_lob.substr (v_blob_content,32767); -- 32767 first characters only
   return utl_raw.cast_to_varchar2 (v_raw_content);
end; -- function get_file_text

wwdoc_api.get_document_blob_content is a Portal 3.0. API function that retrieves the BLOB content of any file stored in table wwdoc_document$ where Portal stores all uploaded files.

Portal 3.0. The secrets to a good layout

Managing a good layout with Portal 3.0. can be a challenging task unless you know a few basics tricks of the trade. The main challenge of a good layout it to give browsing users the same look and feel for your web site whether they are using a 800 by 600 display or a 1024 by 768 one. If you think that nobody uses 800x600 anymore, think again. Take a look at a few famous web sites like http://www.cnn.com, http://www.yahoo.com, http://www.datek.com, http://www.etrade.com, http://www.amazon.com,  http://www.1800flowers.com, http://www.oracle.com. They all look the same in both 800x600 and 1024x768. Most of them actually choose to do this by only using 800 pixels of horizontal space event if the browser has 1024 available. Others like Amazon.com and Oracle.com utilize all the space they can get by making clever uses of percentages in HTML table definitions.

In Portal 3.0.6.x we do not have the luxury of portlets widths defined in pixels yet. Portal lets us size regions of a page in percentages of the total width of the screen. These regions are then translated by Portal into HTML table rows and columns. In a way this is probably for the better because it forces Portal web designers to be more resourceful than using simple pixel definitions and that results in full utilization of the real estate offered by the browser.

So, how do we get Portal to display a portal style web page with one banner across the top and three columns below: one navigation bar on the left, one content area in the middle and one promotion area on the right? And how do we get all this to line up in any resolution greater or equal to 800 by 600? It is not that complicated once you know a few tricks of the web design trade.

A lot of the Portal pages that you use to login or navigate are defined in Portal. If you think that they look OK, check how they are defined in Portal. They are a great source of ideas and best practices.

Portal 3.0. Misleading documentation: wwdoc_api.get_document_blob_content

function get_document_blob_content(p_name in varchar2) return blob;
  * get the document content, given a document name
  * this function returns the content of a blob document, given the
  * unique name of the document
  * <template>
  *    BLOB := wwdoc_api.get_document_blob_content(p_name => -- in varchar2);
  * </template>
  * <code>
  *    l_blob_content := wwdoc_api.get_document_blob_content(p_name => '4883');
  * </code>
  * @param     p_name in varchar2 unique name of document
  * @return    BLOB blob content of document
  * @exception DOCUMENT_NOT_FOUND

Without file name extension as the documentation above suggests you get this error:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL30.WWDOC_API", line 446
ORA-01403: no data found
ORA-06512: at "PORTAL30.COPY_HTML", line 19
ORA-06512: at line 1

So this is how the <code> element should read:

  * <code>
  *    l_blob_content := wwdoc_api.get_document_blob_content(p_name => '4883.GIF');
  * </code>

Portal 3.0. Navigation Manager

The navigation bars need to have their own look and feel, their own style. If you check that 'use page style box' at the bottom of the tab 'Main' in the Navigation
Bar Manager then all your navigation bar style settings will be ignored and it will be difficult for you to understand why.

Here is an extract of Portal's online help :

Select this option if you want this navigation bar's portlet to inherit  the style of the page that contains it. If you do not select this option, the portlet will use the style assigned to the navigation bar.

Wireless  + GPS all in One

Verizon Wireless Inc. and U.S. Wireless Corporation have announced the successful completion of technical field trials in Baltimore of U.S. Wirelessí cellular location technology, CDMA RadioCamera system.

The technology trial was conducted over a nine month period and encompassed a comprehensive four stage field test program that included real-time processing of live AMPS and CDMA callers, covering substantially all of the downtown locations.

U.S. Wirelessí RadioCamera system provides wireless carriers with continuous real-time location data needed to meet Phase II of the FCC E-911 mandate. Location information provided by the RadioCamera system also enables other services, including highway traffic control and planning, enhanced directory assistance, localized traffic condition reports and fleet location. The advanced CDMA location capability was enabled through a joint development and cross licensing agreement between U.S. Wireless and QUALCOMM Inc.

U.S. Wireless will also conduct an Operation Readiness trial in Washington, DC to help wireless carriers further evaluate the RadioCamera network, technology, scalability and commercial readiness to meet the FCC mandate.

3rd Generation Wireless Network coming to the US

QUALCOMM and Sprint PCS announced that they have started trials in the U.S. of a third generation (3G) broad band wireless network.

The trials are for a CDMA 1x Multi-Carrier (MC) voice and data network. The trials will include testing and verification of the 144Kbps 3G data capabilities over the Sprint PCS network.

Inter operability testing and qualification with network hardware from multiple infrastructure manufacturers will also be included in the trials.

The companies previously made what they claimed to be the first 3G voice call in North America. In a statement, they said that the data trials should lead to commercial deployment of the network by Sprint PCS in the second half of 2001.

PL/SQL -- Christmas Time!

After all these years working with Oracle products I can't believe that I just found out about this mine of information. So I'll share it with you as a little Christmas present (note: I am writing this on November 6th...). Have you ever looked at this directory? $oracle_home/rdbms/admin? I'm looking at it on my 8i Release 2 server machine and a door just open... The source code of PL/SQL! Guess what file dbmsjob.sql contains? Pretty well documented code on top of this. And you get to find out that Bob Jenkins created the package DBMS_JOB in 1993. I checked the internal Oracle database. As of november 2000 Bob still works for Oracle. He's been one of us for 11 years actually!

And how about standard.sql I thought... No author there though... But I saw code like:
  function NVL (b1 BOOLEAN, b2 BOOLEAN) return BOOLEAN is
    if (b1 IS NULL) then return (b2); else return(b1); end if;
  end NVL;

That's PL/SQL programmed in PL/SQL, though the core stuff is very often done with a pragma  interface to C...

UTL_HTTP crashes 8.1.6.

UTL_HTTP causes a lot of trouble in 8.1.6. It will run successfully 2 times and crash SQL*Net the 3rd time. This is documented as Bug# 1334230. The error message that you will get when users try to connect after utl_http has run three times is: ORA-12500: TNS_Listener failed to start a dedicated server process.

What happens? During one call to UTL_HTTP.REQUEST, WSAStartup is called twice, and WSACleanup is called three times. WSACleanup affects all sockets open in the process. It closes the primary socket between the client and the shadow. Since both SQL*Net and the RDBMS have previously called WSAStartup, we crash on the third call when the open socket count reaches zero, and all of the sockets (including the one for the client connection) are closed.

This is fixed in 8.1.7. but you can also apply patches (install this one first) and

Hervé Deschamps is a Technical Manager with Oracle Corporation. Over the years he has developed a number of applications using Oracle Development Tools and others.  He has an extensive experience of all phases of the development life cycle. He is also known in the technical community for his article publications centered around best custom development practices and for his user group presentations. You can reach him by e-mail at herve.deschamps@.oracle.com. He also maintains a web site full of articles, scripts, tips and techniques at http://www.iherve.com.