Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
7.4k views
in SQL Server Reporting Services by 12 12 19

I am using Oracle 11g as an Oracle Database server and I am trying to connect to one of Oracle Database from Microsoft Report Builder through the Oracle Provider for OLE DB, everything was working as expected until I tried to use Stored Procedure as Query type in SSRS DataSet, and I got this error ORA-06550: line 1, column 7: PLS-00201: identifier must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored.

In the Datasource setting, I am using Oracle Provider for OLD BD that connects successfully!

Oracle Provider for OLED to read stored procedure in SSRS Report Builder

Also, it's the DataSet settings where the Stored Procedure List is not listed in SSRS Microsoft Report Builder as displayed below

Stored Procedure List is not listed in SSRS Microsoft Report Builder

And when I typed the Stored procedure name manually, then click refresh fields, I got this error

ORA-06550: line 1, column 7: PLS-00201: identifier must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored.

ORA-06550: line 1, column 7: PLS-00201: identifier must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored.

Below is a copy from my Oracle stored procedure that I am trying to call in SSRS Report Builder?

create or replace NONEDITIONABLE PROCEDURE PROCEDURE2(cursorParam OUT SYS_REFCURSOR,icode in VARCHAR )
 IS
  BEGIN
   OPEN cursorParam FOR
    SELECT *  from SYSB.Table1 where IDCODE = icode;
END ;

The above Oracle Stored Stored run successfully in Oracle DB Server but I can't use it in SSRS report Builder, WHY?


1 Answer

1 like 0 dislike
by 158 185 373
selected by
 
Best answer

First of all, you should be aware of the Oracle Provider for OLE DB DOESN'T support executing the Oracle Database Stored Procedure.

How to use Oracle Stored Procedure in SSRS?

To can use an Oracle Stored Procedure in Microsoft Report Builder,

  • You have to define the data source in SSRS Report Builder to use the Oracle Database Connection type instead of OLE DB connection type.

Use Oracle Stored Procedure in SSRS

Read Also

by 12 12 19
0 0
Thanks Mohamed, it's very helpful for me, but I can't use Oracle Database instead of OLEDB, I got this error "The selected data extension ORACLE is not installed or cannot be loaded.  Verify that the selected data extension is installed on the client for local reports and on the report server for published reports." Can you help me on this please?
If you don’t ask, the answer is always NO!
...