Your Location is: Home > Sql

Loop ending early in trying to create XML using PL/SQL via concurrent programs in Oracle EBS

From: Monaco View: 3962 Jan Paolo Dumlao 

Question

this is my base code that has been working in the past. Ive been trying to create a XML file using PLSQL and concurrent programs for EBS. i have done successfully in the past using the first set of codes. but currently my second code stops at the line shipping address and only gets 1 row set.

 LOOP
      FND_FILE.put_line(FND_FILE.OUTPUT,'<ROW>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ACCOUNT_NUMBER>'||rec.ACCOUNT_NUMBER||'</ACCOUNT_NUMBER>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ACCOUNT_NAME>'||replace(rec.ACCOUNT_NAME,'&','and')||'</ACCOUNT_NAME>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ADDRESS1>'||rec.ADDRESS1||'</ADDRESS1>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CITY>'||rec.CITY||'</CITY>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<STATE>'||rec.STATE||'</STATE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GEOGRAPHY_NAME>'||rec.GEOGRAPHY_NAME||'</GEOGRAPHY_NAME>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<POSTAL_CODE>'||rec.POSTAL_CODE||'</POSTAL_CODE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRX_DATE>'||rec.TRX_DATE||'</TRX_DATE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRX_NUMBER>'||rec.TRX_NUMBER||'</TRX_NUMBER>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DESCRIPTION>'||rec.DESCRIPTION||'</DESCRIPTION>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<receipt_date>'||rec.receipt_date||'</receipt_date>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<receipt_number>'||rec.receipt_number||'</receipt_number>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CIMA_REV>'||rec.CIMA_REV||'</CIMA_REV>');



  FND_FILE.put_line(FND_FILE.OUTPUT,'</ROW>');
END LOOP;
FND_FILE.put_line(FND_FILE.OUTPUT,'</ROWSET>');

this is my current code that ive been making and it stops at <SHIP_ADDRESS1></SHIP_ADDRESS1>

 FOR REC IN C_DATA
    LOOP
      FND_FILE.put_line(FND_FILE.OUTPUT,'<ROW>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_NUMBER>'||rec.Invoice_Number||'</INVOICE_NUMBER>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_DATE>'||rec.Invoice_Date||'</INVOICE_DATE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_AMOUNT>'||rec.Invoice_Amount||'</INVOICE_AMOUNT>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_TYPE>'||rec.Invoice_Type||'</INVOICE_TYPE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_BALANCE>'||rec.Invoice_Balance||'</INVOICE_BALANCE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_TO>'||rec.Ship_to||'</SHIP_TO>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_TO2>'||rec.Ship_to2||'</SHIP_TO2>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_ADDRESS1>'||rec.Ship_address1||'</SHIP_ADDRESS1');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_2ADDRESS2>'||rec.Ship_address2||'</SHIP_2ADDRESS2>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BILL_TO>'||rec.Bill_to||'</BILL_TO>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BILL_TO2>'||rec.Bill_to2||'</BILL_TO2>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BILL_ADDRESS1'||rec.Bill_address1||'</BILL_ADDRESS1>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BILL_ADDRESS2>'||rec.Bill_address2||'</BILL_ADDRESS2>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DUE_DATE>'||rec.Due_Date||'</DUE_DATE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ORIGINAL_TOTAL_AMOUNT>'||rec.Original_Total_Amount||'</ORIGINAL_TOTAL_AMOUNT>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<AMOUNT_DUE>'||rec.Amount_Due||'</AMOUNT_DUE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DAYS_LATE>'||rec.Days_Late||'</DAYS_LATE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_TAX>'||rec.Invoice_Tax||'</INVOICE_TAX>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_AND_HANDLING>'||rec.Ship_and_Handling||'</SHIP_AND_HANDLING>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER_NUMBER>'||rec.Customer_number||'</CUSTOMER_NUMBER>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER_NAME>'||rec.Customer_name||'</CUSTOMER_NAME>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LINE_NUMBER>'||rec.Line_Number||'</LINE_NUMBER>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PRODUCT_CODE>'||rec.Product_Code||'</PRODUCT_CODE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DESCRIPTION>'||rec.Description||'</DESCRIPTION>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<QUANTITY>'||rec.Quantity||'</QUANTITY>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<UNIT_PRICE>'||rec.Unit_Price||'</UNIT_PRICE>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ORDER_NUMBER>'||rec.Order_Number||'</ORDER_NUMBER>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PO_NUMBER>'||rec.PO_Number||'</PO_NUMBER>');
                  enter code hereFND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIPPING_INSTRUCTIONS>'||rec.Shipping_Instructions||'</SHIPPING_INSTRUCTIONS>');
                  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<AMOUNT>'||rec.Amount||'</AMOUNT>');

Anything I can do to stop this from happening?

Best answer

It looks like you are missing > in your closing tag for '</SHIP_ADDRESS1' as well as in your opening tag for '<BILL_ADDRESS1'.

Instead of building your XML file with string concatenation (which could lead to malformed XML like you currently have), I would recommend building your XML using an XMLTYPE within your select statement. This way, you could build your XML all at once, then write the entire contents to a file in one go. Changing your code to function like this would greatly reduce the amount of code you would need to write and remove the need to loop through each line potentially improving performance.

In the example below, I used an example query to generate some "invoice" data, but you can change that to whatever you are using for your cursor.

DECLARE
    l_xml   XMLTYPE;
BEGIN
    WITH
        invoice_data (invoice_number, invoice_date, invoice_amount)
        AS
            (    SELECT LEVEL, SYSDATE - LEVEL, ROUND (DBMS_RANDOM.VALUE (0, 10000), 2)
                   FROM DUAL
             CONNECT BY LEVEL <= 100)
    SELECT XMLELEMENT (
               "ROWSET",
               XMLAGG (
                   XMLELEMENT (
                       "ROW",
                       XMLCONCAT (XMLELEMENT ("INVOICE_NUMBER", invoice_number),
                                  XMLELEMENT ("INVOICE_DATE", invoice_date),
                                  XMLELEMENT ("INVOICE_AMOUNT", invoice_amount)))))
      INTO l_xml
      FROM invoice_data;

    DBMS_XSLPROCESSOR.clob2file (l_xml.getClobVal, 'TMP', 'testfile.txt');
END;
/

If for some reason you need to read the file back, you can read the file like this:

DECLARE
    l_clob   CLOB;
BEGIN
    l_clob := DBMS_XSLPROCESSOR.read2clob ('TMP', 'testfile.txt');
    DBMS_OUTPUT.put_line (DBMS_LOB.SUBSTR (l_clob, 32767, 1));
END;
/