Function works in PL/SQL but not in PowerBi

Question

When I call the function, it works in SQL*Plus but doesn't work in PowerBI.

I opened PowerBI> Get Data> Oracle> Entered server name> Went to advanced options to enter sql below

SELECT * FROM TABLE(TESTPOWERBI);

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

Anyone have experience in solving this?

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;

Get rid of the semicolon on the end of `SELECT * FROM TABLE(TESTPOWERBI);` when running it from PowerBI and try it again. — Bob Jarvis - Слава Україні, Jun 07 '18 at 11:42
ok AT PowerBI Environement, currently it says VW_PEOPLE_TYPE invalid identifier at TESTPOWERBI — RStyle, Jun 08 '18 at 02:22

kara · Answer 1 · 2018-06-07T12:16:37.797

0

You mixed your delimiters ; and /.

Clean it up and add one at the end behind GRANT EXECUTE ON TESTPOWERBI TO PUBLIC.

In addition you got a typo: for i in 1 .. vwt.count only two dots.

This should work:

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;

Here some discussion about delimiters: When do I need to use a semicolon vs a slash in Oracle SQL?

edited Jun 07 '18 at 12:16

answered Jun 07 '18 at 09:16

kara

3,205
4
20
34

I need to add the slashes, im using sqlplus. Yea was a typo when I posted but actual code has no typo – RStyle Jun 07 '18 at 09:31
If you need the slashes do it consistantly: `;` to end the command and `/` to end the row. Row 4: No semicolon, Last row: No semicolon no slash. – kara Jun 07 '18 at 09:38
ok just read your link, wow. My sqlplus throws error if I do not enter / after a create statement – RStyle Jun 07 '18 at 09:49
Did you try to add a `/` at the end? Did you try to add a `;` in row 4? – kara Jun 07 '18 at 11:03
ok AT PowerBI Environement, currently it says VW_PEOPLE_TYPE invalid identifier at TESTPOWERBI, line 6 – RStyle Jun 08 '18 at 02:22
There is an additional "L " in the definition: `VW_PEOPLE_TYPEL` should be `VW_PEOPLE_TYPE`. – kara Jun 08 '18 at 06:09

Function works in PL/SQL but not in PowerBi

1 Answers1