Dynamic query to get dynamic columns from rows

Question

I have been trying to create PL/SQL statement that creates a dynamic query in order to get dynamic columns. Since I don't have much idea about these Oracle PL/SQL statements; I am confused about few things.

Is it mandatory to have stored procedure for creating dynamic queries ?

The following query does not throw any error and even results nothing. What I am trying to do in the following query is to get sum of FKOD_AMOUNT according to FKBAB_SOURCE_ID which is foreign key for PFS_SOURCE_ID.

    declare
    sql_query varchar2(3000) := 'select FKOM_OFFICE_ID,FKBAM_BUDGET_ID ';
    begin
        for x in (select distinct PFS_SOURCE_ID,PFS_SOURCE_ENG from PBS_FC_SOURCE WHERE PFS_UPPER_SOURCE_ID!=0 )
        loop
            sql_query := sql_query ||
                ' , sum(case when FKBAB_SOURCE_ID = '||x.PFS_SOURCE_ID||' then FKOD_AMOUNT ELSE 0 end) as '||x.PFS_SOURCE_ENG;
                dbms_output.put_line(sql_query);
        end loop;
        sql_query := sql_query || ' FROM FMS_K_OFFICEWISE_DTL
        JOIN FMS_K_OFFICEWISE_MST ON FMS_K_OFFICEWISE_MST.FKOM_OFFICE_MST_ID=FMS_K_OFFICEWISE_DTL.FKOD_OFFICE_MST_ID
        JOIN FMS_K_BUDGET_ALLOCATION_DTL ON FMS_K_BUDGET_ALLOCATION_DTL.FKBAD_BUDGET_ALLOC_DTL_ID=FMS_K_OFFICEWISE_DTL.FKOD_BUDGET_ALLOC_AD_ID
        JOIN FMS_K_BUDGET_ALLOCATION_MST ON FMS_K_BUDGET_ALLOCATION_MST.FKBAM_BUDGET_ALLOC_ID=FMS_K_BUDGET_ALLOCATION_DTL.FKBAB_BUDGET_ALLOC_ID
        JOIN PBS_FC_BUDGET ON PBS_FC_BUDGET.PFB_BUDGET_ID=FMS_K_BUDGET_ALLOCATION_MST.FKBAM_BUDGET_ID
        GROUP BY FKOM_OFFICE_ID,FKBAM_BUDGET_ID ';
        dbms_output.put_line(sql_query);
    end;

How can I execute 'sql_query' ?

@bluefeet : can you help on this ? Moderators closed my previous question. — hsuk
– hsuk, Commented Jan 7, 2013 at 6:28
You can do it using execute immediate sql_query. But where do you want the result go? — Florin Ghita
– Florin Ghita, Commented Jan 7, 2013 at 6:43
where do you want the result go means ? Can't I run get results on the same page ? — hsuk
– hsuk, Commented Jan 7, 2013 at 6:45

Florin Ghita · Accepted Answer · 2013-01-07 10:27:55Z

1

To have an answer(removed my comments), this is pl/sql, not a select statement, so you can't just run it.

With execute immediate you can use INTO clause to store the results into some variables or arrays.

But you may make the query a cursor, put the cursor into a pipelined function and then

select * from table(your_pipelined_function)

answered Jan 7, 2013 at 10:27

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hsuk Over a year ago

Can you give me an example ?

Florin Ghita Over a year ago

http://www.oracle-base.com/articles/misc/pipelined-table-functions.php http://www.akadia.com/services/ora_pipe_functions.html http://www.adp-gmbh.ch/ora/plsql/exec_immediate.html

Collectives™ on Stack Overflow

Dynamic query to get dynamic columns from rows

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related