How to execute this procedure in PL/SQL?

Question

I have this table in the below format:

Persnbr | Userfieldcd | Value
01      | Port | Funds   
01      | Vip1 | Systems  
02      | Port | Bank  
02      | Vip1 | Authority   

This is how I want it:

Persnbr | Port  | Vip1
01      | Funds | Systems   
02      | Bank  | Authority

As I dont know the all the fields in the userfieldcd column, I am trying to dynamically pivot the table. So I am using this procedure but I dont know how to call it in PL/SQL developer. I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
sql_query varchar2(1000) := 'select persnbr ';

begin
    for x in (select distinct userfieldcd from persuserfield order by 1)
    loop
        sql_query := sql_query ||
            ' , min(case when userfieldcd = '''||x.userfieldcd||''' then value else null end) as '||x.userfieldcd;

            dbms_output.put_line(sql_query);
    end loop;

    sql_query := sql_query || ' from persuserfield group by persnbr order by persnbr';
    dbms_output.put_line(sql_query);

    open p_cursor for sql_query;
end;
/

When I call the procedure using:

VARIABLE x REFCURSOR  
BEGIN  
       dynamic_pivot_po(:x)  
    END  
    /

it gives me

ORA-00900: Invalid SQL statement.

Answer 1

You can do it with PIVOT operator, but it require hardcoding all the pivoting values.

select * from (select Persnbr, Userfieldcd, value from pivot_test) pivot (max(value) for Userfieldcd in ('Port', 'Vip1'));

Building the query dynamically as your example:

declare
    in_clause varchar2(256);
    sel_query varchar2(256);
    n number := 0;
begin
    for x in (select distinct userfieldcd from persuserfield)
    loop    
        if n <> 0 then 
                in_clause := in_clause || ', ';
        end if;
        in_clause := in_clause ||  '''' || x.userfieldcd || '''';
        n := 1;    
    end loop;
    sel_query := 'select * from (select Persnbr, userfieldcd, value from persuserfield) pivot (max(value) for userfieldcd in ('||in_clause||'));';
    dbms_output.put_line (sel_query);
end;
/

Answer 2

You are missing a semicolon:

VARIABLE x REFCURSOR  
BEGIN  
   dynamic_pivot_po(:x); -- here
END; -- and here
/