Dynamic queries

[khushbubhalla]

I am writing a procedure in which i am using dynamic queries.
I am assigning the dynamic query to a variable and then executing the query .
but as in when the query grows i can t keep it in a variable.
so how shud i execute the query
another problem is the result set is a set of records , how can i get the records


My proc as of now
Create or replace procedure datacompare
(tab_name1 In varchar2,
tab_name2 In varchar2,
Col_name in varchar2,
business_keys varchar2 )
IS
query varchar2(32767);
v_count Number(10);
V_result varchar2(32767);
begin
query:='Select count(*) from (
select '|| tab_name1||' , a.* from
(Select '||Col_name||','||business_keys||' from ' ||tab_name1||
' minus
select '||Col_name||','||business_keys||' from '||tab_name2||
' order by '||business_keys||
')A union all
select '|| tab_name2||' , b.* from
( select '||Col_name||','||business_keys||' from ' ||tab_name2||
' minus
select '||Col_name||','||business_keys||' from '||tab_name1||
' order by '||business_keys||
')B ) order by '||business_keys||' '
;
dbms_output.put_line(query);
execute immediate query into v_count;
dbms_output.put_line(v_count);
end;
/

[Saii]

1. One way is to break the query and assign to different variables and concatenating these variables.
2. For multi row select, use ref cursor like OPEN cursor for statement;