OraFAQ Forum: SQL & PL/SQL » Remove null records from collection

Home » SQL & PL/SQL » SQL & PL/SQL » Remove null records from collection (11.2.0.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Remove null records from collection [message #659509]

Thu, 19 January 2017 08:26

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi,

I have a collection which has column names and I populate a string by concatenating the column names.
There may be null values in the collection which I would like to avoid concatenating.

e.g.

declare
   type t_tt is table of varchar2(20);
   l_tt t_tt := t_tt();
   l varchar2(2000);
begin
   l_tt.extend;
   l_tt(1) := 's';
   l_tt.extend;
   l_tt(2) := '';
   l_tt.extend;
   l_tt(3) := 'r';
   
--
   for i in 1..l_tt.last
   loop
      IF(l_tt(i) IS NOT NULL)  --> I have added IF condition to ignore null values.
      THEN
      l := l||','||l_tt(i);
      END IF;
   end loop;
   dbms_output.put_line(l);
--
end;
/

This works as expected, are there any built in functions to avoid null values after it is loaded into collection, I can think of not null option while loading but that does not fit in our application.
or
are there any other ways of doing it.

Thank you in advance.

Regards,
Pointers

Report message to a moderator