Oracle forms passing parameters to a ‘DEFAULT_WHERE’ clause

March 11, 2012

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

Advertisements