PL/SQL Function - Bulk Collect into and Pipe Row

Kosta Published at Dev

Kosta

I am new to PL/SQL have issue with output value of a function.

I want to execute a SQL statement in a function and return the results. The function will be executed with following command: select * from table(mypkg.execute_query('1'));

I was using following article as refence "Bulk Collect Into" and "Execute Immediate" in Oracle, but without success.

It seems that I am using wrong data type. System returns issue on following line: PIPE Row(results)

create or replace package mypkg
as 
    type node is table of edges%ROWTYPE;
    function execute_query (startNode in varchar2) RETURN node PIPELINED;
end;


create or replace package body mypkg
as
   function execute_query(startNode in varchar2) RETURN node PIPELINED
    AS
        results node;
        my_query VARCHAR2(100);
        output VARCHAR2(1000);
        c sys_refcursor;
    BEGIN
        my_query := 'SELECT DISTINCT * FROM EDGES WHERE src='|| startNode;
        
        open c for my_query;
        loop
            fetch c bulk collect into results limit 100;
            exit when c%notfound;
                PIPE Row(results);
        end loop;
        close c;
    END;
end;

I tried several options with cursor but wasn't able to return the value. If you have idea how to return the data by using something else than PIPELINED, please let me know.

Thanks for your support!

Sayan Malakshinov

Fixed body:

create or replace package body mypkg
as
   function execute_query(startNode in varchar2) RETURN node PIPELINED
    AS
        results node;
        my_query VARCHAR2(100);
        output VARCHAR2(1000);
        c sys_refcursor;
    BEGIN -- don't use concatenation, it leads to sql injections:
        my_query := 'SELECT DISTINCT * FROM EDGES WHERE src=:startNode';
        -- use bind variables and bind them using cluase "using":
        open c for my_query using startNode;
        loop
            fetch c bulk collect into results limit 100;
            -- "results" is a collection, so you need to iterate it to pipe rows:
            for i in 1..results.count loop
                PIPE Row(results(i));
            end loop;
            exit when c%notfound;
        end loop;
        close c;
    END;
end;
/

Collected from the Internet

Please contact [email protected] to delete if infringement.