Secured content area views - how to embed item links? - Oracle Application Server Portal

Hi,
the content area views are really great for implementing a specialized search mechanism.
Unfortunately I need to display links to the items (url, text, file, ...) as well. And I need to display the folder link to that item as well.
How can I do it?
portal30.wwv_thinghtml.item_link and getitemurl didn't do it. Neither did portal30.wwsbr_link_util.get_folder_url work to get the folder link.
Any suggestions?
Best regards,
~Dietmar.

You'll have to figure out the structure of the URLs and format them explicitly. There's enough information in the content area views and underlying tables to format the URLs for most objects. Note that the documented content area views are secured (users will only see what they are authorized to see), whereas going directly against the underlying tables is not. Therefore, you should always try to use the views, or at least join to them if you need information directly from a table.
We don't document the URL structure because they can (and will) change from one release to the next.
For example, here is a procedure that lists subfolder links for a give parent folder:
<pre>
create or replace
PROCEDURE SHOW_SUBFOLDERS (
p_parentid IN VARCHAR2,
p_caid IN VARCHAR2)
as
sfURL varchar2(256);
imgSRC varchar2(20) := '/images/tfold.gif';
begin
for c in ( SELECT s.id,s.display_name
from portal30.wwsbr_all_folders s,
portal30.wwv_user_corners p
where s.id = p.id
and p.parentid = p_parentid
and s.caid = p.siteid
and p.siteid = p_caid )
loop
sfURL := 'PORTAL30.wwv_main.main?p_cornerid='
|| c.id || '&p_siteid=' || p_caid;
htp.p('<a href="' || sfURL
           || "><img src="/images/tfold.gif" border="0"></a>');
htp.p('<a href="' || sfURL || ">'
|| c.display_name || '</a>');
end loop;
exception
when others then
htp.p(sqlerrm);
end;/
</pre>
Regards,
Jerry

Related

Display pdf files in portal

I am trying to display a pdf file from within portal. I would like the user to select a link that includes the url of the pdf. These pdf files are specific to the user logged into the application so it is not the same static file. The pdf files are previously generated by a batch program and we want to use portal to allow their display. I currenly have a QBE Report that shows various information about a given record & would like to include a link that launches the pdf if the user decides to view the report based on the information on the record. Any ideas as to how this could be accomplished ? 
Hi.
I'm not sure if this is going to work for you, but I'll tell you my idea. I'd place a link that would take the user to a PL/SQL procedure stored in my database. The procedure would check the user that is currently connected and show them their pdf. I guess you'd have a table with the relationship between users and pdf files and then just show them the file using the owa_util.url_redirect or something similar.
Hope it helps.
Mercedes. 
Are the pdfs stored on the file system or in the database? One way of going about it would be to name the generated pdf with some unique value that can be in turn related to the portal user's user id. Then you can use wwctx_api.get_user to get the user id for the current portal user and use it to somehow configure the url to link to the pdf. Only a thought. 
Thanks for the reponses. I could not use the owa_util.url_redirect procedure because it exposed the URL and the end user could try any file name and retrieve a PDF even if they should have access to it. We didn't want to create seperate directories for every group of users either. I then decided to store the PDF's in the DB into BLOB (or LOB, I believe would work also) fields. Then I was able to get the data via a Package (or could have used a Portal Form). I used a Package because we wanted to audit when a PDF was read. Here is a sample of the PL/Sql code to retrieve the data.
--
-- Re-directs the URL to display the PDF file associated w/ passed Bulletin Id
--
procedure show_bulletin
(p_bul_id in number
)
is
c_prc_func varchar2(200) := 'show_bulletin'
;
l_dir_nm varchar2(500);
l_file_nm oe_bulletin_pdf.file_nm%type;
--
begin
g_err_msg := c_start || c_pkg || '.' || c_prc_func
|| ' - PortalUser: ' || portal30.wwctx_api.get_user
|| ' BulId: ' || p_bul_id
;
tr_msg.log_msg(g_err_msg)
;
l_file_nm := get_pdf_file(p_bul_id);
--
if l_file_nm is not null then
declare
l_size integer;
l_blob blob;
l_tmpblob blob;
l_mimetype varchar2(4000);
l_rowid urowid;
begin
htp.init;
SELECT BLOB_FILE
INTO l_blob
FROM BROKER_ADM.OE_BULLETIN_PDF
where bul_id = p_bul_id
;
l_size := dbms_lob.getlength(l_blob);
dbms_lob.createtemporary(l_tmpblob,true);
dbms_lob.copy(l_tmpblob,l_blob,l_size,1,1);
owa_util.mime_header('application/pdf');
htp.p('Content-length: '|| l_size);
htp.p('Pragma: no-cache');
htp.p('Cache-Control: no-cache');
htp.p('Expires: Thu, 01 Jan 1970 12:00:00 GMT');
owa_util.http_header_close;
wpg_docload.download_file(l_tmpblob);
end;
--
-- Log User Audit of PDF view
--
log_user_audit
(p_bul_id
,l_file_nm
);
--
-- Update Broker / Bulletin that Bulletin Picked-Up
--
log_bulletin_pickup
(p_bul_id
,portal30.wwctx_api.get_user
);
else
g_err_msg := 'No PDF found for Bulletin Id: ' || p_bul_id;
tr_msg.log_msg(g_err_msg)
;
htp.p(g_err_msg);
end if;
--
g_err_msg := c_start || c_pkg || '.' || c_prc_func
|| ' - PortalUser: ' || portal30.wwctx_api.get_user
|| ' BulId: ' || p_bul_id
|| ' PDF: ' || l_file_nm
;
tr_msg.log_msg(g_err_msg)
;
--
exception
when others then
g_err_msg := 'Unexpected error in ' || c_pkg || '.' || c_prc_func || ' Sql: ' || sqlerrm;
tr_msg.log_msg(g_err_msg)
;
htp.p(g_err_msg)
;
raise_application_error(-20001, g_err_msg);
end show_bulletin;

How to add URLs in apage without using edit mode

Hello everybody.
We have Portal 9.0.4.1 under Solaris, and we are just beggining using it :-)
We created a section for links (to urls) in the home page of each user. We would like our users to add links to their most used pages (for example, metalink.oracle.com), but we do not want them to enter in edit mode, we would like some functionallity like the "add portlet" in the customize option.
Any help will be very apreciated.
Thanks in advance.
Lisandro 
hi lisandro,
here is the solution to your problem:
http://www.oracle.com/technology/products/ias/portal/pdf/oow_10gr2_1336_fender.pdf
on page 15 you find the chapter 'Adding and Editing Items in Page View Mode'.
regards, christian 
Christian:
Thank you very much for your help, that was almost what we were looking for.
Do you know if there is a way to generate that URL dynamically, so that that link can be used on a template deployed across different pages?
Thanks in advance,
Lisandro 
Hi Lisandro,
That is precisely what I have done on a few sites. The approach I take is to parse the URL of the page using JavaScript in order to garner all of the ids needed for the edit link. I hard code the region to which I want the items added since it is the one value you can't get form the URL. (You could go the extra step and lookup the region by name from the database if you didn't want to hard code the region id.)
Here is an example of one the code sets I use. You may need to customize the parsing section depending on how many levels of tabs etc. In this particular case, it was a fairly complex page with various levels of tabs.
==========
<script language="JavaScript" type="text/javascript">
<!--
function additem()
{
additemurl.p_looplink.value = document.URL;
spliturl=document.URL.split(",");
splitcaid=spliturl[0].split("=");
additemurl.p_siteid.value=splitcaid[1];
additemurl.p_containerpageid.value=spliturl[1];
splitcorner=spliturl[2].split("_");
additemurl.p_cornerid.value=splitcorner[2];
// document.write("Pagegroup="+additemurl.p_siteid.value+" Page="+additemurl.p_containerpageid.value+" Tab="+additemurl.p_cornerid.value);
additemurl.submit();
}
//-->
</SCRIPT>
<FORM name="additemurl" action="http://<portalURL:port>/pls/portal/PORTAL.wwv_additem.selectitemtype" method="get">
<input type="hidden" name="p_cornerid">
<input type="hidden" name="p_siteid">
<input type="hidden" name="p_regionid" value="12041">
<input type="hidden" name="p_looplink">
<input type="hidden" name="p_containerpageid">
</form>
<div align="center"><image alt="Add item" src="<portalURL:port>/images/additem.gif" border="0"><font class="titleorimageid1siteid0">Add file or link</font></div>
==========
You can, of course, put in the desired style statements to match your site and, don't forget, the user still needs to have edit rights to the tab or page for the link to work.
I've used similar code on 9iR2, 10gR1 and 10gR2 versions. This is only one approach to your question, and, technically, Oracle could change the structure of their URL's like they did from 9iR1 to 9iR2, so this code is could be broken by future upgrades.
Rgds/Mark M. 
The example is generic and not hardcoded to any region on the page ... but has the same "warning" that Mark mentioned about this example not working in future versions if Oracle changes the URL structure:
The Add Item example shows how to create a new item type ...adding an item of this type to any region allows you to add content to that region
The Edit Item example show how to show a edit link or icon next to the item in view mode.
Add Item
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
This code sample shows how to create an item allows the content contributor to add items to the page group/page/region that the sample item is placed in.
If you want your content contributor to add items to multiple regions on a page, add the sample item to all regions that you wish to expose this functionality in.
Step 1: Create a PL/SQL procedure
Create the following procedure:
Create or Replace Procedure <schema>.ADDITEM_URL
(
p_itemid IN VARCHAR2,
p_pageid IN VARCHAR2,
p_siteid IN VARCHAR2)
as
v_url varchar2(2000);
v_region varchar2(2000);
v_looplink varchar2(2000);
v_host varchar2(2000);
begin
select folder_region_id into v_region
from portal.wwsbr_all_items
where id = p_itemid and
caid = p_siteid;
if instr(portal.wwctx_api.get_host, ':') = 0 then
v_host := portal.wwctx_api.get_host;
else
v_host := substr(portal.wwctx_api.get_host,
1,
(instr(portal.wwctx_api.get_host, ':')-1))
|| '%3A'
|| substr(portal.wwctx_api.get_host,
(instr(portal.wwctx_api.get_host, ':')+1));
end if;
v_looplink := 'http%3A%2F%2F'
|| v_host
|| '%2Fportal%2Fpage%3F_pageid%3D'
|| p_siteid
|| '%2C'
|| p_pageid
|| '%26_dad%3D'
|| portal.wwctx_api.get_dad_name
|| '%26_schema%3D'
|| portal.wwctx_api.get_product_schema
|| '&p_containerpageid='
|| p_pageid;
v_url := portal.wwctx_api.get_base_url
|| 'portal.wwv_additem.selectitemtype?'
|| 'p_cornerid=' || p_pageid
|| '&p_siteid=' || p_siteid
|| '&p_regionid=' || v_region
|| '&p_looplink=' || v_looplink;
htp.p('<img src="/images/additem.gif"');
exception
when others then
htp.p('error');
end;
Once the procedure has been created, grant EXECUTE permission to PUBLIC.
Step 2: Create a custom item type
This custom item type will be associated with the PL/SQL procedure created above. Placing an item of this type on a page will give the content contributor something to click on while the page is in view mode to call the add item wizard.
Go to the Procedures tab of the new item type and associate with PL/SQL procedure.
Ensure the “Display Procedure Results With Item” checkbox is checked.
Step 3: Add item of type “Add_Item” to a page.
1.     Configure the Page Group to allow items of your new type to be added
2.     Add an item of type “add_item” to your page.
3.     For the region containing your new item, in the properties, ensure the “Associated Functions” attribute is in the Displayed Attributes list.
Edit Item
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
This code sample draws an Edit icon for content contributors to click on while the page is in view mode to edit the item with the default Edit Item wizard.
Step 1: Create a PL/SQL procedure
Create or Replace Procedure <schema>.EDITITEM_URL
(
p_itemid IN VARCHAR2,
p_pageid IN VARCHAR2,
p_siteid IN VARCHAR2)
as
v_looplink varchar2(2000);
v_url varchar2(2000);
v_subtype varchar2(2000);
v_itemtype varchar2(2000);
v_catid varchar2(2000);
v_catcaid varchar2(2000);
v_parentid varchar2(2000);
v_host varchar2(2000);
begin
select subtype
,itemtype
,category_id
,category_caid
,parent_item_id
into v_subtype
,v_itemtype
,v_catid
,v_catcaid
,v_parentid
from portal.wwsbr_all_items
where id = p_itemid and
caid = p_siteid;
if instr(portal.wwctx_api.get_host, ':') = 0 then
v_host := portal.wwctx_api.get_host;
else
v_host := substr(portal.wwctx_api.get_host,
1,
(instr(portal.wwctx_api.get_host, ':')-1))
|| '%3A'
|| substr(portal.wwctx_api.get_host,
(instr(portal.wwctx_api.get_host, ':')+1));
end if;
v_looplink := 'http%3A%2F%2F'
|| v_host
|| '%2Fportal%2Fpage%3F_pageid%3D'
|| p_siteid
|| '%2C'
|| p_pageid
|| '%26_dad%3D'
|| portal.wwctx_api.get_dad_name
|| '%26_schema%3D'
|| portal.wwctx_api.get_product_schema
|| '&p_containerpageid='
|| p_pageid;
v_url := portal.wwctx_api.get_base_url
|| 'portal.wwv_edit_tab.edititem?'
|| 'p_thingid=' || p_itemid
|| '&p_cornerid=' || p_pageid
|| '&p_siteid=' || p_siteid
|| '&p_subtype=' || v_subtype
|| '&p_itemtype=' || v_itemtype
|| '&p_topicid=' || v_catid
|| '&p_topicsiteid=' || v_catcaid
|| '&p_cornerlinkid='
|| '&p_parentid=' || v_parentid
|| '&p_action=update'
|| '&p_currcornerid=' || p_pageid
|| '&p_language='
|| portal.wwctx_api.get_nls_language
|| '&p_looplink=' || v_looplink;
htp.p('<img src="/images/ed-item.gif"');
exception
when others then
null;
end;
Step 2: Associate Procedure with Item Types
For every item type that you wish to supply an edit icon to show while the page is in view mode, call the EDITITEM_URL procedure created in the step above.
Step 3: Show Associated Function attribute
In order for the procedure associated with the item type to execute, you must ensure the region properties are set on the Style/Attributes tab have the Associated Function listed as one of the Displayed Attributes.
Thank you candace and Mark, you have been of great help. I was able to implement Candace proposed solution with only minor changes.
Now I'm needing a feature to delte those items. Do you know it that is possible?
Thank you again,
Lisandro 
I was able to delete items modifying Candeca solution to obtain the following procedure, wich allows the user to delete de Item:
CREATE OR REPLACE Procedure DELETEITEM_URL
( p_itemid IN VARCHAR2,
p_pageid IN VARCHAR2,
p_siteid IN VARCHAR2)
as
v_looplink varchar2(2000);
v_url varchar2(2000);
v_regionid varchar2(2000);
v_host varchar2(2000);
v_masterthingid varchar2(2000);
begin
select     folder_region_id, MASTERID
into      v_regionid ,v_masterthingid
from portal.wwsbr_all_items
where id = p_itemid and
caid = p_siteid;
if instr(portal.wwctx_api.get_host, ':') = 0 then
v_host := portal.wwctx_api.get_host;
else
v_host := substr(portal.wwctx_api.get_host,
1,
(instr(portal.wwctx_api.get_host, ':')-1))
|| '%3A'
|| substr(portal.wwctx_api.get_host,
(instr(portal.wwctx_api.get_host, ':')+1));
end if;
v_looplink := 'http%3A%2F%2F'
|| v_host
|| '%2Fportal%2Fpage%3F_pageid%3D'
|| p_siteid
|| '%2C'
|| p_pageid
|| '%26_dad%3D'
|| portal.wwctx_api.get_dad_name
|| '%26_schema%3D'
|| portal.wwctx_api.get_product_schema;
v_url := portal.wwctx_api.get_base_url
|| 'PORTAL.wwv_thinghtml.content_actions_screen?'
|| 'p_page_id=' || p_pageid
|| '&p_page_site_id=' || p_siteid
               || '&p_masterthingid=' || v_masterthingid
|| '&p_siteid=' || p_siteid
|| '&p_thingid=' || p_itemid
|| '&p_regionid=' || v_regionid
|| '&p_currcornerid='
|| '&p_tabstring='
               || '&p_mode=16'
|| '&p_back_url=' || v_looplink;
               
htp.p('<img src="/images/ed-del.gif"');
exception
when others then
null;
end;
/
THe steps to implement it are the same as described above.
Thanks to all
Lisandro

Apex 4.2: PL/SQL table formatting - referencing CSS

Hello
I have a form that displays all fields for a single Activity (one row from my Activities table).
On that form, I am using PLSQL to pull out all the rows in my Milestones table that are relevant for the single Activity displayed.
It's all working fine, except the PLSQL table is really ugly.
I'm hoping there is an easy way to tap into the CSS stored for tables shown e.g. on standard reports.
My PLSQL is below. Does anyone know how I can reference the CSS?
DECLARE
CURSOR MY_CUR IS
SELECT MILESTONE_SUBCAT NM,
MILESTONE_DATE DT,
T.NAME TYPE,
L.NAME TARGET
FROM AA_WL_ACT_MILESTONES M
JOIN AA_WL_LOV_MILESTONES L
ON L.SEQUENCE = M.MILESTONE_CAT
JOIN AA_WL_LOV_MILE_TYPE T
ON T.SEQUENCE = M.MILESTONE_TYPE
WHERE MILESTONE_ACT_ID = :P41_ACTIVITIES_ID;
rec_cur_select MY_CUR%ROWTYPE;
BEGIN
HTP.P('<table><tr><th><b>ID</b></th><th><b>Milestone</b></th><th><b>Type</b></th><th><b>Audience</b></th><th><b>Date</b></th></tr>');
open MY_CUR;
loop
fetch MY_CUR
into rec_cur_select;
exit when MY_CUR%NOTFOUND;
HTP.P('<tr><td width="30">' || MY_CUR%ROWCOUNT || '</td><td width="100">' || rec_cur_select.NM ||
'</td><td width="70">' || rec_cur_select.TYPE || '</td><td width="100">' || rec_cur_select.TARGET ||
'</td><td width="120">' || rec_cur_select.DT || '</td></tr>');
END LOOP;
HTP.P('</table>');
CLOSE MY_CUR;
END;Thanks
Emma 
Your standard reports probably contain a reference to a CSS style for the HTML Elements (table, tr, td). Inspect that and just get the same code in your PL/SQL.
So something like:
HTP.P('<table class="tableClass"><tr class="trHeading"> ... etc ... ');But if you're so happy with a regular standard report layout ... why not use your select in a standard report (instead of coding your own PL/SQL)?

ASSIGN A SQL DYNAMICALLY TO A REPORT .

Hello again everyone, by code i need to assign a variant sql depending on an element to a report ? by code how it looks like i have i deas but i want to hear from you . thanks in advanced . i will be posting also if nobody else reply soon. i gotta fly =) kind regards.
There are a number of solutions to similarly vague problems. Without more context, it's difficult to suggest the most appropriate solution.
Thx, i am just trying  Declare   Cursor c_Dept Is      Select Deptno            ,Dname            ,Loc      From   Dept;   --   Cursor c_Emp(p_Deptno Number) Is      Select Empno            ,Ename            ,Job      From   Emp      Where  Emp.Deptno = p_Deptno;Begin     Htp.p('<div class="mytree-container">');   --   For Rec_d In c_Dept Loop      Htp.p('<ul>');      Htp.p('<h4>' || Rec_d.Dname || '</h4>');      --      For Rec_e In c_Emp(Rec_d.Deptno) Loop         Htp.p('<li>' || Rec_e.Ename || '</li>');      End Loop;      --      Htp.p('</ul>');   End Loop;   --     Htp.p('</div>');End;  looks enough     works . kind regards
3515580 wrote:Update your your forum profile with a recognisable username instead of "3515580": Video tutorial how to change nickname availableThx, i am just tryingDeclare
Cursor c_Dept Is
Select Deptno
,Dname
,Loc
From Dept;
--
Cursor c_Emp(p_Deptno Number) Is
Select Empno
,Ename
,Job
From Emp
Where Emp.Deptno = p_Deptno;
Begin
Htp.p('<div class="mytree-container">');
--
For Rec_d In c_Dept Loop
Htp.p('<ul>');
Htp.p('<h4>' || Rec_d.Dname || '</h4>');
--
For Rec_e In c_Emp(Rec_d.Deptno) Loop
Htp.p('<li>' || Rec_e.Ename || '</li>');
End Loop;
--
Htp.p('</ul>');
End Loop;
--
Htp.p('</div>');
End;
 Why? What does that have to do with the question? It is not a report, it's not dynamic in that the data source is not changing at runtime, nor is anything varying "depending on an element". Why the pointless screen shots? Contrast the simplicity and utility of typing "I have created a PL/SQL Dynamic Content region" as 46 bytes of accessible, searchable text with capturing and pasting 315KB of useless images.looks enoughWhich means what?worksIn what way? It generates invalid HTML as the content model for the ul element is violated. The standard approach to generating such content would be to use a list, or a report region with a custom template.

image retrieval error

Hi,
I have a weird problem. I have a blob field in two tables. Both contain a potential thumbnail of a given document which is stored in a different table. I have a simple web application that displays the thumbnails using the PL/SQL web cartridge.
Here is the problem. In one environment (A HP 64 machine) the procedure works fine, it loads which ever image is required with no problems. The other environment that I am running is identical and runs on windows 2000 pro. In this environment one table allows me to show the thumbnails, the other does not...I have placed checks in the code to see whether the BLOB is being retrieved correctly for the thumbnail that is not showing; and it is definitely there.
Has anyone seen anything like this before ?
Here is a copy of the procedure,
-----------------------------------------------------------------------------------------
PROCEDURE download_thumbnail(
pn_item_id IN number
) IS
CURSOR c_items ( id IN NUMBER ) IS
SELECT *
FROM bvhe_portal.bvhe_items
WHERE 1=1
AND item_id = id;
r_curr_item c_items%ROWTYPE; -- current item
lv_filename VARCHAR2(128) := 'thumb.gif';
lblob_dummy blob;
lv_dummymime VARCHAR2(128);
     lv_desc VARCHAR2(128);
BEGIN
OPEN c_items(pn_item_id);
FETCH c_items INTO r_curr_item;
IF c_items%FOUND THEN
IF (dbms_lob.getlength(r_curr_item.thumbnail) != 0) THEN
owa_util.mime_header('image/gif', FALSE);
htp.p('Content-Length: ' || dbms_lob.getlength(r_curr_item.thumbnail));
          
IF (instr(r_curr_item.file_name,'.',-1,1) != 0) THEN
lv_filename := substr(r_curr_item.file_name,1,instr(r_curr_item.file_name,'.',-1,1)-1)||'_thumb.gif';
END IF;
htp.p('Content-Disposition: inline; filename="'||lv_filename||'"');
owa_util.http_header_close;
wpg_docload.download_file(r_curr_item.thumbnail);
ELSE
     
-- start of thumbnail section
---------------------------------------------------------------------------------
-- This section does not return an image ??????
---------------------------------------------------------------------------------
SELECT thumbnail, mime_type, description
INTO lblob_dummy, lv_dummymime, lv_desc
FROM bvhe_portal.bvhe_item_types
WHERE 1=1
AND item_type_id = r_curr_item.item_type_id;
     owa_util.mime_header('image/gif', FALSE);
htp.p('Content-Length: ' || dbms_lob.getlength(lblob_dummy));
--htp.p('Content-Disposition: inline; filename="'||lv_desc || '_' || lv_filename||'"');
owa_util.http_header_close;
wpg_docload.download_file(lblob_dummy);
END IF;
ELSE
null;
--htp.p('Item could not be located.');
END IF;
CLOSE c_items;
END;
---------------------------------------------------------------------------------
If anyone could help I would be most appreciative,
Thanks,
Mark 
Hmmmm,
From a 10 minute look over, I can't see anything wrong. If your data was in an interMedia data type, you would be able to do a "checkProperties" to see if the image data was correct.
You might want to ask this question in the Oracle Portal forum that sopports the htp and owa packages.
Larry

Categories

Resources