CODEWITHSUNDEEP
oracle-databaseplsqlpivot
Jean Willian S. J.
Jean Willian S. J.

Reputation: 117

Error in PL/SQL when using dynamic PIVOT

I'm trying to create a dynamic PIVOT command because i don't know the exact number of columns that will be needed in the "in" clause. The problem in this approach is that i'm getting an error after the execute immediate where it says that the datatypes are inconsistent even though the expected values are matching. I also tried to use sys_refcursor but the same error happened. What could it be?

    set serveroutput on;
    declare 
      storage_var clob;
      storage_query clob;
      type table_model is table of varchar2(100) index by pls_integer;
      tabl table_model;
    begin

     SELECT DISTINCT LISTAGG('''' || scd_local.descricao || '''',',')
     WITHIN GROUP (ORDER BY scd_local.descricao) INTO storage_var FROM  scd_local; 
     --Creates a list of values to to be used in the pivot command

     storage_query := 'select * from (select doc.nome, loc.descricao 
                    from scd_documento doc, scd_local_doc doc_loc, scd_local loc 
                    where doc.nome = doc_loc.id_doc and loc.id = doc_loc.id_local 
                    order by 1, 2)
                    pivot 
                    (max(descricao) for descricao in ( ' || storage_var || ' ))';

     dbms_output.put_line(storage_query);

     execute immediate storage_query bulk collect into tabl;  
     --Gives an error: "inconsistent datatypes: expected %s got %s"

     for i in 1.. tabl.count 
     loop
       dbms_output.put_line(tabl(i));
     end loop;
    end;
    /

Model

enter image description here

Upvotes: 0

Views: 523

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17944

As has been pointed out in the comments, the unknown column structure of your cursor is going to make it very difficult to fetch the results into PL/SQL.

However, if all you are really trying to do is send the results back to the client via DBMS_OUTPUT, you can actually do this by using DBMS_OUTPUT.RETURN_RESULTS.

Here is a modified version of your PL/SQL block that prints a cross-tab of users and object types from DBA_OBJECTS, with each cell showing the number of objects of the given type owned by the given user.

declare 
  storage_var clob;
  storage_query clob;
  l_ref_cur SYS_REFCURSOR;
begin

 SELECT LISTAGG('''' || o.object_type || '''',',')
 WITHIN GROUP ( ORDER BY o.object_type)
 INTO   storage_var
 FROM ( SELECT DISTINCT OBJECT_TYPE FROM dba_objects ) o;

 storage_query := 'select *
                   from ( SELECT owner, object_type FROM dba_objects ) o
                   pivot
                   (count(*) for object_type in (' || storage_var || '))
                   order by 1';


 dbms_output.put_line(storage_query);

 OPEN l_ref_cur FOR storage_query;  
 dbms_sql.return_result(l_ref_cur);
end;
/

Upvotes: 0

Aleksej
Aleksej

Reputation: 22959

As an aside, you don't need the table SCD_DOCUMENTO in your query.

The issue is that you are fetching a variable number of values into a structur: the dynamic query gives a result in which the number of columns depends on the values in your tables, so you can't know in advance how many columns will the result have.

This way, you can not fetch the result into a fixed number of structures, because you don't know at compile time how many variables you need to use to fetch your result.

Upvotes: 2

Related Questions