APEX

ORA-01403: no data found Problem when using AUTOMATIC ROW FETCH to populate a form.

1) Created a FORM on EMP using the wizards. This creates an AUTOMATIC ROW FETCH
TABLE NAME - EMP
Item Containing PRIMARY KEY - P2099_EMPNO
Primary key column - EMPNO

By default the automatic fetch has a ‘Process Error Message’ of ‘Unable to fetch row.’

2) Created a HTML region. Within this region add
text item P2099_FIND_EMPNO
Button GET_EMP to submit
Branch Modified the conditional branch created during button creation to set P2099_EMPNO with &P2099_FIND_EMPNO.

If I then run the page, enter an existing employee number into P2099_EMPNO and press the GET_EMP button the form is populated correctly. But if I enter an employee that does not exist then I get the oracle error ORA-01403: no data found and no form displayed but a message at the top of the page ‘Action Processed’.I was expecting a blank form to be displayed with the message ‘Unable to fetch row.’

I can work around this by making the automated fetch conditional so that it checks the row exists first. Modify the Fetch row from EMP automated fetch so that it is conditional
EXIST (SQL query returns at least one row)

select 'x'
from EMP
where EMPNO = :P2099_EMPNO


But this means that when the employee exists I must be fetching from the DB twice, once for the condition and then again for the actual row fetch.

Rather than the above work around is there something I can change so I don’t get the Oracle error? I’m now wondering if the automatic row fetch is only supposed to be used when linking a report to a form and that I should be writing the fetch process manually. The reason I haven’t at the moment is I’m trying to stick with the automatic wizard generation as much as I can.

Any ideas?

Thanks Pete