oracle sql developer is truncating my results

Question

I'm calling a stored function like this:

select XML_INVOICE.GENERATE_XML_DOC('84200006823') from dual;

The query results then show up in a table underneath, which I can right click and select "Export Data" -> XML

<?xml version='1.0'  encoding='UTF8' ?>
<RESULTS>
  <ROW>
    <COLUMN NAME="XML_INVOICE.GENERATE_XML_DOC('84200006823')" <![CDATA[<xml>yada yada</xml><morexml>...]]></COLUMN>
  </ROW>
</RESULTS>

The problem is the "..." - SQL Developer (2.1.0.63 on Linux) is not showing all the data - its truncating the result and appending the ellipsis. This is of no use to me. How do I get it to export ALL of my data?

Answer 1

1) Insert the result of the SP into a table

select XML_INVOICE.GENERATE_XML_DOC('84200006823') into schema.table from dual;

(will be only one row)

Use exp to export table

EXP username/password@instance TABLES=(tablename)

Answer 2

Another solution would be to write directly a XML file see example below, and adjust to fit:

CREATE OR REPLACE PROCEDURE output_xml_file 
(inFileName         Varchar2 )   --user defined prefix for file name        
 AS

            fp UTL_FILE.FILE_TYPE;   
                v_filename  VARCHAR2(150);
            v_XML_Result VARCHAR2(4000);    

BEGIN

          v_filename:=infilename||'.xml';

          fp := UTL_FILE.FOPEN('c:\pathtofolder', v_filename, 'W', 4000);

          select XML_INVOICE.GENERATE_XML_DOC(inFileName) into v_XML_Result from dual; 

          UTL_FILE.PUT_LINE(fp, v_XML_Result);
          UTL_FILE.FCLOSE(fp);

          EXCEPTION

            WHEN OTHERS THEN

              UTL_FILE.FCLOSE(fp);
              raise;
    END output_xml_file;
    /
    SHOW ERRORS;
    GRANT EXECUTE ON output_xml_file TO PUBLIC;