Forum Discussion

wireless90's avatar
wireless90
New Member
8 years ago
Solved

ERROR Getting Data From Oracle function ora-00933

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

 

 

  •  

    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

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support

     

    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