DECLARE
    T_OUT_FILE UTL_FILE.FILE_TYPE;
    T_BUFFER VARCHAR2(32767);
    T_AMOUNT BINARY_INTEGER := 1000;
    T_POS INTEGER := 1;
    T_CLOB_LEN INTEGER;
    P_DATA CLOB;
    P_DIR VARCHAR2(100) := '<%=odiRef.getOption("lDir")%>';
    P_FILE VARCHAR2(100) := '<%=odiRef.getOption("lFileName")%>';
BEGIN
    SELECT XMLSERIALIZE(
        DOCUMENT XMLTYPE(XML_REQUEST) AS CLOB INDENT SIZE = 4
    ) XML_RESULT INTO P_DATA FROM (
        SELECT XMLELEMENT("soapenv:Envelope",
            XMLATTRIBUTES(
                'http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soapenv",
                'http://xmlns.oracle.com/apps/orderCapture/priceBook/priceBookService/types/' AS "xmlns:typ",
                'http://xmlns.oracle.com/apps/orderCapture/priceBook/priceBookService/' AS "xmlns:pric"
            ),
            XMLELEMENT("soapenv:Body",
                XMLELEMENT("typ:createPriceBook",
                    XMLELEMENT("typ:priceBookHeader",
                        XMLFOREST('MXN' AS "pric:CurrencyCode"),
                        XMLFOREST('#DSD_CPQ.V_NAME_FPRICEBOOK' AS "pric:Name"),
                        XMLFOREST(TCR.LISTADEPRECIO AS "pric:Description"),
                        XMLFOREST('ACTIVE' AS "pric:StatusCode"), (
                            SELECT XMLAGG(
                                XMLELEMENT("pric:PriceBookItem",
                                    XMLFOREST(INVENTORY_ITEM_ID AS "pric:InvItemId"),
                                    XMLELEMENT("pric:ListPrice", XMLATTRIBUTES('MXN' AS "currencyCode"), PRECIODETALLISTA),
                                    XMLELEMENT("pric:PriceUOMCode", DEFAULT_UOM_CODE)
                                )
                            ) FROM TMP_CPB_REQUEST
                        )
                    )
                )
            )
        ).GETCLOBVAL() XML_REQUEST
        FROM TMP_CPB_REQUEST TCR WHERE TCR.PRODUCT_NUMBER = (
            SELECT MIN(PRODUCT_NUMBER) FROM TMP_CPB_REQUEST
        )
    );

    T_CLOB_LEN := DBMS_LOB.GETLENGTH(P_DATA);
    T_OUT_FILE := UTL_FILE.FOPEN(P_DIR,P_FILE, 'W', 32767);

    DBMS_OUTPUT.PUT_LINE(' LENGTH : ' || T_CLOB_LEN);
    WHILE T_POS < T_CLOB_LEN LOOP
        DBMS_OUTPUT.PUT_LINE(' T_POS BEFORE : ' || T_POS);
        DBMS_LOB.READ(P_DATA, T_AMOUNT, T_POS, T_BUFFER);
        UTL_FILE.PUT(T_OUT_FILE, T_BUFFER);
        UTL_FILE.FFLUSH(T_OUT_FILE);
        DBMS_OUTPUT.PUT_LINE(' T_POS AFTER : ' || T_POS);
        T_POS := T_POS + T_AMOUNT;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('COMPLETED WRITING');
    UTL_FILE.FCLOSE(T_OUT_FILE);

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