Forum Discussion
Resolver IOracle Stored Proc in PBI Desktop
For anyone in the same boat as me - Oracle stored proc with ref cursors can be executed within power bi desktop. It just need to be run with cursor variable declaration.
Resolver IFor anyone in the same boat as me - Oracle stored proc with ref cursors can be executed within power bi desktop. It just need to be run with cursor variable declaration.
Helper IVCan you please share more info on how to call oracle stored procedure with sys_refcursor from power bi
- RakeshSinghr
Within Oracle:
CREATE OR REPLACE PROCEDURE [SCHEMA].[STOREDPROCNAME]
( P_RC OUT SYS_REFCURSOR )
AS
BEGIN
OPEN P_RC FOR SELECT COL1,COL2,COL3 BLAH BLAH BLAH
END;
Within Power BI Desktop:
DECLARE P_RC SYS_REFCURSOR;
BEGIN
[SCHEMA].[STOREDPROCNAME](P_RC);
DBMS_SQL.RETURN_RESULT(P_RC);
END;
Hope this helps.
Regards
Rakesh Singh
Rakesh --
I am successfully calling an Oracle stored procedure returning a sys_refcursor in Power BI Desktop; but when I publish my report to Power BI Service using an enterprise gateway (premium workspace), the dataset errors trying to refresh saying the column doesn't exist in the rowset. Have you been able to refresh your report in Power BI Service calling an Oracle stored proc returning a sys_refcursor?
- RakeshSinghr
Yes, it worked for me over gateway. There is no reason why it will work in power bi desktop but not via gateway. Execute the stored proc using gateway datasource account credentials to see if there are any permission issues
- siva_powerbi
Hi Rakesh
Thanks for the Code.
I am not getting any error during connection but when tried to save changes, getting error missing right paranthasis. Checked the syntax in advanced editor and everything looks correct but still I can see error.
Any ideas.
Thanks
siva
- siva_powerbi
Thanks Rakesh,
Code is working, didn't do anything special just restarted the Power BI.
Thanks
Siva
Did you really get this to work from Power BI service via the Gateway?? If so, how did you do it??
