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
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.

  • siva_powerbi's avatar
    siva_powerbi
    Icon for Helper IV rankHelper IV
    6 years ago

    Can you please share more info on how to call oracle stored procedure with sys_refcursor from power bi

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

      Within Oracle:

      CREATE OR REPLACE PROCEDURE [SCHEMA].[STOREDPROCNAME]

      ( P_RC OUT SYS_REFCURSOR )

      AS

      BEGIN

      OPEN P_RC FOR SELECT COL1,COL2,COL3 BLAH BLAH BLAH

      END;

       

      Within Power BI Desktop:

       

      DECLARE P_RC SYS_REFCURSOR;

      BEGIN

      [SCHEMA].[STOREDPROCNAME](P_RC);

      DBMS_SQL.RETURN_RESULT(P_RC);

      END;

       

      Hope this helps.

       

      Regards

      Rakesh Singh

       

       

      • Sandie's avatar
        Sandie
        New Member
        5 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?