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;
-- 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博客 ” ，链接：https://blog.itpub.net/11136/viewspace-672202/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
没有了~
请登录后发表评论
登录
全部评论
