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

[savitaa]

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

[Anonymous · Accepted solution]

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.

[savitaa]

Thanks so much for your feedback !

[RakeshSinghr]

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

[savitaa]

Hi RakeshSinghr ,

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

Thanks,

Savita