Home/Questions/Q 3935
Answered
Greg
Greg

Greg

View Profile
  • 1
Professional
Asked: 2020-12-04T07:06:18+05:30

ORA-00920 in SELECT clause when interrogating user role.

  • 1

I have an APEX 20.2 page region based on selecting rows from a table. This works fine, but if I want an administrator to see all records, I would have thought I could add the line:

OR apex_acl.has_user_role(:APP_ID, :APP_USER, ‘ADMINISTRATOR’)

An example would be:

select ALBUM_TITLE,

       YEAR_OF_RELEASE,

       ALBUM_ID,

       ARTIST_ID,

       ALBUM_TYPE_ID,

       COMMENTS,

       COVER_ART_BLOB_CONTENT,

       COVER_ART_MIME_TYPE

  from MUSIC_ALBUMS

  where 0=1 OR apex_acl.has_user_role(:APP_ID, :APP_USER, 'ADMINISTRATOR')

But when I do this, the query returns the error

What am I doing wrong?

 

apex

    2 Answers

    1. Vinish

      Vinish

      View Profile
      Best Answer
      Legendary

      Some inbuilt packaged functions do not work with SQL queries. You can create a function and can use it in your query. Below is an example:

      create or replace function user_has_role (i_app_id in number, i_app_user in varchar2)
return varchar2
is
l_is_admin boolean := false;
v_value varchar2(1);
begin
l_is_admin := APEX_ACL.HAS_USER_ROLE (
p_application_id => i_app_id,
p_user_name => i_app_user,
p_role_static_id => 'ADMINISTRATOR' );

if not l_is_admin then
v_value := 'N';
else
v_value := 'Y';
endif;

return v_value;

end;

      Then use the above function in your query:

      select ALBUM_TITLE,
YEAR_OF_RELEASE,
ALBUM_ID,
ARTIST_ID,
ALBUM_TYPE_ID,
COMMENTS,
COVER_ART_BLOB_CONTENT,
COVER_ART_MIME_TYPE
from MUSIC_ALBUMS
where 0=1 OR user_has_role(:APP_ID, :APP_USER) = 'Y'
      • 0

    You must login to add an answer.