How do I return multiple columns using plsql

Question

I have a SAR protected oracle database from which I need to expose a table to PowerBI.

I am not familiar with PLSQL.

I have managed to expose a column of the table to PowerBI.

Help is needed in 2 areas

1) I require help from you guys to return selective columns from the table

2) I require help from you guys to return all the columns from the table

DROP TYPE testarr;
CREATE OR REPLACE TYPE testarr IS TABLE OF VARCHAR2(70);

/
GRANT EXECUTE ON testarr TO public;
DROP FUNCTION TestPowerBI

CREATE OR REPLACE FUNCTION TestPowerBI
RETURN testarr AUTHID CURRENT_USER AS
o_recorset SYS_REFCURSOR;
arr testarr := testarr();
pragma autonomous_transaction;

BEGIN
     sar.pk_sar_enable_roles.............
     commit;
     OPEN o_recordset FOR
          SELECT NAME FROM vw_people;
     FETCH o_recordset BULK COLLECT INTO arr;
     CLOSE o_recordset;
     RETURN arr;

END TestPowerBI

Grant execute on TestPowerBi to public;

PLSQL is a programming language. What you want is an sql query, not a function. — Sam M, Jun 07 '18 at 02:40
PowerBi UI does not allow us to get through SAR protected data. This is a work around. Is there a way to use PLSQL to return 2 columns together? — RStyle, Jun 07 '18 at 02:46
Have your function return a cursor rather than a table type. See the answer on this question: https://stackoverflow.com/questions/2153053/how-to-return-a-resultset-cursor-from-a-oracle-pl-sql-anonymous-block-that-exe?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — Sam M, Jun 07 '18 at 03:03
btw, what is SAR protected data? I did a web search and the acronym has lots of definitions. — Sam M, Jun 07 '18 at 03:03
Now PowerBi states cannot access rows from a non-nested table item. Sar is just a security access role given here internally to access a table — RStyle, Jun 07 '18 at 03:30

score 4 · Accepted Answer · answered Jun 07 '18 at 03:49

You may create a PIPELINED TABLE function.

let's say this is your table.

create table vw_people ( ID INTEGER, NAME VARCHAR2(10));
INSERT INTO vw_people(id,name) VALUES ( 1,'Knayak');
commit;

create an object and a collection of the object type

CREATE OR REPLACE TYPE vw_people_typ AS OBJECT( ID INTEGER,NAME VARCHAR2(10)); 
CREATE OR REPLACE TYPE vw_people_tab AS TABLE OF vw_people_typ;

This is your function

CREATE OR REPLACE FUNCTION testpowerbi RETURN vw_people_tab
    PIPELINED
    AUTHID current_user
AS
    vwt   vw_people_tab;
    PRAGMA autonomous_transaction;
BEGIN
     sar.pk_sar_enable_roles;
     commit;
    SELECT
        vw_people_typ(id,name)
    BULK COLLECT
    INTO vwt
    FROM
        vw_people;

    FOR i IN 1..vwt.count LOOP
        PIPE ROW ( vw_people_typ(vwt(i).id,vwt(i).name) );
    END LOOP;
END testpowerbi;
/

Query the output of the function as a TABLE

select * from TABLE(TestPowerBI);

        ID NAME      
---------- ----------
         1 Knayak

Thanks this works well in PLSQL. But PowerBI is returning a "Sql Command not properly ended" error — RStyle, Jun 07 '18 at 07:24

How do I return multiple columns using plsql

1 Answers1