Did You Know? -- March 2001

By Hervé Deschamps, Oracle Corporation.


iHerve.com (from Alain Gougeon, La Paz, Bolivia)

I did not do this on purpose and I did not notice until Alain pointed it out. 'iHerve' if you pronounce it in Spanish means 'it boils'. So I guess my web site is a hot one, huh? :-)


Web crawler built with Oracle Technology
Web crawling: Oracle has plans for a new product (built on interMedia) called iSearch.  No commitment yet to integrate that with Portal. Try isearch.us.oracle.com for more info.


Portal 3.0. Tips and Tricks

Calling Portal Forms
Here is a neat way of calling another form without hard coding module id:
PORTAL30.wwv_user_utilities.get_url('EXAMPLE_APP.LINK_TO_UPD_EMP', 'empno', empno, '_empno_cond', '=').
This will return the entire url.  So you can store it to a varchar2 string (l_url) and call the form using PORTAL30.wwv_redirect.url(l_url);
 
Cleaning up orphaned LOVs
If you have left over LOVS that got orphaned when deleting an application (old bug) you can clean up by going to SQL*Plus as PORTAL30 and running these commands:
delete from wwapp_application_detail$
where object_owner = <the app name> and object_name = <lov name>;
delete from wwv_modules$
where owner = <app name> and name = <lov name>;
delete from wwv_usr_cat_lov$
where owner = <app name> and lov_name = <lov name>;

Goodies from Portal package wwctx_api
wwctx_api is a very useful Portal package for security purposes and for various path queries. The package contains other functions and procedures not listed here.
    function get_user return varchar2;
    function get_user_id return number;
    function is_logged_on return boolean;
    function get_db_user return varchar2;
    function get_nls_language return varchar2;
    function get_ip_address return varchar2;
    function get_login_time return date;
    function get_product_schema return varchar2;
    function get_proxy_server return varchar2;
    function get_proxy_port return integer;
    function get_sessionid return NUMBER;
    function is_session_authenticated return BOOLEAN;
    function get_product_version return varchar2;
    function get_image_path return varchar2;
    function get_proc_path return varchar2;
    function get_server_protocol return varchar2;
    function get_servlet_path return varchar2;

Where to find the page titles?
Since the page titles are translatable, the string is stored in the text column of the NLS table (wwnls_strings$). The title_id and the description_id of the page correspond to the id of the NLS table. This NLS table is full of other interesting information. Check it out!

Print a Portal SiteMap with an SQL*Plus script
--
--
-- Purpose: List Complete Content Folder Hierarchy
--
-- Modification history
-- person      date    comments
-- ---------   ------  -------------------------------------------
-- hdd         021401  Initial version
--
--

set head  off
set pages 1000
set lines 140

col f_title format a50

spool list_f.txt
select crn.siteid site, crn.id fol_id, LPAD(crn.title,length(crn.title)+(level-1)*4,'.') f_title
from wwv_corners crn
where crn.siteid >10  -- only user defined folders
start with crn.parentid = 0 and  -- for optimizer
           crn.id       = 1 and  -- for optimizer
           crn.siteid   > 10
connect by crn.parentid = prior crn.id and
           crn.siteid   = prior crn.siteid and
           crn.language = prior crn.language;

spool out



Easy debug information from Portal
You get some useful information if you append "&_debug=1" to the URL of any portal page.  In addition to timing info, you'll find out how caching is being used for each portlet.  Debug will report one of the following values:

Security levels in Portal
There are a number of security levels for each object type in Portal. For example, there are 7 levels for Folders. Here is a quick query to show you where Portal store all that information:

select otype.name, privilege_code, sec.name
from wwsec_privilege$ sec,
     wwsec_priv_object_type$ otype
where otype.id = sec.object_type_id
order by 1,2

The results are in the table below.
 
FOLDER 100 VIEW
FOLDER 200 STYLE
FOLDER 300 CREATE_WITH_APPROVAL
FOLDER 400 CREATE_WITH_APP_AND_STYLE
FOLDER 500 MANAGE
FOLDER 600 MANAGE_AND_STYLE
FOLDER 700 OWN


Pushing this a step further I have included a query that I used times and times again. It provides a list of folders to which a given Portal user has at least a 'Create With Approval' clearance through any Role granted to that user as long as the role name ends in '_PUBLISH' or '_APPROVE'. Let me know if you want the util package.

cursor get_dest (p_user_id number) is
            select id, siteid, title, language
            from wwv_corners
            where (id, siteid) in
                        (
                        select
                               to_number(util.snip_between(priv.name,'/', null)) folder_id,
                               to_number(util.snip_between(priv.name,null,'/')) site_id
                        from   wwsec_sys_priv$ priv

                        where  priv.grantee_group_id in (
                                                            select prs_grp.group_id
                                                            from   wwsec_flat$     prs_grp,
                                                                   wwsec_group$    grp
                                                            where  prs_grp.person_id = p_user_id and
                                                                   prs_grp.person_id != 0 and
                                                                   grp.id = prs_grp.group_id and
                                                                   (grp.name like '%_PUBLISH' or
                                                                    grp.name like '%_APPROVE')
                                                        ) and
                               priv.object_type_name = 'FOLDER' and
                               priv.privilege_code >= 300
                   )
             order by title;


Portlet API
The place to go for Portlets goodies to re-use in your code is any package whose name starts like wwpro_api%.


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.