You may need to pass multiple parameters pragmatically prior executing a query with a block many times while users are provided with multiple parameter choices, here is an example explaining how deal with different kind of parameters(strings, numeric values & date)
if :ctrl.nulls_only = 'N' then
SET_BLOCK_PROPERTY('LINES',DEFAULT_WHERE, 'ACCOUNTING_DATE BETWEEN NVL('||''''||:CTRL.START_DATE||''''||',ACCOUNTING_DATE) AND NVL('||''''||:CTRL.END_DATE||''''||',ACCOUNTING_DATE)' ||' AND ATTRIBUTE1 IS NOT NULL AND ATTRIBUTE1=NVL('||''''||:CTRL.BEN_WHO||''''||',ATTRIBUTE1) AND MAIN_ACCOUNT='||:CTRL.MAIN_ACCOUNT);
/* where string and date parameters are passed in as strings and numeric parameters are passed in as numeric values*/
elsif :ctrl.nulls_only = 'Y' then
SET_BLOCK_PROPERTY('LINES',DEFAULT_WHERE, 'ACCOUNTING_DATE BETWEEN NVL('||''''||:CTRL.START_DATE||''''||',ACCOUNTING_DATE) AND NVL('||''''||:CTRL.END_DATE||''''||',ACCOUNTING_DATE)' ||'AND ATTRIBUTE1 IS NULL');
end if;
GO_BLOCK('LINES');
EXECUTE_QUERY;
Passing parameters to DEFAULT_WHERE could become complex with additional scenarios when an in-line query should be passed in. Give it a try, and we hope the above example gives a budding programmer a good starting.
Regards,
Admin