Is it possible to changeth number of input parameters for dynmic SQL at run time?

, ,
1

Hi All,

I am using a Dynamic SQL to call an Oracle query. Now this query has number of conditions in where clause. The user can pass a value or not, so if I am using the variable place holder(?) then I am not able to do null check.

The where caluse is something like this:

where ( lmi_ref_no = ? or ? is null )
and( request_primary_borrower = upper(?) or ? is null )

If user does not pass lmi number, then I have to skip that search parameter in where clause. But in that case ? becomes blank and the where clause becomes ’ ’ is null which fails.

Is there a way to defineth input parameters at run time or atleast pass null bvalue in them?

Note: I am avoiding using ${} construt because of DBA directive.

2

Why would a DBA have a say in your use of ${}? That’s purely a JDBC adapter construct and something the DB never sees.

For the scenario you describe, you’d use the Dynamic SQL template to create an adapter service. The caller of the adapter service would build the where clause. Refer to the JDBC Adapter User’s Guide for details. It covers this scenario. The caller will need to do the variable substitution before making the call.