I've had another think about this. We can probably do it easier.
If the parameter HAS a value, then add:
'column = :parameter'
to the query. If it does NOT have a value, then add:
':parameter is null'
to the query.
In that way, you will always have the same number of binds, and hence not need a bind array. So your code ends up like:
l_sql :=
'select distinct TBB.RECEIPT_NUMBER as "Receipt",
TBB_USER as "ID",
TBB_NAME as "Name",
TBB_DESC as "Description",
TBR_TRANS_DATE as "Trans_Date",
TBR_FEED_DOC_CODE as "Feed_Code",
TBR_AMOUNT as "Amount",
TBR_PAY_DETAIL_CODE as "Pay_Code",
TBR_CHARGE_DETAIL_CODE as "Charge_Code"
from tbb,
tbr'
if :f1_parm_EB_Receipt is not null then
l_sql := l_sql || ' and TBB_RECEIPT_NUMBER = :1';
else
l_sql := l_sql || ' and :1 is null';
end if;
if :f1_parm_eb_bannerid1 is not null then
l_sql := l_sql || ' and tbb_user = :2';
else
l_sql := l_sql || ' and :2 is null';
end if;
if :f1_parm_EB_AcctCode is not null then
l_sql := l_sql || ' and tbr_charge_detail_code = :3';
else
l_sql := l_sql || ' and :3 is null';
end if;
open my_cursor for l_sql using f1_parm_EB_Receipt, f1_parm_eb_bannerid1, f1_parm_EB_AcctCode;
so the number of binds is constant, but the optimizer will "filter out" all the static "is null" conditions at optimization time. So