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?
