How to format the output file created with sqlplus?Why one of the output blocks appears two times?

Question

I used this steps Oracle 10g - Write queries results to file in order to create a xml file. So this is the sql script which I loaded with

c:>sqlplus -s username/[email protected] < tmp.sql > output.txt

  set pagesize 0;
    set serveroutput on
    set termout off       
    set verify off     
    set heading off    
    set long 999
    set lines 999
    SET FEEDBACK OFF
    SET HEAD OFF

    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 dual;
    SELECT '<ExchangeRates>'||chr(10)||'<ExchangeRate>'||chr(10)||
           '<harpeml:technicalIndicator>'||'02'||'</harpeml:technicalIndicator>'||chr(10)||
           '<harpeml:currencyExchangeRateType>'||''||'D'||'</harpeml:currencyExchangeRateType>'||chr(10)
            FROM dual;
    SELECT  '<harpeml:baseCurrencyCode>'||abrev||'</harpeml:baseCurrencyCode>'  FROM(SELECT abrev FROM v_domaine where type = 'DEVISE' ORDER BY  dbms_random.normal)WHERE rownum = 1;
    SELECT 
            '<harpeml:counterCurrencyCode>'||abrev||'</harpeml:counterCurrencyCode>'FROM(SELECT  abrev FROM v_domaine where type = 'DEVISE' ORDER BY  dbms_random.normal)WHERE rownum = 1;
    SELECT 
            '<harpeml:startValidityDate>'||''||to_char(sysdate -  4,'DD/MM/YYYY')||''||'<harpeml:startValidityDate>'||chr(10)||
             '<harpeml:countryCode>'||abrev||'</harpeml:countryCode>'FROM(SELECT  abrev FROM v_domaine where type = 'pays' ORDER BY  dbms_random.normal)WHERE rownum = 1;   
    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 dual;
    /

This is the result:

<?xml version="1.0" encoding="UTF-8"?>
<!--Sample XML file generated by XMLSpy v2010 rel. 2 (http://www.altova.com)-->
<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">
<Header>
<harpeml:technicalIndicator>HDR</harpeml:technicalIndicator>
<harpeml:orderNumber>0000000161</harpeml:orderNumber>
<harpeml:dataSelectionDate>02/07/2012</harpeml:dataSelectionDate>
<harpeml:extractionTimeStamp>06-JUL-12 09.29.01.812631 AM +03:00</harpeml:extractionTimeStamp>2</Header>

<ExchangeRates>
<ExchangeRate>
<harpeml:technicalIndicator>02</harpeml:technicalIndicator>
<harpeml:currencyExchangeRateType>D</harpeml:currencyExchangeRateType>

<harpeml:baseCurrencyCode>BOB</harpeml:baseCurrencyCode>
<harpeml:counterCurrencyCode>SGD</harpeml:counterCurrencyCode>
<harpeml:startValidityDate>02/07/2012<harpeml:startValidityDate>
<harpeml:countryCode>BDI</harpeml:countryCode>

<harpeml:exchangeRate>162</harpeml:exchangeRate>
<harpeml:appreciationOrDepreciationReport>1</harpeml:appreciationOrDepreciationReport>
<harpeml:dataSourceSystem>freetext</harpeml:dataSourceSystem>
</ExchangeRate>
</ExchangeRates>
</HarpeML_CBS_IMX_ExchangeRate_Flow>

**<harpeml:exchangeRate>163</harpeml:exchangeRate>
<harpeml:appreciationOrDepreciationReport>1</harpeml:appreciationOrDepreciationReport>
<harpeml:dataSourceSystem>freetext</harpeml:dataSourceSystem>
</ExchangeRate>
</ExchangeRates>
</HarpeML_CBS_IMX_ExchangeRate_Flow>**

Does anyone have idea why the last block appears two times? And could you give any suggestion how to remove the empty lines from the output file?

APC · Accepted Answer · 2012-07-06 07:45:44Z

3

In SQL*Plus a semi-colon executes a statement. So does a slash.

You have terminated each of your selects with a ; which executes them. Then you have terminated your script with a / which causes the last statement to be executed again.

As for the blank lines, that is just an artifact of running several distinct statements. I wouldn't worry about them. certainly XML doesn't care.

Hawever this is a labourious way of generating XML files: Oracle has a whole slew of XML features: you should learn to use them.

answered Jul 6, 2012 at 7:45

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Schmitt · Accepted Answer · 2012-07-06 07:40:54Z

2

The last block appears twice because you have a / at the end of your script - this executes the last query again.

Just replace the / with exit, and this problem should be solved.

answered Jul 6, 2012 at 7:40

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

2 Comments

APC Over a year ago

Exit isn't necessarily desirable. It's useful when invoking sqlplus in a shell script; it's a pain in the neck when we're running the scripts ourselves.

Frank Schmitt Over a year ago

@APC That's true. But since the OP just wants to do an export from a shell script, it doesn't hurt in this case.

Collectives™ on Stack Overflow

How to format the output file created with sqlplus?Why one of the output blocks appears two times?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related