Oracle Analytics Publisher (BIP)

Products Banner

Bi Publisher Data model returning error when run, after using system variable in the sql query.

Received Response

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 x,y,z

FROM a, b, c

Where (condition)


WHERE (INSTR(LOWER(:xdo_user_roles), 'xyz_role_custom') > 0 )


  • Hi @Bishal Dutta

    Did you defied this Variable at Data model level? If the customer having more than 1 role how you are gathering during runtime?



  • Hi @Mallikarjuna Kuppauru-Oracle , yes we defined the Variable in the Data Model level.

    And regarding the role, that is where we are facing the issue. If there are limited roles provisioned to the session user, then the query is working and returns results only if the session user has the role which is predefined in the query. When the session user has more roles, the query returns an error (exceeds the limit).

    Do you have any solution to this or any alternative method to apply role-based security in the Data Model level?

    Best Regards,