Run your page with query, click the session link in developer toolbar at bottom of page, it will show all the page items and their value in session state... If your P3_AGENCY_COMP_STATUS_ID has a value in session state, it will appear there...
It means that the value of P3_AGENCY_COMP_STATUS_ID is blank (most likely, NULL)
Your SQL statement is matching NULL = column, which is always FALSE ==> no rows will return... ever.
Why is it NULL?
You have a bug in your code that you have to hunt down and fix.
For me, most of the time, it is due the Item name being misspelled somewhere in the app.
(check PL/SQL, SQL in current page and the Branching code from the prior pages.)
.. or.. being left out entirely in the Branch.
If you're doing a Calculation for P3_AGENCY_COMP_STATUS_ID, make sure the Calculation is being done before the Process.
(eg set Calculation to OnLoad BEFORE header and Process to OnLoad AFTER header)
I changed the Fetch Row statement to this:
for c1 in (
select a.agency_name, s.action_ind, f.function_type
from euser.v_agency a, agency_component_status s, application_teams_int i, function f
where agency_comp_status_id = :P3_AGENCY_COMP_STATUS_ID and a.AGENCY_ID = s.agency_id and i.application_team_id = s.application_team_id and f.function_id = i.function_id)
:P3_AGENCY_NAME := c1.agency_name;
:P3_ACTION := c1.action_ind;
:P3_FUNCTION := c1.function_type;
Session state for P3_AGENCY_COMP_STATUS_ID is RESET_TO_NULL.
What does it mean and how can I fix it?
Where is P3_AGENCY_COMP_STATUS_ID getting a value from?? If you are trying to use it to get a value for a page item, in a select statement IT MUST HAVE A VALUE, else it's null and your select is going to return a null value... Changing the select that you USE the P3_AGENCY_COMP_STATUS_ID item in is NOT going to solve your problem...
By the time they had diminished from 50 to eight, the other dwarves began to suspect "Hungry".
where agency_comp_status_id = :P3_AGENCY_COMP_STATUS_ID
When P3_AGENCY_COMP_STATUS_ID is NULL...
Your WHERE clause will always evaluate as FALSE.
You will always get 0 rows.
Remember: NULL compared to anything (even another NULL) will always evaluate as FALSE.
The "fix" depends on the business logic.
When P3_AGENCY_COMP_STATUS_ID is null, what information do you want to pull back?
Start by giving a simple CREATE TABLE and some INSERT statements to populate it.
Then describe what data you want to extract out when P3_AGENCY_COMP_STATUS_ID is NULL and when it is NOT NULL.
I am not setting it to null. I wrote my own insert statement:
l_application_team_id number := :P3_APPLICATION_TEAM_ID;
l_agency_comp_status_id number := :P3_AGENCY_COMP_STATUS_ID;
insert into application_teams_int (
returning APPLICATION_TEAM_ID into l_application_team_id;
:P3_APPLICATION_TEAM_ID := l_application_team_id;
insert into agency_component_status (
returning agency_comp_status_id into l_agency_comp_status_id;
:P3_AGENCY_COMP_STATUS_ID := l_agency_comp_status_id;
Is there anything wrong with it?