declare 

v_message varchar2(32767);
v_message1 varchar2(32767);

begin

SELECT '<?xml version="1.0" encoding="UTF-8"?>' || chr(10) ||
       '<!--Sample XML file generated by XMLSpy v2010 rel. 2 (http://www.altova.com)-->' || chr(10) ||
       '<HarpeML_CBS_IMX_ExchangeRate_Flow  xsi:noNamespaceSchemaLocation="HarpeML_CBS_IMX_ExchangeRate(REF-IMX-1)_v0.0.00.xsd"  xmlns:harpeml="http://www.harpeml.com"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'||chr(10)||
       '<Header>'||chr(10)||
       '<harpeml:technicalIndicator>'||'HDR'||'</harpeml:technicalIndicator>'||chr(10)||
       '<harpeml:orderNumber>'||''||lpad ( imk.nextval, 10, '0' )||'</harpeml:orderNumber>'||chr(10)||
       '<harpeml:dataSelectionDate>'||''||to_char(sysdate -  4,'DD/MM/YYYY')||''||'</harpeml:dataSelectionDate>'||chr(10)|| 
        '<harpeml:extractionTimeStamp>'||''||CURRENT_TIMESTAMP||''||'</harpeml:extractionTimeStamp>'||chr(50)||'</Header>'
        FROM into v_message1 dual;
 
 v_message := v_message || v_message1;

SELECT '<ExchangeRates>'||chr(10)||'<ExchangeRate>'||chr(10)||
       '<harpeml:technicalIndicator>'||'02'||'</harpeml:technicalIndicator>'||chr(10)||
       '<harpeml:currencyExchangeRateType>'||''||'D'||'</harpeml:currencyExchangeRateType>'||chr(10)
        FROM into v_message1 dual;

v_message := v_message || v_message1;

SELECT  '<harpeml:baseCurrencyCode>'||abrev||'</harpeml:baseCurrencyCode>' into v_message1 FROM(SELECT abrev FROM v_domaine where type = 'DEVISE' ORDER BY  dbms_random.normal)WHERE rownum = 1 ;

v_message := v_message || v_message1;

SELECT 
        '<harpeml:counterCurrencyCode>'||abrev||'</harpeml:counterCurrencyCode>' into v_message1 FROM(SELECT  abrev FROM v_domaine where type = 'DEVISE' ORDER BY  dbms_random.normal)WHERE rownum = 1;

v_message := v_message || v_message1;

SELECT 
        '<harpeml:startValidityDate>'||''||to_char(sysdate -  4,'DD/MM/YYYY')||''||'<harpeml:startValidityDate>'||chr(10)||
         '<harpeml:countryCode>'||abrev||'</harpeml:countryCode>' into v_message1 FROM(SELECT  abrev FROM v_domaine where type = 'pays' ORDER BY  dbms_random.normal)WHERE rownum = 1;   

v_message := v_message || v_message1;

SELECT
        '<harpeml:exchangeRate>'||rpad ( imk.nextval, 3, '51' )||'</harpeml:exchangeRate>'||chr(10)||
        '<harpeml:appreciationOrDepreciationReport>'||'1'||'</harpeml:appreciationOrDepreciationReport>'||chr(10)||
        '<harpeml:dataSourceSystem>'||'freetext'||'</harpeml:dataSourceSystem>'||chr(10)||
        '</ExchangeRate>'||chr(10)||
        '</ExchangeRates>'||chr(10)||
        '</HarpeML_CBS_IMX_ExchangeRate_Flow>'
        from  into v_message1 dual;

v_message := v_message || v_message1;

dbms_output.put_line(v_message);

end;
/