Hello,
I'm using Apex 4.2.5.00.08 and a database of 11.g on a Windows 10 platform.
I have an existing page validation that works well. The validation looks at the table lit_auth to see if the user is valid to edit this time slot. The column lit_time in the table lit_auth has values like 10:00 AM, 04:00 PM, etc.
| Declare |
| | l_requested_datetime timestamp; |
| | l_requested_time varchar2(256); |
| | l_has_auth varchar2(1); |
| Begin |
| | Select lit_date into l_requested_datetime from lit_event |
| | Where prim_key = :P27_lit_event_fkey; | |
| | l_requested_time := to_char(l_requested_datetime, 'HH:MI AM'); |
| | Select 'x' into l_has_auth from lit_auth |
| | Where lit_time = l_requested_time |
| | And username =:APP_USER; |
| | return true; |
| | Exception |
| | When no_data_found then |
| | return false; |
| End; |
Now I'd like to enhance the validation to allow for the prior conditions and also allow users that are a music_supervisor. Those users are stored in a table LIT_LOOKUP. The column lu_type would have to equal 'music_supervisor' and the lu_value would have to equal the Username.
I tried a few different approaches, the last shown below, but Apex gives me an error "ORA-01422: exact fetch returns more than requested number of rows"
I know that the users are not duplicated between the LIT_LOOKUP and LIT_AUTH tables. I can't figure out how to put an OR condition in a Page Validation / Function Returning Boolean.
| Declare |
| | l_requested_datetime timestamp; |
| | l_requested_time varchar2(256); |
| | l_is_supervisor varchar2(1); |
| | l_has_auth varchar2(1); |
| Begin |
| | Select lit_date into l_requested_datetime from lit_event |
| | Where prim_key = :P27_lit_event_fkey; | |
| | l_requested_time := to_char(l_requested_datetime, 'HH:MI AM'); |
| | Select 'x' into l_is_supervisor from LIT_LOOKUP HL |
| | Where hl.lu_type = 'music_supervisor' |
| | And hl.lu_value =:APP_USER; |
| | Select 'x' into l_has_auth from LIT_AUTH HA |
| | Where (ha.lit_time = l_requested_time |
| | And ha.username =:APP_USER) |
| | Or (l_is_supervisor = 'x'); |
| | If l_has_auth = 'x' |
| | Then return true; |
| | Else return false; |
| | End If; |
| End; |
Thanks for looking at this.