Oracle SP calling with date parameter in Power BI Desktop.

[sahubibhuti45]

Hi Team,

I wanted to call an Oracle Stored Procedure with date parameter input from my desktop, can anyone help in here.

Thanks

[v-nmadadi-msft]

Hi @sahubibhuti45 

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions  provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

[v-nmadadi-msft]

Hi @sahubibhuti45 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @sahubibhuti45 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-nmadadi-msft]

Hi @sahubibhuti45  ,
Thanks for reaching out to the Microsoft fabric community forum.

Before you can connect to an Oracle database using Power Query, you need to install the Oracle Client for Microsoft Tools (OCMT). To connect to an Oracle database with the on-premises data gateway, 64-bit OCMT must be installed on the computer running the gateway.
Source: Power Query Oracle database connector - Power Query | Microsoft Learn

Once this is done you can follow the instructions provided by @rohit1991   to call a Stored Procedure with date parameter input.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[rohit1991 · Accepted solution]

Hi @sahubibhuti45 

In Power BI Desktop you can call an Oracle Stored Procedure with a date parameter using a query like this:

EXEC MyPackage.MyProcedure(TO_DATE('2024-01-01','YYYY-MM-DD'))

Steps:

1. Get Data >> Oracle database.

2. In the SQL statement box, paste your EXEC command.

3. To make the date dynamic, create a Power Query parameter (Manage Parameters >> type Date) and replace the hardcoded date with:

EXEC MyPackage.MyProcedure(TO_DATE(' " & Date.ToText(ParameterDate,"yyyy-MM-dd") & " ','YYYY-MM-DD'))

This way you can pass a date parameter from Power BI into your Oracle SP.

https://learn.microsoft.com/en-in/power-query/power-query-query-parameters