8 Replies Latest reply on Mar 19, 2013 2:34 PM by Jason_(A_Non)

    xtra characters & line in XML file

    997301
      I have a file with several errors in our xml file created by Oracle, it is adding an extra character in the strings and created a second line for that string, which we have not been able to fix(see example) any ideas would be appreciated.


      name = "BOX_NBR">1</variable><variable
      name = "TOTAL_BOXES">2</variable><
      *<variable_*
      name = "DELIVERY_ID">5257664</variable><variable
      name = "PICKING_ORDER">20104</variable><variable
        • 1. Re: xtra characters & line in XML file
          odie_63
          How is this file created? Post some code if you can.

          What's the database version? (SELECT * FROM v$version)
          • 2. Re: xtra characters & line in XML file
            997301
            Hi,

            We are using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.

            This is the code how we are generating XML.

            with getLPN as
            (
            select mt.move_order_line_id
            ,mt.cartonization_id
            ,mt.content_lpn_id
            ,mt.pick_slip_number
            ,nvl(mt.cartonization_id, mt.content_lpn_id) lpn_id
            ,mt.container_item_id
            ,mt.subinventory_code
            ,mt.locator_id
            from mtl_material_transactions_temp mt

            inner join MTL_TXN_REQUEST_LINES_V mtrl
            on mt.move_order_line_id = mtrl.line_id
            and mtrl.REQUEST_NUMBER between ':param_3' and ':param_4'
            where 1=1
            and (mt.move_order_line_id is not null
            or mt.cartonization_id is not null
            or mt.content_lpn_id is not null)
            union all
            select move_order_line_id
            ,transfer_lpn_id
            ,content_lpn_id
            ,pick_slip_number
            ,nvl(mt.transfer_lpn_id, mt.content_lpn_id) lpn_id
            ,null
            ,mt.subinventory_code
            ,mt.locator_id
            from mtl_material_transactions mt
            inner join MTL_TXN_REQUEST_LINES_V mtrl
            on mt.move_order_line_id = mtrl.line_id
            and mtrl.REQUEST_NUMBER between ':param_3' and ':param_4'
            where 1=1
            and (mt.move_order_line_id is not null
            or mt.transfer_lpn_id is not null
            or mt.content_lpn_id is not null)
            ), getLocInfo as
            (
            select wpsv.pick_slip_number
            ,mt.lpn_id
            ,wlpn.license_plate_number
            ,mil.subinventory_code
            ,mil.inventory_location_id
            ,mil.segment1 locator
            ,nvl(mil.picking_order,999999) picking_order
            ,wda.delivery_id
            ,msi.container
            ,replace(oeh.sold_to, '&', '&'||'amp;') sold_to
            ,oeh.order_number
            ,decode(oeh.sold_to,'CABELAS INC',null,oeh.tp_attribute1) tp_attribute1
            ,replace(oeh.ship_to_address5, '&', '&'||'amp;') ship_to_address5
            from wsh_pick_slip_v wpsv
            inner join mtl_item_locations mil
            on wpsv.from_subinventory = mil.subinventory_code
            and wpsv.from_locator_id = mil.inventory_location_id

            inner join (select distinct * from getLPN where lpn_id is not null) mt
            on wpsv.move_order_line_id = mt.move_order_line_id
            and wpsv.from_subinventory = mt.subinventory_code
            and wpsv.from_locator_id = mt.locator_id

            inner join wms_license_plate_numbers wlpn
            on wlpn.lpn_id = nvl(mt.cartonization_id, mt.content_lpn_id)

            inner join wsh_delivery_details wdd
            on wpsv.move_order_line_id = wdd.move_order_line_id

            inner join wsh_delivery_assignments wda
            on wdd.delivery_detail_id = wda.delivery_detail_id
            and wda.delivery_id is not null

            left outer join (select organization_id, inventory_item_id, nvl(segment1,'.') container from mtl_system_items) msi
            on mt.container_item_id = msi.inventory_item_id
            and wdd.organization_id = msi.organization_id

            inner join (select header_id, order_number, sold_to, tp_attribute1, ship_to_address5 from oe_order_headers_v) oeh
            on wdd.source_header_id = oeh.header_id
            ), getPickingOrder as
            (
            select lpn_id
            ,license_plate_number
            ,subinventory_code
            ,min(nvl(picking_order,999999)) picking_order
            from getLocInfo
            group by lpn_id,license_plate_number,subinventory_code
            ), getBoxCounts as
            (
            select loc.license_plate_number lpn
            ,loc.lpn_id
            ,loc.pick_slip_number
            ,loc.subinventory_code
            ,loc.locator
            ,loc.picking_order
            ,loc.delivery_id
            ,loc.container
            ,substr(loc.sold_to,1,27) customer_name
            ,loc.order_number
            ,loc.tp_attribute1
            ,loc.ship_to_address5
            ,row_number() over (partition by loc.pick_slip_number,loc.delivery_id order by count(loc.license_plate_number)) box_nbr
            ,count(loc.license_plate_number) over (partition by loc.pick_slip_number,loc.delivery_id) total_boxes
            from getLocInfo loc
            inner join getPickingOrder po
            on loc.lpn_id = po.lpn_id
            and loc.subinventory_code = po.subinventory_code
            and loc.picking_order = po.picking_order
            group by loc.license_plate_number
            ,loc.lpn_id
            ,loc.pick_slip_number
            ,loc.subinventory_code
            ,loc.locator
            ,loc.picking_order
            ,loc.delivery_id
            ,loc.container
            ,loc.sold_to
            ,loc.order_number
            ,loc.tp_attribute1
            ,loc.ship_to_address5
            ), getSiblings as
            (
            select bc.lpn
            ,bc.subinventory_code
            ,nvl(substr(XXWMCWSH_SHIP_INTEGRATION_PKG.get_LPN_subinventories(bc.lpn_id,bc.subinventory_code),1,26),'.') also_found_in
            from getBoxCounts bc
            )

            select
            ((XMLElement("labels",XMLATTRIBUTES(':param_1' AS "_FORMAT",':param_2' as "_PRINTERNAME", ':param_3' as "_JOBNAME", '1' as "_QUANTITY"),
            XMLAgg(
            XMLElement("label",
            XMLColAttVal(box.subinventory_code,
            sib.also_found_in,
            box.lpn,
                                box.locator,
            box.container,
            box.box_nbr,
            box.total_boxes,
            box.delivery_id,
            box.picking_order,
            box.customer_name,
            box.order_number,
            box.tp_attribute1,
            box.ship_to_address5
            )
            ) order by box.delivery_id,box.subinventory_code,box.picking_order,box.locator,box.lpn

            )
            )
            ).extract('/*')
            ).getclobval() xml
            from getBoxCounts box
            left outer join getSiblings sib
            on box.lpn = sib.lpn
            and box.subinventory_code = sib.subinventory_code
            • 3. Re: xtra characters & line in XML file
              odie_63
              Thanks for the details but obviously I can't say what's wrong if I can't reproduce.

              What happens when you run the query alone in a GUI (such as SQL*Developer)?
              Is the resulting CLOB OK?

              How's the CLOB written to disk? Spool, UTL_FILE ?


              In the first post you mentioned <variable> elements, but XMLColAttVal is supposed to generate <column> element, could you explain?

              Also, you probably don't want to do this :
              replace(oeh.sold_to, '&', '&'||'amp;') sold_to
              Oracle already takes care of escaping it for you when passed through a SQL/XML function.

              Edited by: odie_63 on 18 mars 2013 16:30
              • 4. Re: xtra characters & line in XML file
                997301
                Thank You for looking into and for XMLColAttVal we are using these commands and we placed these in file called label.dtd in loftware. I will run the sql and let you know the results.

                <!ELEMENT labels (label)*>
                <!ATTLIST labels _FORMAT CDATA #IMPLIED>
                <!ATTLIST labels _JOBNAME CDATA #IMPLIED>
                <!ATTLIST labels _QUANTITY CDATA #IMPLIED>
                <!ATTLIST labels _DUPLICATES CDATA #IMPLIED>
                <!ATTLIST labels _PAGES CDATA #IMPLIED>
                <!ATTLIST labels _PRINTERNUMBER CDATA #IMPLIED>
                <!ATTLIST labels _PRINTERNAME CDATA #IMPLIED>
                <!ATTLIST labels _TRAY CDATA #IMPLIED>

                <!ELEMENT label (variable)*>
                <!ATTLIST label _FORMAT CDATA #IMPLIED>
                <!ATTLIST label _JOBNAME CDATA #IMPLIED>
                <!ATTLIST label _QUANTITY CDATA #IMPLIED>
                <!ATTLIST label _DUPLICATES CDATA #IMPLIED>
                <!ATTLIST label _PAGES CDATA #IMPLIED>
                <!ATTLIST label _PRINTERNUMBER CDATA #IMPLIED>
                <!ATTLIST label _PRINTERNAME CDATA #IMPLIED>
                <!ATTLIST label _TRAY CDATA #IMPLIED>

                <!ELEMENT variable (#PCDATA)>
                <!ATTLIST variable name CDATA #IMPLIED>
                • 5. Re: xtra characters & line in XML file
                  997301
                  Hi this is how the file generating and this is working when we run the job for number of labels up to 8 and when we try to run the job having more than 8 it is generating like below and if you look for the label 9 for one of the field it is having << characters instaed of one < before Variable name

                  </variable><<variable name = "LOE">46

                  -----------------------------------------------

                  <?xml version="1.0" encoding="UTF-8" standalone="no"?>
                  <!-- Whitespace is not allowed at this location. -->
                  <!DOCTYPE labels SYSTEM "C:\Program Files\Loftware Labeling\label.dtd">
                  <labels FORMAT="HOOKBOXI.lwl" PRINTERNAME="hisspq1" JOBNAME="1951594" QUANTITY="1">
                  <label><variable name = "LABEL_NUMBER">1</variable><variable name = "JOB">1951594</variable><variable name = "ASSEMBLY">6331M-7</variable><variable name = "FIXED_LOT_MULT">4</variable><variable name = "JOB_QTY">36</variable><variable name = "DUE_DATE">2013-03-16</variable><variable name = "WIRE">C072</variable><variable name = "HOOK_MACH">PLTN</variable><variable name = "LBS_TRAY">0</variable><variable name = "FINISH">TIN1</variable><variable name = "HEAT_TREAT">485</variable><variable name = "MYLAR_TEST_H">None</variable><variable name = "MYLAR_TEST_F">None</variable><variable name = "CLASS_CODE">DISCRETE</variable><variable name = "LOCATION">BLKSTG</variable><variable name = "SUBINV">STOCKROOM</variable><variable name = "DESCRIP">RNGE KOFFSET</variable><variable name = "LOE">46</variable><variable name = "FBDRAW">D#6331-7</variable><variable name = "PRINT_DATE">2013-03-18</variable><variable name = "NBR_LABELS">9</variable><variable name = "FY1">1</variable><variable name = "FY2">3</variable>
                  </label>
                  <label><variable name = "LABEL_NUMBER">2</variable><variable name = "JOB">1951594</variable><variable name = "ASSEMBLY">6331M-7</variable><variable name = "FIXED_LOT_MULT">4</variable><variable name = "JOB_QTY">36</variable><variable name = "DUE_DATE">2013-03-16</variable><variable name = "WIRE">C072</variable><variable name = "HOOK_MACH">PLTN</variable><variable name = "LBS_TRAY">0</variable><variable name = "FINISH">TIN1</variable><variable name = "HEAT_TREAT">485</variable><variable name = "MYLAR_TEST_H">None</variable><variable name = "MYLAR_TEST_F">None</variable><variable name = "CLASS_CODE">DISCRETE</variable><variable name = "LOCATION">BLKSTG</variable><variable name = "SUBINV">STOCKROOM</variable><variable name = "DESCRIP">RNGE KOFFSET</variable><variable name = "LOE">46</variable><variable name = "FBDRAW">D#6331-7</variable><variable name = "PRINT_DATE">2013-03-18</variable><variable name = "NBR_LABELS">9</variable><variable name = "FY1">1</variable><variable name = "FY2">3</variable>
                  </label>
                  <label><variable name = "LABEL_NUMBER">3</variable><variable name = "JOB">1951594</variable><variable name = "ASSEMBLY">6331M-7</variable><variable name = "FIXED_LOT_MULT">4</variable><variable name = "JOB_QTY">36</variable><variable name = "DUE_DATE">2013-03-16</variable><variable name = "WIRE">C072</variable><variable name = "HOOK_MACH">PLTN</variable><variable name = "LBS_TRAY">0</variable><variable name = "FINISH">TIN1</variable><variable name = "HEAT_TREAT">485</variable><variable name = "MYLAR_TEST_H">None</variable><variable name = "MYLAR_TEST_F">None</variable><variable name = "CLASS_CODE">DISCRETE</variable><variable name = "LOCATION">BLKSTG</variable><variable name = "SUBINV">STOCKROOM</variable><variable name = "DESCRIP">RNGE KOFFSET</variable><variable name = "LOE">46</variable><variable name = "FBDRAW">D#6331-7</variable><variable name = "PRINT_DATE">2013-03-18</variable><variable name = "NBR_LABELS">9</variable><variable name = "FY1">1</variable><variable name = "FY2">3</variable>
                  </label>
                  <label><variable name = "LABEL_NUMBER">4</variable><variable name = "JOB">1951594</variable><variable name = "ASSEMBLY">6331M-7</variable><variable name = "FIXED_LOT_MULT">4</variable><variable name = "JOB_QTY">36</variable><variable name = "DUE_DATE">2013-03-16</variable><variable name = "WIRE">C072</variable><variable name = "HOOK_MACH">PLTN</variable><variable name = "LBS_TRAY">0</variable><variable name = "FINISH">TIN1</variable><variable name = "HEAT_TREAT">485</variable><variable name = "MYLAR_TEST_H">None</variable><variable name = "MYLAR_TEST_F">None</variable><variable name = "CLASS_CODE">DISCRETE</variable><variable name = "LOCATION">BLKSTG</variable><variable name = "SUBINV">STOCKROOM</variable><variable name = "DESCRIP">RNGE KOFFSET</variable><variable name = "LOE">46</variable><variable name = "FBDRAW">D#6331-7</variable><variable name = "PRINT_DATE">2013-03-18</variable><variable name = "NBR_LABELS">9</variable><variable name = "FY1">1</variable><variable name = "FY2">3</variable>
                  </label>
                  <label><variable name = "LABEL_NUMBER">5</variable><variable name = "JOB">1951594</variable><variable name = "ASSEMBLY">6331M-7</variable><variable name = "FIXED_LOT_MULT">4</variable><variable name = "JOB_QTY">36</variable><variable name = "DUE_DATE">2013-03-16</variable><variable name = "WIRE">C072</variable><variable name = "HOOK_MACH">PLTN</variable><variable name = "LBS_TRAY">0</variable><variable name = "FINISH">TIN1</variable><variable name = "HEAT_TREAT">485</variable><variable name = "MYLAR_TEST_H">None</variable><variable name = "MYLAR_TEST_F">None</variable><variable name = "CLASS_CODE">DISCRETE</variable><variable name = "LOCATION">BLKSTG</variable><variable name = "SUBINV">STOCKROOM</variable><variable name = "DESCRIP">RNGE KOFFSET</variable><variable name = "LOE">46</variable><variable name = "FBDRAW">D#6331-7</variable><variable name = "PRINT_DATE">2013-03-18</variable><variable name = "NBR_LABELS">9</variable><variable name = "FY1">1</variable><variable name = "FY2">3</variable>
                  </label>
                  <label>
                  <variable name = "LABEL_NUMBER">6</variable><variable name = "JOB">1951594</variable><variable name = "ASSEMBLY">6331M-7</variable><variable name = "FIXED_LOT_MULT">4</variable><variable name = "JOB_QTY">36</variable><variable name = "DUE_DATE">2013-03-16</variable><variable name = "WIRE">C072</variable><variable name = "HOOK_MACH">PLTN</variable><variable name = "LBS_TRAY">0</variable><variable name = "FINISH">TIN1</variable><variable name = "HEAT_TREAT">485</variable><variable name = "MYLAR_TEST_H">None</variable><variable name = "MYLAR_TEST_F">None</variable><variable name = "CLASS_CODE">DISCRETE</variable><variable name = "LOCATION">BLKSTG</variable><variable name = "SUBINV">STOCKROOM</variable><variable name = "DESCRIP">RNGE KOFFSET</variable><variable name = "LOE">46</variable><variable name = "FBDRAW">D#6331-7</variable><variable name = "PRINT_DATE">2013-03-18</variable><variable name = "NBR_LABELS">9</variable><variable name = "FY1">1</variable><variable name = "FY2">3</variable>
                  </label>
                  <label><variable name = "LABEL_NUMBER">7</variable><variable name = "JOB">1951594</variable><variable name = "ASSEMBLY">6331M-7</variable><variable name = "FIXED_LOT_MULT">4</variable><variable name = "JOB_QTY">36</variable><variable name = "DUE_DATE">2013-03-16</variable><variable name = "WIRE">C072</variable><variable name = "HOOK_MACH">PLTN</variable><variable name = "LBS_TRAY">0</variable><variable name = "FINISH">TIN1</variable><variable name = "HEAT_TREAT">485</variable><variable name = "MYLAR_TEST_H">None</variable><variable name = "MYLAR_TEST_F">None</variable><variable name = "CLASS_CODE">DISCRETE</variable><variable name = "LOCATION">BLKSTG</variable><variable name = "SUBINV">STOCKROOM</variable><variable name = "DESCRIP">RNGE KOFFSET</variable><variable name = "LOE">46</variable><variable name = "FBDRAW">D#6331-7</variable><variable name = "PRINT_DATE">2013-03-18</variable><variable name = "NBR_LABELS">9</variable><variable name = "FY1">1</variable><variable name = "FY2">3</variable>
                  </label>
                  <label><variable name = "LABEL_NUMBER">8</variable><variable name = "JOB">1951594</variable><variable name = "ASSEMBLY">6331M-7</variable><variable name = "FIXED_LOT_MULT">4</variable><variable name = "JOB_QTY">36</variable><variable name = "DUE_DATE">2013-03-16</variable><variable name = "WIRE">C072</variable><variable name = "HOOK_MACH">PLTN</variable><variable name = "LBS_TRAY">0</variable><variable name = "FINISH">TIN1</variable><variable name = "HEAT_TREAT">485</variable><variable name = "MYLAR_TEST_H">None</variable><variable name = "MYLAR_TEST_F">None</variable><variable name = "CLASS_CODE">DISCRETE</variable><variable name = "LOCATION">BLKSTG</variable><variable name = "SUBINV">STOCKROOM</variable><variable name = "DESCRIP">RNGE KOFFSET</variable><variable name = "LOE">46</variable><variable name = "FBDRAW">D#6331-7</variable><variable name = "PRINT_DATE">2013-03-18</variable><variable name = "NBR_LABELS">9</variable><variable name = "FY1">1</variable><variable name = "FY2">3</variable>
                  </label>
                  <label><variable name = "LABEL_NUMBER">9</variable><variable name = "JOB">1951594</variable><variable name = "ASSEMBLY">6331M-7</variable><variable name = "FIXED_LOT_MULT">4</variable><variable name = "JOB_QTY">36</variable><variable name = "DUE_DATE">2013-03-16</variable><variable name = "WIRE">C072</variable><variable name = "HOOK_MACH">PLTN</variable><variable name = "LBS_TRAY">0</variable><variable name = "FINISH">TIN1</variable><variable name = "HEAT_TREAT">485</variable><variable name = "MYLAR_TEST_H">None</variable><variable name = "MYLAR_TEST_F">None</variable><variable name = "CLASS_CODE">DISCRETE</variable><variable name = "LOCATION">BLKSTG</variable><variable name = "SUBINV">STOCKROOM</variable><variable name = "DESCRIP">RNGE KOFFSET</variable><<variable name = "LOE">46</variable><variable name = "FBDRAW">D#6331-7</variable><variable name = "PRINT_DATE">2013-03-18</variable><variable name = "NBR_LABELS">9</variable><variable name = "FY1">1</variable><variable name = "FY2">3</variable>
                  </label>
                  </labels>

                  Appreciate for your help.
                  • 6. Re: xtra characters & line in XML file
                    Jason_(A_Non)
                    To restate the questions Odie asked you

                    ===================================
                    What happens when you run the query alone in a GUI (such as SQL*Developer)?
                    Is the resulting CLOB OK?

                    How's the CLOB written to disk? Spool, UTL_FILE ?
                    ===================================

                    You provided the output of some unknown process. We cannot offer to help you without knowing what that process is. As he was asking. Are you running this as a SQL statement via SQL*Plus, SQL*Developer, etc. Is this SQL inside a PL/SQL block of code. Is this part of some other language?

                    What happens when you run the SQL query only in SQL*Plus or a different GUI tool. Are the results the same/correct?

                    How is this file getting written to disk. Could that be the cause of the extra character that you are seeing in the file on disk.

                    We can't see your machine so in order to help, you have to provide enough details for us to be able to reproduce the issue you are seeing.
                    • 7. Re: xtra characters & line in XML file
                      997301
                      Hi ,

                      Yes when we run the query alone it is working.

                      We are using UTL_FILE to write to disk.

                      We are using below package to write to disk so please see below details and let me know if you need anything else.

                      create or replace PACKAGE BODY XXTEST_print_XML_PKG
                      is
                      /*********Procedure generate_output ******************
                      ** Accepts the query which has to be converted into XML
                      ** Also accepts Path where the file is to be written
                      ** And accepts the file name -
                      ** note : it does not add .xml to the file name by default
                      **
                      **
                      ****************************************************/
                      procedure generate_output1 ( errbuf OUT VARCHAR2,
                                retcode OUT NUMBER,
                      cp_query_path in Varchar2,
                      cp_query_file in Varchar2,
                      cp_path in Varchar2 ,
                                     cp_file in Varchar2 ,
                      cp_dtd_text in Varchar2,
                      param_1 in varchar2 default g_varchar2,
                      param_2 in varchar2 default g_varchar2,
                      param_3 in varchar2 default g_varchar2,
                      param_4 in varchar2 default g_varchar2,
                      param_5 in varchar2 default g_varchar2,
                      param_6 in varchar2 default g_varchar2,
                      param_7 in varchar2 default g_varchar2,
                      param_8 in varchar2 default g_varchar2,
                      param_9 in varchar2 default g_varchar2,
                      param_10 in varchar2 default g_varchar2,
                      param_11 in varchar2 default g_varchar2,
                      param_12 in varchar2 default g_varchar2,
                      param_13 in varchar2 default g_varchar2 ,
                      param_14 in varchar2 default g_varchar2,
                      param_15 in varchar2 default g_varchar2,
                      param_16 in varchar2 default g_varchar2,
                      param_17 in varchar2 default g_varchar2,
                      param_18 in varchar2 default g_varchar2,
                      param_19 in varchar2 default g_varchar2,
                      param_20 in varchar2 default g_varchar2
                                     )
                      is
                      queryCtx DBMS_XMLquery.ctxType;
                      result1 CLOB;
                      errorNum NUMBER;
                      errorMsg VARCHAR2(200);
                      writeException Exception;
                      retmsg Varchar2(200);
                      p_in_file           UTL_FILE.FILE_TYPE;
                      query_text varchar2(15000);
                      in_text varchar2(1000);
                      p_file           UTL_FILE.FILE_TYPE;
                      TYPE cur_typ IS REF CURSOR;
                      c_cursor cur_typ;
                      --v_text  varchar2(32767) ;
                      v_text clob;
                      n_count Number;
                      x_text varchar2(32767);
                      n number;
                      x_check varchar2(32767);
                      x_loop boolean;
                      begin
                      p_in_file:=UTL_FILE.FOPEN(cp_query_path,cp_query_file, 'R');
                      query_text := '';
                      LOOP
                      BEGIN
                      in_text := '';
                      UTL_FILE.GET_LINE (p_in_file , in_text);
                      select replace(in_text, ':param_1' , param_1) into in_text from dual;
                      select replace(in_text, ':param_2' , param_2) into in_text from dual;
                      select replace(in_text, ':param_3' , param_3) into in_text from dual;
                      select replace(in_text, ':param_4' , param_4) into in_text from dual;
                      select replace(in_text, ':param_5' , param_5) into in_text from dual;
                      select replace(in_text, ':param_6' , param_6) into in_text from dual;
                      select replace(in_text, ':param_7' , param_7) into in_text from dual;
                      select replace(in_text, ':param_8' , param_8) into in_text from dual;
                      select replace(in_text, ':param_9' , param_9) into in_text from dual;
                      select replace(in_text, ':param_10' , param_10) into in_text from dual;
                      select replace(in_text, ':param_11' , param_11) into in_text from dual;
                      select replace(in_text, ':param_12' , param_12) into in_text from dual;
                      select replace(in_text, ':param_13' , param_13) into in_text from dual;
                      select replace(in_text, ':param_14' , param_14) into in_text from dual;
                      select replace(in_text, ':param_15' , param_15) into in_text from dual;
                      select replace(in_text, ':param_16' , param_16) into in_text from dual;
                      select replace(in_text, ':param_17' , param_17) into in_text from dual;
                      select replace(in_text, ':param_18' , param_18) into in_text from dual;
                      select replace(in_text, ':param_19' , param_19) into in_text from dual;
                      select replace(in_text, ':param_20' , param_20) into in_text from dual;
                      query_text := query_text || ' ' || in_text ;
                      EXCEPTION
                      WHEN NO_DATA_FOUND THEN EXIT;
                      WHEN OTHERS THEN
                      fnd_file.put_line(fnd_file.output,sqlerrm || 'error:' || length(query_text ));
                      exit;
                      END;
                      END LOOP;
                      UTL_FILE.FCLOSE(p_in_file);
                                p_file:=UTL_FILE.FOPEN(cp_path, cp_file, 'W',32767);
                                UTL_FILE.PUT_LINE(p_file,'<?xml version="1.0" encoding="UTF-8" standalone="no"?>' );
                                UTL_FILE.PUT_LINE(p_file,'<!DOCTYPE labels SYSTEM "' || cp_dtd_text || '">');
                      OPEN c_cursor FOR query_text;
                      LOOP
                      FETCH c_cursor INTO v_text;
                      EXIT WHEN c_cursor%NOTFOUND;
                      loop
                      n := 0;
                      select length(v_text) into n_count from dual;
                      if n_count > 8191 then
                      n:= 8192;
                      loop
                      select substr(v_text,n,9) into x_check from dual;
                      if x_check = '</column>'
                      then
                      exit;
                      end if;
                      n := n-1;
                      end loop;
                      select substr(v_text,1,n+9) into x_text from dual;
                      x_loop := true;
                      E lse
                      x_loop := false;
                      select v_text into x_text from dual;
                      End if;
                      UTL_FILE.PUT_LINE(p_file, replace(replace(x_text ,'<column','<variable'),'/column','/variable'));
                      if not x_loop then
                      exit;
                      end if;
                      select substr(v_text,n+9, length(v_text)) into v_text from dual;
                      End loop;
                      end loop;
                      close c_cursor;
                      fnd_file.put_line(fnd_file.output,'XML File generated Sucessfully');
                      utl_file.fclose(p_file);
                      EXCEPTION
                           WHEN UTL_FILE.INVALID_OPERATION THEN
                      fnd_file.put_line(fnd_file.log,'Exception Occured -- Could not open file');
                      retcode := -1;
                           WHEN UTL_FILE.INVALID_PATH THEN
                      fnd_file.put_line(fnd_file.log,'Exception Occured -- Invalid path in open file statement');
                      retcode := -1;
                           WHEN UTL_FILE.INVALID_MODE THEN
                      fnd_file.put_line(fnd_file.log,'Exception Occured -- Invalid mode in open file statement');
                                retcode := -1;
                           WHEN UTL_FILE.INVALID_FILEHANDLE THEN
                      fnd_file.put_line(fnd_file.log, 'Exception Occured -- Invalid file handle');
                      retcode := -1;
                      when others then
                      retcode := -1;
                      utl_file.fclose(p_file);
                      end; --end procedure
                      end; -- end package
                      • 8. Re: xtra characters & line in XML file
                        Jason_(A_Non)
                        Yes when we run the query alone it is working.
                        So the issue has nothing to do with the query and everything to do with the method you are using to write out the file.

                        For future reference:
                        {message:id=9360002}

                        For reading/writing the file, consider replacing all that code you have setup with the following two methods that Oracle provides (See [url http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xslpro.htm#ARPLS70144]Summary of DBMS_XSLPROCESSOR Subprograms for documentation)
                        dbms_xslprocessor.read2clob for reading in the file
                        dbms_xslprocessor.clob2file for writing out the clob

                        All lines like this
                        select replace(in_text, ':param_16' , param_16) into in_text from dual;
                        are equivalent to
                        in_text := replace(in_text, ':param_16' , param_16);
                        given that you can do the replace in PL/SQL and you do not need to make a SQL call to perform the task.

                        Also with all those replaces of parm values, you are trashing your shared pool. Go to [url http://asktom.oracle.com/pls/asktom/f?p=100:1:0::NO:::]Ask Tom and search on bind variables and read up on how to use "USING" to pass along the values of "param_1", "param_2" etc instead of hard-coding them into the SQL.

                        Why are you using XMLColAttVal if you are renaming the node names it creates? Why not just use XMLElement/XMLForest instead in your SQL and avoid the work of renaming nodes. Create the XML correct in the first pass and save yourself the work.