I have a trouble with Oracle Portal. I'm going to try to explain it clearly ;)
If I put this code on a Dynamic page and I add this Dynamic page to a Portal page: declare v_caid portal.wwsbr_all_folders.caid%type; begin HTP.P('step 1 <br /><br />'); select f.caid into v_caid from portal.wwsbr_all_folders f where f.name = 'XXXXX'; HTP.P('step 2 <br /><br />'); end;
It only works if I'm logged into my portal. Otherwise, this code never print "step 2" (step 1 it's printed always, independently you are logged or not). It seems it's not possible to execute the query if the user is not logged on the portal and I need it could be done. I've looking for a solution unsuccesfully. The page has been setup for public users can view it.
I've tried to execute Grant execute on PORTAL.WWSBR_ALL_FOLDERS to PORTAL_PUBLIC; (I readed on this forum) but I get an ORA-02204 error.
Thanks on advance and please, excuse me about my poor English level ;)
First of all, try Grant Select instead of Grant execute, execute is for procedures/functions and all_folders is a view. And aosl grant to PUBLIC and not PORTAL_PUBLIC, but if you could create and store your dynamic portlet then I guess it is already done.
Then you should manage exceptions in your code. I guess you don't have any folder named "XXXX". In that case select generates a no data found exception and stops PL/SQL execution.
"exception when other then htp.p(SQLERRM);" before your "end;".
It will give you the exact error and you'll be able to manage it.
Thanks a lot for answering.
I've executed these statements:
Grant select on PORTAL.WWSBR_ALL_FOLDERS to PORTAL_PUBLIC;
Grant select on PORTAL.WWSBR_ALL_FOLDERS to PUBLIC;
With the same result (The query only works if I’m logged).
Effectively I haven’t any folder with XXXX name. It was an example. On my real code I have an existing folder.
After managing exceptions I get an ORA-01403 no data found error what seems to give you the reason. But the data exists. Otherwise it should not work although I was logged because it’s the same query.
It seems to be a permissions subject, but I don’t know what permissions I must change…
Is your existing "folder" set to be publicly viewable? The WWSBR_ALL_FOLDERS view includes the WWV_USER_CORNERS view which does a security check (i.e. is the page public, does the logged in user has the privilege to view the page, etc.) in its where clause.
In your navigator, go to the page which you use in your query. Go to properties and then to the access tab. Is that page public? If not, your query won't find the information, since the wwsbr_all_folders view uses security to check if a user (public or logged in) has access to the that page. If the user does not have access or the page is not public, the view won't return a record for it. That's why there is a difference between the query when you're logged on or not.
Please excuse me for the delay.
You are right 857717. The parent of the folder I specified was not visible for public users. After reviewing a lot of things I was sure I had reviewed that. Obviously, I was wrong ;)
I really apreciate your help.
Thanks you very much.