Did You Know? -- April 2001

By Hervé Deschamps, Oracle Corporation.

Portal Apps: Super LOVs

Have you tried using a Dynamic LOV ? Where by you defile 2 LOVS -LOV1 and LOV2. LOV2 has a bind variable attached to it (the field name of LOV1). Selecting LOV1 will in turn reload the page with values populate for LOV2 .
For example:

Create a dynamic lov (LOV_DEPT) on department: select dname, deptno from scott.dept;
Create another dynamic lov (LOV_DEPT_JOB) to show the job position of a department: select job from scott.emp where deptno = :dept; Notice that there is a bind variable in the query,
Create a form (form on table) and set column deptno displayed as combox using LOV_DEPT, set column job displayed as combox using LOV_DEPT_JOB and set the value for the bind variable from column deptno.
Run the form, every time you change the value of deptno, the form will be refreshed and the values for job will be changed.

Portal Page Information

If you want to identify the record in table wwpob_page$ for a page that you know by its title (display name), try this query.

select p.*
from wwpob_page$ p,
wwnls_strings$ s
where s.id = p.title_id and
s.text_start = 'Page For93';

Portal Page Information through the API

If you want to retrieve a denormalized version of the page record try calling wwpob_api_page.get_page like in the example below. That procedure will give you extra stuff beyond what you can find in table wwpob_page$: title, description, titleimage, rolloverimage.

procedure test_page_01
as
     l_page          wwpob_api_page.page_record;
     l_pageid number := 90;
begin
     begin
         l_page := wwpob_api_page.get_page(p_pageid=>l_pageid);
     exception
         when others then
             wwerr_api_error_ui.show_inline_html;
             return;
     end;

htp.p('<html><body>');
 htp.p('id = '||to_char(l_page.id )||' ');
 htp.p('parent_id = '||to_char(l_page.parent_id )||' ');
 htp.p('name = '||l_page.name ||' ');
 htp.p('title_id = '||to_char(l_page.title_id )||' ');
 htp.p('title = '||l_page.title ||' ');
 htp.p('titleimage = '||l_page.titleimage ||' ');
 htp.p('titleimage_id = '||to_char(l_page.titleimage_id )||' ');
 htp.p('rolloverimage = '||l_page.rolloverimage ||' ');
 htp.p('rolloverimage_id = '||to_char(l_page.rolloverimage_id )||' ');
 htp.p('description_id = '||to_char(l_page.description_id )||' ');
 htp.p('description = '||l_page.description ||' ');
 htp.p('layout_id = '||to_char(l_page.layout_id )||' ');
 htp.p('style_id = '||to_char(l_page.style_id )||' ');
 htp.p('page_type = '||l_page.page_type ||' ');
 htp.p('has_banner = '||to_char(l_page.has_banner )||' ');
 htp.p('has_footer = '||to_char(l_page.has_footer )||' ');
 htp.p('exposure = '||to_char(l_page.exposure )||' ');
 htp.p('show_children = '||to_char(l_page.show_children )||' ');
 htp.p('is_public = '||to_char(l_page.is_public )||' ');
 htp.p('inherit_priv = '||to_char(l_page.inherit_priv )||' ');
 htp.p('is_ready = '||to_char(l_page.is_ready )||' ');
 htp.p('execute_mode = '||to_char(l_page.execute_mode )||' ');
 htp.p('cache_mode = '||to_char(l_page.cache_mode )||' ');
 htp.p('cache_expires = '||to_char(l_page.cache_expires )||' ');
 htp.p('</body></html>');
end;

Portal 3.0. Login/Logout Programmatically

All you need is in the package portal30.wwsec_app_priv. This includes not only the login/out process but also generating the appropriate link depending on whether or not the user in logged in.

How to get your base URL with Portal 3.0. API

wwv_util.getbaseurl does the job. It's a function that returns a varchar2 string like 'http://hdescham-lap/pls/portal30/'.

How to create URL items in Portal 3.0.

All the URLs related to items get stored in wwsbr_url$ table. There is an "internal" API that allows you to add/delete/modify URLs. Here is the spec with some amount of documentation. Also, see the pkg spec for more info.

/**
 * Sets the URL value for an existing record
 *
 * This procedure updates the url value column given the id,
 * siteid and language
 *
 * <template>
 * wwsbr_api_url.setURL (
 * p_url => -- in varchar2,
 * p_id => -- in number,
 * p_object_id => -- in number,
 * p_object_siteid => -- in number,
 * p_object_type => -- in varchar2,
 * p_language => -- in varchar2 default wwsbr_global.language);

Htp.prn

We all all know htp.p, but what about htp.prn. It's the same except it does not generate a CR character.

HTML: Link Style

Here is an example of how you can get links to show on users' browsers without any underlining. Actually, it even works when you define the style in the <body> part of the html document.

<html>
<head>
<STYLE TYPE="text/css">

</STYLE>
</head>
<body>
<a href="http://www.iherve.com">iHerve</a>
</body>
</html>

DMBS_LOB.SUBSTR

Parameterwise: that beast has nothing to do with the usual PL/SQL substr.
select dbms_lob.substr(text,100, 1)
from wwv_text
where id = 12023
This means "select the 1st 100 characters". In regular PL/SQL, that would be written:
substr(l_text,1,100).

Temporary Password Change

This is an old trick for DBAs. If you don't understand, you probably don't need to.

SQL> select password from dba_users where username='SCOTT';
PASSWORD
-----------------------------
F894844C34402B67

SQL> alter user scott identified by lion;
User altered.

SQL> connect scott/lion
Connected.

REM Do whatever you like...

SQL> connect system/manager
Connected.

SQL> alter user scott identified by values 'F894844C34402B67';
User altered.

SQL> connect scott/tiger
Connected.

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.