Hi Experts,
I am filling a collection with data then I have to write the content of the collection to a file. However due to the complexities of the query
I end up filling duplicate data in the collection. Now when I write in the file, there are duplicate entries.
As of now to avoid the duplication what I am doing is
CREATE TYPE my_collection_obj AS OBJECT (
order_number VARCHAR2(240),
waybill_number VARCHAR2(240),
ship_to_location varchar2(240)
);
/
CREATE OR REPLACE TYPE my_collection_tab AS TABLE OF my_collection_obj ;
/
declare
my_colection my_collection_tab := my_collection_tab ();
v_line varchar2(32000);
begin
my_colection.extend(3);
--Populating the collection
my_colection(1):= my_collection_obj('123abc','2wertf345','PARIS');
my_colection(2):= my_collection_obj('123abc','2wertf345','PARIS');
my_colection(3):= my_collection_obj('123def','2wertf987','LONDON');
for rec_cur in (select distinct order_number,waybill_number,ship_to_location from table(my_colection))
LOOP
v_line := NULL;
v_line := rec_cur.order_number||','||rec_cur.waybill_number||','||rec_cur.ship_to_location;
--UTL_FILE.put_line (v_file_handle, v_line);=> this is what i need to use
dbms_output.put_line (v_line); -- just printing it for demonstration purpose
END LOOP;
END;
but this method takes time as i need to loop through the entire row of distinct collection. Is there a better way to do this ?