How to create links based on WWSBR_ALL_ITEMS? - Oracle Application Server Portal

how can I create a report based on WWSBR_ALL_ITEMS?
I need to emulate the Portal Search feature. Thus I need a
report to query the view WWSBR_ALL_ITEMS. This works all just
But now I need to embed links for the user to
- click on the folder and navigate there (direct access urls may
do the trick) and
- click on the item and the user opens it.
The problem with the items (text, file, url, pl/sql) is that I
don't know how to create the link.
I've tried several api calls but none really does the job.
Direct access urls will only work for documents, but not for
text items.
So, how is it done?
Thanx and best regards,
~dietmar aust.

You need to construct a call to the procedure.
This is an undocumented API call (and will likely change in
future releases), but you should be able to figure what
parameters it needs for the different types of items you want to


Function to get page/tab id

Is there a function that will return the current page id OR url (works because you can parse the url _page parameter to get the id).  I know that a portlet can get the url.  But, what if you want to have a generated portlet, let’s say calendar, then there is a problem. 
Basically, I want to use the page id as a key so that different data will show up in the calendar based on what page a calendar is on. I know that page parameters allow you to do this, but want to avoid my users from having to set a page parameter each time they create a new page that will include the calendar portlet. We have other portlets other then the calendar that we want to function uniquely on page as well.
This sounds a bit complex and tricky what you are trying to achieve and I doubt you can do it unless with a really hard work. Does it really make sense to make this?
Re: I want Page Id and Site Id inside the portlet... might give you a clue on the page id/tab id... but not sure per your description this is exactly what you want.
I hope it helps...
Thanks for the help. I was unable to get get_value to work, however, the page_url does work from the portlet_runtime_record.
Now, all I need is a regular expression to parse it which should not be too bad.
I know that this is a tough issue. However, we are setting up 100s of pages for groups in my company. Each step in the process that can be automated will result in fewer calls to the support team, so there is significant drive to eliminate steps.
Where this approach fails is with our calendar portlet. However, I am playing with publishing it as a page (based on the link you provided) as well as embedding a iframe in a portlet and calling via that method. The calendar does not expose the portlet_runtime_record in the areas where you can place code so it needs to be passed in I suppose.
Right, Dean... This is not easy to do. ;-) And it will only work from a portlet... iframe will take that away from you. It would be nice if later on you'll share the solution with the community.
I also had a similar problem like yours, but with me I wanted to show a list of events that
are going to take place. This list will be de pending on where the portlet has been placed, the so called landingpages.
At first I was also thinking about grabbing the page_id, but this requires a lot of
maintenance. eg, what if a content manager creates a new page, you'll have to add the
new page id in your code or the database.
Now im using default customization (editDefaultsPage), thats the yellow pencil icon in
Portal. The content manager adds my portlet to the page and has to tag a few checkboxes
on the edit Defaults Page to decide what events are visible on that certain page.
Perhaps you can do something similar for your Calendar?
Hope it works out for you 
We have been looking at using portlet instance id and tying data to a portlet instance in the database, but there can be issues if a portlet is deleted with orphaning data.
The more I think about it the more it makes sense to have the users customize with a key they provide but that defaults to the tab name of the tab the on which the portlet resides. The pencil is the place to do this, thanks for that and your insight.
Parsing the url works. Unfortunatly, the regular expression processing in 9i DB does not give you the full complement of RE expressions, especailly with short match tools so I don't know if it will be able to really parse the url with just an Oracle pattern package regular expression. But it can be parsed easily enough with just the string functions. Probably will be faster anyway. 
I take it you're using PL/SQL, im using java on my end, but I guess the problems are the same :)
Why do you need to parse the URL, isnt this case enough: Page designer drops a portlet
on the page, goes to editDefaults, selects what data should be visible (eg with checkboxes)
in the Calendar and presses OK
Or is there something that im missing?
kind regards

"Interrupting" the display of the home page

Not sure how common or uncommon this is, but here's my delimna. We are using Oracle Portal as an intranet for our employees. We are a non-profit Christian organization and we need all of our employees to annually "sign" our statement of faith confirming their agreement in what we believe. We are attempting to electroni-fy this process. The current proposal is that when someone logs in to the intranet website, they would be prompted to read the statement of faith and check a box indicating they do agree with it. They would be givin the option to not check the box, but the prompting would continue each time the log in until they do check the box. Again, this whole process would happen once a year, so it would be ideal to setup something that would trigger this to happen every, say July 1 or whatever.
Any suggestions on how to do this with Oracle Portal? Do we need to implement javascript to "interrupt" the home page? Maybe simply changing the home page to use a different template each year? Any good, simple solutions for this? 
Hi Cheeky: There are certainly different ways you could accomplish this, but the cleanest option will require some degree of PL/SQL skills so this information can be stored in a database table. If you have someone that can write some simple PL/SQL and JavaScript, you could do an approach like the following:
1) Create a table that keeps the username, the date they last agreed, and the date they were last asked
2) Create a portal page that has the statement of faith and a simple HTML form that when submitted invokes a PL/SQL procedure stored in the portal DB that updates the date asked each time the page is presented and updates the date agreed in the above table IF they checked the checkbox
3) Create a simple dynamic page that looks up the current user and checks if the date they last agreed was > 1 year ago. If so and the date last asked is not today, use JavaScript to redirect them to the statement of faith page. If not, do nothing. Put this dynamic page in the footer of the portal pages
Rgds/Mark M. 
Thank you for that outline of how you would accomplish this. Since this solution seems to only take PL/SQL and JS knowledge, I'm pretty confident I should be able to do it with no problem. I thought I understood Oracle Portal pretty well, but unfortunately, not well enough, because I'm unclear about two points in your explanation: a "simple dynamic page" and the "footer" of the portal pages.
I am sure those are common tasks/features with Oracle Portal, but I'm just not familiar with how to implement them. I probably know what a dynamic page is, but just not familiar with that terminology.
Would you be able to either describe how to access/create these or suggest some web page(s) where I can read up on them? Thanx. 
Glad it helped. To flesh out what I meant --
Dynamic Page: A dynamic page is a portal object which can combine standard HTML and/or JavaScript, etc with PL/SQL. To create one, go to Navigator by clicking the link in the top right corner of any page while in edit mode. In Navigator, click on the Providers tab then click on the locally built providers link. If you do not already have a locally built provider for your portal, you may want to create a new Database Provider. A provider is simply a container to hold various types of objects. A database provider is associated with a specific schema in the Infrastructure database under whom all the objects are run.
Once you have your provider (or using one that already exists), go into that provider by clicking its display name in Navigator. You can then create a dynamic page by clicking the link on the Create new... line at the top. The dynamic page automatically contains sample code which shows how to combine HTML with PL/SQL. It assumes any code entered is standard HTML unless it is wrapped with <ORACLE> and</ORACLE> which are the custom tags to indicate the contained code is PL/SQL. You can do a large complex block of PL/SQL in between the Oracle tags or just call a stored procedure from the database depedning on your needs. You can also call portal APIs assuming the database user associated with the provider has appropriate permissions.
What I was envisioning was using the wwctx_api.get_user to get the username of the current user, doing a select from your table where you are storing this information. Depending on how automated you want to go, you could put in exception handling that if that user is not found, automatically inserts a new record. Otherwise, it looks up the date they last agreed to the SOF. If > SYSDATE-365, then you can update the record to reflect they saw the SOF today and use the ability of PL/SQL to render HTML (such as htp.p) to render JavaScript that will cause the redirect.
Footer of the page is not a portal term. I simply mean you add this dynamic page in a common element that exists on all of your portal pages. Generally, I add them at the bottom of the bage so they don't get half a page drawn and then are redirected. Since the dynamic page is essentially invisible, you could put it anywhere. I usually do things like this in a region I create at the bottom of the page called something clever like 'footer'. Create a blank region and add your dynamic page by clicking on add portlet in your new region and then clicking the Portlet Staging Area link which is where new providers hide.
It might take a little tweaking and be careful you don't put it on the page they are being redirected to or you will create a looping redirect, but it is definitely doable and a neat trick to add to your portal skills.
Hope that gets you started
Rgds/Mark M. 
Thanx again for your clear explanation and insight. After following along with your text, I realized I am quite familiar with those concepts in the Oracle Portal environment. I was just not used to the terminology "Dynamic Page" even though that is obviously what they are called. I usually just refer to them as portlets, probably because that's how we use them on a page.
What I was not familiar with was dynamically creating javascript which would run while the page was being built. I'm most familiar with creating javascript functions in the header of pages which I would call during triggering events on the page. As is with most old time Oracle developers, I'm not a web developer, but am attempting to learn along the way.
Thanx again for the time to explain how this functionality could be accomplished. 
Hope you don't mind me delving into more detail than you needed. I had no idea how comfortable you were with portal. Post back if you need a hand with the HTML/JavaScript side. I came to oracle the opposite direction and started on the web side and picked up Oracle.
Rgds/Mark M. 
If the users must log in / authenticate before they can access anything on the intranet then I would just customize the login portlet. That would be the best way.

How to display word document inline

I am using Oracle Portal Version:
Need help in finding a method to display a word document inline as part of a web page. When a word document is uploaded as a portal file item, the only display option provided is to display the file in a full browser or new browser. I have used pl/sql server pages (PSP) and interMedia to filter an uploaded word document from a db into a text format and display it on a web page. I am wondering if this is the only method for displaying inline that I can use with a word document uploaded in the portal or if the portal has a built in functionality to accomplish the same thing.
Seems that the portal version 3.0.9 had an option to display the item in the content area.
Thank you for any assistance. 
one solution that i can think of is adding a HTML portlet to the page and display the doc in an iframe.
sample code:
<iframe src="" />
there is no display option for rendering items in-line out-of-the-box.
another solution i can think of is coding a custom attribute (e.g. pl/sql) to this.
Thanks for the suggestions.
I thought about using an iFrame, but prefer not to.
Also tried to build a custom file item just for the word documents, but could not find a way to control the link that downloads the file. The link automatically displays either the display name or the file name and clicking it downloads the file. If I could have the link call a package then I could download the file and filter it into text/html. Let me know if you know of a method to control what the link does.
I wonder if I can build a custom item with an attribute that allows browsing for a file on the pc. Then the custom item would call a package to upload the file into a document table. I want to use a custom item to use the add item functionality to keep the same look and feel of the site administration.
Any code you have that may be useful would be appreciated.
hi jon,
here is how it works in order to generate the same link than you see when moving with the mouse over the item.
an example:
create a custom item type/attribute and pass the item id to the PL/SQL procedure. the important thing is to know the item id.
now in the pl/sql code you can use this item id to easily generate the URL.
the way this works is using the content management view wwsbr_all_items. you take the passed in item id and select from this view. the column you need to select in order to generate the URL for this item is NAME.
select name from wwsbr_all_item where id=<id passed from the item>;
now to construct the URL take the following URL and append the selected value for NAME.
this is exactly the URL you are looking for.
more info on the content management views and APIs can be found here:
regarding your second question: there are several file upload portlets available via the developer services (free membership) on OTN -
I am already utilizing the pls/portal/url/ITEM/itmeName for displaying items.
I also believe I have found a way to place documents inline. This package was used in customize search portlet and I found that I can also use this to filter a word document into an html page. Here is an example of what I placed in a pl/sql item:
I tested it and it looks like it will work.
I would like to hear any thoughts you have on this method. 
if this works fine for you then i would go ahead and use this method. the only thing you need to keep in mind is that these internal APIs can change from version to version without any notice.
may be somebody know about display word document inline not from wwdoc_document$ table 
The de-support of the api's was a big discussion within the team this week.
You are correct with the internal api's and it may not be a very good practice using them.
The portal seems to be limited in what it can provide and I use the api's to provide some customization to the portal. If the api's change it will cause havoc.
Thank you for your assistance in this matter.

Page parameter as url on page?

Hi all,
We have a requirement to use a page parameter in a URL. so that when a user logs in, there id would be passed as part of a url.
So if user 1 logs in it will pass 1 in the url bar
If user 2 logs in it will pass 2 etc
Any ideas 
I suspect your goal can be achieved simpler than you think. If you want to pass the userid to one or more of your portlets, you can do so by mapping the userid system variable to your portlets' paramters. (Enable page parameters and events for your page group, edit the page properties, navigate to the parameters tab, and perform the mapping.)
Some more information about your requirement might be helpful too...
Hi Peter
We already have page parameters as you suggested implemented but for this requirement i dont htink they can be used this is the requrement:
We need to be able to pass a user id, and based on this user pull back a certan document out of a folder so if user 1logs in 1.pdf will be shown and if user2 logs in 2.pdf will be shown as so on.
Any help on how you think we could acheive this functionality would be great
Good to hear that you're using page and portlet parameters! :-)
What exactly do you mean when you say: "pull back a certain document"?
Do you want to display PDF documents in a portlet? Or in the full browser window? Or are you constructing links that point to the PDF documents?
Yes thats right, we have a library of PDFs and based on parameters we want the url to point to these PDFs..
I have created a Omni Portlet that takes the number of the coloumn and points to the specific PDF and this seems to be working for one of the requirements.
The other requirement that we are struggling with is that a user will have a specific chart (PDF) displayed to them based on there portal user id. So when user 1 logs in they get 1.pdf from a folder that is pointed to via the url. This should appear within a region if thats possible??
This changes on a daily basis so we cannot keep uploading the document to the database instead we want it to point via the url.
The other issue is that they cant see other users pdfs so i dont know if this is possible.
Thanks for your help
Hi Peter,
To the best of my knowledge, the only way you can display a PDF document on an HTML page "in place" is using iframes.
You'll have to create a portlet programmatically (using Java or PL/SQL [Passing parameter to a dynamic page portlet]). Programmatically you can access the current userid (you can also read the userid system variable that is passed as a parameter to the portlet from the page level). And finally you have to construct the iframe tags, which would look something like this:
<iframe src="http://myhost/myuserid.pdf" height="200" width="400"></iframe>where you'd obviously construct the src URL dynamically, using the currently logged on user's ID.
Hope this helps,
Hi Peter
Thanks for the quick response. Dynamically updating the URL is what i am struggling with. I do not know how to pass parameters into the URL. With Omni portlet you use ##Param1## but how can you pass the user is into the url so that we would have:
How do we know how to pass the parameter and what are they called?
We use the user Id API with one of our reports but it doesnt appear to work in the url do you have any suggestions or any documents you suggest reading?
Also can you suggest any docs/websites for researching how to do the java/pl/sql programming?
Thanks very much for your help 
this is a good spot for Java Portlets:
The Portal Developer guide is long but worth a read:
There is a good example on getting user credentials within a portlet on the OID portlets in the PDK. See
I got a group based (as oposed to user) authorization scheme working well based on the example.
Good luck, Hernando

show HREF content in a portlet

If i use HREF, the content is opened in a new
page. But my requirement is to open html/pdf files in a portlet. The way i know is to read the file either using util_file or utilities. Since i may have to read huge files, is there any other way to do so?. 
you could use (though I have never been able to implement is successfully). Add a pl/sql item which reads the file from a directory (defined using CREATE DIRECTORY). Remember to change the mime type before using the utl_cast to display the data.
Alternatively, if you know where the files are going to be stored, make a hyperlink using the pl/sql tiem (with display item directly in folder area selected).
I like the latter, less voerhead on server.
Store docs in DB, create virtual path (similar to the /docs/) and write a pl/sql item which creates the link 
I've gone thru your solutions. The docs are stored in DB only. But my problems are like this :
1) I cannot user dbms_lob since ths pl/sql buffer is 32K and my file sizes are in MB.
2) I need to create all my folders and items dynamically and hence i cannot go to the portal GUI and create a pl/sql item. ( Can we create the pl/sql item dynamically?.)
3) I have a dynamic portal page with tabs , common header and footer and each tab represents a page. From one of the pages which contains hyperlink to the doc, i should open the doc in a page which should also have the tabs, header , footer etc. but with the content in half the page ( i.e in a portlet).
That's where i am unable to proceed.
Hope i am clear in my expression and awaiting reply.
My problem is quite similar. And still thinking the possibilities. I've tried tree different methord: URLServices from localhost, iFrame and SSI.
URLServices works but it's not good enough. I can't embedd images and simultaneously trim header/footer as wanted to. Each page needs new page and portlet definition. In my case it possible, i have less than 20 pages. You propably can't use use this because of dynamic directories.
iFrame works, but only with MSIE. Want to take a RISK?
I don't know how to continue with SSI. It works in iAS, even in multihomed (virtual)Apache. But i have no idea, how should it be done.
Thats why I'm trying to clarify what to do with URLPortlet, XML and XSL. I think XSL files are processed on the fly. Still i'm worried about the performance.
As you noted, iFrames are probably the best approach, as long as all your users are using MSIE (I think that Netscape 6 now supports iFrames as well).
If you want some assistance with custom portlets, please consult the PDK forum.