How to build the query dynamically

Question

I am creating a query like this:

v_sql:=' SELECT abc FROM '||v_table||' 
 WHERE area IN ('''||v_area||''') 
 AND (
 ('''||p_supp_nbr||''' IS NULL) 
 OR 
 supplr_vn IN ('''||p_supp_nbr||''')
 )

Now when the p_supp_nbr is NULL then its not taking.

I get like this:

((''IS NULL) OR (supplr_vn =''))

Its checking space in the table

but i want ((NULL IS NULL) OR (supplr_vn =NULL)) so that condition bcomes ineffective

• user554546: There's no need to check whether or not null is null.
• Mani: I will need to check bcos it is an optional parameter.Currently its checking space in the table
• user554546: No, you don't understand. There's no need to include null is null as a part of any where clause. Simply don't include that part of the where clause if p_supp_nbr is null.
• Mani: If I dont include when the user dont enter anything in the parameter i need to neglect the condition. If I check supplr_Vvn=NULL then its wrong.SO that condition is required
• user554546: 0_0 Ummm...you don't ever want to check whether anything equals null. Null isn't a value. Just alter the where clause depending on which (if any) of your parameters happen to be null.

Answer 1

Don't do that! You risk SQL Injection issues. Use bind variables:

v_sql:= 'SELECT abc FROM '||v_table||
 ' WHERE area = :v_area
 AND (
 :p_supp_nbr IS NULL
 OR 
 supplr_vn = :p_supp_nbr
 )

Of course since you know at the point of generating the dynamic SQL whether the value is null you could instead do this:

v_sql:= 'SELECT abc FROM '||v_table||' 
 WHERE area = :v_area';
IF p_supp_nbr IS NULL THEN
 v_sql := v_sql || ' AND :p_supp_nbr IS NULL';
ELSE
 v_sql := v_sql || ' AND supplr_vn = :p_supp_nbr';
END IF;

Either way you can then bind the values at runtime like this:

OPEN my_refcursor FOR v_sql USING v_area, p_supp_nbr;

• glh: I like the second one best! But isn't the first part of the if statement superfluous? Wouldn't this work: IF p_supp_nbr IS NOT NULL THEN v_sql := v_sql || ' AND supplr_vn = :p_supp_nbr'; END IF;
• Tony Andrews: @glh I should have explained in my answer: yes it is logically superfluous, but Native Dynamic SQL only works when the number of bind variables is fixed, so we need to reference :p_supp_nbr in the query whether we use it or not.

Answer 2

Try this:

v_sql:=' SELECT abc FROM '||v_table||'
WHERE area IN ('''||v_area||''') 
 AND (('''||p_supp_nbr||''' IS NULL) 
 OR supplr_vn IN ('''||NVL(p_supp_nbr, 'NULL')||'''))

• Mani: In the above Query when p_supp_nbr IS NULL then its checking ' ' IS NULL.so space is not NULL right

Answer 3

General Oracle example:

DECLARE
 v_sql1 VARCHAR2(200);
 v_sql2 VARCHAR2(200);
 v_tab_name VARCHAR2(200):= 'scott.emp';
 v_listCol VARCHAR2(200):= 'job';
 v_list VARCHAR2(200):= '''MANAGER'', ''CLERK''';
 --
 v_colName VARCHAR2(200):= 'comm';
 v_comm NUMBER:= 1400;
BEGIN
 v_sql1:= 'SELECT * FROM '|| v_tab_name ||' WHERE '||v_listCol ||' IN (:v)';
 v_sql2:= 'SELECT * FROM '|| v_tab_name ||' WHERE ('||v_colName ||' = :v OR '||v_colName ||' IS NULL)';
 --
 EXECUTE IMMEDIATE v_sql1 USING v_list;
 dbms_output.put_line(v_sql1);
 --
 EXECUTE IMMEDIATE v_sql2 USING v_comm;
 dbms_output.put_line(v_sql2);
END;
/