DBMS_XMLGEN.GETXML + DBMS_LOB.SUBSTR + UTL_FILE导表数据XML文件

贴出我写的Function： CREATE OR REPLACE FUNCTION F_EXPORT2XML RETURN INTEGER IS  RESULT INTEGER := 0;  XMLHDL DBMS_XMLGEN.CTXTYPE;  --LINE   VARCHAR2(2000);  LINE   LONG;  XMLDOC CLOB;  --XMLTEXT VARCHAR2(32767);  XMLTEXT LONG;  --STR            VARCHAR2(32767);  XML_FILE       UTL_FILE.FILE_TYPE;  TOTAL_LENGHS   INTEGER;  SUBSECTION     INTEGER := 0;  SUBSECTION_LEN INTEGER := 0;  IDX2           INTEGER;  CHARWIDTH      INTEGER;  SQLERRTEXT     VARCHAR2(32767);BEGIN  CHARWIDTH := LENGTHB('汉');  -- create ctxhandle  --XMLHDL := DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM SRV_RES');  XMLHDL := DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM INFO_TOKEN where rownum < 20000 order by tk_serial');  -- custerm config  --DBMS_XMLGEN.SETMAXROWS(XMLHDL, 10);  /*  DBMS_XMLGEN.SETROWSETTAG(XMLHDL, 'Packet');  DBMS_XMLGEN.SETROWTAG(XMLHDL, 'Record');*/  DBMS_XMLGEN.SETNULLHANDLING(XMLHDL, DBMS_XMLGEN.EMPTY_TAG);
  -- generate xml format data into clob  XMLDOC       := DBMS_XMLGEN.GETXML(XMLHDL);  TOTAL_LENGHS := LENGTH(XMLDOC);  IF TOTAL_LENGHS > 0 THEN    -- display the xml content    SUBSECTION_LEN := 32767 / CHARWIDTH;    LOOP      IF XMLTEXT IS NOT NULL THEN        XMLTEXT := XMLTEXT ||                   DBMS_LOB.SUBSTR(XMLDOC, 32767,                                   SUBSECTION_LEN * SUBSECTION + 1);            ELSE        XMLTEXT := DBMS_LOB.SUBSTR(XMLDOC, 32767,                                   SUBSECTION_LEN * SUBSECTION + 1);      END IF;      EXIT WHEN XMLTEXT IS NULL;      LOOP        EXIT WHEN XMLTEXT IS NULL;        IF UTL_FILE.IS_OPEN(XML_FILE) = FALSE THEN          XML_FILE := UTL_FILE.FOPEN('EXPDIR', 'xml_info_token.xml', 'w',                                     32767);        END IF;        IDX2 := INSTR(XMLTEXT, CHR(10));        IF IDX2 = 0 THEN          EXIT;        ELSE          LINE := SUBSTR(XMLTEXT, 1, IDX2 - 1);        END IF;        UTL_FILE.PUT_LINE(XML_FILE, LINE);        XMLTEXT := SUBSTR(XMLTEXT, IDX2 + 1);      END LOOP;      SUBSECTION := SUBSECTION + 1;    END LOOP;    IF XMLDOC IS NOT NULL AND DBMS_LOB.ISTEMPORARY(XMLDOC) = 1 THEN      DBMS_LOB.FREETEMPORARY(XMLDOC);    END IF;    IF XMLDOC IS NOT NULL AND DBMS_LOB.ISTEMPORARY(XMLTEXT) = 1 THEN      DBMS_LOB.FREETEMPORARY(XMLTEXT);    END IF;      -- get row count    --DBMS_OUTPUT.PUT_LINE(DBMS_XMLGEN.GETNUMROWSPROCESSED(XMLHDL));    UTL_FILE.FCLOSE(XML_FILE);    -- close ctxhandle    DBMS_XMLGEN.CLOSECONTEXT(XMLHDL);  END IF;  RESULT := 1;  RETURN(RESULT);EXCEPTION  WHEN OTHERS THEN    SQLERRTEXT := SQLERRM;    IF XMLDOC IS NOT NULL AND DBMS_LOB.ISTEMPORARY(XMLDOC) = 1 THEN      DBMS_LOB.FREETEMPORARY(XMLDOC);    END IF;    IF XMLDOC IS NOT NULL AND DBMS_LOB.ISTEMPORARY(XMLTEXT) = 1 THEN      DBMS_LOB.FREETEMPORARY(XMLTEXT);    END IF;    IF UTL_FILE.IS_OPEN(XML_FILE) THEN      UTL_FILE.FCLOSE(XML_FILE);    END IF;    RETURN(RESULT);END F_EXPORT2XML;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11136/viewspace-672202/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11136/viewspace-672202/