UTL_FILE.GET_LINE trims my XML tags

Question

I have been struggling on my own for quite some time now with this problem. This is the function i'm using

FUNCTION change_header_XML2 (l_nom_archivo IN VARCHAR2 DEFAULT NULL) RETURN number IS


  F UTL_FILE.FILE_TYPE;
  V_LINE VARCHAR2 (32767);

  --File read variables
  fileHandler UTL_FILE.FILE_TYPE;

  -- Variables control
  l_encabezado boolean := TRUE;
  l_var1 varchar2(32767)  := '';
  l_tag_fin number := 0;
  l_temp_var1 varchar2(32767) := 0;

  BEGIN

--Open file from directory
    F := UTL_FILE.FOPEN ('/home/dir1/dir2/', l_nom_archivo, 'R', 32767); --path for reading file

    fileHandler:=utl_file.fopen('/dir3/',l_nom_archivo,'W'); --Creates a new file

    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN --Reads XML line by line until EOF
          UTL_FILE.GET_LINE(F, V_LINE, 1000);
          IF V_LINE IS NULL THEN
            EXIT;
          END IF;

          IF (l_encabezado) THEN --changes the encoding of XML from UTF8 to ISO-8859-1
            l_encabezado := FALSE;
            l_var1 := '<?xml version="1.0" encoding="ISO-8859-1"?>';
          ELSE
            l_var1 := V_LINE; -- reads everything else
          END IF;                

      UTL_FILE.put_line(fileHandler, CONVERT(l_var1, 'WE8ISO8859P1', 'UTF8'));   --Inputs the readed line into the file. *Here is where the length trims the xml tag*

          utl_file.fflush(fileHandler);  --Save file in dir

        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            EXIT;
          WHEN OTHERS THEN
            HTP.P('<br><BR>An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
        END;
      END LOOP;
    ELSE
      HTP.P('The File dosent exists');
      dbms_output.put_line('The File dosent exists');
    END IF;

    UTL_FILE.FCLOSE(F);

    --Closes file
    utl_file.fflush(fileHandler);  
    UTL_FILE.FCLOSE(fileHandler);  

    return 1;

  EXCEPTION
     WHEN OTHERS THEN
        HTP.P('<br>An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
        dbms_output.put_line(SUBSTR( SQLERRM|| ', ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),0,999));

END change_header_XML2;

The purpose of this code is to change the encoding type from UTF8 to ISO-8859-1 from a XML files. To do this I download the file(s), save them into my server, then, with the code pasted above, i open said file, read a line from the XML, and paste it again in the same file. When I read the first line (l_encabezado var being TRUE, then false), i change it to the encoding type of my choice. Every line after the first one is read and pasted as it is.

The problem comes, i think, with the following line:

UTL_FILE.GET_LINE(F, V_LINE, 1000);

When a line from the file is too long, UTL_FILE cuts the line and paste it into the file, then it continues to read it and then paste the rest in a next line below the one previously posted. That's fine with normal text, but sometimes my XML end tags gets trimmed like in the following image:

XML image

The job_Major_List end tag gets cut off in 2 separate lines which breaks the XML file altogether. I tried changing the value of 1000 PLS_INTEGER for the function UTL_FILE.GET_LINE but couldn't make it work, I seem to fail to understand how PLS_INTEGER's works.

Answer 1

You are introducing unwanted line-terminator characters with UTL_FILE.PUT_LINE, use UTL_FILE.PUT instead.