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

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

 

 

dludw0969's avatar
dludw0969
Frequent Visitor
4 years ago

Hi RakeshSinghr, 

Your answer below worked for me for the stored procedure, thank you for posting.   Can you please post an example if I want to have a  select from a table with where clause  in the SQL Statement box under Advanced Option when Get Data from Oracle?         Just fyi I am joinging several tables in this select statement and returning rows .