Unable to execute oracle stored procedure from PowerBI desktop

[MSSRamarao]

Unable to execute oracle stored procedure from PowerBI desktop

I am calling as below in Sql Statement box:

CALL P_GET_BRMS_MEMBER_AUDIT_DETAILS_BIP ('1259024')

Error: "This native database query isn't currently supported."

Please help me with correct syntax.

Also is there a way to create parameter (like free text) on the power Bi report to accept user input and pass it to query 

[ToddChitt]

Can we asume that this statement, when run in an Oracle tool, returns rows as expected? And are you using the native Oracle connector? Or Oracle ODBC? If ODBC it may not be allowed.

[MSSRamarao]

Yes, the query is running fine in Oracle tool and returns rows as expected..

We are using Oracle Client 12c (64-Bit) 12.1.0.2  to connect to Oracle db from Power BI Desktop. Can you please advise how to verify if I am using native oracle connector or ODBC.

Also, how can i add parameter (like Customer Id) to the report. User has to enter user input and pass it to query dynamically.

[ToddChitt]

When you first set up the Query from Get Data in Power BI, if you selected ODBC then you are using ODBC. Sounds like not.

I know in SQL I can do the same thing with an EXEC <stored procedure name> with something like this:

EXEC [dbo].[usp_UsersByReputation] 2

and the Power Query M script it generates is this:

let
      Source = Sql.Database("localhost", "StackOverflow2013", [Query="EXEC [dbo].[usp_UsersByReputation] 2"])
in
      Source

Notice tha the input parameter (2) is enclosed in quotes. So if I wanted to substitute that for a Power Query Parameter, that snipet would be:

[Query="EXEC [dbo].[usp_UsersByReputation] " & #MyInputParameter ]

Not sure what to tell you about your inability to run stored procedures in Oracle. Sorry.

[MSSRamarao]

I am able to execute the oracle stored procedure with parameter using OLEDB connection like below:

= OleDb.DataSource("provider=OraOLEDB.Oracle.1;data source=ep25-scan01:1521/EDBPR03svc.uhc.com", [Query="CALL P_GET_BRMS_MEMBER_AUDIT_DETAILS_BIP ("& Per_Id &")"])

Only question left is:

How can I make this parameter visible on the report so that  end user can enter a value and run the report.