Forum Discussion
Oracle 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.
Can you please share more info on how to call oracle stored procedure with sys_refcursor from power bi
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?
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
Yes, I have tested running this SP using the credentials we use on the Data Source on the Gateway and is able to run/render data. The error message we get in Power BI service is:
The '<pii>Column Name</pii>' column does not exist in the rowset. Table: Query1
It seems like it is not able to "catch" the final record-set generated by the SP.
Do you remember doing anything else in specific either on your SQL script or on settings on the Oracle Client installed on the Gateway server?
Thanks...
I am having this same issue, error message is:
The '<pii>Column Name</pii>' column does not exist in the rowset. Table: Query1And I have exhausted all possible Google searches trying to find a solution for this.
Any feedback on this will be appreciated!
Anonymous did you ever get this working (calling Oracle stored procedure through gateway)
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
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??
Hi RakeshSinghr,
Your answer below worked for me for the stored procedure, thank you for posting. Can you please post an example if I want to have a select from a table with where clause in the SQL Statement box under Advanced Option when Get Data from Oracle? Just fyi I am joinging several tables in this select statement and returning rows .
Rakesh, it's working perfect in Import mode. when I used the same syntax in Direct mode, it's giving syntax error like right paranthesis missing.
How we can call in Direct Query mode?
sravanpesari were you able to resolve the issue. I am facing the same issue in directquery mode. Do you have any suggestions
How we use the SP contains in and out param.
