Search code examples
oraclecrystal-reports

views calls a function on a different database but crystal reports "invalid identifier" error

I've created a view (generic_view) on a database (database1) in schema (general_schema). One of the columns in this view is populated by a function (generic_function) which is stored in the same schema but on a different database (database2). I'm calling the function like:

general_schema.generic_function@database2(param1, param2, ... paramN) AS function_column

The view and function both compile, and the view generates data for the function_column function. The problem occurs when I try to use this view in crystal reports to generate a report. When I try to add the view I get the error: ORA-00904: "general_schema"."generic_function": invalid identifier

I tried removing all of the content inside the function so that the function just returns 0, but that didn't seem to help.

I'm I calling the function wrong?

Solution

  • The schema names being the same in different databases doesn't mean they're the same schema. You'll need to have a database link set up in database1 to allow access to database2, and then in database2 you'll need to be sure that the user specified in the database link has been granted EXECUTE access to general_schema.generic_function.

    Best of luck.