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='||: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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: