ERROR Getting Data From Oracle function ora-00933

[wireless90]

Hi guys help needed. So I tried getting data from Oracle sql via the function below.

Error: We encountered an error while trying to connect. SQL Command not properly ended

SQL: Select * From Table(TESTPOWERBI);

DROP TYPE VW_PEOPLE_TABLE;

DROP TYPE VW_PEOPLE_TYPE;

CREATE OR REPLACE TYPE VW_PEOPLE_TYPE AS OBJECT(NAME VARCHAR2(70), ALIAS VARCHAR2(90));
/
CREATE OR REPLACE TYPE VW_PEOPLE_TABLE AS TABLE OF VW_PEOPLE_TYPEL
/
CREATE OR REPLACE FUNCTION TESTPOWERBI RETURN VW_PEOPLE_TABLE
PIPELINED
AUTHID CURRENT_USER
AS
VWT VW_PEOPLE_TABLE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	SELECT
		VW_PEOPLE_TYPE(NAME, ALIAS)
	BULK COLLECT
	INTO VWT
	FROM MYDATABASE;

	FOR i in 1 ... VWT.COUNT
	LOOP
		PIPE ROW (VW_PEOPLE_TYPE(VWT(i).NAME, VWT(i).ALIAS));
	END LOOP;


END TESTPOWERBI;
/
GRANT EXECUTE ON TESTPOWERBI TO PUBLIC

[v-yuta-msft · Accepted solution]

Hi wireless90,

It's very not recommended to implement transation SQL Script in power bi, because that may change data in your database. SQL Statement supported by power bi when connecting to database(SQL Server, Oracle) are like this pattern:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Regards,

Jimmy Tao