CODEWITHSUNDEEP
oracle-databaseplsqlpowerbipipelinesar
RStyle
RStyle

Reputation: 885

How do I return multiple columns using plsql

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;

Upvotes: 3

Views: 2184

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

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

Upvotes: 4

Related Questions