Forum Discussion

RakeshSinghr's avatar
RakeshSinghr
Icon for Resolver I rankResolver I
6 years ago
Solved

Oracle Stored Proc in PBI Desktop

Hi,       I need help with executing an oracle stored procedure that simply returns few columns. I'm getting "Oracle:ORA00900: invalid SQL statement" error.   I'm using ref cursor - see below co...
Need Help
  • RakeshSinghr's avatar
    RakeshSinghr
    6 years ago

    For anyone in the same boat as me - Oracle stored proc with ref cursors can be executed within power bi desktop. It just need to be run with cursor variable declaration.

Sandie's avatar
Sandie
New Member
4 years ago

Rakesh --

 

I am successfully calling an Oracle stored procedure returning a sys_refcursor in Power BI Desktop; but when I publish my report to Power BI Service using an enterprise gateway (premium workspace), the dataset errors trying to refresh saying the column doesn't exist in the rowset.  Have you been able to refresh your report in Power BI Service calling an Oracle stored proc returning a sys_refcursor?

RakeshSinghr's avatar
RakeshSinghr
Icon for Resolver I rankResolver I
4 years ago

Yes, it worked for me over gateway. There is no reason why it will work in power bi desktop but not via gateway. Execute the stored proc using gateway datasource account credentials to see if there are any permission issues

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Yes, I have tested running this SP using the credentials we use on the Data Source on the Gateway and is able to run/render data. The error message we get in Power BI service is:
        The '<pii>Column Name</pii>' column does not exist in the rowset. Table: Query1

    It seems like it is not able to "catch" the final record-set generated by the SP.

    Do you remember doing anything else in specific either on your SQL script or on settings on the Oracle Client installed on the Gateway server?

    Thanks...

  • Sandie's avatar
    Sandie
    New Member
    4 years ago

    thank you for the response Rakesh.  As another poster, Chuy, responded with (we work in the same organization and he is assisting me with trying to troubleshoot the error).  The report and SP executes using the gateway credentials.  But we get the error below when refreshing the data set after the report has been published to Power BI Service using the gateway:

     

    The error message we get in Power BI service is:
        The '<pii>Column Name</pii>' column does not exist in the rowset. Table: Query1

    I have also read that some versions of Oracle client do not support sys_refcursors -- could this be the issue for our Gateway server?  Is there any other Gateway server configuration that needs to be enabled?  This report runs and refreshes without error in Power BI Desktop on multiple machines.