How to call/execute a oracle stored procedure from powerbi desktop

[RakeshP]

Hi All,

I am able to see the tables in my database to prepare data for my report in PBID.But i have stored procedure in my oracle database which i want to use the result of that stored procedure as data for the report.

How can i call/execute from PBID.

Any ideas please. thanks in advance.

Regards,

Rakesh.P

[Neero]

Hi, in  powerbi desktop my stored procedure works fine and I can get data to  my report through SQL SP, But When I published the report it doesn't refresh the data (Looks like the stored procedure doesn't work) .

[greggyb]

Do you have the Personal or Enterprise Gateway set up to allow access to on-prem data sources?

[Neero]

I'm using Enterprise Gateway, any way I used the Import machanism to get data from the SP, then it works fine but the issue is, then I have to shedule the data refresh method, But I need real time data to be displayed, for that I need to use Direct Query but I cannot use Direct Query with a SP , is there any way that I can use a Stored Procedure to pull data with Direct Query,

The error I get is " Microsoft SQL:Incorrect syntax near the  keyword 'EXEC' "

[greggyb]

Direct Query requires that you connect to views or tables that exist in the RDBMS. There is a DAX-to-SQL translation layer that transforms the DAX queries generated by the Power View query engine into SQL.

There is no currently supported way of combining Direct Query with a stored procedure. Can you schedule the stored procedure to run on the server every 5 minutes (or even 1 minute) and dump its output into a physical table, then use Direct Query against that?

This might require assistance from the DBAs at your organization, but is the only way I can think of to get what you want.

[Neero]

Thanks a lot for the comments, and I think I will do some workaround as you mentioned

[RakeshP]

Not able to call/execute ORACLE stored procedure.

Are there any one who sucessfully call/executed ORACLE stored procedure and used the ouptut as data in power bi??

[RakeshP]

Are there any one?

[yscheng]

Yes, I have sucessfully invoked ORACLE stored procedures and used th return result sets as data in Power BI.

For instance, my Oracle package/sp code:

CREATE OR REPLACE PACKAGE BODY MY_SCHEMA.MY_PACKAGE IS

PROCEDURE MY_STORED_PROCEDURE
(
    inParam1    DATE,
    inParam2    PLS_INTEGER,
    RC1         OUT SYS_REFCURSOR
) IS
BEGIN
    ...

    OPEN RC1 FOR
    SELECT ...;
END MY_STORED_PROCEDURE;

END MY_PACKAGE;

Then, following is all my Power Query code:

let
    Source = Json.Document(Web.Contents("http://dbwebapi.mysite.com/oradev/my_schema.my_package.my_stored_procedure/json?inParam1=2016-04-22&inParam2=100")),
    ResultSet1 = Table.FromRecords(Source[ResultSets]{0})
in
    ResultSet1

To get this point, I installed a generic Web API proxy for Oracle which automatically translate between Oracle stored procedures and Web API, it requires no coding and no configuration (except the database connection string). You can have a look at https://github.com/DataBooster/DbWebApi/wiki and focus on https://github.com/DataBooster/DbWebApi#power-query-client.The installation of the proxy service can be downloaded from https://dbwebapi.codeplex.com/releases.

Hope this would help.

[Neero]

Thanks a lot for the comments, and I think I will do some workaround as you mentioned

[itchyeyeballs]

Is it not feasible to convert the SP to a function to get around this?

[greggyb]

When importing from any RDBMS source, you should see an option called "SQL Statement (optional)". You can execute arbitrary SQL from this screen, including calling a stored procedure to populate the table with data.

[Anonymous]

Hi.

I have a stored procedure in oracle DB. I need to call it in power bi.

This is what i have used in the direct query mode :- 

EXEC NV661_1674_VG.apps.xxnao_map_user_apps_init ('NV661_1674_VG', 'SERVER1\JADMIN', 'Tableau_Mapping_user'))

I have used call,execute also instead of EXEC. But still it is not working.

Please help.

Regards,

Malvika

[RakeshP]

Hi Greg,

I have tried to do this but not successful. My oracle stored prcoedure returns a refcursor. No inputs for the procedure.

How can i  call this from SQL Statement(Optional). 

Can you please help.

Regards,

Rakesh.P

[itchyeyeballs]

@RakeshP what code did you use? If you used "execute" try "call" instead.

Alternativley it may be worth converting the stored procedure into a function (depending on what it does exactly) within a new view, this could be referenced directly by PBI.

[Anonymous]

Hi,

When I tried to call a function or stored procedure. It throws error like "ORA-06576": not a valid function or procedure name