oracle dynamic queries

Question

I am new with these oracle dynamic queries, I am just trying to build a dynamic query, so I could get records according to it.

Here is my PL/ SQL statement:

declare
sql_query varchar2(5000) := '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 ORDER BY PFS_SOURCE_ID ASC )
loop
    sql_query := sql_query ||
        ' , nvl(sum(case when FKBAB_SOURCE_ID = '||x.PFS_SOURCE_ID||' then FKOD_AMOUNT ELSE 0 end),0) as source_'||x.PFS_SOURCE_ID;
        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    
WHERE PFB_LEVEL_ID=2 GROUP BY FKOM_OFFICE_ID,FKBAM_BUDGET_ID ';
dbms_output.put_line(sql_query);
end;

How to get records from this above statement since I hope the query seems fine ?

Frank Schmitt · Accepted Answer · 2013-01-08 07:21:30Z

2

One possibility is using a ref cursor - you build your SQL statement and open the ref cursor for this statement afterwards.

Simplified:

declare
  l_cursor sys_refcursor;
  sql_query varchar2(4000);
begin
  sql_query := 'select 1 as value from dual';
  open l_cursor for sql_query;
end;

Depending on your IDE / host language, you can then iterate over the cursor etc.

For example, to print a ref cursor in SQL Plus:

variable x refcursor
set autoprint on
declare
  sql_query varchar2(4000);
begin
  sql_query := 'select 1 as value from dual';
  open :x for sql_query;
end;

This concept is even more powerful in 11g, because you can

use a CLOB for the SQL statement (thus allowing you to dynamically generate queries of arbitrary length)
convert a ref cursor to a DBMS_SQL cursor (and vice versa), which gives you much more flexibility if you need to get the column count and column types at runtime

answered Jan 8, 2013 at 7:21

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

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

4 Comments

hsuk Over a year ago

Thanks for your help, but I didn't understand what you explained. I am using Toad as Oracle client. Can't I simply run that generated dynamic SQL and get records ? Is it must to use the cursor ?

hsuk Over a year ago

And I have already viewed the records using ref cursor via stored procedure, I don't have much idea about these, I got the records, but only on Toad, I could not get those records from server side, PHP. stackoverflow.com/questions/14208698/…

Frank Schmitt Over a year ago

Of course, if you only want to check your results "by hand", you can run the generated SQL statement manually in TOAD without using the ref cursor approach.

Frank Schmitt Over a year ago

Regarding PHP: according to php.net/manual/en/function.oci-new-cursor.php PHP apparently can handle ref cursors - did you try this?

Collectives™ on Stack Overflow

oracle dynamic queries

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related