-- Tom's original test_dump_csv procedure
-- with some formatting and comments:
create or replace procedure test_dump_csv
as
    l_rows  number;
begin
    l_rows := dump_csv ( 
-- replace the query on the next line with yours:    
    'select * from all_users where rownum < 25'
-- replace the delimiter, directory, and file below with yours:
    , ',', '/tmp', 'test.dat' 
    );
end;
/
--
--
-- adaptation of above procedure to your needs:
create or replace procedure test_dump_csv
as
    l_rows  number;
begin
    l_rows := dump_csv ( 
'select r.DESTINATION_NATIONAL_ID "Code",
        r.CALL_PROCESSING_ELEMENT1_ID "ENT_ID",
        r.Routing_label_id "Routing_Label",
        r.Partition_id "Code_Par",
        "1stRTE_A","1stRTE_Z","1stRTE_%",
        "2ndRTE_A","2ndRTE_Z","2ndRTE_%",
        "3rdRTE_A","3rdRTE_Z","3rdRTE_%",
        "4thRTE_A","4thRTE_Z","4thRTE_%",p.Partition_id "RL_Par"
 from   route r,
        (select rlr.routing_label_id,rl.partition_id,
                max(decode(route_sequence,1,ROUTE_ENDPOINT1,null)) "1stRTE_A",
                max(decode(route_sequence,1,ROUTE_ENDPOINT2,null)) "1stRTE_Z",
                max(decode(route_sequence,1,ROUTE_PROPORTION)) "1stRTE_%",
                max(decode(route_sequence,2,ROUTE_ENDPOINT1,null)) "2ndRTE_A",
                max(decode(route_sequence,2,ROUTE_ENDPOINT2,null)) "2ndRTE_Z",
                max(decode(route_sequence,2,ROUTE_PROPORTION)) "2ndRTE_%",
                max(decode(route_sequence,3,ROUTE_ENDPOINT1,null)) "3rdRTE_A",
                max(decode(route_sequence,3,ROUTE_ENDPOINT2,null)) "3rdRTE_Z",
                max(decode(route_sequence,3,ROUTE_PROPORTION)) "3rdRTE_%",
                max(decode(route_sequence,4,ROUTE_ENDPOINT1,null)) "4thRTE_A",
                max(decode(route_sequence,4,ROUTE_ENDPOINT2,null)) "4thRTE_Z",
                max(decode(route_sequence,4,ROUTE_PROPORTION)) "4thRTE_%"
         from   routing_label_routes rlr,routing_label rl
         where  rl.routing_label_id=rlr.routing_label_id
         group by rlr.routing_label_id,rl.partition_id) p
         where p.routing_label_id=r.routing_label_id(+)
UNION
select r.DESTINATION_NATIONAL_ID "Code",
       r.CALL_PROCESSING_ELEMENT1_ID "ENT_ID",
       r.Routing_label_id "Routing_Label",
       r.Partition_id "Code_Par",
       "1stRTE_A","1stRTE_Z","1stRTE_%",
       "2ndRTE_A","2ndRTE_Z","2ndRTE_%",
       "3rdRTE_A","3rdRTE_Z","3rdRTE_%",
       "4thRTE_A","4thRTE_Z","4thRTE_%", p.Partition_id "RL_Par"
from   route r,
       (select rlr.routing_label_id,rl.partition_id,
               max(decode(route_sequence,1,ROUTE_ENDPOINT1,null)) "1stRTE_A",
               max(decode(route_sequence,1,ROUTE_ENDPOINT2,null)) "1stRTE_Z",
               max(decode(route_sequence,1,ROUTE_PROPORTION)) "1stRTE_%",
               max(decode(route_sequence,2,ROUTE_ENDPOINT1,null)) "2ndRTE_A",
               max(decode(route_sequence,2,ROUTE_ENDPOINT2,null)) "2ndRTE_Z",
               max(decode(route_sequence,2,ROUTE_PROPORTION)) "2ndRTE_%",
               max(decode(route_sequence,3,ROUTE_ENDPOINT1,null)) "3rdRTE_A",
               max(decode(route_sequence,3,ROUTE_ENDPOINT2,null)) "3rdRTE_Z",
               max(decode(route_sequence,3,ROUTE_PROPORTION)) "3rdRTE_%",
               max(decode(route_sequence,4,ROUTE_ENDPOINT1,null)) "4thRTE_A",
               max(decode(route_sequence,4,ROUTE_ENDPOINT2,null)) "4thRTE_Z",
               max(decode(route_sequence,4,ROUTE_PROPORTION)) "4thRTE_%"
        from   routing_label_routes rlr,routing_label rl
        where  rl.routing_label_id=rlr.routing_label_id
        group by rlr.routing_label_id,rl.partition_id) p
        where p.routing_label_id(+)=r.routing_label_id'
    , ',', '/home/oracle', 'crtrep_csv.txt' 
    );
end;
/
SHOW ERRORS