Generate CSV in Source and POST to batchload in ORDS

GENERATE_CSV_AND_POST.sql

DECLARE
  l_csv_clob     CLOB;
  l_response     CLOB;
  lt_parm_names  apex_application_global.VC_ARR2;
  lt_parm_values apex_application_global.VC_ARR2;
BEGIN
  -- Build the header line.
  l_csv_clob := TO_CLOB('"ITEM_ID","ITEM_NUMBER","DESCRIPTION","COUNTRY_OF_MFG","CHANNEL","ITEM_UOM","DATE_INTRODUCED","STATUS","ORDERABLE"'||chr(13));
  -- Loop through records building the CSV content.
  FOR csv_rec IN
    (SELECT ITEM_ID||chr(44)
        ||chr(34)||ITEM_NUMBER||chr(34)||chr(44)
        ||chr(34)||DESCRIPTION||chr(34)||chr(44)
        ||chr(34)||COUNTRY_OF_MFG||chr(34)||chr(44)
        ||chr(34)||CHANNEL||chr(34)||chr(44)
        ||chr(34)||ITEM_UOM||chr(34)||chr(44)
        ||chr(34)||TO_CHAR(DATE_INTRODUCED,'YYYY-MM-DD HH24:MI:SS') || chr(34)||chr(44)
        ||chr(34)||STATUS||chr(34)||chr(44)
        ||chr(34)||ORDERABLE||chr(34)||chr(13)  data
    FROM   rest_transfer_test) LOOP
    l_csv_clob := l_csv_clob || TO_CLOB(csv_rec.data);
  END LOOP;
  
  -- Call the ORDS handler for the REST Enabled Table appending /batchload to indicate
  --   we want to send it some csv data to load.
  apex_web_service.set_request_headers(p_name_01 => 'Content-Type', p_value_01 => 'text/csv');
  lt_parm_names(1)  := 'batchRows';
  lt_parm_values(1) := '100000';      -- Send all 10,000 rows at once.
  lt_parm_names(2)  := 'truncate';
  lt_parm_values(2) := 'Truncate';    -- Truncate the table before laoding
  lt_parm_names(3)  := 'dateFormat';
  lt_parm_values(3) := 'YYYY-MM-DD HH24:MI:SS';   -- Format String for the Date Field.
  lt_parm_names(4)  := 'enclosures';
  lt_parm_values(4) := '"';
  l_response := apex_web_service.make_rest_request
   (p_url         => 'https://www.example.com/ords/prod/cndemo/rest_transfer_test/batchload',
    p_body        => l_csv_clob,
    p_parm_name   => lt_parm_names,
    p_parm_value  => lt_parm_values,
    p_http_method => 'POST');
  dbms_output.put_line('Response: '||l_response);
END;