Home » SQL & PL/SQL » SQL & PL/SQL » Remove null records from collection (11.2.0.4.0)
Remove null records from collection [message #659509] Thu, 19 January 2017 08:26 Go to next message
pointers
Messages: 439
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
Re: Remove null records from collection [message #659512 is a reply to message #659509] Thu, 19 January 2017 09:59 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

repexp_replace(var, ',+', ',')

[Updated on: Thu, 19 January 2017 09:59]

Report message to a moderator

Previous Topic: Collections
Next Topic: How to create an object table
Goto Forum:
  


Current Time: Sun Feb 25 09:23:41 CST 2018

Total time taken to generate the page: 0.07934 seconds