topic Re: How to call Oracle stored procedure from Power BI report builder(Paginated Reports) in Developer

How to call Oracle stored procedure from Power BI report builder(Paginated Reports)

savitaa — Thu, 24 Sep 2020 13:33:31 GMT

Hi All,

I need to call an Oracle Stored Procedure from Power BI report builder(Paginated Reports) . I have created the oracle data source sucessfully. I am able to run oracle sql queries as well but I am having issues calling oracle SP. Could any anyone please let me know the steps and possible provide an example?

Thanks,

Savita

Re: How to call Oracle stored procedure from Power BI report builder(Paginated Reports)

RakeshSinghr — Thu, 24 Sep 2020 21:47:26 GMT

hope this helps - https://community.powerbi.com/t5/Desktop/Oracle-Stored-Proc-in-PBI-Desktop/m-p/1318697#M571159

Re: How to call Oracle stored procedure from Power BI report builder(Paginated Reports)

savitaa — Fri, 25 Sep 2020 01:55:37 GMT

Hi RakeshSinghr ,

My question is about Power BI report builder for paginated reports and not Power bi Desktop.

Thanks,

Savita

Re: How to call Oracle stored procedure from Power BI report builder(Paginated Reports)

Anonymous — Mon, 22 Nov 2021 13:28:37 GMT

Okay, I was running into this same issue with Paginated reports and this is what I have learned.

Oracle 12c you can use the DBMS_SQL.RETURN_RESULT(CursorName)
Orace 11g its not easy at all or very easy depending how you look at it.
- Just write a sql statement, yes this can be a maintanence nightmare, but you work with what they give you.
- Write a function that returns a cursor the use:
  - select extractvalue(column_value,'/ROW/LAST_NAME') LAST_NAME from table(xmlsequence(FunctionName(param)))
  - The above is slow and returns everything as a varchar

For 11g I tried to use pipeline functions, but it still doesn' work once I publish the report and it has to use the on premise gateway. I am making one last ditch effort to see if the PBI Admins installed the ODP.net correctly on those gateway servers.

I hope this helped.

Re: How to call Oracle stored procedure from Power BI report builder(Paginated Reports)

savitaa — Mon, 22 Nov 2021 14:14:52 GMT

Thanks so much for your feedback !