Oracle functions working in one schema but not the other.

[esar]

Hi Experts,

We are using an oracle function last_day() in many of cognos reports. This function works fine in say database schema A. But the same function does not work when i point the same report to another schema B of the same database.

UDA-SQL-0219 The function "add_months" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.

I have been breaking my head on this since yesterday. Anyone have any thoughts?

From TOAD the function works in both the schemas.

[bdbits]

Actually, the error message is indicating a problem with add_months(). What leads you to think it is with last_day()?

A couple of thoughts... When you are in Toad, are you logging in to Oracle with the same credentials you use when running the report? Not sure about these, but some functions can be restricted by security.

This error is often due to using expressions that call database-specific functions for other than the database you are actually using, though that does not sound like the situation here. Another possibility is that you are referencing data items that are not actually in the query, if so try adding the data items to the report and see if that resolves the error. You can always hide the item if you do not want it to actually appear on the report, or remove it from the report and leave it in the query but then you may need to bind it to an item that does appear on the report (via the property "Properties").

[esar]

Failure   RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.   RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.UDA-SQL-0219 The function "last_day" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.



Sorry copied the wrong error.. The right one is above.


Just found that one of load balancing content store db server was down since yesterday. Had it brought up and the issue is gone.. Weird.. Not sure how that is related but the issue is gone.

Thankyou bdbits.