Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need help with executing an oracle stored procedure that simply returns few columns.
I'm getting "Oracle:ORA00900: invalid SQL statement" error.
I'm using ref cursor - see below code - throws an error. It works well in sql developer.
CREATE OR REPLACE PROCEDURE schemax.TestSP
AS
c1 SYS_REFCURSOR;
BEGIN
open c1 for
SELECT Col1,Col2
FROM LinkDBServer;
DBMS_SQL.RETURN_RESULT(c1);
END;
execute schemax.TestSP
Using Pipened functions - there's no error in Powerbi desktop
CREATE OR REPLACE TYPE t_type
AS OBJECT
(
col1 NUMBER(22,14) NULL,
col2 VARCHAR2(120)
);
CREATE OR REPLACE TYPE t_table_type
AS TABLE OF t_type;
CREATE OR REPLACE FUNCTION fnc_Get_t_Pipelined
RETURN t_table_type
PIPELINED
AS
BEGIN
FOR v_Rec IN (SELECT Col1, Col2 FROM LinkDBServer where rownum<=10) LOOP
PIPE ROW (t_type(v_Rec.Col1, v_Rec.Col2));
END LOOP;
RETURN;
END;
SELECT Col1,Col2 FROM TABLE(fnc_Get_t_Pipelined());
Any suggestions on how to execute an oracle proc within Power BI desktop pls?
Solved! Go to Solution.
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.
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, 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?
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 .
Did you really get this to work from Power BI service via the Gateway?? If so, how did you do it??
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
thank you for the response Rakesh. As another poster, Chuy, responded with (we work in the same organization and he is assisting me with trying to troubleshoot the error). The report and SP executes using the gateway credentials. But we get the error below when refreshing the data set after the report has been published to Power BI Service using the gateway:
The error message we get in Power BI service is:
The '<pii>Column Name</pii>' column does not exist in the rowset. Table: Query1
I have also read that some versions of Oracle client do not support sys_refcursors -- could this be the issue for our Gateway server? Is there any other Gateway server configuration that needs to be enabled? This report runs and refreshes without error in Power BI Desktop on multiple machines.
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: Query1
And 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)
I have recently had this issue and opened a case with Microsoft -- it is a known limitation at present; but did receive feedback just this week from the Power BI Engineering group that they are going to add this functionality gap as a feature request to be included on their roadmap -- no ETA as of yet though on when this will be addressed.
Hey @klinejordan ! No, this is a known limitation that is well described on the online documentation, I just couldn't find that document before opening my question in here.
Mark Vaillancourt pointed me out to the document where this is disclosed:
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
HI @RakeshSinghr,
What connection mode are you works? AFAIK, you can't invoke stored procedures in SQL statements when you work on direct query mode.
Regards,
Xiaoxin Sheng
Hi @RakeshSinghr ,
maybe this blog post will help.
https://hatfullofdata.blog/power-query-function-to-execute-a-procedure/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |