Share via

SSRS report with oracle source - fail on functions

KS_23 0 Reputation points
2023-02-03T11:00:54.29+00:00

Hi,

Thanks for your time.

I'm new to ssrs reports and involved in ssrs server & underlying oracle db migration.

the old versions: Oracle db v12, ssrs report builder v12, oracle client (TBC)

the new version: Oracle db v19, ssrs report builder v15, oracle client OLEDB

the sql objects are fairly in sync and a simple ssrs report with a select statement works well.

however, in the earlier setup, the source for most of datasets are Oracle Functions and these are working well.

In the new setup, these reports fail with Oracle error:

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for dataset 'dataset1'. (rsErrorExecutingCommand)

ORA-06550: line1, column 7: PLS-00221: 'GETREGIONGROUPS' is not a procedure or is undefined ORA-06550: line1, column 7: PL/SQ: Statement Ignored.

For an example, the underlying function is like

create or replace Function Function1 return sysrefcursor as cur sysrefcursor;

begin

*open cur for select * from table1 where...*

*return cur*

end;

It's possible i might have missed some essential information, happy to add as necessary.

Thanks

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,878 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AniyaTang-MSFT 12,421 Reputation points Microsoft Vendor
    2023-02-06T02:01:24.61+00:00

    Hi @KS_23

    From what I've searched, a common reason for the error "'GETREGIONGROUPS' is not a procedure or is undefined" to appear is a typo, or it's actually a stored function, not a stored procedure, check those. For more about these, you can refer to this link.

    According to your description, you have adopted the OLE DB connection type, maybe you can check whether the connection steps are correct, you can refer to this document: OLE DB Connection Type (SSRS).

    I'm sorry that I don't know much about oracle, I can only provide you with this information, maybe you can go to oracle-related platforms for help.

    Best regards,

    Aniya

    0 comments