We created a data model and defined a condition in the sql query using the system variable ':xdo_user_roles' (which fetches all roles of the session user) and using it, searched for pre-determined roles; and only if the session user had the predetermined role, the query would run and fetch the data. This was done to incorporate data security in the sql query level for the Data Model.
Error Details: The system variable ':xdo_user_roles' is fetching the session user's roles as just one text parameter, which is often exceeding the text parameter limit whenever a user having a large number of roles assigned to them is trying to run the data model or the related reports. We are receiving an error message saying "ORA-01460: unimplemented or unreasonable conversion requested".
Have you came across any such situation or do you have a solution to it?
The format of the query is as follows:
SELECT*
FROM
(
SELECT x,y,z
FROM a, b, c
Where (condition)
)
WHERE (INSTR(LOWER(:xdo_user_roles), 'xyz_role_custom') > 0 )
