I am new to APEX and I am developing an application to manage Oracle database user accounts. I have a page (8) that has text items for the username and the database name. I have a submit button (this works fine) that will issue the ALTER USER... statement to reset the user's password and unlock the account. I have another button that should:
The issue is that FIND_USER_IN_DB does not get populated and page 11 which is a simple report with the following query:
WHERE username = :FIND_USER_IN_DB;
How do I accomplish this?
Thanks for your help.
I assume FIND_USER_IN_DB is an item on page 8. If FIND_USER_IN_DB has been found on page 8 but not submitted, it may be that the query on page 11 will find it has no value saved in the Session yet. If you look in Session State -- using "Session" button at the bottom of the developer screen, does FIND_USER_IN_DB have a value "in Session State" when you get to page 11?
How can this be solved.
1) Does it make sense -- in terms of your application -- to do a Submit on page 8 before you branch. If so, that may work because FIND_USER_IN_DB will then be defined for the query. .
2) I assume you are referencing back to a page 8 item because you don't want to duplicate this item on page 11. Otherwise you might be able to pass this value to a page 11 item in a Branch to URL or Page branch. (But it still might require submitting it first. I'd have to check further.)
Hope this helps,
Thanks for your reply. FIND_USER_IN_DB is defined as an application item. I have two buttons on page 8. One button, SUBMIT, will submit the page and process a PL/SQL procedure that changes the user's password to a temporary password, expires the password and sends the user an email to that effect. The second button, FIND_DATABASE, which will be used by the app users to find all databases where that username is defined so that a correct database SID can be chosen for the password reset. This button does a Redirect to Page in this Application to page 11. It also sets FIND_USER_IN_DB to the value in &P8_USERNAME.. When checking the session state, the FIND_USER_IN_DB is NULL as is P8_USERNAME. That is my delemma. How can I get the value entered in P8_USERNAME to populate FIND_USER_IN_DB for use on page 11? If there is another way to do this without using an application item, I am game.
Ah, as I read more closely ... You say
FIND_USER_IN_DB is defined as an application item.
Application items get treated differently from Page Items. They aren't on any page so there's nothing to directly tie them to a particular page operation. [You may have tried an obvious approach that doesn't work.] Please check where you
sets FIND_USER_IN_DB to the value in &P8_USERNAME.
Does it say "Page Item" / "Item" there? Check the selector? Can you select an Application Item. If memory serves, my (sad) experience is that you can list an Application Item here but it doesn't "work" and there's no warning that it's a problem -- except it doesn't work! Application Items can't be given values this way. Hey, it is what it is. That's the way it doesn't work!
So I believe you must "Submit" FIND_USER_IN_DB and then you can reference it on Page 11. There must be several ways to do this. I'll seaarch / ponder for a plain vanilla one.
Well, maybe I have to take it back. There is this documentation that suggests you can pass Application Items. http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/bldapp_item_app.htm#BCEHFDDJ See Note in Paragraph 8.
"Note: If you must set this item's value in session state using Ajax, then an Unrestricted protection level must be used for the item (for example in Dynamic Actions, Set Value, Page Items to Submit or Cascading LOVs, Page Items to Submit)."
I'll try to run a test. Still, the best solution for me still seems to assign the value of P8_USERNAME to FIND_USER_IN_DB and then submit the page which will plce the value in session.
(more) I was able to pass an Application Item if I set Session State Protection to Unrestricted. Probably not a good idea!
A page redirect is actually nothing more than the rendering of an anchor tag or a call of apex.navigation.redirect. The link provided by these is generated when the page is being rendered. So when the page has rendered (loaded) and the session state of P8_USERNAME was NULL, then the link will have an empty value there. There is nothing sad about the fact that when you change an item's value on a page that the session state would not automatically change and is only about understanding session state.
Let's say that you would submit the item to session state before the redirect though. Would this change anything? No. The link, as I pointed out, has already been rendered, and no session state substitution is done on a redirect.
You would better be off by submitting the page through the button instead of a redirect. This will set the session state of your item to the value you want. You can then use a branch to redirect to the correct page while setting the value of an item with the session state of P8_USERNAME. You might even want to use a process or computation to set the value of the application item instead of through a link, so that you can keep the application item not manipulable by the user.
Thanks, Howard and Tom. I changed the FIND button to SUBMIT, modified the PL/SQL procedure to set FIND_USER_IN_DB and created a branch to page 11. This works! The value of FIND_USER_IN_DB is set when looking at the session state of the page. I still have an issue with my SELECT statement returning no rows.
WHERE username = &FIND_USER_IN_DB;