Convert a sql query output to xml file

The following program shows how to create a xml file for the data returned by a sql query

DECLARE
  l_file_Name    VARCHAR2(30);
  l_dir          VARCHAR2(90);
  l_clob         CLOB  ;
  l_maxlen       NUMBER := 32767;
  l_buffer       VARCHAR2(32767);
  l_qry          VARCHAR2(2000);
  l_newline_pos  NUMBER;
  l_clobLen      NUMBER;
  l_file         UTL_FILE.FILE_TYPE;
  l_start_pos    NUMBER    := 1;

BEGIN
  
  l_file_name := 'skm_ftp.xml';
  l_dir       := '/tmp';
  l_qry       := 'SELECT lookup_code'       ||
                 '     , meaning'           ||
                 '     , description'       ||
                 '  FROM fnd_lookup_values' ||
                 ' WHERE lookup_type = ''REQUEST_PROBLEM_CODE''';

  SELECT DBMS_XMLGEN.getxml(l_qry)
    INTO l_clob
    FROM DUAL;
 
  l_clobLen := DBMS_LOB.GETLENGTH(l_clob);
  l_file    := UTL_FILE.FOPEN(l_dir, l_file_name,'W',l_maxlen);

  WHILE l_start_pos < l_clobLen LOOP
    l_buffer := DBMS_LOB.SUBSTR(l_clob, l_maxlen, l_start_pos);     
    EXIT WHEN l_buffer IS NULL;
    l_newline_pos  := INSTR(l_buffer,CHR(10),-1);
    IF l_newline_pos <> 0
    THEN
      l_buffer := SUBSTR(l_buffer,1,l_newline_pos-1);
      l_start_pos := l_start_pos + l_newline_pos;
    ELSE
      l_start_pos := l_start_pos + l_maxlen;
    END IF;
    UTL_FILE.PUT_LINE(l_file, l_buffer,TRUE);
  END LOOP;

  UTL_FILE.FCLOSE(l_file);

EXCEPTION
WHEN OTHERS THEN
  IF UTL_FILE.IS_OPEN(l_file) THEN
    UTL_FILE.FCLOSE(l_file);
  END IF;
  RAISE;

END;
/