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

Question

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!

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

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.

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?

Answer 1

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.

• Then build your Oracle Connection String like the following:

    Data Source=ServerName;Unicode=True
  

  Where ServerName is the defined alias in tnsnames.ora

Read Also

• How to install Oracle Client Data Access for Microsoft Report Builder?
• How to Configure tnsnames.ora file for Visual Studio

Comments

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?