0

Can we use :xdo_user_name for filter logic with WebLogic?

I created a datamodel in oracle BI Publisher，with where clause like below:

WHERE (EMPLOYEE_NO = :xdo_user_name)

which works normally if I login with an employee account.

But I would like to make it show all data when I login as 'weblogic'，so I changed it to:

WHERE (EMPLOYEE_NO = :xdo_user_name
OR (CASE WHEN :xdo_user_name = 'weblogic' THEN 1=1 END))

Error is shown:

Character dat， right truncation occurred:for example， an update or insert value is a string that is too long for the column， or a datetime value cannot be assigned to a host variable， because it is too small.

Not so sure about why this error show up. Please advise.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Carson Hung
  • 1
  • `OR (CASE WHEN :xdo_user_name = 'weblogic' THEN 1=1 END)` is the same as `OR :xdo_user_name = 'weblogic'` ? Anyway... how is the value bound, by position or by name? If by position then you might be picking up the wrong value somewhere. – Alex Poole Oct 27 '22 at 10:04

1 Answers1

0

Try it like this...

WHERE EMPLOYEE = CASE WHEN :xdo_user_name = 'weblogic' THEN EMPLOYEE ELSE :xdo_user_name END

this way, when logged in as 'weblogic', where condition will return True for every row - otherwise it will be True just for rows where EMPLOYEE = :xdo_user_name.
Regards...

d r
  • 3,848
  • 2
  • 4
  • 15