PL/SQL writing a SQL Query to execute into a table

[mintz87]

i'm really rusty in the PL/SQL (spent last 5 yrs in SQL Server)

I'm wanting to have a PL/SQL procedure build SQL queries when called.

here is what i have so far:

declare
a_sel varchar2(255) := 'select * from VISIT_VIEW where';
begin
dbms_output.put_line ('select * from VISIT_VIEW where ');
for row_c in
(select ss_criteria_column_name, ss_criteria_data_detail
from saved_search_criteria where ss_id = '41')
loop dbms_output.put_line (' ' || row_c.ss_criteria_column_name || ' = ' || row_c.ss_criteria_data_detail );
for row_d in (select ss_id from saved_search_criteria where ss_id = '41')
loop dbms_output.put_line (' and ');
end loop; end loop;
end;


the ss_id will need to be grouped by the same ss_id per query.
right now this query adds an extra 'and' at the end of the query.
the data may or may not have multiple where clauses per query.

the ouput i'm trying to get is the following then execute each query into a single temp table:

select * from VISIT_VIEW where SURVEY_ID = '815' and CORPORATE_ID = '001000000' and PRODUCT_CODE = '509'

select * from VISIT_VIEW where SURVEY_ID = '810' and CORPORATE_ID = '5670000' and PRODUCT_CODE = '57' and STATE = 'TX'

select * from ....... (continues until no rows left in table)

thanks in advance

[asp2]

Use execute immediate 'select .......' instead to build ur dynamic sql and execute it ,

see this for more info on execute immediate statement :
http://download.oracle.com/docs/cd/B..._statement.htm