Search code examples
oracleoracle-apexapex

using :APP_USER in a query in apex application

I have a query in my apex page and I want to see the result of this query in the page:

    select locs.LOCATION_DESCRIPTION "LOCATION", rsrv.Room_ID "Room Number", 
    rsrv.WORKPLACE_ID "Desk Number", rsrv.RESERVATION_DATE "Date", rsrv.TIME_FROM 
    "From", rsrv.TIME_TO "To"
       FROM WPB_RESERVATIONS rsrv, WPB_USERNAMES usrs, WPB_LOCATIONS locs
          WHERE upper(usrs.APEX_USERNAME) = upper(:APP_USER)
               AND (upper(rsrv.FIRST_NAME) = upper(usrs.FIRST_NAME) AND upper(rsrv.LAST_NAME) = 
    upper(usrs.LAST_NAME))
               AND rsrv.LOC_ID = locs.LOC_ID
               AND rsrv.RESERVATION_DATE >= SYSDATE-1

but it shows nothing. IT seems this part doesnt work:

    WHERE upper(usrs.APEX_USERNAME) = upper(:APP_USER)

because when I change it to this I can see the results that I want:

    WHERE upper(usrs.APEX_USERNAME) = 'MS'

I've read all topics about using :APP_USER or v(APP_USER) and tried different solutions, but non of them works.

Solution

  • Well, if it works when you hardcode 'MS' and it does not work when you do UPPER(:APP_USER) that indicates that those 2 values are not the same. You have not mentioned what the value for the APP_USER is. Best way to check is to open the session state window from the developer toolbar and check what the value for "User" is.