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'

I finally got around to trying this but it still did not work. I think the problem is I am not submitting the page, but issuing explicit PL/SQL code to update the database. I have now tried 2 other options posted by Vinnish (Oracle Apex Validation Without Submit Using JavaScript and Ajax Callback) but they did not provide joy either.

The current situation (using Vinnish’s AJAX method) is that the buttons return an error message saying that it cannot insert the record with a null value for such and such column (standard ORACLE error message), THEN  it displays the user-friendly error message, even though the actions generate the user-friendly message before it tries to insert the record.

The first action reads as follows:
apex.server.process('validate_comment',
{
pageItems : '#P11_NEW_POST, #P11_SELECTED_NODE'
}
,
{
dataType : 'text', success : function(data)
{
if(data != 'SUCCESS') apex.message.alert(data);
}
}
)

The second action performs the insert.

How do I get the first action to abort on error?

Postscript: I have made this work by changing the INSERT code so it only attempts an INSERT if the 2 items are not null. That way, I only get to see the user-friendly error message.

Is there a neater way of doing this?