Crystal Reports XI with Oracle Function wm_concat()

Question

Within Crystal Reports XI I'm trying to have a SQL Expression Field with Oracle 10g function wm_concat(column_name). It appears that Crystal Reports XI is not handling this function properly. If I execute the function wm_concat() within the database, it works fine, however, when trying to use it in Crystal Reports it displays a blank page. Whats interesting is that this field is within a sub-report, yet the whole report displays as a blank page.

Crystal Reports can tell at design-time that this function is valid (It doesn't let you save the SQL Expression if its not valid). So its a runtime error. I have also tested using various Oracle functions such as TO_CHAR() and REPLACE() and these all work fine with Crystal Report SQL Expressions. Any ideas?

Answer 1

WM_CONCAT isn't a built-in function, and isn't necessarily installed with a database. You could try WMSYS.WM_CONCAT, as the function is actually owned by the WMSYS schema.

You may need to add a specific grant. By default, if it is there, it has EXECUTE granted to PUBLIC but your DBA may have revoked that and replaced it with specific grants.

Answer 2

I couldn't get WM_CONCAT to work; Since the queries execute flawless in Oracle, but not in Crystal Reports I would assume it is something within Crystal Reports. I ended up having to make a PL/SQL Stored Procedure and call it from the Crystal Report. I called the Stored Procedure from a SQL Expression Field within Crystal Reports.