Oracle SP with out ref cursor not working with SSRS server

harshal1788 1 Reputation point
2021-03-04T21:57:19.493+00:00

am trying to build SSRS report which using Oracle SP as backend to get data from Oracle DB. SP have only one input parameter & out put parameter ref cursor. On my client machine this is working fine but once I deployed on SSRS server it giving me error.
But same SP without any parameter or with one input parameter working on SSRS server
ODAC Driver Installed on SSRS server.
Error Message:
System.Data.OracleClient.OracleException (0x80131938): ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'get_name'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

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,870 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Joyzhao-MSFT 15,571 Reputation points
    2021-03-05T03:57:07.413+00:00

    Hi @harshal1788 ,
    You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred.The error will occur: ORA-06550: line num, column num: str
    Try the following steps:

    1.Refer to the line and column numbers (in the error message) to find the compilation error and correct it. Then try recompiling your code.
    Let's look at an example of how to resolve an ORA-06550 error. For example, if you created a procedure called TestProc as follows:

    SQL> CREATE OR REPLACE PROCEDURE TestProc  
      2  AS  
      3    vnum number;  
      4  BEGIN  
      5    vnum := vAnotherNum;  
      6  END;  
      7  /  
    
    ---Warning: Procedure created with compilation errors.  
    

    2.This procedure was created with compilation errors. So if we try to execute this procedure, we will get an ORA-06550 error as follows:

    SQL> execute TestProc();  
    BEGIN TestProc(); END;  
    
    *  
    ERROR at line 1:  
    ORA-06550: line 1, column 7:  
    PLS-00905: object EXAMPLE.TESTPROC is invalid  
    ORA-06550: line 1, column 7:  
    PL/SQL: Statement ignored  
    

    3.You can run the SHOW ERROR command to view the errors as follows:

    SQL> show error procedure TestProc;  
    Errors for PROCEDURE TESTPROC:  
    
    LINE/COL ERROR  
    -------- -----------------------------------------------------------------  
    5/1  PL/SQL: Statement ignored  
    5/9  PLS-00201: identifier 'VANOTHERNUM' must be declared  
    

    4.As you can see, the error is caused by the variable called VANOTHERNUM not being declared. To resolve this error, we can modify our TestProc procedure to declare the variable as follows:

    SQL> CREATE OR REPLACE PROCEDURE TestProc  
      2  AS  
      3    vnum number;  
      4    vAnotherNumber number;  
      5  BEGIN  
      6    vAnotherNum := 999;  
      7    vnum := vAnotherNum;  
      8  END;  
      9  /  
    
    Procedure created.  
    

    5.And now when we execute our TestProc procedure, the ORA-06550 error has been resolved.

    SQL> execute TestProc();  
    
    PL/SQL procedure successfully completed.  
    

    Best Regards,
    Joy


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Olaf Helper 43,166 Reputation points
    2021-03-05T06:49:20.837+00:00

    out put parameter ref cursor

    That's not supported by SSRS, it requires a plain record set.

    0 comments No comments