Failsafe HTML Content Layout for Oracle Portal

One well known feature of Oracle Portal is the ability to create HTML Content Layout, and apply these layouts to page regions. The online documentation has for instance an example on how to check if a link should open in the current window or in a new window:

<oracle>
begin
if '#item.displayoption#' = 'link' then
   htp.p('<a href="#item.url#">View item in current window</a>');
elsif '#item.displayoption#' = 'linktonewwindow' then
   htp.p('<a href="#item.url#" target="_blank">View item in new window</a>');
else
   htp.p('Display item directly in page');
   htp.br;
   htp.p('#item.content#');
end if;
end;
</oracle>

On the surface that looks very good, but what if you got regions that shall support both links and text items ? The problem of referring to #item.content# (or the #item.content.value#) multiple times, is that the generated code allocates space for the length of #item.content# each time it is referred. For instance, if your region contains both links and a 15k text item, and you refer to #item.content# three times, the space used will be 45k + the size of the links. This is far above the PL/SQL 32k limit, and your portal page will not render properly, and you will get the following error thrown in the browser:

Internal Error (WWC-00006)
Execution of the PL/SQL-block failed: , (WWC-40015)
Preference path not found: oracle.portal.wsrp.<YOUR_PORTLET_1_NAME> (WWC-51000)
Preference path not found: oracle.portal.wsrp.<YOUR_PORTLET_2_NAME> (WWC-51000)
Preference path not found: oracle.portal.wsrp.<YOUR_PORTLET_3_NAME> (WWC-51000)
Preference path not found: oracle.portal.wsrp.<YOUR_PORTLET_4_NAME> (WWC-51000)
and so on, for each of the portlets mounted on your page.

The solution is to use more PL/SQL code, and less HTML Content Layout Substitution tags:

<oracle>
DECLARE
  p_target VARCHAR2(6);
  p_content VARCHAR2(32000); 
BEGIN
  p_content := SUBSTR('#item.content.value#',1,32000);
  htp.p('#ITEM.STATUSINHTML#');
  if '#item.displayoption#' IN ('link','linktonewwindow') then
    if '#item.displayoption#' = 'link' then
      p_target := '_self';
    elsif '#item.displayoption#' = 'linktonewwindow' then
      p_target := '_blank';
    end if;
    htp.p('<a href="'||p_content||'" target="'||p_target||'">#ITEM.TITLE.VALUE#</a>');
  else
    htp.br;
    htp.p(p_content);
  end if;
END;
</oracle>

This way, the potentially huge #item.content.value# is referred to only once, and it's also made sure it never exceeds 32k. (You cannot create text items larger than 32k through the Rich Text Editor or through the Portal PL/SQL API. But since text items are saved in CLOBs, they could potentially have been populated through a custom API and be larger than 32k).

Best DEBUG for WNA in OAS 10.1.2

In my experience the best way to trace what's going on if configuring SSO with WNA is to enable JAZN debug. In OAS 10.1.2 it can be enabled with -Djazn.debug.log.enable=true as a Java startup parameter for OC4J_SECURITY

Finding grants from data dictionary

Unless you make an full export with grants you won’t get all grants when doing a export/import with Oracle. So if you don’t have a list of all grants across those schemas you imported, you will have a huge job of compiling and establishing which grants are missing.

The solution to this is to find those grants through the data dictionary on the source system. For instance, if you want to find all grants made by the PORTAL schema, the query would be like this:


SET HEADING OFF
SET PAGES 999
SPOOL grants.sql
SELECT 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO '||grantee||DECODE(grantable,'YES',' WITH GRANT OPTION;',';')
FROM DBA_TAB_PRIVS
WHERE grantor = 'PORTAL';
SPOOL OFF
EXIT


Now you have everything you need in the grants.sql and this script can now be run on the target system.

Syncronize and optimize Portal text indexes

To improve performance with Oracle Text based searches in Oracle Portal, you should on a regular basis make sure to syncronize and optimize Portal text indexes (if this is not allready set up during installation).

sqlplus portal/<password>

exec wwv_context.sync
exec wwv_context.optimize
exit

Which languages are installed in my Portal ?

select title from portal.WWNLS_SYS_LANGUAGE$
where installed <> 0;

Oracle Q-Quote

To avoid quoting quotes in string, Oracle 10g offers the Q-Quote technique.

Let’s say yoy want to select the following from dual: I’m into rock’n’roll

This would normally mean you should quote the three quotes, but with the Q-Quote technique it’s much simpler:

SQL> SELECT q'[I'm into rock'n'roll]' FROM dual;

Mac keyboard combinations on a Windows keyboard

I recently bought a Mac mini and a Belkin KVM switch, which enables me to use my existing monitor, keyboard and mouse with both my current Windows PC and my new Mac mini. There are however quite a few characters I couldn’t find when using the Windows keyboard on Mac, so I will use this thread to remember them. Thanks to Allan for supplying many of the keystrokes for a Windows keyboard with Danish layout.

1. Commercial at @ : ALT Gr-* (just ALT-* in some applications)


2. Pipe | : ALT-i


3. Left brace { : SHIFT-ALT-8


4. Right brace } : SHIFT-ALT-9


5. Apple Command Key: Windows Key


6. Less than < : §


7. Greater than > : SHIFT-§


8. Copy : Windows Key-C


9. Cut : Windows Key-X


10. Paste : Windows Key-V


11. Quit Application : Windows Key-Q


12. Dollar $ : <


13. Euro € : SHIFT-4


14. Backslash \ : SHIFT-ALT-7

I also found this article, which suggests a software solution to the problem. When I get a Mac keyboard, I should study this. The layout of a Mac keyboard can also be useful.