Oracle passes in a potentially empty parameter for querying

Some of the fields in our database tables may be empty, and the incoming query parameters may be empty.

For example

Select  from  Pos_supplier_mappings  wherein  (3324,4520067,4520097);

The query result set is as follows

mapping_id	party_id	vendor_id	supplier_reg_id
3324	6092236	3761
4520067			1
4520097			29

In most cases, when we stitch SQL statements in Java, we determine if the passed arguments are empty.

        //Organization ID        if((vendor_id! =NULL) && (! "". Equals (vendor_id))) {          if(Clausecount > 0) {whereclause.append ("and"); } whereclause.append ("vendor_id =:"); Whereclause.append (++bindcount);          Parameters.addelement (vendor_id); Clausecount++; }        Else{            if(Clausecount > 0) {whereclause.append ("and"); } whereclause.append ("VENDOR_ID is null"); Clausecount++; }

The concatenation of SQL is mostly as follows:

Selectpsm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id fromPos_supplier_mappings PSMwherepsm.vendor_id is NULL;Selectpsm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id fromPos_supplier_mappings PSMwherepsm.party_id is NULL;Selectpsm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id fromPos_supplier_mappings PSMwherepsm.supplier_reg_id is NULL;

Such stitching is cumbersome and prone to error.

Scenario Two: Flexible use of the NVL (PARAM1,PARAM2) function.

The original SQL is as follows:

SELECTpsm.mapping_id, psm.party_id, psm.vendor_id, psm.supplier_reg_id frompos_supplier_mappings PSMWHERENVL (psm.party_id,- About)=NVL (:1, NVL (psm.party_id,- About))    andNVL (psm.vendor_id,- About)=NVL (:2, NVL (psm.vendor_id,- About))    andNVL (psm.supplier_reg_id,- About)=NVL (:3, NVL (psm.supplier_reg_id,- About))

idea that if the parameter passed in is empty, then the column with null values in the table is identical.

The format of the parameters is as follows:

this. Setwhereclauseparam (0, party_id); this. Setwhereclauseparam (1, vendor_id); this. Setwhereclauseparam (2, supplier_reg_id);

Note: You cannot use the following form when passing in parameters

String party_id = party_id+ "";

This form causes the party_id of the null value to be changed to the string ' null ' in SQL.

Oracle passes in a potentially empty parameter for querying