Oracle forms passing parameters to a ‘DEFAULT_WHERE’ clause

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=NVL('||''''||:CTRL.MAIN_ACCOUNT||''''||',MAIN_ACCOUNT)');

/* where string and date parameters are passed in as strings and numeric parameters are passed in as numeric values. If you have to do NVL with NUMERIC values, you must pass it as a string by enclosing single quotes around it. Check the MAIN_ACCOUNT example above*/
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,

Rajesh

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.