Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RakeshP
Helper I
Helper I

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

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

15 REPLIES 15
Neero
New Member

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
Resident Rockstar
Resident Rockstar

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

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
Resident Rockstar
Resident Rockstar

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.

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

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

Are there any one?

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.

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

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

greggyb
Resident Rockstar
Resident Rockstar

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
Not applicable

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

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

@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
Not applicable

Hi,

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors