Bind variable is NULL in dynamic SQL

When we pass bind variables to search condition (WHERE), some bind variable can be NULL. The right way to use bind variables (optional NULL) in a dynamic SQL is demostrated below:

e.g.


w_sql := ' select TSARPPRZOFF,     ' ||
 '        TSARPPRZOFFFID,  ' ||
 '        TSARPMXN,        ' ||
 '        TSARPMXNFID,     ' ||
 '        TSARPSCONTO,     ' ||
 '        TSARPTSCONTO,    ' ||
 '        TSARPSCONTOFID,  ' ||
 '        TSARPTSCONTOFID  ' ||
 ' from   tst_pma_articolo ' ||
 ' where  1 = 1 ';

IF p_id_articolo <> tesi_util.CARATTERE_CAMPO_NULLO THEN
 w_sql := w_sql || ' and tsarpid = ' || p_id_articolo || ' ';
END IF;

IF p_c_promozione <> tesi_util.CARATTERE_CAMPO_NULLO THEN
 w_sql := w_sql || ' and tsarptpnopr = ' || p_c_promozione || ' ';
END IF;

IF p_c_tema <> tesi_util.CARATTERE_CAMPO_NULLO THEN
 w_sql := w_sql || ' and tsarpnthem = ''' || p_c_tema ||''' ';
END IF;

IF p_c_prestazione <> tesi_util.CARATTERE_CAMPO_NULLO THEN
 w_sql := w_sql|| ' and tsarpnprs = ' || p_c_prestazione || ' ';
END IF;

OPEN w_cursor for w_sql;

LOOP
 ...
END LOOP;

should be like:


w_sql := ' select TSARPPRZOFF,     ' ||
 '        TSARPPRZOFFFID,  ' ||
 '        TSARPMXN,        ' ||
 '        TSARPMXNFID,     ' ||
 '        TSARPSCONTO,     ' ||
 '        TSARPTSCONTO,    ' ||
 '        TSARPSCONTOFID,  ' ||
 '        TSARPTSCONTOFID  ' ||
 ' from   tst_pma_articolo ' ||
 ' where  1 = 1 ';

 IF p_id_articolo is not null THEN
 w_sql := w_sql || ' and tsarpid = :B1 ' ;
 ELSE
 w_sql := w_sql || ' and :B1 is null ';
 END IF;

 IF p_c_promozione is not null THEN
 w_sql := w_sql || ' and tsarptpnopr = :B1' ;
 ELSE
 w_sql := w_sql || ' and :B2 is null ' ;
 END IF;

 IF p_c_tema is not null THEN
 w_sql := w_sql || ' and tsarpnthem = ''' || p_c_tema ||''' ';
 ELSE
 w_sql := w_sql || ' and :B3 is null ' ;
 END IF;

 IF p_c_prestazione is not null THEN
 w_sql := w_sql|| ' and tsarpnprs = ' || p_c_prestazione || ' ';
 ELSE
 w_sql := w_sql || ' and :B4 is null' ;
 END IF;

 OPEN w_cursor for w_sql using p_id_articolo, p_c_promozione, p_c_tema, p_c_prestazione;</em>
 LOOP
 ...
 END LOOP;</em>

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: