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.

Would you like to actually tell us what you're trying to accomplish? — Jacobm001, Aug 07 '13 at 15:18
Oracle 11 and higher has a built in 'PIVOT', see one of Tim Hall's excellent summaries: http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php — Colin 't Hart, Aug 07 '13 at 15:30
@Jacobm001 the reason I am trying to do this dynamically is because the fields in the "userfieldcd" column are not consistent and tend to change. — Rafey, Aug 07 '13 at 15:35
@Rafeyiftikhar See [this answer](http://dba.stackexchange.com/a/30125/9003) on executing the procedure — Taryn, Aug 07 '13 at 15:41
@bluefeet thank you! But when I run: variable x refcursor exec dynamic_pivot_po(:x) print x, it gives me ORA-00900 Invalid sql statement. why is that? — Rafey, Aug 07 '13 at 16:06
@Rafeyiftikhar Did you try the exact code that is in the other answer? The code you posted above to execute is different. — Taryn, Aug 07 '13 at 17:00
@bluefeet yes I used the exact code from the other answer. I just updated this question. I am using PL/SQL developer 7.1.5. — Rafey, Aug 07 '13 at 17:08

score 3 · Answer 1 · answered Aug 07 '13 at 19:49

3

You are missing a semicolon:

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

answered Aug 07 '13 at 19:49

a_horse_with_no_name

69,148
13
135
173

vegatripy · Accepted Answer · 2013-08-08T10:53:53.087

2

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;
/

edited Aug 08 '13 at 10:53

answered Aug 07 '13 at 16:35

vegatripy

659
1
7
17

the reason why I dont want to do this is because I dont know all the pivoting values and they are always being updated. – Rafey Aug 07 '13 at 16:55
I've edited the answer to do a similar procedure as yours with `PIVOT` operator An alternative is to use operator PIVOT XML which allows you to use the wildcard ANY, but it will return a formatted XML result – vegatripy Aug 08 '13 at 10:53
thank you for this!! but when I try to run this it gives me: ORA-01008: not all variables bound. Why do you think that is? – Rafey Aug 08 '13 at 13:34
Check the code after you copy/paste/edited it. Check all variables. Maybe you are renaming a variable, but didn't rename it in every line. – vegatripy Aug 09 '13 at 10:05

How to execute this procedure in PL/SQL?

2 Answers2

Linked

Related