Forum Discussion
Solved
Oracle Stored Proc in PBI Desktop
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 co...
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.
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
How we use the SP contains in and out param.