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.

RakeshSinghr's avatar
RakeshSinghr
Icon for Resolver I rankResolver I
5 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
5 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...