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 code - throws an error. It works well in sql developer.

 

 

CREATE OR REPLACE PROCEDURE schemax.TestSP
AS
c1 SYS_REFCURSOR;
BEGIN

open c1 for
SELECT Col1,Col2
FROM LinkDBServer;
DBMS_SQL.RETURN_RESULT(c1);

END;

execute schemax.TestSP

 

Using Pipened functions - there's no error in Powerbi desktop

CREATE OR REPLACE TYPE t_type
AS OBJECT
(
col1 NUMBER(22,14) NULL,
col2 VARCHAR2(120)
);


CREATE OR REPLACE TYPE t_table_type
AS TABLE OF t_type;

 

CREATE OR REPLACE FUNCTION fnc_Get_t_Pipelined
RETURN t_table_type
PIPELINED
AS
BEGIN

FOR v_Rec IN (SELECT Col1, Col2 FROM  LinkDBServer where rownum<=10) LOOP

PIPE ROW (t_type(v_Rec.Col1, v_Rec.Col2));

END LOOP;

RETURN;
END;

SELECT Col1,Col2 FROM TABLE(fnc_Get_t_Pipelined());

 

Any suggestions on how to execute an oracle proc within Power BI desktop pls?

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.

20 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI RakeshSinghr,

    What connection mode are you works? AFAIK, you can't invoke stored procedures in SQL statements when you work on direct query mode.
    Regards,
    Xiaoxin Sheng

  • 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